Facts Tables linking to different granularity of a Conformed Dimension

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.

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.

What he said.

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

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.

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.

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.