Parent-Child as outrigger (snowflake) vs fact FK ( star)

Last Post 08 Jul 2011 07:46 AM by PaulMcKibben. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
KevP
New Member
New Member

--
04 Apr 2011 12:16 AM
I have a design choice and was wondering if anyone could give benefit of their experience. I'm just putting together a cube for sales analysis at a transactional grain level using Kimball methodology.
Scenario I'm mulling over is DimProduct and DimProductGroup tables. Each product in DimProduct table belongs to a ProductGroup. Now the ProductGroup is a ragged heirarchy which I'm representing with a Parent-Child relationship in DimProductGroup through attributes in DimProductGroup(ParentProductGroupId) referencing  DimProductGroup(ProductGroupId).

Could anyone please give me the pro's and con's for each of the two options whereby:

Option 1 - Outrigger table where FactSale(ProductId) references DimProduct(ProductId) and DimProduct(ProductGroupGroupId) references DimProductGroup(ProductGroupId).

Option 2 - FactSales(ProductId) references DimProduct(ProductId) and FactSales(ProductGroupId) references DimProductGroup(ProductGroupId)

Any help greatly appreciated. Thanks.
Peter Schmitz
New Member
New Member

--
04 Apr 2011 01:51 AM
Very simplified, the advantage of snowflaking is that you save on storage. The disadvantage is additional complexity, and perhaps some performance dip due to the additional join.

I kind of miss option 3, though, where you make a product dimension table which incorporates a hierarchy for the productgroup.

I.e.

ProductID, ProductGroupID, ProductGroupDescription, ProductDescription

Just some quick thoughts.

Peter


KevP
New Member
New Member

--
04 Apr 2011 05:39 AM
Hi Peter,

Thanks for the reply.

Kimball methodology offers use of a snowflake outrigger table (no more than 2 references away from the fact table), but I was wondering what the impact would be of having an outrigger which is a parent-child self-referencing table.

I don't think we can have option 3 you propose because we need to be able to drill down a ragged heirarchy from an unknown number of ProductGroup levels.

I do lean towards using Option2 and having ProductGroupId referenced by the fact table. But my lack of practical experience is prompting me to stop and think and about this one.
PaulMcKibben
New Member
New Member

--
08 Jul 2011 07:46 AM
KevP,

I realize that this is late, but I believe that Option 1 or 3 are the best options.

I say that because my experience is that your clients will probably want to drill down from the Product Group to the Product. Both option 1 and 3 would allow that.


Acceptable Use Policy
---