Thursday, August 1, 2013

SSAS (Left) Outer Join

PROBLEM:


Generally, MDX statement returns the resultset that closely resembles SQL INNER JOIN. That means only matching records are returned. Sometimes, we have a requirement where we need to display all the Dimension records, regardless whether they exist in the Fact or not.


RESOLUTION:


The MDX returns resultset as INNER JOIN when the MDX contains NON EMPTY behavior. If this is removed from the Dimension axis, then the MDX will behave like OUTER JOIN.


INNER JOIN with NON EMPTY key word

SELECT
NON EMPTY {[DimDate].[DateYear].[DateYear]} ON ROWS,
NON EMPTY {[Measures].[VisitCount]} ON COLUMNS
FROM [FactVisit];
 

OUTER JOIN without NON EMPTY

SELECT
{[DimDate].[DateYear].[DateYear]} ON ROWS,
NON EMPTY {[Measures].[VisitCount]} ON COLUMNS
FROM [FactVisit];