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?orDo 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?