OLAP Data model to Support Dimension with Multiple Attributes

Question

I need inputs to decide best approach for modeling dimensions with multiple attributes in OLAP system.

Consider the example of super market.

If a shirt is available in 10 colors and 3 sizes, I would like to know total sales by color or total sales by size. I want to create dimension table to store product information and use its surrogate key in Sales fact table.

Below are my thoughts:

1) As prize of product remains same for different colors and sizes, I don’t want to create different product id for each combination.

2) I can create Junk Dimension to store 30 (10 colors * 3 sizes) combinations and refer its surrogate key along with surrogate key of product dimension in Sales fact table.

There is problem with approach two: supper market also sells pen which are diff in colors, footwears which are different in colors and sizes. So, I can not create separate Junk dimension for each product. Also, I can’t create single Junk dimension to store all attributes like color and size as the table will be huge in size.

What is the best approach to implement data model for such application using OLAP?

Answer 1

I would create two fact keys: prod_variation_1 and prod_variation_2, both pointing to the same d_prod_variation dim. The variations can be used for color and size for some product types, or flavor and packaging for others.

Here is what d_prod_variation will look like:

In the BI tool user will need to select the Department to get the choice of Variation filters.

The Fact record specifying a Medium size Blue shirt will look like this: