Lempster (1/14/2014)
Koen Verbeeck (1/14/2014)
Option A doesn't seem directly to be a good idea, as this requires two surrogates keys in the fact table. However, only one of those two can be filled in at the time. This can lead to issues against the grain of the fact table.I take your point Koen and your knowledge in this area is better than mine (:-)), but as long as there are surrogate keys that define 'Not Applicable' or 'Has Happened Yet' etc., then this isn't a problem is it? I agree that it would not be good to have NULL values.
For example, in the Kimball Data Warehouse Toolkit book, they cover the concept of a Promotions dimension in relation to Retail Sales; there is not always going to be a promotion in place and so the Sales fact table would include whatever surrogate key denotes 'no promotion in place', but it doesn't affect the grain.
Regards
Lempster
That is correct, but the example in the Data Warehouse Toolkit is an optional dimension and it doesn't take part in the grain. In your case, the product or service dimension is in fact part of the grain: "The fact table measures the order dollars for a customer on a specific day on an invoice for a product or a service."
It's the or in your grain declaration you should worry about.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP