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