Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Degenerate dimension or regular dimension Expand / Collapse
Author
Message
Posted Thursday, July 17, 2014 7:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 8:58 AM
Points: 6, Visits: 91
I am designing a retail sales data warehouse. The FactRetailSales table is at the line item level of granularity.

Placement of the InvoiceNumber as a degenerate dimension would usually be ideal...
Order numbers, invoice numbers, and bill-of-lading numbers almost always appear as degenerate dimensions in a dimensional model." -Kimball

However, Kimball also says:
Although surrogate keys aren’t typically assigned to degenerate dimensions, each situation needs to be evaluated to determine if one is required. A surrogate key is necessary if the transaction control numbers are not unique across locations or get reused. For example, the retailer’s POS system may not assign unique transaction numbers across stores. ... Obviously, control number dimensions modeled in this way with corresponding dimension tables are no longer degenerate.


In my case, the unique key for an invoice is a combination of Invoice Number and Store Number.

So...
Can I use the combination of my InvoiceNumber degenerate dimension and the either StoreNumber or even Store surrogate key as my Natural Key in the FactRetailSales?
or
Do I break the Invoice out to its own dimension (DimInvoice)? Then do I bring StoreNumber (or StoreKey) along with it? Wouldn't that make it a snowflake?

Post #1593581
Posted Thursday, July 17, 2014 7:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
Personally I would go for the first option.
Degenerate dimension of InvoiceNumber and Store SK.

There is no use in putting it into an extra dimension. There are no descriptive attributes and the number of rows is equal to the fact table.
I can also imagine there would be no actual analysis against the InvoiceNumber, right?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1593582
Posted Thursday, July 17, 2014 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 8:58 AM
Points: 6, Visits: 91
Thanks for the quick reply.

There are a few elements at the invoice number level that are repeating in the FactRetailSales table (InvoiceNumber, WorkOrderNumber, InvoiceDate and WorkOrderDate. I'm fine with flattening them all out to maintain the Star formation.

My main concern is that there is going to be quite a bit of analysis at the invoice level.

I am designing this DW for a tire and car service retailer. The executives will want to know when a customer came in for a tire, did they also get services (and all possible combinations). We will want to do a lot of customer profiling across the invoice.

I'm using SQL Server 2012 BI tools, I'm only worried I'm going to make my life more difficult when I start working on the cube design.
Post #1593586
Posted Thursday, July 17, 2014 11:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 8:58 AM
Points: 6, Visits: 91
If there are invoice level reports being built, would it be acceptable to create a single composite key of InvoiceNumber + StoreNumber?

And if the requests for Invoice level reporting becomes significant, an aggregated fact table on InvoiceNumber?


Thanks again,
Gabriel
Post #1593727
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse