Snowflake? Snowstorm!

Robert Angel

Hi,

I know the standard advice is to flatten a snowflake structure to subsume the outer 'limbs' of the snowflake into the first dimension that joins to the fact, in the logical layer, where no better alternative exists.

i.e. In the physical layer

Fact table to table B

Table B to table C

Solution in Business Model

Model fact as normal

Model Table B as normal and join to fact as normal

Drag table C onto the logical table source for table B

But what is best practise with multi-branch snowflakes, is it the same.

i.e. in the physical layer

Fact table to table B

Table B to Table C

Table C to Tables D, E, F

Table D to Table G

Do I still follow the simple example

i.e.

Model fact as normal

Model table B as normal

Logical join Fact to Table B

Drag tables onto logical table source to create a single LTS based on multiple tables

If so how best to handle null data in tables C to G?

Would you do this in practise or would you create a view with outer joins in the DB to cope with this?

Thanks for your input,

Robert.

Christian Berg-0racle

Nope. In-LTS joins with inner/outer join spec!

Robert Angel

Hi Christian,

outer only works so far though surely?

If A - B - C - D

A to B outer

B to C outer

C to D outer

Will we still get data in A if you include the entire chain; A,B,C,D; in the queries and the 'break' is in B?

thanks,

Robert.

Christian Berg-0racle

If there's a break in the chain the join will go on trying but you won't ever get C or D