• You're dealing with type-specific stars. (see chapter 13 of Star Schema - The complete reference).

    There are 3 options:

    * create one dimension to support both. (option B) This is a good choice of most attributes are the same. This is not a good choice of products differ too much from services, giving you way too much columns of which half of them is NULL each time.

    * create a core star and custom stars. The core dimension will hold all attributes that are in common with products and services. (most likely Name, SKU, surrogate key). The custom dimension (DimProduct and DimService) will store the type specific attributes. Important is that an item in the DimProduct dimension will have the same surrogate key as in the core dimension. That way you can link them together. The fact table links only to the core dimension. You also create a core fact table (with facts relating to products and services, allowing you to do cross-analysis) and custom fact tables, with specific facts relating to either product or service. The advantage of this model is that you can do a type specific analysis pretty easily (for example using only the service dimenions and custom fact), or you can do cross-analysis on both types using the core tables. Cross-analysis on type-specific attributes will of course be difficult. This option also can get quite messy if there are slowly changing dimensions type 2.

    * a generic approach, where you store the dimension info as value/type pairs into the dimension table. Very flexible, you can store everything you want but hard to query.

    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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP