Management Reporter - Fixed or Not Fixed, That is the Question

For those of you following my Management Reporter performance tragedy here is Act II. I received my QFE . Now it sounds ironic that I would be experiencing a performance issue with something called PerformancePoint, but what is more ironic is that the fix did not really fix the problem. I have seen a marked improvement in the report generation times BUT it is still not good enough.

Most of the reports halved in the time taken to run. A report with a large reporting tree did not return in the previous version (canned after 8 hours) but took 23 minutes in the "fixed" version. So the gains are excellent but how does this stack up to the data. Not well, the 23 minute report only needed to fetch 3310 rows of data from the underlying PPS cube. I ran the report a number of times in succession, hoping that the trick of "warming" the cache might be my salvation. Performance increases were seen and this report took 12 minutes to run. So from not being rendered at all to just 12 minutes means that the people at Microsoft did some serious work. Now from a technical perspective one has to admire the effort. From an end users perspective I still say "toooooooo sloooooooowwwww".

Surely it cannot take 12 minutes to return 3310 rows of data from a technology such as OLAP which was designed for fast and efficient retrieval. So what is the problem? In order to make sense of this I profiled the cube being connected to by MR and what I found is quite interesting.

Reports are made up of a combination of rows, columns and then reporting trees. It seems that for every row in the report an MDX query is built to return the required data and then fired off to the server for each entity in the entity reporting tree. Here is an example of the MDX query. I have replaced the parameters with the actual member names. They follow the query in the profiler in XML format. Now take note of the red code which is the entity reference.

WITH
-- Column Sets and Members
-- Columns General: Define a set and calculated member for each unique filter criteria across all columns.
SET [Criteria480369GLScenarioCriteria] AS
{
([Scenario].[All Members].[Actual])
}
SET [Criteria480370GLTimeDataViewCriteria] AS
{
([TimeDataView].[All Members].[YTD])
}
SET [Criteria480371GLTimeCriteria] AS
{ [Time].[Financial Calendar].&[772007013] }
MEMBER [Time].[Financial Calendar].[Criteria480371GLTimeCriteria] AS SUM ([Criteria480371GLTimeCriteria])
SET [Criteria480372GLCurrencyCriteria] AS
{
([Currency].[All Members].[ZAR])
}
SET [Criteria480377GLScenarioCriteria] AS
{
([Scenario].[All Members].[Budget])
}
SET [Criteria480378GLTimeCriteria] AS
{ [Time].[Financial Calendar].&[772008025] }
MEMBER [Time].[Financial Calendar].[Criteria480378GLTimeCriteria] AS SUM ([Criteria480378GLTimeCriteria])
-- Column46363
SET [Column46363] AS
{
( Criteria480369GLScenarioCriteria, Criteria480370GLTimeDataViewCriteria, [Time].[Financial Calendar].[Criteria480371GLTimeCriteria], [Account].[DebitCreditMemberId].[All], Criteria480372GLCurrencyCriteria )
}
-- Column46364
SET [Column46364] AS
{
( Criteria480377GLScenarioCriteria, Criteria480370GLTimeDataViewCriteria, [Time].[Financial Calendar].[Criteria480378GLTimeCriteria], [Account].[DebitCreditMemberId].[All], Criteria480372GLCurrencyCriteria )
}
-- Column46365
SET [Column46365] AS
{
( Criteria480369GLScenarioCriteria, Criteria480370GLTimeDataViewCriteria, [Time].[Financial Calendar].[Criteria480378GLTimeCriteria], [Account].[DebitCreditMemberId].[All], Criteria480372GLCurrencyCriteria )
}
-- Row Sets and Members
MEMBER [ValueMember] AS [Measures].[Value]
MEMBER [ValueMemberWithSignReversal] AS [Measures].[Value] * IIF([Account].[MemberId].CurrentMember.Properties("DebitCreditMemberId") = "Credit", -1, 1), NON_EMPTY_BEHAVIOR=[Measures].[Value]
-- Row 61640
MEMBER [61640] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[7XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA46])
)
-- Main Select Statement
SELECT
{ [Column46363], [Column46364], [Column46365] }
PROPERTIES MEMBER_CAPTION ON COLUMNS,
NON EMPTY
{ [61640] }
PROPERTIES MEMBER_CAPTION ON ROWS
FROM [FinancialBudgets]

This code returns 1 row of data.

So if you have 60 entities in your Reporting Tree Definition this code is going to run exactly as it is 60 times with only the row number and entity changing. So why not modify the MDX and send only 1 query through per line and modify the row set to include ALL entities? I tested my theory and modified the code with 3 additional entities. The run time was the same but it returned 3 rows of data.

Here is the part that is amended:

-- Row 38186
MEMBER [38186] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[3XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA62])
)
MEMBER [38187] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[3XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA63])
)
MEMBER [38188] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[3XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA64])
)
-- Main Select Statement
SELECT
{ [Column19387], [Column19388], [Column19389] }
PROPERTIES MEMBER_CAPTION ON COLUMNS,
NON EMPTY
{ [38186],[38187],[38188] }
PROPERTIES MEMBER_CAPTION ON ROWS
FROM [FinancialBudgets]

I am curious as to why MS has elected to fire 1 MDX query per report row for each entity and have put the question to them.

I did notice one additional feature with the hotfix. The "number of records processed" counter on the "Generate Report" screen now shows the count in 100 increments as the rows are retrieved. In the previous version you would have to wait until the end of the report for the total only so you had no idea of the progress.

I will report back once I have more information from Microsoft.

- Paul Steynberg