Friday, 15 August 2008

Performance Issues with PPS Management Reporter - Update

After months of testing PerformancePoint Server Management Reporter in it's current state, I have concluded that it will not handle the volumes that our company requires. It is way to slow and will just frustrate the business. The problem lies with the whole design of the system. I did a full analysis of the MDX code and how the system operates and found that the client (on your machine) sends an MDX code string to the Analysis Server for every combination of lines in your report to the entities specified in your reporting tree. So if you have a 50 line report and you are running it for 10 entities you will send out 500 MDX queries, one at a time. As you can imagine this makes my ZX81 (with the 64Kb Ram Pack) look like Usain Bolt pumped up on steroids.

Do not despair, all is not lost. I had a very positive conversation last night with some members of the team working on this issue. I suspect that we could see two positives in the near future. The first being a MASSIVE improvement on SQL Server 2008. PPS should be certified to work on SQL Server 2008 by SP2 which is planned for December 2008. Another idea kicked around is changing the architecture to a service type environment on the server and use multi-threading and the power available. I would guess that this type of change would only be done for V2, so let's wait and see. As soon as I get my grubby paws on a copy of SP2 to test I will post an item on performance improvements.

- Paul Steynberg


DimitryT said...

As you may know MR deployment consists of two parts:
one is the core application that allows you to define reports, users, etc.;
the second is the connector (or provider in techie talk) to your financial data repository.
The implementation of the connector is a key to how well the application is performing. The provider framework allows to connect MR to basically any financial repository (relational or multi-dimensional) as long as it knows about GL account, calendar, set of books and optionally other dimensions. So with a bit of more advanced logic code for the connector to your system can be/should be modified to consolidate DB queries. The framework is smart enough to then deploy them to the correct row/column places. It is totally unnecessary and wasteful to issue a database query for basically each cell of your report.

Paul Steynberg said...

Dimitry, we agree with your summary, however the connector to the PPS model is a standard issue with MR and our policy is not to mess with code where we do not have to. We would rather Microsoft address the issue internally so that we have proper support going forward.