Problem with Different level of grain, BRIDGE and Combining two Dimensions

Dear All,

I have below structure

Fact Table
                     |-------------|
                     |DIMCODE1    |
|----------|          |-------------|
| Code1  --| _____ | CODE1        |
|----------|          | DESC          |            |-------------------------------|                    |-------------------------------|              
|----------|           -------------              |BRIDGE                                 |                   | Bus Fun Dim                   |
|----------|          |-------------|            |------------------------------|                   |-------------------------------|
|----------|          |DIMCODE2    |            |CODE1|CODE2|Business Def Key|---------------|Business Def Key            | <--  |
|----------|          |-------------|            |C001   |NULL  |Bkey01       |                          | Parent Business Def Key     |---> |
| Code2  --|------- |CODE2         |          |C001   |NULL  |Bkey02       |                         | Business Defination Desc    |
|----------|          |DESC           |             |         |C002  |Bkey02       |                         --------------------------------
|----------|          |-------------|             |         | C002  |Bkey03      |  
|----------|                                      ____________________________
|----------|          
| Balance--|
| Count  --|
|----------|


Here Code 2 and Code 1 are two different level of grains, i am implementing the same in SSAS.

I want to see Parent Business Def Key  wise Balance/ count and to achieve parent key to operate on both Level of grains viz.
Business Def A ---- Bus Def B
                          ----- Business Def D
                                  -- Code 1                  Balance/Count
                                  -- Code 2                  Balance/Count
                          ----- Code 2
                          ----- Code 1
                   ---- Bus Def C
                                  -- Code 1                  Balance/Count
Any inputs?

Regards,
Ajit

Hi - I'm not entirely sure I have understood your data model/question completely so apologies if I haven't...

Two thoughts occur to me:
1. If the combination of Code1 and Code2 define your Business Def Key (which I think is what your Bridge table is showing - each unique combination of Code1 and Code2 values maps to a single Business Def Key) then why not put the Business Def Key directly in your Fact table?

2. If you are saying that Code1 and Code2 are mutually exclusive (I think that's what your comment about different grains means) - does that mean that whenever Code1 has a value then Code2 = 0 (or whatever your "does not exist" Dim record value is) and Code1 = 0 whenever Code2 has a value?
If this is the case, and you therefore have records of different grains in the same fact table, then you have broken the cardinal rule for designing fact tables. The first thing you do when designing a fact table is define your grain (not grains) and then you must stick to it. Breaking this rule causes all sorts of issues in your Dimensional Model - as you may have now discovered if that's what you have done

Regards,

What you appear to be describing is a hierarchy of some sort. For a hierarchy bridge to work you need to combine the dimensions that make up the hierarchy into a single table. This also implies the surrogate keys of those dimensions must be mutually exclusive.

If you do not have mutually exclusive keys (I, for one, tend to use a single sequence for assigning surrogate keys to dimensions) one option is to include a degenerate value in the fact that is used with the surrogate as a composite key of the combined dimension. The PK of the combined dimension would be the degenerate value and the surrogate key of the true dimension.

The hierarchy bridge is explained in The Data Warehouse Toolkit. It includes such things as the parent and child keys, parent and child level and distance between the parent and child. All possible parent-child relationships at all levels is represented in the bridge, allowing you to traverse the bridge without the need for recursive logic.

Hi,


Thank you Ngalemmo and Nick for your kind inputs.


As correctly analysed by Ngalemmo, the keys are mutually exclusive.

DImCode1 contains 5000 values and Dimcode2 contains 200 values (eg. combination to some of DimCode1 1500 Value make pair with 100 Business Definition and 120 of Dimcode 2 make pair with let say 50 pairs with Business Definition). If i put the parent hierarchy pairs also in the bridge the combinations in Bridge is expected to grow and will become difficult to manage, to avoid this i seek parent child in the business function dimension.

Also i am finding myself clueless to merge the two dimensions in one as the keys for both dims are mutually exclusive connecting to business defination dim in Many to Many fashion.

I also tried making two connecting bridges to make (Bridge-1: DIM1-Business def pairs and Bridge-2: DIM2-Business def and connected both bridges to Business Defination Dim) , still unable to see measures under Fact from Business Dimension as it asks for either reference from DIMcode1 or Dimcode2 but not both.

And yes, i would also like to know more about including a degenerate value in the fact that is used with the surrogate as a composite key of the combined dimension.



Thanks and Regards,
Ajit