I have a product dimension that for most of its unique product keys, represents an individual product. But it also has some unique product keys that actually represent a group of products.
this is because the model has two fact tables, one for sales at product and day granularity and another for budgets at product category and day level (It doesn't go down to individual product level just the category). The idea being that both facts can join on product key even though they are actually different granularities.
I suspect this is a fundamental flaw in the model design and would be inclined to create a new dimension named budget product category that has the category key and then add this to both fact tables (based on associated product category ID from the budget fact and the product category that is associated with the product in the sales table)
I just wanted to ask, has anyone else encountered this and if so what approach did you take to resolve? Also, can a single dimension table have individual records at different hierarchical levels (product & product category) where they all appear at face value to be products?
