I am creating dimension of product properties for sales facts.

Property of product depend of product type. For example: - Type = smartphone. Properties = model, OS, size - Type = book. Properties = author, title

How dimension should be for this case?

Should I create dimension which contain ALL properties? In this case dimension content will be sparse, there will be many null values.

| DimKey | Type | Model | OS | Size | AUTHOR | TITLE |

OR, should I create dimension for each? In this case sales fact will have many FKs.

| FactKey | Quantity | Total | Book_FK | Smartphone_FK | .... |

Is there any other way to do this?

2 Answers 2


I would not create a separate foreign key for each product type. This is known as a centipede fact table.


I would consider creating separate fact tables for products which can logically be grouped. ie FactTechnologySales, FactBookSales. And then you would have DimTechnologyProducts and DimBooks dimensions. This would minimise NULL fields in your dimension table. (note that I would actually populate these NULL fields with N/A rather than leave them NULL).

If you want to only have one fact table, then create a product dimension with attributes like Product Name, Product Description, Product Category, Product Type. Where Product Description is Model + OS + Size for the smartphones and Author + Title for books. Use the Product Category and Type fields to create a hierarchy for users to drill down. For any other attributes that don't fit into the description, then put them in a Junk dimension.

  • hi Ben, unfortunately we have huge number of products, which makes impossible to create fact table for each Nov 5, 2014 at 6:06
  • I wouldn't create a separate fact table for each product, you would group similar products/product types together. Nov 5, 2014 at 6:19

If it's that sparse, I wouldn't try to model it via a star schema in the first place.

The answer from Ben Oastler is right on point regarding the difficulties of modelling such a dimension. You either have a lot of fact tables, a lot of dimension columns or you concatenate most of those properties into simpler attributes.

However, it's precisely to tackle this kind of problems that NoSQL solutions were created. If your data doesn't naturally fit on a table structure, perhaps you shouldn't try to.

Have a look at, for example, MongoDB. Each document is a product and only attributes that actually exist are set and they can be hierarchized.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.