January 20, 2014 at 7:57 am
Hi, relatively new to data-modelling data warehouses so bare with me.
I have a Product dimension table that connects to my Sales Fact table.
Sales want to report on the categories that the product fits into. There are multiple categories for each product.
Am I supposed to duplicate entire rows in the Product Dimension for each category the product fits into or am I supposed to snowflake my current star schema with a ProductCategory dimension and link table between the two?
I'm afraid that if I do the former then my Dimension table will become huge and if I do the latter then the model is not simple enough for me to hand over to end users.
Any thoughts will be gratefully appreciated.
Thanks
Tim
January 20, 2014 at 11:38 pm
"There are multiple categories for each product."
That's unusual. Could you give an example? Normally, you have a category hierarchy (Category, Subcategory...)
January 21, 2014 at 1:02 am
There are two options:
* just add extra columns to store the different catagories. Easiest solution by far, but the least flexible.
* snowflake to a category dimension and add a bridge table in between. More difficult but very flexible.
To meet the less tech savvy users in between, you could add a column "Primary category" to the product dimension. You could write simply queries where each product has only 1 category. People who know how to write SQL can use the bridge table to report on all categories.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply