Facts Tables linking to different granularity of a Conformed Dimension

View previous topic View next topic Go down

Facts Tables linking to different granularity of a Conformed Dimension

Post  breadbox008 on Mon Jun 06, 2011 3: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-07

View user profile

Back to top Go down

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

Post  BoxesAndLines on Tue Jun 07, 2011 5: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

Posts: 1078
Join date: 2009-02-04
Location: USA

View user profile

Back to top Go down

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

Post  Bob Probst on Wed Jun 08, 2011 12: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

View user profile http://datajuggler.blogspot.com/

Back to top Go down

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

Post  hang on Thu Jun 09, 2011 2: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: 519
Join date: 2010-05-08
Location: Brisbane, Australia

View user profile

Back to top Go down

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

Post  ngalemmo on Thu Jun 09, 2011 7: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

Posts: 2544
Join date: 2009-05-16
Location: Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  hang on Sat Jun 11, 2011 5: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: 519
Join date: 2010-05-08
Location: Brisbane, Australia

View user profile

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