none
Excel Pivot Table with Date Hierarchies - query performance degradation

    General discussion

  • For the sake of this explanation, I’m going to try and keep it simple. Slicing the data by additional dimensions only makes the issue worse. I’ll keep this description to one fact table and three dimensions. Also, I’m fairly new to SSAS Tabular; I’ve worked with SSAS Multidimensional in the past.

    We’ve got a fact table that keeps track of bill pay payments made over time. Currently, we only have about six months of data, with the fact row count at just under 900,000 rows. The grain is daily.

    There is an Account dimension (approx. 460,000 rows), with details about the individual making a payment.

    There is a Payment Category dimension (approx.. 35,000 rows), which essentially groups various Payees into groups which we like to report on: Automobile Loan, Mortgage, Insurance, etc.

    There is the requisite Date dimension  (exactly 62093 rows-more days than we need?), which allows visibility as to what is being paid when.

    Using this DW model, I’ve created a SSAS BISM Tabular model, from which Excel 2010 is ultimately used to perform some analysis, using Pivot Tables. In the tabular model, for easier navigation (doing what I’ve always done in SSAS MultiDimensional), I’ve created several Date Hierarchies, Year-Month, Year-Quarter-Month, etc.

    There are currently only two measures defined in the Tabular model: one for the “Sum of PaymentAmount”; one for the “PaymentsProcessed”.

    OK, in Excel 2010, using a Pivot Table, drag the “Sum of PaymentAmount” measure to the Values section, next to/under the PivotTable Field List. Not too exciting, just the grand total of all Payments, for all time.

    Drag the “YearMonth” hierarchy (from the Date dimension) to the “Column Labels” section. After expanding the year hierarchy to see the months, now the totals are for each of the months, for which we have data, for June through November, 2013.

    Drag the “PaymentCategory” (from the Payment Categories dimension) to the “Report Filter” section. Filter accordingly: We just want to see the monthly totals for “Automobile Loans”.

    Now, some details. Drag the “AccountSK” (hiding the actual account numbers) to the “Row Labels” section. This shows all accounts that have made Automobile Loan payments over the last six months, showing the actual payment amounts.

    So far, so good. Remember, I’m using a Date Hierarchy here, in this case “YearMonth”

    Now, if any of the other attributes on the Account dimension table, say “CreditScore”, or “LongName”, are subsequently dragged over to the “Row Lables” section, under the “AccountSK”, the results will never come back, before timing out or by giving up and pressing ESCape!

    If this exact scenario is done by removing the Date Hierarchy, “YearMonth” from the “Column Labels” and replace it with “Year” and “MonthName” attributes from the Date dimension, these fields not being in any sort of hierarchy, adding an additional “Account” attribute does not cause any substantial delay.

    What I’m trying to find out is why is this happening? Is there anything I can do as a work around, other than what I’ve done by not using a Date Hierarchy? Is this a known issue with DAX and the query conversion to MDX? Something else?

    I’ve done a SQL Profiler trace, but I’m not sure at this point what it all means. In the MDX query there is a CrossJoin involved. There are also numerous VertiPaq Scans which seems to be going through each and every AccountSK in the Account dimension, not just the ones filtered, to get an additional attribute (About 3,600 accounts which are “Automobile Loan” payments.).

    Any thoughts?

    Thanks! Happy Holidays!


    AAO

    Tuesday, December 24, 2013 7:44 PM

All replies

  • I'm interested to investigate more.

    Can you provide the MDX query generated in the two scenarios? The one that never returns (using YearMonth hierarchy) and the one that works well (without using the YearMonth hierarchy)?

    Thanks,

    Marco


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Thursday, December 26, 2013 6:49 AM
  • Thanks for your reply Marco. I've been reading your book, too, getting into Tabular.

    I've set up the Excel Pivot Table using either the Year/MonthName levels, or the YearMonth hierarchy and then adding the additional attribute for the CreditScore.

    Incidentally, when using the YearMonth hierarchy and adding the CreditScore, all is well, if the Year has not been "opened". When this is done, I suspect the same thing is going on.

    From SQL Profiler, each of the individual MDX queries below (formatted a bit for readability).

    Thanks!

    // MDX query using separate Year and MonthName levels, NO hierarchy.

    SELECT 
    NON EMPTY 
    Hierarchize(
    DrilldownMember(
    CrossJoin(
    {[Date].[Year].[All],[Date].[Year].[Year].AllMembers}, 
    {([Date].[MonthName].[All])}
    )
    ,[Date].[Year].[Year].AllMembers, [Date].[MonthName]
    )
    )
    DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME 
    ON COLUMNS, 

    NON EMPTY 
    Hierarchize(
    DrilldownMember(
    CrossJoin(
    {[Accounts].[AccountSK].[All],[Accounts].[AccountSK].[AccountSK].AllMembers}, 
    {([Accounts].[CreditScore].[All])}
    )
    ,[Accounts].[AccountSK].[AccountSK].AllMembers, [Accounts].[CreditScore]
    )

    DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME 
    ON ROWS  
    FROM [PscuPrototype] 
    WHERE ([PaymentCategories].[PaymentCategory].&[Automobile Loan],[Measures].[Sum of PaymentAmount]) 
    CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

    // MDX query using separate YearMonth hierarchy (Year, MonthName).
    SELECT 
    NON EMPTY 
    Hierarchize(
    DrilldownMember(
    {{DrilldownLevel({[Date].[YearMonth].[All]},,,INCLUDE_CALC_MEMBERS)}}, 
    {[Date].[YearMonth].[Year].&[2013]},,,INCLUDE_CALC_MEMBERS
    )

    DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME 
    ON COLUMNS,

    NON EMPTY 
    Hierarchize(
    DrilldownMember(
    CrossJoin(
    {[Accounts].[AccountSK].[All],[Accounts].[AccountSK].[AccountSK].AllMembers}, 
    {([Accounts].[CreditScore].[All])}
    )
    ,[Accounts].[AccountSK].[AccountSK].AllMembers, [Accounts].[CreditScore]
    )

    DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME 
    ON ROWS  
    FROM [PscuPrototype] 
    WHERE ([PaymentCategories].[PaymentCategory].&[Automobile Loan],[Measures].[Sum of PaymentAmount]) 
    CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS


    AAO

    Tuesday, December 31, 2013 6:32 PM
  • If you would be able to create a repro workbook (scrambling original data) so that the problem can be easily reproduced, it would be nice.

    I think this is a problem I already observed in Tabular, but having a repro on a smaller PowerPivot workbook would be much better for opening an incident with MS (and pushing for a solution on other channels, including Connect).

    Marco


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Wednesday, January 01, 2014 9:50 AM
  • some years ago I had a very similar issue which i reported at connect - without any feedback yet at all ...

    https://connect.microsoft.com/SQLServer/feedback/details/734222/powerpivot-using-hierarchies-is-much-slower-then-using-single-attribute-levels

    I also think that the issue is related to the MDX parser for tabular/PowerPivotthough, its very hard to investigate into the parser ofc ...

    -gerhard


    - www.pmOne.com -

    Thursday, January 02, 2014 10:49 AM
    Answerer
  • Thank you Gerhard, I'm investigating on these issues and this is another interesting use case. I'll update if there are news on that.

    Marco

    Thursday, January 02, 2014 11:38 AM
  • I haven't had time to get the Excel worksheet together, but I did come across a solution/work around to this problem. It's actually from one of your co-authors of the SSAS "Tabular" book. It was also almost a year ago.

    Please see: Natual and Unnatural Hierarchies:

    http://cwebbbi.wordpress.com/2012/02/28/natural-and-unnatural-hierarchies-in-the-ssas-2012-tabular-model/

    It all makes sense to me, as creating Hierarchies in SSAS MultiDimensional (MD), as well as appropriate Attribute Relationships and KeyColumns, was much more "visible" and interactive. I was curious as to how this could be done in Tabular. It isn't per se and requires a different approach.


    AAO

    Thursday, January 02, 2014 8:55 PM