Scott Larson

Computing Financial Ratios In PerformancePoint M&A

Recently a client of mine had a need to display some financial ratios in a PerformancePoint dashboard. These were relatively standard ratios, yet the source data from which these ratios were calculated (a SQL Server Analysis Cube) did not pre-calculate these ratios for us. While it was decided ultimately these calculations should be done at the cube level, in the interest of time, we realized that the PerformancePoint KPIs would have to do the calculations for us, at least in the short term.

Unfortunately there isn't a very business-user focused method of doing calculations within a PerformancePoint KPI. You can't simply select your Current Assets measure and divide by your Current Liabilities measure. Instead you must use an MDX tuple formula. I really hope that eventually some sort of expression builder is available for these situations, but in the mean time, here are the steps to create a ratio based on balance sheet data in the Adventure Works DW database.

Create an Adventure Works data source, and point it to the Finance cube in the Adventure Works DW Analysis Services database. (It's actually just a Finance perspective but that's another blog)

Create a new KPI, and edit the data mappings for the Actual "metric" by clicking on the link

Click "Change Source" and select the Adventure Works source you just created

Click on the "Use MDX tuple formula" and type in the appropriate MDX formula . (you DO know MDX, right?)

Note: For demonstration purposes the above MDX statement reference members by name, not key. In a real implementation it would be wise to reference them by ID. By the way, for those that care, this is my favorite MDX resource: http://www.databasejournal.com/features/mssql/article.php/1495511.

Add this and any other ratios to a scorecard, add the scorecard to a dashboard, and deploy the dashboard.

I'm beginning to suspect that the use of PerformancePoint, at least in it's early releases, will require at least functional knowledge of MDX and PerformancePoint Expression Language (PEL) for PerformancePoint Planning, neither of which are exactly business-user friendly.   That being said, many of these types of calculations will be similar, so it's not unrealistic for a very data-savvy business user to be able to handle some of these expressions without TOO much I.T. assistance.

posted on Thursday, November 08, 2007 7:43 PM