Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Aggregation changes when adding 6th scoped measure to a pivot table

Answered Aggregation changes when adding 6th scoped measure to a pivot table

  • Monday, March 04, 2013 4:34 PM
     
     
    Sign In to Vote
    0

    I have 3 measures in a measure group all defined as LastNonEmpty.  I created 13 hidden counts so I could properly display the aggregation values.  These are product SKUs (pricing, cost, etc) with weekly data points.  When displayed by store, the aggregation should be the avergage of the prices of the last nonempty value.

    To keep properly aggregate at the month, quarter, and year levels I scoped each measure like the following:

    SCOPE([Measures].[Regular Retail]);
    This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,
    [Measures].[Regular Retail]/1,
    [Measures].[Regular Retail]/[Measures].[Regular Retail Cnt]);
    NON_EMPTY_BEHAVIOR(This) = [Measures].[Regular Retail Cnt];
    END SCOPE; 

    This works fine with 5 or fewer measures, as demostrated here at the month level:

    However, look at what happens when I add a 6th measure:

    What were previously averages are now sums!  It doesn't matter which measures I use... if there are 5 or fewer it works fine, but once I add the 6th it changes to sums... any ideas on why this is happening?

    Comments/suggestions would be greatly appreciated.

    -Matt

     

All Replies

  • Monday, March 04, 2013 4:49 PM
     
     
    Sign In to Vote
    0
    At the beginning that should read '13 measures in a measure group all defined as LastNonEmpty'...
     
  • Tuesday, March 05, 2013 8:35 AM
    Moderator
     
     
    Sign In to Vote
    0

    Strange...Can you post the whole of your code? It looks like a rogue scope statement has triggered some aggregation.

    Just to be clear, are the new measures you've created real measures with aggregate function count or are they calculated measures that you're overwriting?

    Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

     
  • Tuesday, March 05, 2013 3:49 PM
     
     
    Sign In to Vote
    0

    Hi, Chris.  Thanks for taking a look at this with me...

    These are real measures loaded from a weekly data feed.  I'm scoping them so they show a proper average of the last nonempty value at any level in the calendar.

    These are the measures and associated counts:

    This is the code I'm using to scope the measures:

    SCOPE([Measures].[Regular Retail]);

    This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[Regular Retail]/1,

    [Measures].[Regular Retail]/[Measures].[Regular Retail Cnt]);

    NON_EMPTY_BEHAVIOR(This) = [Measures].[Regular Retail Cnt];

    END SCOPE; 

    SCOPE([Measures].[Raw Cost]);

      This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[Raw Cost]/1,

    [Measures].[Raw Cost]/[Measures].[Raw Cost Cnt]);

    NON_EMPTY_BEHAVIOR(This) = [Measures].[Raw Cost Cnt];

    END SCOPE; 

    SCOPE([Measures].[Landed Cost]);

      This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[Landed Cost]/1,

    [Measures].[Landed Cost]/[Measures].[Landed Cost Cnt]);

    NON_EMPTY_BEHAVIOR(This) = [Measures].[Landed Cost Cnt];

    END SCOPE; 

    SCOPE([Measures].[Promo Retail]);

      This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[Promo Retail]/1,

    [Measures].[Promo Retail]/[Measures].[Promo Retail Cnt]);

    NON_EMPTY_BEHAVIOR(This) = [Measures].[Promo Retail Cnt];

    END SCOPE; 

    SCOPE([Measures].[Clearance Retail]);

      This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[Clearance Retail]/1,

    [Measures].[Clearance Retail]/[Measures].[Clearance Retail Cnt]);

    NON_EMPTY_BEHAVIOR(This) = [Measures].[Clearance Retail Cnt];

    END SCOPE;

    SCOPE([Measures].[ACE Level 1 Price]);

      This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[ACE Level 1 Price]/1,

    [Measures].[ACE Level 1 Price]/[Measures].[ACE Level 1 Price Cnt]);

    NON_EMPTY_BEHAVIOR(This) = [Measures].[ACE Level 1 Price Cnt];

    END SCOPE;

    SCOPE([Measures].[ACE Level 2 Price]);

      This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[ACE Level 2 Price]/1,

    [Measures].[ACE Level 2 Price]/[Measures].[ACE Level 2 Price Cnt]);

    NON_EMPTY_BEHAVIOR(This) = [Measures].[ACE Level 2 Price Cnt];

    END SCOPE;

    SCOPE([Measures].[Avg]);

      This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[Avg]/1,

    [Measures].[Avg]/[Measures].[Avg Cnt]);

    NON_EMPTY_BEHAVIOR(This) = [Measures].[Avg Cnt];

    END SCOPE;

    SCOPE([Measures].[Chain Avg Cost]);

      This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[Chain Avg Cost]/1,

    [Measures].[Chain Avg Cost]/[Measures].[Chain Avg Cost Cnt]);

    NON_EMPTY_BEHAVIOR(This) = [Measures].[Chain Avg Cost Cnt];

    END SCOPE;

    SCOPE([Measures].[High]);

      This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[High]/1,

    [Measures].[High]/[Measures].[High Cnt]);

    NON_EMPTY_BEHAVIOR(This) = [Measures].[High Cnt];

    END SCOPE;

    SCOPE([Measures].[Low]);

      This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[Low]/1,

    [Measures].[Low]/[Measures].[Low Cnt]);

    NON_EMPTY_BEHAVIOR(This) = [Measures].[Low Cnt];

    END SCOPE;

    What's odd is that it happens regardless of which measures I include.  As soon as I add the 6th measure to the pivot table it reverts to sums.  I'm wondering if this isn't an Excel issue...

     
  • Tuesday, March 05, 2013 4:26 PM
    Moderator
     
     
    Sign In to Vote
    0

    It does sound very strange, but I'm not sure this is an Excel issue even if it could be related to how Excel generates its MDX.

    Some things to try... First, don't use Non_Empty_Behavior at all - it's better to add the logic to an IIF() statement if you want your calculation to return null in certain circumstances. Second, rather than using IIF() to check what level you're at, you could use two separate scope statements instead. So for example your code might look something like this:

    SCOPE(MEASURES.LOW);
     SCOPE([CALENDAR].[DATE].[DATE].MEMBERS);
      THIS = IIF(MEASURES.[LOW CNT]=0, NULL, MEASURES.[LOW]/MEASURES.[LOW CNT]);  
     END SCOPE;
     SCOPE([CALENDAR].[DATE].[ALL]);
      THIS = MEASURES.[LOW];
     END SCOPE;
    END SCOPE;

    (I've guessed that level 4 of your Fiscal Calendar hierarchy is a Date here; in general it makes things easier if you scope using attribute hierarchies and not user hierarchies like your Fiscal Calendar hierarchy).

    HTH,

    Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

     
  • Tuesday, March 05, 2013 4:29 PM
     
     
    Sign In to Vote
    0
    Thanks, Chris.  Let me give your suggestion a shot and I'll report back.
     
  • Tuesday, March 05, 2013 8:04 PM
     
     
    Sign In to Vote
    0

    Well, I'm still trying to understand what's causing this issue (as opposed to just finding a way around it).  I like to know WHY something is happening... I ran profiler and captured the MDX queries from Excel with 5 measures, and then with 6... here's the output:

    *** With 5 Measures ***

    SELECT {[Measures].[Regular Retail],[Measures].[Promo Retail],[Measures].[ACE Level 1 Price],[Measures].[ACE Level 2 Price],[Measures].[Chain Avg Cost]}

    DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Product].[SKU Number].[All]})})), Hierarchize(AddCalculatedMembers({DrilldownLevel({[Store].[Store Number].[All]})}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM (SELECT ({[Product].[SKU Number].&[25125]}) ON COLUMNS  FROM [WestLakeSales]) WHERE ([Calendar].[Fiscal Calendar].[Fiscal Month Name].&[20132]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

    *** With 6 Measures ***

    SELECT {[Measures].[Regular Retail],[Measures].[Promo Retail],[Measures].[ACE Level 1 Price],[Measures].[ACE Level 2 Price],[Measures].[Chain Avg Cost],[Measures].[Raw Cost]}

    DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Product].[SKU Number].[All]})})), Hierarchize(AddCalculatedMembers({DrilldownLevel({[Store].[Store Number].[All]})}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM (SELECT ({[Product].[SKU Number].&[25125]}) ON COLUMNS  FROM [WestLakeSales]) WHERE ([Calendar].[Fiscal Calendar].[Fiscal Month Name].&[20132]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

    The only difference is the addition of the 6th measure in the select.  Everything else is the same.  When I run these queries in SSMS I get the same results... it returns sums when there are 6 or more measures.  That eliminates it as an Excel issue.

    In the trace data, when it runs 'correctly' it generates over 40 query subcube response lines.  When there are 6 or more measures it only returns 22 response lines and appears to include an additional dimension attribute from one of the dimensions.  I need to dig deeper into that...

     
  • Tuesday, March 05, 2013 8:13 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hmm, nothing out of the ordinary in the queries - I suspect it's just the addition of the extra measure that triggers a different query plan. Overall it feels like a bug, so you might want to open a case with MS about this; however, it would still be good to find a workaround. 

    One other thing to try would be using calculated measures for these calculations, rather than overwriting the real measures.

    Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

     
  • Tuesday, March 05, 2013 8:34 PM
     
     
    Sign In to Vote
    0

    Thanks, Chris.  Here's the final bit of info before I move on to a workaround...  I went verbose in profiler and found that when adding the 6th measure it changes the calendar dimension attribute to return all members for Fiscal Week Name, as shown here (top is 5, bottom is 6):

    I'll move on to finding a workaround.  I'll post back here when I figure out how best to do it, in case it comes up again.

     
  • Wednesday, March 06, 2013 8:18 AM
    Moderator
     
     
    Sign In to Vote
    0

    In this case, it sounds as though using the following connection string properties:
    Disable Prefetch Facts=True; Cache Ratio=1
    Would stop this happening (this article has a little background on these settings: http://blogs.msdn.com/b/sqlcat/archive/2007/04/26/how-to-warm-up-the-analysis-services-data-cache-using-create-cache-statement.aspx) but while you can set these values in SQL Server Management Studio (http://msdn.microsoft.com/en-us/library/cc645898.aspx) it would be difficult to ensure that every client tool set them.

    Otherwise, I think your only option is to change your code somehow or open a case with MS.

    Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

     
  • Wednesday, March 06, 2013 4:06 PM
     
     Answered
    Sign In to Vote
    0

    Chris, I tried the connection string properties and they did not change the results.  However, I have solved the issue.

    While it may still be a bug in SSAS, since the query behavior was changing the data returned from the [Fiscal Calendar].[Fiscal Week Name] attribute I just added those members to the scope statement and the problem is gone.  Now it returns the average of the last nonempty value at any level of the calendar hierarchy, regardless of the number of measures I include in the query.

    SCOPE([Measures].[Regular Retail]

          ,[Calendar].[Fiscal Calendar].[Fiscal Week Name].members); 

    This = IIF([Calendar].[Fiscal Calendar].CurrentMember.Level.Ordinal < 4,

    [Measures].[Regular Retail]/1,

    [Measures].[Regular Retail]/[Measures].[Regular Retail Cnt]); 

    NON_EMPTY_BEHAVIOR(This) = [Measures].[Regular Retail Cnt]; 

    END SCOPE;  

    Thanks for your time, Chris.  Hope this helps someone else in the future.

    • Marked As Answer by Bendare2 Wednesday, March 06, 2013 4:06 PM
    •