Resolving loops in a star schema with 5 fact tables and 6 dimension tables

[Former Member]

Hello

I have a star schema, ie 5 FACT tables and 7 dimension tables, All fact tables share the same dimension tables, some FACT tables share 3 dimesnsions, while other share 5 dimensions.

I did adopt the best practices, and as recommended in the book, I tried to resolve them using Context, as it is the recommended option to Alias in a star schema setting. The contexts are resolved, but I still have loops. I also cleared the Multiple SQL Statement for each context option, but no luck. I need to get this resoved ASAP

[Former Member · Accepted solution]

Hi Patil,

It is not clear what exactly is the problem. As a starting point you could set the context up so that it only covers the joins from fact to dimension.

Fact A, joins Dim 1, Dim 2, Dim 3, and Dim 4

Fact B, joins Dim 1, Dim 2, Dim 3, Dim 4 and Dim 5

Fact C, joins Dim 1, Dim 2, Dim 3, Dim 4 and Dim 6

Fact D, joins Dim 1, Dim 2, Dim 3, Dim 4 and Dim 7

Fact E, joins Dim 1, Dim 2, Dim 4 and Dim 6

If each of these are contexts are done and just cover the joins from fact to dim then you should be not get loops.

If you could lay out your joins like above then it may be possible to specify the contexts/aliases that should work.

Regards

Alan

[Former Member · Accepted solution]

Hello,

Thanks for the response. I do have the context which joins the fact and dimensions however not necessarily in the order of FACT to Dim, however some are Dim on left hand side to Fact on right hand side.

When I click each context it shows the right joins, however when I click detect context again, it asks me to override of the the existing context. ?

In a star schema model, should one use Context or Alias, or both.

The book says that Context is the best option for star schemas, where all joins are 1 :m (dim to fact)

Will this resolve FAN and CHASM traps as well.

[Former Member · Accepted solution]

Hi Patil,

I am not sure what the detect is doing. You might be better off doing them manually. Switch to View, List Mode and check what contexts have been created.

I would recommend creating one context for each fact table and only cover the joins that are direct from the fact table to the dimension or dimension to fact.

Regards

Alan