Friday, July 13, 2007

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

No comments: