SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Degenerate dimension or regular dimension


Degenerate dimension or regular dimension

Author
Message
gmontanaro
gmontanaro
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 200
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?
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62334 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
gmontanaro
gmontanaro
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 200
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.
gmontanaro
gmontanaro
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 200
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search