Preview of PerformancePoint 2007 Server (Microsoft)

THIS PAGE REPRESENTS ONLY A VERY SHORT EXTRACT FROM THE FULL PREVIEW.

TO VIEW THE FULL PREVIEW YOU CAN PURCHASE THE PREVIEW INDIVIDUALLY OR PURCHASE AN ANNUAL SUBSCRIPTION TO THE OLAP REPORT WHICH ALLOWS ACCESS TO ALL OLAP REPORT CONTENT.

Calculation functionality

PerformancePoint has to work around Analysis Services’ well-known query performance problems in planning and other financial applications. These arise from the fact that in conventional Analysis Services cubes, MDX calculations all happen at run-time.

Analysis Services 2005 does include some server caching, but most MDX scripts are executed on-the-fly. This has the advantages of fast cube processing and impressively compact cube sizes, but the disadvantage of slow and variable query performance in models with complex calculations that go beyond aggregations. For example, allocations are easy enough to program in MDX, but are inefficient if performed after the cube is aggregated. This is because any calculation that include the aggregated items must then also be performed on-the-fly, which can include much of the model. Furthermore, though MDX is a strong (if non-intuitive) multidimensional calculation language, there are other calculations for which MDX is less suitable than SQL.

Microsoft has tackled these two problems by adding two new calculation options in PerformancePoint, MdxQuery and SQL, and yet another new language in which to describe the rules, PEL (PerformancePoint Expression Language). Of course, it still supports conventional post-aggregation MDX scripts, which it refers to as MdxScript.

• MdxQuery rules are executed by the Analysis Services MDX engine at leaf level (ie, pre-aggregation), but the results are stored back in the relational fact table. They use data within the cube and can include the results of conventional MDX scripts, but are unlike normal MDX scripts in that the results of MdxQuery rules are exported back to the fact table where they persist in a non-volatile form. They can subsequently be used in cubes without having to be re-processed. These are procedural rules that are only run during model reprocessing or when explicitly invoked. A typical use of MdxQuery rules would be allocations.
• SQL implementations are run as stored procedures, using the SQL engine, and operate using fact table (leaf) level data. They therefore cannot take advantage of any calculations performed by MdxScript rules or cube aggregations. They are best used for bulk processing of simple calculations, such as generating an initial forecast by adding 10 percent to last year’s values. In such cases, they can be much faster than MDX calculations, and the results, like MdxQuery calculations are non-volatile. However, the range of calculations is more limited, and cannot include many MDX or PerformancePoint functions. Some other functions are supported with limitations.

PerformancePoint already includes a large number of pre-defined templates to make it easier to build rules. We expect the number to rise, based on user requests to Microsoft. They come in a number of categories, including:

• Cumulative values (YTD, half-year to date, moving average, moving total, etc). The results of these calculations would be held in members of the TimeDataView pre-defined dimension, and they take account of the aggregation method for each account. Such rules would be implemented as MdxScript.
• Variances (from last period, from last year, between two scenarios, etc). The results of these calculations would be held in members of the Scenario or TimeDataView pre-defined dimensions, and they take into account whether positive variances are regarded as favorable (eg, revenues) or unfavorable (eg, costs) for each account. Such rules would be implemented as MdxScript.
• General financial calculations (eg, NPV, IRR, average daily balance). The results of these calculations would be held in members of the Account pre-defined dimension, and implemented as Native MDX Scripts ie, not in PEL).
• Depreciation calculations (straight line, declining balance, double declining balance, sum-of-years). These use Excel functions which must be installed on the PerformancePoint server.
• Financial intelligence processes

PerformancePoint also has specific pre-built functionality for consolidation (including performing eliminations for investments in subsidiaries, minority interests, intercompany transactions, etc), currency conversion, intercompany reconciliation, export of results, etc.

Analysis Services data write-back is also too slow for serious planning applications with more than a handful of users. Like OutlookSoft, Comshare and Clarity Systems before it, Microsoft has had to implement direct write-back to a relational fact table, rather than using Analysis Services’ standard write-back mechanism. This is still not going to be as fast as writing back directly to a MOLAP cube (on disk or in RAM), but will be much faster than the default Analysis Services method.

THIS PAGE REPRESENTS ONLY A VERY SHORT EXTRACT FROM THE FULL PREVIEW.

TO VIEW THE FULL REVIEW YOU CAN PURCHASE THE PREVIEW INDIVIDUALLY OR PURCHASE AN ANNUAL SUBSCRIPTION TO THE OLAP REPORT WHICH ALLOWS ACCESS TO ALL OLAP REPORT CONTENT.