Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Difficulty flipping from relational modeling to dimensional modeling Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 9:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:50 AM
Points: 78, Visits: 448
Im building my first star schema

Order fact table with Product, retailer. date and customer dimensions so that's fine.

im finding it hard getting my mind set out off my normal N-normal form approach. for example my products are mostly dresses which are supplied by designers.

1) should this designer information be stored in a designer table that joins to the product table which then joins to the fact table. This would start to build a snowflake schema which from what ive read should be avoided if possible.

2) should the designer info then be its own dimension table with the key stored in the fact table, if i adopt this approach i could have many dimension tables as our dresses alone come in different colors and materials,

3) or should the designer info be merged into the product table with the likes of the color and material etc.

so kind of getting lost in drawing a line in the sand as to what is a table of its own and whats not?
Post #1568028
Posted Wednesday, May 14, 2014 2:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:20 AM
Points: 246, Visits: 663
ps_vbdev (5/6/2014)
Im building my first star schema

Order fact table with Product, retailer. date and customer dimensions so that's fine.

im finding it hard getting my mind set out off my normal N-normal form approach. for example my products are mostly dresses which are supplied by designers.

1) should this designer information be stored in a designer table that joins to the product table which then joins to the fact table. This would start to build a snowflake schema which from what ive read should be avoided if possible.

2) should the designer info then be its own dimension table with the key stored in the fact table, if i adopt this approach i could have many dimension tables as our dresses alone come in different colors and materials,

3) or should the designer info be merged into the product table with the likes of the color and material etc.

so kind of getting lost in drawing a line in the sand as to what is a table of its own and whats not?


Personally, I would create a seperate dimension for the designer, and have a key for it in the orders fact table. That supports very simple reporting of something like "What is the $ value of all orders that were from designer ps_vbdev?"
Post #1571037
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse