Dimension for property tree

[neo.helios]

I have a mail entity in source system which has multiple groups of properties in one to many relationships. In source , which is a relational model, we create the join tables and have a FK constraint from the main table to store the data with one to many relations. What is the best way to design it in the dimensional model

To give an example, I have an entity "MEDICINE" and it has the following groups of properties in the source system

General Properties - like Name, manufacture, location etc
Physical Properties - like Color, Odour, Form, package etc
Chemical Properties - like composition, solubility, expandability
Environmental Conditions - Temperature, moisture etc.

and assume that there could be multiple records of each property. What is the best way to group them into a dimension in warehouse

[hang]

If it's all about the "MEDICINE" dimension that is not a monster dimension (million rows), I would denormalise all your properties and their groups into the single dimension. With multi-valued properties, you could concatenate them into a single comma delimited text if it’s just about display, or if you need more granular dimensionalities, you may have bridge tables to store the 1-m relationship.

[neo.helios]

Thanks Hang. If the number of groups of these properties in huge, then building bridge tables for this multi-valued attributes might results into creating lot of dimension tables in the warehouse. Would that be a good approach?

[hang]

With multi-valued attributes, Kimball’s approach is to snowflake the dimension by bridge or factless fact table. So it does mean breaking down into more tables. However in general, only small number of attributes are multi-valued. In your case you might need to trade off between granularity and complexity. Concatenation may be a way for a compromise.

[Sponsored content]