locked
Powerview vs Powerpivot (Excel) hierarchy performance RRS feed

  • General discussion

  • 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

    Monday, August 12, 2013 4:09 PM

All replies