Results 1 to 3 of 3

Thread: Non Empty Behavior

Hybrid View

  1. #1
    Join Date
    Nov 2008
    Posts
    4

    Non Empty Behavior

    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

  2. #2
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Here's a Little Straightforward Guidance ... I Think ...

    This is a link to what I feel is a good, detailed treatment of the subject matter you reference:

    http://www.sqlserveranalysisservices...%20Hintsv1.htm

    (See the "Calculation Non Empty Behavior" / other sections).

    HTH. Let us know if you have further issues / questions.

    Bill

  3. #3
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Were You Able to Accomplish Your Objectives?

    Let us know if we can answer further questions or assist in other ways.

    Thanks!

    Bill

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •