Would you normalize a product-category hierarchy in an analytical model?
Instruction: Each product belongs to one category at a time, but products can be reclassified. Reports must group old sales by the category that applied when each sale occurred and support both product and category analysis. Compare a flattened product dimension with a normalized category hierarchy. Explain version keys, late facts, join cardinality and how you would evaluate the design.
Updated
Official answer
First preserve what the report means: a sale must retain its category as of the sale, even after the product is reclassified. Joining old sales to only the product's current category would silently rewrite history.
A practical star design uses a versioned product dimension. When category changes, create a new product version with its own surrogate key and validity interval. Each fact references the version applicable at the sale time. Store the category attributes needed for reporting on that version. Define how to resolve late sales and corrections to effective dates.
A normalized hierarchy can also work, but its historical references must be explicit. A category table containing only today's assignments is insufficient. Ensure fact-to-product-version and product-version-to-category-version joins select exactly the intended historical rows, without duplication from overlapping validity periods.
I would start with the simpler model the reporting tool supports, then measure representative product and category queries and maintenance costs. Normalizing the hierarchy may centralize shared attributes, but it is not a substitute for history rules or a guaranteed speed improvement.
Sources
Microsoft: slowly changing dimensions; Kimball: type 2 dimensions.