Thursday, July 16, 2009

MDX ParallelPeriod vs NON_EMPTY_BEHAVIOR

This one is a quick one:
In MS SQL Server Analysis Services the MDX function ParallelPeriod and the NON_EMPTY_BEHAVIOR setting are both important and useful. But together they can be just too much of a good thing and cause confusion. Here's a bit of intelligence I have obtained the hard way and haven't seen documented elsewhere.
Suppose for a simplistic example that this year you sell 1 unit to A Co. and 1 unit to B Co. And last year you sold 1 unit to A Co. and 1 unit to C Co.
Now you may usefully define an MDX variable called SalesLY based on the ParallelPeriod function and your regular Sales variable. All very well, you can now compare Sales (this year) with SalesLY (last year).
Then if you know your Analysis Services well - but not quite well enough - you may observe that SalesLY derives from Sales and set the NON_EMPTY_BEHAVIOR of SalesLY to Sales.
And then what happen?
Sales.[All] and SalesLY.[All] both correctly remain at 2. SSAS will not make any calculation error, but the presentation is not likely to be what you want:
A Co. Sales=1, SalesLY=1
B Co. Sales=1, SalesLY=
All : Sales=2, SalesLY=2
The SalesLY column no longer add up! What happened to last year's sale to C Co.? It's quite simple really: Since (this year's) Sales is empty (non-existent) for C Co. and the behavior of SalesLY is now explicitly linked to that, there is now no way last year's sales to C Co. will show up in a listing of SalesLY, except - fortunately - in the [All] aggregation.
So, beware of concurrent use in SSAS of the ParallelPeriod MDX function and the NON_EMPTY_BEHAVIOR setting!

This was a very technical posting to contrast the previous philosophical postings. It was also a quick way to get started again after spending the first half of July in the Scottish Highlands and, notably, Orkney! - A wonderful place for a vacation when it almost only rains during the nights (luckily!) and you dislike crowded areas and take a real interest in history and pre-history.

1 comment:

  1. Beautiful entry - I have been dealing with the same issue for the last 4-5 days..
    Manish

    ReplyDelete