On PerformancePoint Server 2007 Planning Features (Part 8)

Part 8 - Consolidation and Reporting

In Part 7 of this PPS Planning series of posts, we briefly covered how the PPS Add-in for Excel is used to enter data into a model, as well as review and approve such data (based on the assignment role of the user) as part of a scheduled cycle of assignments. In this long overdue (!) and final instalment in this series, we will take a glance at the role of associations and business rules in consolidating data for subsequent reporting.

Table 8.1 – Aligning Budget Activities to PerformancePoint Planning Tasks

8.1 Challenges in Consolidation

Consolidation of approved data contributions into a single location for financial reporting is necessary to provide a uniform view of a company’s plans. This exercise may be tedious for companies with an organizational structure that consists of many business units, product divisions or geographic locations; as you may recall from earlier posts, a PPS Planning application can be arranged into a hierarchy of model sites and subsites to reflect an organizational structure. Furthermore, although businesses may plan, budget or forecast for detailed conditions within a model subsite, decision-makers may only be concerned with higher-level figures in a less granular model. For example, in a company that sells a variety of products in several countries, stakeholders may only be concerned with summary revenue budget figures for specific countries without concern for how detailed product line targets contribute within each country. Meanwhile, product managers accountable for sales in each country can focus on approving more granular revenue targets for discrete products. Instead of resorting to duplication of effort in integrating data into both the detailed and summary models, we can take advantage of associations in PPS Planning that ultimately support management reporting in such situations.

Note: To be certain, the list of challenges in consolidation is far longer than those described here. The aim is to keep things brief as a function of covering PPS Planning at a high level.

8.2 Model, Dimension and Member Associations in PBM

In PBM, we have a dedicated workspace that allows us to configure associations that serve as conduits for moving and aggregating fact data between models. An association itself is expressed using three levels of detail: the model association, dimension associations within the model association and member associations within a given dimension association.

A model association (also called a model-to-model association) represents a coarse linkage between a source and destination model. Figure 8.1 shows the New Association dialog box that allows us to configure a source and destination model (residing in either a single model site or between model sites) for a model association. A model association also allows us to specify an Aggregation Function that indicates whether a Sum, Average, Min or Max function is perfomed on data as it moves into the destination model.

Figure 8.1 – New Association Dialog Box

Within a model association, a number of dimension associations are used to qualify the dimensions and membersets (remember: membersets are analogous to dimension hierarchies) that are involved between the source and destination in a model. Once a model association is created in PBM, we can access the dimension associations by clicking on the Dimension Association tab in the Associations Workspace. In Figure 8.2 we can create a dimension association for the Scenario dimension between both models by highlighting the Scenario dimension in each model and clicking on the Add Mapping button.

Figure 8.2 – Dimension Associations Tab in PBM

Note also that we may have additional dimension definitions that may not necessarily ‘line-up’ between models. Here, we must qualify the scope of members for an outstanding dimension—in a source model, this means that we include the tuples (or model cube cell intersections) of values that are aligned to the members to be included in the Aggregation Function. In a destination model, a single member can be specified to store the aggregated value (thereby performing a rollup of the source aggregated values).

Member associations are finely-grained pathways that allow us to quickly specify identical mappings between source and destination members that share a specific name, or, many-to-one mappings between a number of source members and a single destination member within a dimension association (this should not be confused with the scope of members we specify for outstanding dimensions within in a model association). We can also customize our mappings for situations where the source and destination member names are dissimilar.

Figure 8.3 – Member Associations Tab in PBM

In Figure 8.3, within the Member Associations tab, we can see the result of fully qualifying an identical mapping of all members of the Scenario dimension between both models, by simply clicking the Map Identical button.

The key to successfully implementing associations can be realized in how we have previously defined our dimensions and crafted our models. Upfront planning of the models to be used in a PPS Planning application is paramount; here, the power of shared dimensions can be realized as we can quickly map commonalities between models based on our knowledge of the members within a dimension.

The value of associations should become evident in situations where we may want to roll-up a number of fact values (in multi-dimensional space, aligned to specific dimension members in a model cube) in a source dimension, to a single member in a destination dimension (again, supplying a value that intersects on the member in the destination model cube). However, there are a few limitations to be aware of with associations. Firstly, associations do not support splitting or distribution of data from a tuple consisting of a single dimension member and value in a source model, to multiple dimension members in a destination model. In other words, one-to-many mappings in a member association are not supported in the current version of PPS Planning. Also, we cannot promote a percentage or fraction of the source aggregated value into the destination model with an association. Finally, an association will not remove scoped data from the source model once it has been allocated to the destination model; this may be ideal for situations where balances must be transfered from one model to another. In order to meet either of these requirements, we can implement one or more allocation business rules (performing either a PushCrossModelAllocation or PullCrossModelAllocation) that leverage the ALLOCATE or TRANSFER PEL (PerformancePoint Expression Language) statements. For completeness, an ALLOCATE statement only changes data in the destination model while a TRANSFER statement changes data in both the source and destination.

Using either approach (associations or allocation business rules), we are able to centralize fact data to support reporting from a single model. We can automate our process by creating a job (using the Process Management workspace in PBM) that executes the association or allocation business rule; this is ideal for allowing authorized users (most likely in an Approver assignment role) to execute the process manually from the PPS Add-in (Figure 8.4), thereby promoting approved data into the central model.

Figure 8.4 – Job Menu Items in the PPS Add-in for Excel (2007)

8.3 Reporting

After spending significant time in PBM, the typical BI developer might breathe a sigh of relief once the heavy lifting to get data into a central model for reporting is complete. Here, we have a few options for exposing our data for end-users.

We can take advantage of the simplicity in using the PPS Add-in for Excel. A report in this context is essentially the same as a form template (which is later instantiated for a given assignment), except that it does not allow data entry. In Figure 8.5, we can see a sample report that illustrates our ability to leverage conditional formatting features in Excel 2007.

Figure 8.5 – Summary Reporting with the PPS Add-in for Excel (2007)

Although we can publish reports from the PPS Add-in to SQL Server 2005 Reporting Services (SSRS 2005), the results may not always be what we expect since some reports may use MDX that cannot be rendered easily by SSRS 2005. Do take the time to review the Help file to learn more about these restrictions.

In my opinion, the more flexible, useful and reliable approach would be to switch gears and use the powerful PerformancePoint Monitoring and Analyzing (PPS-M&A) toolset. Here, we can surface data in a dashboard report (either with or without a scorecard) and implement further extensibility afforded by the PPS Monitoring SDK. Figure 8.6 shows the same information from Figure 8.5 (showing Budget and Forecast revenues along with Actuals) in a PPS Dashboard.

Figure 8.6 –Performance Reporting with PPS Monitoring Dashboard

Other options include using ProClarity 6.3 or PerformancePoint Professional Management Reporter (the latter of which is currently in beta at http://connect.microsoft.com/).

This concludes our high-level review of the PPS Planning feature set. To be certain, there is much more to the story on PPS Planning that can be found in the product documentation, at MSDN and TechNet, in the PerformancePoint forums, on the many informative blogs available (links to more than a few of these are listed in the right border of this blog) as well as in several books that will certainly emerge in the foreseeable future. In my opinion, the more you know, the further you go, so do take advantage of all the resources that present themselves on the subject of PPS Planning as they surface.

Thanks for reading, happy holidays, and good hunting with PPS Planning!

- Adrian Downes