none
Performance issue with ProClarity Grand Totals RRS feed

  • Question

  • Some queries with Grand Totals and Subtotals in ProClarity Pro 6.3 take a very long time to run especially when comparing to other OLAP browsers.

    The latest update 6.3.2229 (Dec-2010) helps in certain queries, but the problem persists in too many situations.

     

    In one of our clients, a specific query runs in a couple of seconds in ProClarity. When we add Grand Totals, it takes over 30 minutes to complete. Here’s a summary of performance comparisons we did with the query with Grand Totals:

    - Excel 2010 Pivot Table:   15 seconds

    - ProClarity 6.3.2229:         30 minutes   - this is about 120 times slower than Excel!!!

    The client is arguing that this is not acceptable.

     

    We were able to replicate the issue using the Adventure Works DW 2008 SE cube database. The difference in performance isn’t as dramatic as in our client, but it clearly indicates major performance issues. Here’s a summary of the tests with AdvWorks:

    - Excel Pivot Table 2010:    20 seconds 

    - ProClarity 6.3.2229:          over 180 seconds  -  this is about 9 times slower than Excel!!!

    - A competing OLAP browser product: about the same result as Excel

     

    Note: Without GTotals, the ProClarity query runs in 5 seconds. With GTotal, it takes 180 seconds which is 36 times slower. We actually have been having such performance issues with GTotals for a while, but this client is putting significant pressure to address it.

     

    At the bottom, you’ll find the environment we used and a couple of changes we did in Adventure Works to replicate the issue (we also replicated the issue with SSAS 2008 R2):

     

    Please advise.

     

    Platform tested:

     

    Cube database: Analysis Services Standard Ed. 64-bit version: 10.0.2531.0

     

     

    VS Project: Adventure Works.sln

     

    To replicate the issue, we added two calculated measures to the Adventure Works.cube:

    [Sales Amount YTD] = sum(ytd([Date].[Calendar]),[Measures].[Sales Amount])

    [Sales Amount Prior YTD] = Sum (YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],1)),[Measures].[Sales Amount])

     

    Following is the MDX query produced by ProClarity. In Excel Pivot Table 2010, we used the same set of reporting parameters to reproduce the report – meaning, the same attributes/dimensions and member selections in rows and columns, same filters, calculated measures and totals.

     

     

    MDX in ProClarity 6.3.2229.104

    WITH MEMBER [Sales Territory].[Sales Territory Region].[All Sales Territories].[ Grand Total] AS 'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { { { [Sales Territory].[Sales Territory Region].[All Sales Territories].CHILDREN } * { [Sales Channel].[Sales Channel].[All Sales Channels].CHILDREN } * { [Promotion].[Promotion Category].[All Promotions].CHILDREN } * { [Promotion].[Promotion].[All Promotions].CHILDREN } * { [Product].[Category].[All Products].CHILDREN } * { [Product].[Model Name].[All Products].CHILDREN } } }, [Sales Territory].[Sales Territory Region] ) } * { EXTRACT( { { { [Sales Territory].[Sales Territory Region].[All Sales Territories].CHILDREN } * { [Sales Channel].[Sales Channel].[All Sales Channels].CHILDREN } * { [Promotion].[Promotion Category].[All Promotions].CHILDREN } * { [Promotion].[Promotion].[All Promotions].CHILDREN } * { [Product].[Category].[All Products].CHILDREN } * { [Product].[Model Name].[All Products].CHILDREN } } }, [Sales Channel].[Sales Channel] ) } * { EXTRACT( { { { [Sales Territory].[Sales Territory Region].[All Sales Territories].CHILDREN } * { [Sales Channel].[Sales Channel].[All Sales Channels].CHILDREN } * { [Promotion].[Promotion Category].[All Promotions].CHILDREN } * { [Promotion].[Promotion].[All Promotions].CHILDREN } * { [Product].[Category].[All Products].CHILDREN } * { [Product].[Model Name].[All Products].CHILDREN } } }, [Promotion].[Promotion Category] ) } * { EXTRACT( { { { [Sales Territory].[Sales Territory Region].[All Sales Territories].CHILDREN } * { [Sales Channel].[Sales Channel].[All Sales Channels].CHILDREN } * { [Promotion].[Promotion Category].[All Promotions].CHILDREN } * { [Promotion].[Promotion].[All Promotions].CHILDREN } * { [Product].[Category].[All Products].CHILDREN } * { [Product].[Model Name].[All Products].CHILDREN } } }, [Promotion].[Promotion] ) } * { EXTRACT( { { { [Sales Territory].[Sales Territory Region].[All Sales Territories].CHILDREN } * { [Sales Channel].[Sales Channel].[All Sales Channels].CHILDREN } * { [Promotion].[Promotion Category].[All Promotions].CHILDREN } * { [Promotion].[Promotion].[All Promotions].CHILDREN } * { [Product].[Category].[All Products].CHILDREN } * { [Product].[Model Name].[All Products].CHILDREN } } }, [Product].[Category] ) } * { EXTRACT( { { { [Sales Territory].[Sales Territory Region].[All Sales Territories].CHILDREN } * { [Sales Channel].[Sales Channel].[All Sales Channels].CHILDREN } * { [Promotion].[Promotion Category].[All Promotions].CHILDREN } * { [Promotion].[Promotion].[All Promotions].CHILDREN } * { [Product].[Category].[All Products].CHILDREN } * { [Product].[Model Name].[All Products].CHILDREN } } }, [Product].[Model Name] ) } }, { { { [Sales Territory].[Sales Territory Region].[All Sales Territories].CHILDREN } * { [Sales Channel].[Sales Channel].[All Sales Channels].CHILDREN } * { [Promotion].[Promotion Category].[All Promotions].CHILDREN } * { [Promotion].[Promotion].[All Promotions].CHILDREN } * { [Product].[Category].[All Products].CHILDREN } * { [Product].[Model Name].[All Products].CHILDREN } } } ) )', SOLVE_ORDER = 1000  MEMBER [Sales Channel].[Sales Channel].[All Sales Channels].[ Grand Total] AS 'AGGREGATE( { [Sales Channel].[Sales Channel].DEFAULTMEMBER } )' , SOLVE_ORDER = 1000  MEMBER [Promotion].[Promotion Category].[All Promotions].[ Grand Total] AS 'AGGREGATE( { [Promotion].[Promotion Category].DEFAULTMEMBER } )' , SOLVE_ORDER = 1000  MEMBER [Promotion].[Promotion].[All Promotions].[ Grand Total] AS 'AGGREGATE( { [Promotion].[Promotion].DEFAULTMEMBER } )' , SOLVE_ORDER = 1000  MEMBER [Product].[Category].[All Products].[ Grand Total] AS 'AGGREGATE( { [Product].[Category].DEFAULTMEMBER } )' , SOLVE_ORDER = 1000  MEMBER [Product].[Model Name].[All Products].[ Grand Total] AS 'AGGREGATE( { [Product].[Model Name].DEFAULTMEMBER } )' , SOLVE_ORDER = 1000  SELECT NON EMPTY { { { [Date].[Calendar].[Month].&[2004]&[7] } * { [Measures].[Sales Amount], [Measures].[Sales Amount YTD], [Measures].[Sales Amount Prior YTD] } } } ON COLUMNS ,

     

     

    NON EMPTY { { { { [Sales Territory].[Sales Territory Region].[All Sales Territories].CHILDREN } * { [Sales Channel].[Sales Channel].[All Sales Channels].CHILDREN } * { [Promotion].[Promotion Category].[All Promotions].CHILDREN } * { [Promotion].[Promotion].[All Promotions].CHILDREN } * { [Product].[Category].[All Products].CHILDREN } * { [Product].[Model Name].[All Products].CHILDREN } } }, ( [Sales Territory].[Sales Territory Region].[All Sales Territories].[ Grand Total], [Sales Channel].[Sales Channel].[All Sales Channels].[ Grand Total], [Promotion].[Promotion Category].[All Promotions].[ Grand Total], [Promotion].[Promotion].[All Promotions].[ Grand Total], [Product].[Category].[All Products].[ Grand Total], [Product].[Model Name].[All Products].[ Grand Total] ) } ON ROWS 

    FROM [Adventure Works]

     

    WHERE ( [Sales Territory].[Sales Territory].[Group].&[North America] )

     

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

     

     

    Thursday, March 24, 2011 11:22 PM