Can anyone provide some clues on the non empty behaviour setting for calculated members? This question has been asked on several posts around several forums but never seems to be answered fully.
We've applied this to a couple of our lowest level calculated members and it has resulted in some serious performance gains - which made us very happy of course.
However, it's also resulted in a couple of situations where we get empty cells when we expect nothing to come back at all. Here's a few queries from AdventureWorks to demonstrate this.
Our goal is to retain the non empty behaviour (NEB) setting so that we can use the performance gains, but avoid the empty cells.
If you run Query 1, with NEB set on Internet Sales Amount, you get all months from the first two quarters. Notice that March and June are empty for post code 2036.
-- Query 1 - all months from first two quarters
WITH MEMBER [Measures].[Internet Sales Amount Test] AS
(closingperiod([Date].[Calendar].[Date], [Date].[Calendar].currentmember.lastchild), [Measures].[Internet Sales Amount])
,FORMAT_STRING = "Currency"
,NON_EMPTY_BEHAVIOR = 'Internet Sales Amount'
SELECT ([Date].[Calendar].[CY 2004].[H1 CY 2004].[Q1 CY 2004].[January 2004]:
[Date].[Calendar].[CY 2004].[H1 CY 2004].[Q2 CY 2004].[June 2004]) on columns,
non empty{[Customer].[Customer Geography].[Postal Code].[2060],
[Customer].[Customer Geography].[Postal Code].[2036],
[Customer].[Customer Geography].[Postal Code].[2055]} on rows
FROM [Adventure Works]
WHERE [Measures].[Internet Sales Amount Test];
For query 2, postal code 2036 is still returned with both quarters as null because months other than the last month in each quarter are non empty
-- Query 2
WITH MEMBER [Measures].[Internet Sales Amount Test] AS
(closingperiod([Date].[Calendar].[Date], [Date].[Calendar].currentmember.lastchild), [Measures].[Internet Sales Amount])
,FORMAT_STRING = "Currency"
,NON_EMPTY_BEHAVIOR = 'Internet Sales Amount'
SELECT ([Date].[Calendar].[CY 2004].[H1 CY 2004].[Q1 CY 2004]:
[Date].[Calendar].[CY 2004].[H1 CY 2004].[Q2 CY 2004]) on columns,
non empty{[Customer].[Customer Geography].[Postal Code].[2060],
[Customer].[Customer Geography].[Postal Code].[2036],
[Customer].[Customer Geography].[Postal Code].[2055]} on rows
FROM [Adventure Works]
WHERE [Measures].[Internet Sales Amount Test];
For query 3, postal code 2036 is no longer returned because we have disabled NEB, and now the non empty row clause is filtering them out. This is what we are trying to acheive but with NEB still enabled.
-- Query 3
-- the result we want, i.e. without the null row for 2036
WITH MEMBER [Measures].[Internet Sales Amount Test] AS
(closingperiod([Date].[Calendar].[Date], [Date].[Calendar].currentmember.lastchild), [Measures].[Internet Sales Amount])
,FORMAT_STRING = "Currency"
--,NON_EMPTY_BEHAVIOR = 'Internet Sales Amount'
SELECT ([Date].[Calendar].[CY 2004].[H1 CY 2004].[Q1 CY 2004]:
[Date].[Calendar].[CY 2004].[H1 CY 2004].[Q2 CY 2004]) on columns,
non empty{[Customer].[Customer Geography].[Postal Code].[2060],
[Customer].[Customer Geography].[Postal Code].[2036],
[Customer].[Customer Geography].[Postal Code].[2055]} on rows
FROM [Adventure Works]
WHERE [Measures].[Internet Sales Amount Test];
So it appears to us that if you choose to enable NEB on the calculated member, it will filter out empty tuples early in the execution plan, but then stops the non empty row clause from working. Is it possible to have both working? Are there any ways around this problem?
Thanks, Jon
