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

On PerformancePoint Server 2007 Planning Features (Part 4)

Part 4 - Defining Business Rules

We continue our coverage of PerformancePoint's Planning Business Modeler (PBM), with an overview of business rules and how they apply to models we define in our PPS Planning applications.

Table 4.1 – Aligning Budget Activities to PerformancePoint Planning Tasks

4.1 Business Rules in PBM

Although it may be standard fare for virtually all Accounting and Finance professionals, potentially the most difficult concept for most of us new to PPS Plan to appreciate is how calculations and assumptions are actually implemented in a model. In PBM these actions are defined as business rules, which are the objects that provide us with opportunities to express assumptions, perform calculations, as well as shift balances within a model (entirely, or within a model sub-set, also known as a sub-cube) or among models (for consolidation purposes). Figure 4.1, below, shows where we are able to access business rules for a given model.

Figure 4.1 – Accessing Business Rules for a Model in the Model Workspace

PPS Plan business rules allow us to perform / automate tasks such as:

• assigning assumption data for a given subset in the absence of an assumption model, or, allocating said data across a number of models


• performing calculations dynamically on a subset of a model, for such purposes as computing share calculations within financial models (note: there is a pre-defined Financial Model with Shares Calculation model-type available in PPS Plan)


• intercompany reconciliations which can, for example, automatically balance (reconcile) differences between buyer and seller entities within an organization


• publish model fact data (read: new budget data) at the end of a budget cycle to destination applications such as SAP, or, with some creativity back into a PPS 2007 dashboard to reflect a modified or stretch target

Figure 4.2 shows how, with a right-click, we are able to create a new business rule while 4.3 illustrates the New Rule dialog box.

Figure 4.2 – Creating a new Business Rule within the Model Workspace

Figure 4.3 – New Business Rule Dialog Box

4.2 Business Rule Scope

Under the covers, a business rule can be implemented as an MDX script SCOPE function, helping to shape a sub-cube within a given model; again, the sub-cube specifies the domain within which an assumption value can be seeded or a calculation executed. Once a business rule has been defined, we can specify its scope by clicking on the Scope Editor button (see Figure 4.4). The Scope Editor that appears is shown in Figure 4.5

Figure 4.4 – The Scope Editor Button

Figure 4.5 – Scope Editor Opened for a Highlighted Business Rule

The Scope Editor, in my opinion, is a really powerful way to quickly craft a specific domain of the model relevant to a business rule. I would go as far to say (without having looked at the public CTP3 beta for SQL Server 2008... yet...) that a similar tool should be made available in Business Intelligence Development Studio for SSAS 2005/2008 development. The Scope Editor uses default members of existing dimensions associated to a model as a starting point, and, each dimension to be deemed 'in scope' for a business rule can be further qualified by clicking the ellipsis button to the right of a given dimension (as shown in Figures 4.6 and 4.7 respectively).

Figure 4.6 – Opening the Dialog to Qualify Dimension Members 'In Scope'

Figure 4.7 –Specifying Dimension Members 'In Scope'

Note: For the less technically inclined, learning MDX in general and SCOPE in particular without help may be difficult. If you aren’t already familiar with MDX, I highly recommend reviewing basic information on the function in SQL Server 2005 Books Online and spending some time with MDX Solutions, 2nd Edition by Chris Webb (et al), for further useful examples.

Business rules can also be implemented as T-SQL executable queries (stored procedures) which are then applied to data directly in the Application Database (described earlier in this series of posts) as opposed to the model (cube) itself. PBM provides PerformancePoint Expression Language (PEL) as a way to concisely define business rules. Once the scope has been established for the rule, a second click on the Scope Editor button reveals the Rule Expression editor (Figure 4.8) that allows us to complete the business rule (Figure 4.9).

Figure 4.8 – Opening the Rule Expression Editor

Figure 4.9 – Completing a Rule Expression

In Figure 4.9, we have an expression which performs a calculation, discounting actual values for specific members of the HR dimension by 2%, within the defined scope. A discount calculation like this can be leveraged by contributing analysts during input of budget data to the model.

4.3 Business Rule Types and Business Rule Sets

Metadata for business rules describes both type and implementation, and are grouped into rule sets which themselves are of a number of types (each of which having a specific purpose in PPS Plan). The type of rule set generally describes the type of rules that can be implemented within the set. PBM uses rule sets to process rules and handle execution results appropriately. As you may have expected, business rule definitions and execution can be secured by PPS Plan roles, and, may also be audited via PBM. Table 4.2 below lists the types of business rules available in PBM:

Table 4.2 – Planning Business Modeler Rule Types (Click image for a closeup)

Table 4.3 summarises the types of rule sets found in PBM (CTP2) as well as the types of rules they can include:

Table 4.3 – Planning Business Modeler Rule Set Types

Finally there are a number of system-defined rules which are baked into existing model types for handling relatively complex operations. Examples of such operations include handling semi-additive behaviour whilst aggregating values among members of an Account dimension, or, for cumulative (or rolling) period-to-date calculations.

Fundamentally, business rules in PPS Plan are designed to save time for an individual assigned to what-if analysis and budget preparation tasks. Once defined, finance intelligence jobs can be created which can automate the execution of certain business rules. Other business rule sets such as assignments can execute every time a model is (re)deployed (read: processed). System-defined rules can also be executed by such jobs. We will revisit jobs in a future article.

With our application and business model defined, we can move on to creating input forms for entering budget data as well as automating approval workflows. Implementing a budgeting cycle with PPS Plan begins in the next post, starting with an overview of the PerformancePoint Add-in for Office Excel 2007.

- Adrian Downes

On PerformancePoint Server 2007 Planning Features (Part 3)

Part 3 - Defining the Model(s)

In Part 2 of this series, we learned that PPS Planning provides two applications for defining and designing business models: Planning Administration Console (PAC) and Planning Business Modeler (PBM). While we glossed over PAC in the last post, this article continues with the conceptual PPS Planning tasks, focusing on the features available in PBM to help design business models for budgeting.

Table 3.1 – Aligning Budget Activities to PerformancePoint Planning Tasks

3.1 Planning Business Modeler (PBM)

With a look and feel reminiscent of the Scorecard Builder application in Business Scorecard Manager 2005 (aka. BSM Builder), PBM is the workhorse of PPS Plan allowing us to:

• specify the Model site we wish to build models in


• define Dimensions (and corresponding Member Properties)


• define Member Sets and Member Views


• define Models Sites and Models


• configure Roles and Users


• create Business Rules for performing calculations, allocating values as assumptions or exporting data


• implementing Process Management which controls assignments of Users or Roles for budget data entry (via Forms) against a specific model, and, automates approval workflows


• further automate either in- or out-bound data synchronizations

Visually, PBM is bound across the top region by a two basic menu bars, and is separated vertically by three main sections: the Workspace Browser to the left, the central Workspace Pane, and the Workspace Actions and Site Tasks menu panes to the right (See Figure 3.1). The Workspace Browser allows us to navigate our model site metadata at the summary level, by a specific model, dimension, forms (for budgetary inputs) and reports, process management (which helps us to manage specific task assignments and approval workflows during a budgeting cycle), business role definition, model security as well as association linkages between models for consolidation and distribution of data among models. The Workspace pane allows contextual management of a specific area of the model site (selected in the Workspace Browser), while the Workspace Actions and Site Tasks menus vary in function depending on the selections made by the user.

Figure 3.1 – PBM Model Site Summary Workspace

3.2 Models and Dimensions

In PBM, models are defined for a given model site in the Models Workspace (Figure 3.2). The distinction between a model and a model site needs to be clarified here. A model site, which contains metadata defining dimensions, member sets and member views, roles and security (known collectively as a business type library, which can be shared, although I’ll stick with the term “metadata” for simplicity), is used to define:

• one or more models, which are accessed via data entry forms and management reports


• process management associated indirectly to the model via forms( those being assigned to a specific role/user)

Model sites, as we learned previously in Part 2, begin with a root model site which usually represents an entire organization. Subsequent model sites can be defined which inherit specific shared metadata from the root model site, or another model site. It follows that a model sub-site inherits metadata from a parent model site.

Figure 3.2 – Creating a New Model in the PBM Models Workspace

Most every multi-step operation is handled in PBM with user-friendly wizards. Figure 3.3 illustrates the Create a Model Wizard which follows on unsurprisingly from clicking ‘Create a Model’ as shown in Figure 3.2 above.

Figure 3.3 – PBM Create a Model Wizard (CTP2)

For those of you relatively unfamiliar with OLAP based concepts (this time, apologies to the BI practitioners out there), a dimension is a collection of information aligned to a given measure (dollar figure, item count, percentage, etc.) in one or more facts. Dimensions themselves can be arranged in a hierarchy of multiple levels, or expressed as a flat list without any levels. The intersections of multiple dimensions’ levels and members with the fact measures ("data points" in multi-dimensional space) are essentially what constitute a cube. Again, in PPS Planning, a model is manifested as a cube behind the scenes. Dimensions also expose member properties which further qualify a given member of a dimension list or hierarchy. Typically, a member set may be defined solely by a sub-set of a dimension hierarchy, while dimension members with common member properties can be grouped together into a sub-set of dimension members, known in PBM as a member view. Figure 3.4 illustrates the Dimensions Workspace in PBM, where we are able to define and import data for our dimensions.

Figure 3.4 – Creating a New Dimension in the PBM Dimensions Workspace

The models defined in PBM can satisfy either simple or complex budgeting requirements; in the latter case, allowing a user to progressively add more dimensions (or member sets) as well as robust calculations for currency translations or consolidations.

Out of the box, PPS Plan provides a number of stock pre-populated dimensions for handling business processes, exchange rates, consolidations (for sharing of calculations between models), as well as other empty dimensions such as Time, Scenario (a de facto standard dimension for budgeting) and Account (reflecting hierarchical items in a general ledger) and Currency. PBM also has a number of pre-defined model types to support basic budgeting requirements with stock Scenario and Time dimension types (a Generic Model) as well as models which support assumptions that can be applied to a given model site, or shared across multiple model sites (Global Assumption Model).

Assumptions, for those who aren’t familiar with the use-case, represent an analyst or manager’s best attempt to predict the future in the absence of past data; while bias does play a certain role in this respect, from a PPS Plan perspective, assumptions can serve as baseline data for modeling.

More complex model types are available for tracking currency exchange rates (an Exchange Rate Model) and financial models for consolidation. It follows that some of the stock dimensions, such as Exchange Rate appear in stock model types like the Exchange Rate Model.

In order to support the input of budget data into our models (which we shall cover later in this series of posts), we will need to define specific business roles and assign them to a given model in question. Figure 3.5 illustrates the Security and Roles workspace in PBM, which allows us to perform such tasks.

Figure 3.5 –The PBM Security and Roles Workspace

Figure 3.6 –Defining a Business Role

3.3 Business Roles in PBM

Business roles can be configured to satisfy flexible groupings of users from the Master User List, which we define in the PAC. One can easily assign specific users (using the dialog box which appears in Figure 3.7 after clicking the ‘Add or Remove Users for this Business Role’ hyperlink in the Workspace Actions and Site Tasks pane) to “Contributor”, “Reviewer” and “Approver” groups (or whatever works for your company). As an example, you may wish to delineate your models by key business driver or budget areas such as Purchasing, Marketing Expense or Capital Expenditure. Thus, a business role for purchasing budget contributors could be given the imaginative name “Purchasing Budget Contributors”.

Figure 3.7 –Adding Users to the Business Role

Figure 3.8 –Specifying Model Access for the Current Business Role

Once we have used PBM to define our models for budgeting, we need to apply specific business rules to the model which determine how model fact data may be calculated, allocated and/or published. Next time, we will take a closer look at business rules in PerformancePoint PBM.

- Adrian Downes