Shrunken dimension in repository

Question

Hello all,

What would be the best way to handle shrunken dimensions in repository(OBIEE 11g)

we have 1. Dim_product

2. Dim_product_code(this is shrunken dimension of Dim_product)

Dim_product is joined to fact_sales on product_key, shrunken dimension is joined to fact_stockout on product_code(for clear reference I have attached the BMM model diagram below)

now requirement is to pull columns from fact_sales,Fact_stockout,Product, above model will not work since these are not at the same grain. what would be the rite way to handle this?

Robert Angel · Answer

Hi,

Fact1 -> Dimension1

Fact2 -> Dimension2

Then Fact1 -> Dimension2

Will work provided the keys that join the dimension to the facts both have a FK in the fact that joins to the unique key in the dimension, with the LTS levels set appropriately to the appropriate dimension hierarchy level.

In my simplified example you could then have analyses with Fact1, Fact2 and Dimension2 in them.

If you set the level to be invariant for the non-conformed level then you could include both Dimensions in an analysis, just not wholly meaningfully.

You other option would be to join via a bridge table.

If this is not helpful then show your exact set up please and the error message you are getting.

User_SX88B · Answer

Robert,

tried multiple way, In first design we had Physical model almost same as above Logical model, there was no common join to bridge the gap.

so changed the design,in physical model we have one dimension Dim_product joining to both the facts on different keys, In BMM we created 2 logical tables based on same physical table but the shrunken dimension(dim_product_code) has where conditions in LTS,

but its not working, we are not getting the desired results.

Robert Angel · Answer

You don't detail any physical joins that bridge the gap, are there any?

i.e. you need a way to conform at least one of the dimensions to join BOTH fact tables, joined via a valid key.