How to set two tables in a report with left Join


 

Sometime it is required to set left join Dimension to fact, not left joining multiple passes.

When you want to show all data from a table irrespective of others.

Demo:

 

Build a normal report with Attribute1, Attribute2 and a Metric.  The SQL will come out like this:

select a12.Attribute1  Attribute1, a13.Attribute2  Attribute2, sum(a11.Fact)  Metric

 from FactTable a11 join DimAttribute1 a12

on (a11.Attribute1Key = a12.Attribute1Key)

join DimAttribute2 a13   on (a11.Attribute2Key = a13.Attribute2Key)

group by a12.Attribute1, a13.Attribute2

But let’s say that you need to left join DimAttribute2 to FactTable.  Simply follow these steps:

 

Step 1: Edit the Attribute

1.In the attribute editor, go to Tools -> VLDB Properties.

2.Change the property Joins -> Preserve all final pass result elements to the third option, Preserve all elements of final pass result table with respect to lookup table but not relationship table.

3.Update Schema.

 

Step 2: Edit the Report

1.In the report editor, go to Data -> VLDB Properties.

2.Change the property Joins -> Preserve all final pass result elements to the fourth option, Do not listen to per report level setting, preserve elements of the final pass according to the setting at the attribute level.

 

With those two options combined, the resulting report now generates this SQL:

select a12.Attribute1  Attribute1, a13.Attribute2  Attribute2, sum(a11.Fact)  Metric

from FactTable a11 join DimAttribute1 a12

on (a11.Attribute1Key = a12.Attribute1Key) left outer join DimAttribute2 a13

on (a11.Attribute2Key = a13.Attribute2Key)

group by a12.Attribute1, a13.Attribute2

 

Please Note that since you need to turn on a report level setting, changing the attribute won’t modify your entire system.  This is nice because you can choose to let some reports to left join on that attribute while not others.  One side effect, that  I noticed is that this attribute is no longer eligible for Intelligent Cubes. if you can happy with that then its Good J

Comments