SSAS: NON EMPTY and INNER/LEFT OUTER JOINing

Today a post about joining in SQL, the NON EMPTY operator and the nonempty() function in MDX. The origin of this blog started when a customer asked why some months on a graph didn't show up. So i started looking at the NON EMPTY operator and the NonEmpty() function.

Prerequisites
At first i tried to create a representative query on AdventureWorks cube. When i finally succeeded i tried to buid a query in SQL and at first glance building the simple MDX query to SQL seems easy but i gave up on this and i decided to built my own lab environment. This confirmed my believe that MDX is a very powerful query language. Hard to understand but when you have mastered it you can create powerful reports. Here is the SQL script is used for this small experiment.

1. Execute this script in SSMS:

USE [NonEmpty]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))
DROP TABLE [dbo].[DimDate]
GO

CREATE TABLE [dbo].[DimDate](
[Date_dim_key] [int] NULL,
[Date_Year] [int] NULL
) ON [PRIMARY]
GO

--TRUNCATE TABLE dbo.DimDate
--GO

INSERT INTO dbo.DimDate
VALUES (1,2000),
(2, 2001),
(3, 2002),
(4, 2003),
(5, 2004),
(6, 2005),
(7, 2006),
(8, 2007),
(9, 2008),
(10, 2009),
(11, 2010),
(12, 2011),
(13, 2012),
(14, 2013),
(15, 2014),
(16, 2015)

------------------------------------------------------------------
USE [NonEmpty]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestFact]') AND type in (N'U'))
DROP TABLE [dbo].[TestFact]
GO

CREATE TABLE [dbo].[TestFact](
[TestFact_ID] [int] identity (1,1) NOT NULL,
[FK_Date_ID] [int] NULL,
[TestFact_Count] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.[TestFact]
VALUES (1,1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 1),
(7, 1),
(8, 1),
(9, 1),
(10, 1),
--(11, 1),
(12, 1),
(13, 1),
(14, 1),
(15, 1),
(16, 1)


2. Build a simple cube on these tables:



Experimenting with the SQL queries
So when the customer was talking about the not showing some months on the x-axis of the graph i immediately realized it had something to do with LEFT and INNER joining problem (but in MDX).

So here are some SQL joining queries:

SELECT *
FROM dbo.TestFact F
INNER JOIN dbo.DimDate D ON F.FK_Date_ID = D.Date_dim_key


SELECT *
FROM dbo.DimDate D
LEFT OUTER JOIN dbo.TestFact F ON F.FK_Date_ID = D.Date_dim_key





BUT i'm building reports on cubes and therefore i can't use SQL queries and therefore i need MDX queries that can handle LEFT joining.

MDX queries
In MDX you have two options (as far is i know) for simulating INNER JOIN/LEFT/RIGHT FULL OUTER JOIN and that is the NON EMPTY keyword and the Nonempty() function. I'll show them both. First the simulation of the INNER JOIN in MDX:

1) INNER JOIN with the NON EMPTY keyword:

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

2) INNER JOIN with the NONEMPTY() function:


SELECT NONEMPTY({[DimDate].[DateYear].[DateYear]}) ON ROWS,
NONEMPTY([Measures].[TestFactCount]} ON COLUMNS
FROM [TestFact];

3) LEFT OUTER JOIN with the NON EMPTY keyword

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

4) LEFT OUTER JOIN with the NONEMPTY() function

SELECT [DimDate].[DateYear].[DateYear] ON ROWS,
NONEMPTY({[Measures].[TestFactCount]}) ON COLUMNS
FROM [TestFact]

Conclusion:
INNER/LEFT/RIGHT OUTER/FULL OUTER joining can be simulated in MDX with the NON EMPTY keyword and/or the NonEmpty Function.

Greetz,
Hennie