Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Facts Tables linking to different granularity of a Conformed Dimension

5 posters

Go down

Facts Tables linking to different granularity of a Conformed Dimension Empty Facts Tables linking to different granularity of a Conformed Dimension

Post  breadbox008 Mon Jun 06, 2011 6:18 pm

Hi

Sorry this may seem like a simple question.

My scenario is I have two facts tables that link to different levels in a Conformed Product Dimension.

Example:
Fact 1 --> Product
Fact 2 --> Product Category

I could create a separate Product Category Dimension and link it to the Fact 2 but the business has requested that the Product --> Product Category hierarchy must be in place. This is why we original collapsed the Categories into the Product Dimension. Is it best practice to have two Dimensions but still keep the Product --> Product Category Hierarchy in the Product Dimension?

The cube is being built in SSAS.

breadbox008

Posts : 2
Join date : 2011-06-06

Back to top Go down

Facts Tables linking to different granularity of a Conformed Dimension Empty Re: Facts Tables linking to different granularity of a Conformed Dimension

Post  BoxesAndLines Tue Jun 07, 2011 8:53 am

It is best practice to separate the dimension if you do not have a product id. You can't really leverage the hierarchy if you are missing the leaf level.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Facts Tables linking to different granularity of a Conformed Dimension Empty Re: Facts Tables linking to different granularity of a Conformed Dimension

Post  Bob Probst Wed Jun 08, 2011 3:52 pm

What he said.

Is one of the fact tables an aggregate of the other?

Bob Probst

Posts : 18
Join date : 2010-05-26

http://datajuggler.blogspot.com/

Back to top Go down

Facts Tables linking to different granularity of a Conformed Dimension Empty Re: Facts Tables linking to different granularity of a Conformed Dimension

Post  hang Thu Jun 09, 2011 5:43 am

if the base fact is in the cube and hierarchy is configured within product dimension, why do you need a another dimension? I thought the aggregates will be calculated automatically along all levels of hierarchy, and you just drop the hierarchy and navigate through it to show the measurements.

hang

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

Back to top Go down

Facts Tables linking to different granularity of a Conformed Dimension Empty Re: Facts Tables linking to different granularity of a Conformed Dimension

Post  ngalemmo Thu Jun 09, 2011 10:04 am

This site needs some sort of icon or color scheme to separate posts relational posts and MDDB posts.

Hang, if this was a cube, you would be correct. An while the data eventually winds up there, the issue is representing the structure in a relational DB, prior to building cubes.

Problem on the relational side is, if this is a multilevel hierarchy and you wish to implement a bridge table, the levels need to be in the same dimension table, otherwise querying the structures can be real difficult.

There are some options here.

1. Flatten the hierarchy. Forget about a bridge and just store all the levels in the product dimension. Works fine if the hierarchy is not deep and the levels are well defined.

2. Put higher level rows in the product dimension and use a bridge. Basically what you are doing now.

3. Split the hierarchy. Have two dimensions, product and product category. Product category would contain rows for all levels above product. Have both FKs in facts that are at product level. Have a bridge between the fact and product category for hierarchy levels above product. Downside is it would not historically reflect changes to product/product category relationships. For that you would need another bridge table to relate product to category (with effective dates), then to the category hierarchy.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Facts Tables linking to different granularity of a Conformed Dimension Empty Re: Facts Tables linking to different granularity of a Conformed Dimension

Post  hang Sat Jun 11, 2011 8:50 pm

Agree, cube is one thing, clear underlying model is another.

In case of two dimension tables, I guess Category dimension should be a shrunken dimension of Product dimension, therefore what is in Category should be in Product dimension as well except surrogate key.

hang

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

Back to top Go down

Facts Tables linking to different granularity of a Conformed Dimension Empty Re: Facts Tables linking to different granularity of a Conformed Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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