Dimension for property tree

View previous topic View next topic Go down

Dimension for property tree

Post  neo.helios on Thu May 05, 2011 11:21 am

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

neo.helios

Posts : 11
Join date : 2010-11-02

View user profile

Back to top Go down

Re: Dimension for property tree

Post  hang on Sat May 07, 2011 8:45 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Dimension for property tree

Post  neo.helios on Tue May 10, 2011 11:44 am

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?

neo.helios

Posts : 11
Join date : 2010-11-02

View user profile

Back to top Go down

Re: Dimension for property tree

Post  hang on Tue May 10, 2011 6:32 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Dimension for property tree

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum