datawarehouse outer joins

dataservices job design
1

hi guys

I need help with datawarehouse structure… warehouse tables built in star schema structure where 1 fact table is connected to multiple dimension tables…

while reporting, needs to get the data from 1 dimension table connected to fact table… but fact table don’t have data for the dimension data… so to show data in report needs to do left outer join…

Is there any other way to do the same… or using left outer join is fine?

scenario is that admission dimension is showing that admission is cancelled but because of data quality issue, there are some students attached to few admission id… there are 100 admission id cancelled and only 10 students attached to 10 admission id…

so in reports needs to show 100 admission id as cancelled with 10 data quality students attached.

hope I explained the scenario properly…

regards

leo_dec11 (BOB member since 2009-06-18)

2

Hi Leo,

As far as I know, for this situation I can tell you to use left outer join, but you have to be careful with the cardinality. Fact table should be 1 to many.

Gustavo

defaro :brazil: (BOB member since 2006-09-01)

3

Hi Leo.

Please also refer:

https://answers.sap.com/questions/555937/datawarehouse-outer-joins.html

Regards. S :smiley:

Shazin :india: (BOB member since 2011-07-19)

4

In the ETL build of Fact and Bridge tables, I tend to outer join the lookup of the natural dimension keys and NVL to a default value where match was not found.

This way during Universe development or even at user query time all joins needed are inner joins which historically were more performant that outer joins.

https://www.kimballgroup.com/2003/02/design-tip-43-dealing-with-nulls-in-the-dimensional-model/

Darth Services :uk: (BOB member since 2007-11-20)