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 7)

Part 7 - Entering Budget Data and Managing Assignments

In Part 6 of this series, we initiated an assignment workflow, enabling our users to contribute, review and/or approve data entry in response to notifications. In this short post, we continue our journey through a PPS Plan cycle, using the PPS Add-in for Excel to submit or review budget data within a model, and, managing the state of the data-entry or review assignment with PPS Planning Business Modeler (PBM).

Table 7.1 – Aligning Budget Activities to PerformancePoint Planning Tasks

7.1 Entering Budget Data

Back in Excel, a contributor is able to see his/her new assignment in the PerformancePoint Action pane. Clicking on it retrieves an instance of the form template from the Planning server. In Figure 7.1 below, we see the open form associated to our assignment, with a region of yellow indicating where we may insert data.

Figure 7.1 – Data Entry Region in Matrix

Although the primary purpose of data entry is for submitting budget data, recall that we can also use data entry in concert with business rules to perform what-if analysis; here, setting up a review-only assignment would be ideal for such situations.

Another collaborative feature carried forward from Business Scorecard Manager (BSM) 2005 is the annotation. Annotations provide users with opportunities to further qualify a specific value with commentary-- in BSM 2005, a user with sufficient rights could add an annotation to any cell in a scorecard. In the PPS Add-in for Excel, annotations are aligned to a data entry cell, and the annotations themselves are stored within the Application Database (SQL Server 2005). On subsequent examination of data entry, for example, a reviewer may gain further insight on a specific budgetary value, or, if the assignment is defined appropriately, then a reviewer may add further commentary as well. Figures 7.2 and 7.3 illustrate the basic steps towards creating an annotation: right-clicking on the yellow data-entry cell, selecting ‘Annotations’ from the context menu and entering text for the annotation.

Figure 7.2 – Selecting the Annotations Item from the Context Menu

Figure 7.3 – Entering Text for a New Annotation

A subtle yet handy feature associated with annotations is the annotation view of the matrix. When this view is enabled, cells with annotations are shaded, helping a reviewer to quickly identify where a contributor has added a comment (see Figure 7.4).

Figure 7.4 – Annotations View Enabled for the Input Form

Once satisfied with data entry, our contributor needs to publish either a draft or final submission of their work to the Planning server. Draft submissions allow for subsequent modifications by contributors, while final submissions prevent such users from submitting further changes via the input form. Users in reviewer and approval roles can also publish their work to the Planning server, with different options to mark their submissions as reviewed or approved. Figure 7.5 shows how the user may submit contributions using the PerformancePoint Action pane.

Figure 7.5 – Submitting Contributions to the Planning Server

7.2 Managing Assignments

The “contribute/examine in Excel and submit to PPS Plan” paradigm changes the state of the assignment on the Planning server, causing successive workflow tasks to execute. We can use PBM to monitor progress of an assignment as well as pre-emptively purge, stop or restart it where necessary. PerformancePoint Planning CTP2 documentation describes six states (Table 7.2) an assignment may be in at any given time during a cycle.

Table 7.2 – Six States of an Assignment in PBM

These assignment states can be managed from the PBM Process Management workspace using the ‘Available Actions’ menu item covered in Part 6. There are nine actions in total (CTP2) which can be performed against a given assignment, depending on the PBM role of the user. Table 7.3 aligns these actions to said roles.

Table 7.3 – Available Assignment Actions by PBM Role

This shorter post rounds out the overall discussion on implementing a budget cycle. All the previous topics in this series broadly describe how PPS Planning may be used to design and develop a business model covering a specific facet of an organisation.

In a previous post adopting a 1:1 approach was suggested, using a specific business model for a given budgeting domain; here, the input form we use as the key interface to a business model reinforces this view towards optimizing the user (contributor/analyst) experience. Nevertheless, most (if not all) businesses require a master budget from which budgeted financial reports are generated, and, this situation often requires consolidation once supporting budgets are complete (e.g., for sales, direct labour, raw materials purchases, capital expenditure, etc.). In large diversified companies, consolidation is a more complex exercise often involving additional intercompany and share-allocation processes-- fortunately, as we addressed in Part 4, PPS Plan offers variety of business rule sets and flexibility in customizing business rules to support budgeting needs from the small business to the large enterprise.

In the next and final post of this series, we will briefly revisit the role of business rules and rule sets in consolidating individual budgets and a few options at our disposal for creating financial reports.

- Adrian Downes

On PerformancePoint Server 2007 Planning Features (Part 6)

Part 6 - Implementing Process Management

Once we have defined and designed business models as well as our input forms, the next step towards implementing a budget cycle is to get our analysts and contributors involved. This post provides an overview of how we use PPS Planning Business Modeler (PBM) to implement workflows relevant to budgeting.

Table 6.1 – Aligning Budget Activities to PerformancePoint Planning Tasks

A budgeting period is defined in PBM Process Management with cycles (of assignments) and jobs. In PBM terms, a cycle is basically a scheduled time frame which contains one or more tasks. An assignment involves the association of a user (which we have configured to a specific role) to a given input form (itself associated to one or more business models), to perform a required data-entry or review task. A PBM job is a system-oriented task for updating data within a model, or moving data between models. Jobs become especially useful if we need to centralize approved data from a number of models before running consolidated financial reports (budgeted financial performance, budgeted financial position, budgeted cash flow, etc.).

Figure 6.1 – Accessing the Process Management Workspace in PBM

6.1 Cycles

In order to setup one or more assignments for a user, we need to first define a cycle. Figure 6.2 illustrates the Create a Cycle wizard that launches from the hyperlink shown to the right in Figure 6.1.

Figure 6.2 –PBM Create a Cycle Wizard

The wizard requires an owner, with the appropriate authority (as defined in PBM), a specific business model, as well as a data entry scope for the cycle. Data entry scope is an important feature in PBM which allows us to control the range of time in the business model (delineated by a time/date/calendar dimension) in which budget contributions (and subsequent reviews) can be made. We also have the ability to define our data entry scope in terms of a current period reference relative to the current period (here, we focus on the level in the time dimension, such as a month or quarter) with an additional offset that adds equivalent blocks of time to the current period. For example, if our business policy is to prepare budgets three months in advance, then we can specify an offset of 1 month to a current period of September 2006 which results in an October 2006 starting range for data entry in the model. We can use the offset to define the ending range as well-- an offset of 3 months from the current period results in a three-month period ending in December 2006. The key benefit to using ranges based on a current period (which we define in our time dimension) and offsets is that the current period can be dynamic. Thus, if our cycle is recurring, the data entry scope becomes a sliding window for budget contributions.

Figures 6.3 through 6.5 illustrate how we arrive at the dialog that specifies a current period reference for data entry, from the Create a Cycle Wizard.

Figure 6.3 – Editing the Starting Data Period

Figure 6.4 – Adding a Current Period Reference

Figure 6.5 – Specifying the level and offset for a Current Period

It is important to note that data entry scope and scheduled time frames for a cycle are not the same: we may, for example choose to schedule a cycle for just the first week of our current period (August 2007), wherein we submit data for the quarter starting in October 2007. Also, when we define out time (date/calendar) dimension, we may also specify that the current period for the dimension may be dynamically derived, thereby helping us to implement a rolling period for budgeting.

The wizard also requires that we specify a member of our Scenario dimension for budgeting. For some, budgeting may require multiple passes within a given time frame in the model; ensuring your Scenario dimension has additional budget “pass” members affords greater flexibility in modelling for a variety of circumstances.

After we specify our scheduled time frame for the cycle, we are prompted by the wizard to specify whether notifications to users with assignments within the cycle are made. You may recall from our coverage of the Planning Administration Console that we configured a master list of users. Entries in this list optionally include email addresses which PPS Plan uses to issue messages to contributors when a new assignment is created or, to reviewers when budget data has been submitted.

Figure 6.6 – The New Cycle in the Process Management Workspace

In Figure 6.6 we can see our defined cycle in the Process Management workspace. Note that the Status of the cycle is set to ‘Running’. If you don’t see this state of the cycle after finishing the Create a Cycle wizard, either hit F5 or refresh the PBM workspace browser. We can also use PBM’s ‘Available Actions’ Process Scheduling Task menu item to start and stop cycles, as well as completely purge a cycle and any assignments it contains from the Planning Server

6.2 Assignments

Once a cycle is defined, the owner of the cycle can create assignments specifying:

• users who will enter and submit budget data
• users who will review and/or approve data entry
• deadlines for submissions and approvals

The key to cycles and assignments is the workflow running behind the scenes. An assignment to a user for data entry, for example, has a different state depending on whether the user has submitted data via a given input form. Once a user submits an assignment, the process scheduler changes the state of the assignment and evaluates the next portion of the defined workflow; as we just covered, if notifications are enabled, then the next person in the workflow will receive a message to either contribute their own data, or review data that someone else entered.

Selecting the ‘Forms Assignment’ tab of the Process Management workspace for our newly created cycle, and clicking the ‘Assign Forms’ hyperlink button in the right-hand Workspace task pane (shown in Figure 6.7) reveals the ‘Assignment Definition Creation Wizard’ (Figure 6.8).

Figure 6.7 –Creating a Forms Assignment for the Cycle

Figure 6.8 –The Assignment Definition Creation Wizard

The Assignment Definition Wizard helps us to identify either one or more contributors on a given assignment, or, a group of contributors arranged in a submission hierarchy. A submission hierarchy represents a collection of contributors and approvers which can be defined to match approval processes among a larger number of users. Here, matching a submission hierarchy to a subset of your company’s organisation chart (for example, within a finance department where managers approve senior analyst contributions, who in turn approve junior analyst contributions) is a convenient way to delineate start-dates and deadlines according to the hierarchy itself. Using the submission hierarchy, you can specify the number days required, at a given level, to complete an assignment (the duration of the assignment), then stagger the start times according to the budgeting period-- this creates a critical path of sorts between the earliest start-time (at the lowest level of the hierarchy of contributors), the delta of durations from level to level, up to the topmost approver.

After selecting our approval chain for an assignment, we can include the input form template we configured and published to the PPS Planning Server. Clicking on the ellipses button in the ‘Form’ region of the wizard’s Data Submission page exposes a dialog box (Figure 6.9) where we can select the appropriate form for the assignment. Although the documentation indicates that more than one form may be used for an assignment, I tend to believe that keeping a 1:1 ratio between assignments and forms may well be simpler and easier to manage.

Figure 6.9 –Selecting an Input Form Template for the Assignment

After selecting the form and defining the starting- and ending-dates for the assignment, we then indicate whether our workflow will include reviews, approvals, reviews and approvals, or, alternately neither reviews nor approvals. Selecting the lattermost option brings us to the end of the wizard, while any of the other options prompt us to specify PBM-defined users to participate in the desired capacity. Reviewers and approvers may, at our discretion, edit submissions from contributors, and we can also specify deadlines for reviews and approvals (see Figure 6.10); this is necessary if we choose not to use a submission hierarchy but still wish to set deadlines for non-contributors.

Figure 6.10 –Specifying Review Conditions for the Assignment

Finally, with our assignment defined, we need to save changes to our model site before starting our assignment. Although we have defined the start-time, in CTP2 the assignment definition still needs to be instantiated (the instance itself started) in order for users to contribute/review/approve as well as for the workflow to perform its magic. For the BI practitioners out there, this is analogous to enabling a SQL Server 2005 job to automatically start at a specific time.

Figure 6.11 – Getting Ready to Enable the Assignment

As shown in Figure 6.11, highlighting the assignment and clicking ‘Available Actions’ in the workspace task pane opens a dialog box, allowing us to start the assignment instance (Figure 6.12).

Figure 6.12 – Starting (Enabling) the Assignment Instance

As we covered earlier in this post, once the assignment is up and running, the lead-off contributor may be notified of their assignment via email-- provided this was previously configured in the PAC Master User List. Furthermore, you can supply a link to the assignment (which automatically opens Excel to display it) in an email, or, you can use the link in a SharePoint web part that can be seen on a personalised MOSS site or WSS 3.0 page.

With the assignment enabled, we may proceed to enter our budgetary information. In Part 7 of this series, we will return to the PPS Add-in for Excel for data entry as well as review our options for managing assignments via PBM.

- Adrian Downes

On PerformancePoint Server 2007 Planning Features (Part 5)

Part 5 - Designing the Input Form

So far, we travelled all the way from digesting basic budgeting concepts, to a high-level overview of the two key tools we use to configure a PPS Planning application as well as to design business models and rules. In this post, we move further along our roadmap, stopping long enough to consider a third application we need to use to interact with our business models.

Table 5.1 – Aligning Budget Activities to PerformancePoint Planning Tasks

Input forms in PPS Plan are central to the non-technical, end-user experience in two major ways:

• As an avenue for controlling budget contributions by key people: here, data is input into our business model and any relevant business rules that perform dynamic calculations are executed. The submitted data is then stored in the business model for subsequent approval.


• As an opportunity for additional what-if analysis: if our business rule sets are set up appropriately, then we can leverage calculations stored in a given business model, in order to see how our input data impacts related values.

5.1 Introducing the PPS Add-in for Excel

Traditionally, business and financial analysts have relied upon spreadsheets for budget data entry as well as detailed analyses. In PPS Plan, the way forward sticks with the familiar Microsoft Excel environment to perform said tasks. PerformancePoint Planning includes a PerformancePoint Add-in for Excel which essentially provides an interface for creating and using input forms and management reports, as well as for analysing data and running certain server jobs.

Note: Although the PPS Add-in for Excel can be used by either Excel 2003 or 2007, we’ll focus our attention on the latter version. Rest assured that the Add-in for Excel 2003 is well documented in CTP2.

Once the PPS Add-in is installed, we can see a new PerformancePoint tab in the Excel 2007 Ribbon as shown in Figure 5.1 below. Bear in mind that these screenshots reflect a CTP2 build.

Figure 5.1: PPS Add-in Ribbon in Excel 2007

Once the tab is selected we are able to connect to an existing PPS Planning server (with the Connect menu item on the left), or we may configure a new connection (as shown on the right). When the connection is established with the server, a PerformancePoint task pane becomes visible and we may begin designing our form. Figure 5.2 shows us that we may begin this task by clicking on the Reports icon in the Authoring ribbon menu group, and selecting New from the context menu. A dialog box appears, prompting us to select the PPS Planning Application that contains our target business model.

Figure 5.2: Creating a New Form

Earlier CTP builds of the PPS Add-in showed a “Form” menu item in the Authoring group-- this appears to have changed with the CTP2 release.

5.2 Enter the Matrix

From the PerformancePoint action pane, we can choose to craft a matrix interface for our form either manually, or, using a Report Wizard to speed things along. A matrix form is reminiscent of the Excel PivotTable style interface some of you may have worked with in the past for ‘slicing and dicing’ SSAS cube data. A typical matrix form is bound by a row, column and filter axes for specific cube dimension members, with a central region for displaying data values (measures).

Figure 5.3: Creating a New Matrix

Clicking on the New Matrix button as shown in Figure 5.3, we may then select the business model our form will interface with. We may also choose whether the form will be used for data entry (for both what-if analysis as well as budget entries), support drill-through (which shows detailed data behind a given aggregate value displayed in the form) as well as cosmetically indent row members depending on their hierarchical position within a dimension or member set (See Figure 5.4). Data entry, as a point of interest, is actually supported by the write-back capability of the target business model’s underlying SSAS 2005 cube.

Figure 5.4: Options for the New Matrix Form

5.3 Selecting Dimension Members

Once the options have been defined for the new matrix form, we can then select specific dimension-hierarchies we wish to include on the form. Here, the value in using member sets may be realized-- in situations when a dimension has too many members, a member set can afford us a subset of the larger dimension, leading to a simpler, easy-to-read (and work with) form. More to the point, using member sets help us to isolate specific regions in the business model we care most about for a particular round of budget planning or analysis.

Figure 5.5 depicts the Select Dimensions for Matrix dialog where we can allocate dimension hierarchies to column, row or filter axes. The PPS Add-in also provides further control of dimension member participation, by allowing us to further filter for specific dimension members within the hierarchies or member sets we have selected for our matrix form.

Figure 5.5: Selecting Dimensions for the New Matrix Form

Beyond filtering, additional options exist that help us to work quickly with large dimensions for our form. We are able to choose a member of a particular dimension and automatically include said members’ immediate children (those related directly beneath a given member in a hierarchy), descendants (all members in the hierarchy which either relate directly as a child or indirectly as a grandchild, great-grandchild, etc.), siblings (those dimension members sharing the same parent as our selected member), more complex ancestral lineage (e.g., leaf-levels-only, or, only bottommost members of a hierarchy relative to either our selected member or the level the member occupies in a dimension hierarchy), as well as fixed ranges of members within a given dimension level.

Since the majority of budgeting is conducted with a period of time in mind (or as policy), the ability to dynamically plan for up-coming periods, without having to create a new form becomes essential. The PPS Add-in also provides flexibility in defining dynamic ranges of time for our matrix form (Figure 5.6), ensuring that our forms can be reused without any fuss. For those of you who need to satisfy rolling forecast requirements, this feature is ideal.

Figure 5.6: Setting a Dynamic Range

With the underlying attribute-based logic of our business model, we can take advantage of member properties as a way to overcome sometimes cryptic dimension member names. The PPS Add-in provides the option to display more user-friendly member property values instead of default member names on a given matrix axis. Naturally, we would need to use Planning Business Modeler in order to include member properties within the dimensions for our models.

5.4 Matrix Styles

In CTP2 of the PPS Add-in, you need to highlight your matrix (or click on the Select All top-left corner of the worksheet) to highlight the entire grid before being able to apply matrix styles: these are the finishing touches you may wish to include in your form to improve readability with your corporate colours (Figure 5.7).

Figure 5.7: Selecting a Matrix Style

Figure 5.8 below shows a completed and very simple matrix form, with a few members of our Account dimension (shown on the rows axis) we may consider for basic expense budgeting. On the columns axis we have a few quarter-level members of the Time dimension along with the Budget member of the Scenario dimension. We also have the Washington state member (from the Regional hierarchy of the Entity dimension) selected for our page filter axis, although we can optionally hide this filter if we wish.

Figure 5.8: Basic Input Form

At this point, we have essentially created what CTP2 documentation calls a form template. A form template is semantically different from a form; the latter is actually an instance of the form template assigned to a specific individual or group for data entry. In order for our PPS Application to recognize the form template for subsequent assignments, we need to save our form template to an appropriate location. If we haven’t done so already, we will need to use Planning Administration Console (PAC) to specify the location. Figure 5.9 shows the Edit Application Dialog from the PAC, where we may specify destination folders for our form templates. According to CTP2 documentation, these locations may also include network shares as well as SharePoint document libraries.

Figure 5.9: Application Form Template Location in PAC

The steps to craft an input form are simple, and so they should. The hard work behind the scenes in configuring the application, designing and secure the business model(s) and defining business rules certainly pays off here and in later steps when we enter budget data and create reports.

The next stop on our trip through PPS Planning features takes us back to Planning Business Modeler where we create assignments for users contributing budget data to a given model (using the forms created here), as well as for users responsible for reviewing and approving said data.

- 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