Powerview vs Powerpivot (Excel) hierarchy performance

• General discussion

• 

  

  0

  Sign in to vote

  Hi,

  I have tried to create a hierarchy in Powerpivot for Excel, which includes 10 levels and allows us to calculate totals for an employee population of around 2000.  I am encountering performance issues in Powerview which aren't apparent in an equivalent Excel Pivot Table created from the same Powerpivot datasource.  I imported the Powerpivot datasource into SSAS Tabular to see what was going on.  I ran SQL Server Profiler on the database when the queries were running and the Powerview report created this DAX:

  EVALUATE
    CALCULATETABLE(
      CALCULATETABLE(
        ROW(
          "Average_Active_Trip_Cost", 'Trips'[Average Active Trip Cost]
        ),
        KEEPFILTERS(
          GENERATE(
            KEEPFILTERS(
              GENERATE(
                KEEPFILTERS(
                  GENERATE(
                    KEEPFILTERS(
                      GENERATE(
                        KEEPFILTERS(
                          GENERATE(
                            KEEPFILTERS(
                              GENERATE(
                                KEEPFILTERS(
                                  GENERATE(
                                    KEEPFILTERS(
                                      GENERATE(
                                        KEEPFILTERS(VALUES('Employees'[Level2])),
                                        VALUES('Employees'[Level3])
                                      )
                                    ),
                                    VALUES('Employees'[Level4])
                                  )
                                ),
                                VALUES('Employees'[Level5])
                              )
                            ),
                            VALUES('Employees'[Level6])
                          )
                        ),
                        VALUES('Employees'[Level7])
                      )
                    ),
                    VALUES('Employees'[Level8])
                  )
                ),
                VALUES('Employees'[Level9])
              )
            ),
            FILTER(
              KEEPFILTERS(VALUES('Employees'[Level10])),
              NOT(ISBLANK('Trips'[Average Active Trip Cost]))
            )
          )
        )
      ),
      KEEPFILTERS(
        FILTER(
          KEEPFILTERS(VALUES('Employees'[Level1])),
          AND(
            'Employees'[Level1] = "Axxxxx xxxxxs",
            ISBLANK('Employees'[Level1]) = ISBLANK("Axxxxx xxxxxS")
          )
        )
      )
    )

  The equivalent Pivotable report with the full hierarchy generated the following MDX:

  SELECT NON EMPTY Hierarchize(DrilldownMember({{DrilldownLevel({[Employees].[Full Reporting Hierarchy].[All]},,,INCLUDE_CALC_MEMBERS)}}, {[Employees].[Full Reporting Hierarchy].[Level1].&[Anthony Bates]},,,INCLUDE_CALC_MEMBERS)) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS  FROM [Model] WHERE ([Measures].[Average Active Trip Cost]) CELL PROPERTIES VALUE, FORMAT_STRING, BACK_COLOR, FORE_COLOR, FONT_FLAGS

  The Powerview report hangs whereas the Powerpivot is generated immediately. 

  Can anyone shed light on the performance issues, and is there a way to force Powerview to use MDX to improve query performance?  If not then we will have to look for a workaround for our queries and reports to calculate summaries across the employee hierarchy.

  Thanks

  Dan

  • Changed type Ed Price - MSFTMicrosoft employee Friday, September 13, 2013 7:08 PM We need the OP to answer questions in order to give an answer

  Monday, August 12, 2013 4:09 PM