How to Join Dimension Tables

[raju111]

Hello friends,

I have one fact table f1 and two dimension tables d1 , d2 .

I have below joins

F1 joins with D1
D1 joins with D2

There is no join between F1 and D2 .How to model these tables in framework manger ??
Any ideas will be appriciated ...

thanks in advance

 

[blom0344]

Your example matches a snowflake schema. The ultimate goal is to model a starschema. In this there is only one dimension level deep from the fact.

so either consolidate the two dimensions to a new one or use an alias on D2 to join to the fact (very much depending on your case)

Ties Blom

 

[raju111]

Thanks for your valuable advise , can you please elobarate little more about "use an alias on D2 to join to the fact "

Thanks in advance...

First one will work for me but i would like to know about second case ...

 

[blom0344]

You cannot join D2 to F1 as this would introduce a loop in the model (F1-D1-D2-F1)
Alias / shortcuts are used in the model to avoid these loops. Dimension tables are often required multiple times in a model. Then you need to resort to alias/shortcuts to solve this.
Look in FM documentation on 'shortcuts'..

Ties Blom

 

[raju111]

For first method ....
My understanding is first import all three tables F1,D1,D2

In database layer just join D1 and D2 and in model layer join F1 query subject with consolidated (D1&D2) query subject ??

Please confirm

Thanks again...

 

[blom0344]

Yes, create a model query subject on F1 and another on on D1-D2. The relationship between D1 and D2 is defined in the database layer. In the model layer define relation between the two model query subjects..

Ties Blom