Inner vs Outer Joins

Products

• EUREKA:Strategy

Issue/Introduction

Inner vs Outer Joins

Environment

Release:
Component: STGY

Resolution

How many times have you been asked if ROLAP does outer joins or inner joins? How about right outer joins and left outer joins? Here is an easy way to remember what they are.

          Yellow     Green     Blue

1. The Inner Join is the green section, the intersection of the 2 circles. This would return only those rows where the two tables have a value in common (per.perkey = f.perkey).
   
   
2. The Right Outer Join includes the blue and green sections. This would return all of the rows from the blue table and the intersected rows of the two tables (per.perkey = *f.perkey and all rows from the blue table).
   
   
3. The Left Outer Join includes the yellow and the green sections. This would return all of the rows from the yellow table and the intersected rows of the two tables (per.perkey* = f.perkey and all rows from the yellow table).
   
   
4. The Full Outer Join includes all rows from both circles regardless of the relationship between the values in the table. This is not a common implementation, but some clients have asked for it.
   
   

NOTE: ROLAP performs Inner Joins on its database queries ONLY. The ROLAP Server will accommodate outer joins when Show Missing Data =TRUE. The join will occur in memory only, no special database query will be sent.

REPORTING EXAMPLE:

However, when 2 fact tables are involved the results can vary. For instance, you have a report that includes all dimensions from both fact tables (unbalanced). CleverPath EUREKA:Strategy will fill the matrix (report) with as much data as it can, this is more representative of an logical full outer join. If your report with 2 fact tables only includes those dimensions that they have in common, it is more representative of a logical inner join.

FACT 1 Per_key Mkt_key Prod_key Fact 1a

FACT 2 Per_key Mkt_key Cust_key Fact 2a

Notice that the market and period keys are the only dimensions that the fact tables have in common.
If you ran the following report with all of the dimensions, the results would be the following:

Sections: Period = Quarter 1, 1997
Rows:
Market = All Regions
Customer = Company A
Columns:
Product = Cleaner
Facts

Quarter 1, 1997
		Fact 1a	Fact 2a	Fact 1a	Fact 2a
		Cleaner	Cleaner	NA	NA
Company A
	Central Region	0	0	100	26
	Eastern Region	0	0	200	257
	Western Region	0	0	32	235
	Southern Region	0	0	47	21
NA
	Central Region	100	26	0	0
	Eastern Region	200	257	0	0
	Western Region	32	235	0	0
	Southern Region	47	21	0	0

However, if only their common dimensions are on the report the report appears as if it is performing an inner join.

Sections: Period = Quarter 1, 1997
Rows: Market = All Regions
Columns: Facts

Quarter 1, 1997
	Fact 1a	Fact 2a
Central Region	100	26
Eastern Region	200	257
Western Region	32	235
Southern Region	47	21