Warehouse - snowflaking

Question

Sales_fact:
Product_ID (FK)
Price

Dim_Product:
Product_ID
Product_Key
Description

Problem I am facing with is what to do in case when I have products which can be in multiple categories. Should I just repeat record in Dim_Product for each category?

Example:

Fact table:
Product_ID (FK): 1
Price: 10.5

Dim_Product:
Product_ID:1
Product_Key: ABC
Description: Laptop
Category_ID: 1

Product_ID:1
Product_Key: ABC
Description: Laptop
Category_ID: 2

Dim_Category:
Category_ID (FK): 1
Description: HP

Category_ID (FK): 2
Description: Toshiba

Answer 1

I would think that you would want to either move category information into dim_product, or allow for a link from the fact table to the category table, or have a different product_id key per category for those in multiple categories. As you have it displayed, there is no way to differentiate between the HP Laptop and the Toshiba Laptop. In fact a query would return both when linked as your tables are designed. I would recommend creating a different product_id for each unique Product_key, Description, Category_ID.

Answer 2

For this situation what you would need is the opposite of a snow flake which is a bridge table between product and category. This bridge table will also act as a factless fact to show which products belong to which category. This is demonstrated in Chapter 15 of Ralph Kimballs "The Data Warehouse Toolkit"