Alright let me paint a picture so you know what we are working with. I have a star schema that tracks high level details about a quote. The fact table includes quote number, freight, Total sales, and all of my dimension keys.
The dimensions I am focusing on and needing help with are the Customer Dimension and Market Segment Dimension.
My sales manager provided me with a excel file provides me with a Customerid and goals broken out by Market Segment. I do not currently house these goals in any tables.
Ex. in snip it.
Customer ID in the spreadsheet lines up with the business key in the Customer Dimension. Each of the Segments match with the Market Segment Dimension.
What are some typical ways to handle this? I was thinking about making a table in SQL Server with CustomerID, Unpivot the column headers, and tie it to my fact table. Just not exactly sure how I should make these relationships. Should I also join Market Segment table to that new table and capture the SegmentID Then tie it to the fact table that way?
Thank you for your response. I actually took care of that part in SQL, the part that I am having a hard time with is building a relationship between this new table and my current star schema. I am not sure exactly where this would fit in to provide me with accurate numbers. This table will cover the goals for each customer, however, I need to provide the user with visuals that display actual vs the goals.