Tuesday, August 14, 2007

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


Alexey said...

Thanks for the sample. I ask for your explanation. In our company the budget of sales is planned in two measures - quantity of units and the average price. Please give me instruction how can I create model in "Planning Business Modeler" for our budget ?

Adrian Downes said...

Hello Alexey.... my sincere apologies for the late response.

The easiest way to meet your business requirement is to make Qty of Units and Average Price members of your Account dimension. This way the VALUES measure group (of one measure) can be distinguished appropriately.

You would need to segment your loads using data integration stored procedures by the measure itself...for example , you could use an INSERT statement supported by a UNION to segregate fact records for Price and Qty of Units.

Finally, you could create a definition business rule to calculate the Average Price (if it is not a value that is loaded from a source system), based on Tuples of VALUES that intersect with other Account members like Revenue for example.

We do something similar in our upcoming Rational Guide on PerformancePoint Planning.

Hope it helps,

- Adrian Downes

Robert Rubocki said...

Great blog. Is there a way to display the annotations on a PPS form/report after they have been captured? Is there a way to make an annotations dimension based on the data stored in the annotations table? We are seeing a lot of interest in being able to enter text into "custom" line items on forms, where users could enter a text description of their value.

I can write an SQL query against the relational database and could create an SSRS report that displays the annotations, but it would be nice to be able to use Excel interface.


Adrian Downes said...

Hi Robert,

Thanks for the kind words. Your annotations-in-a-report business case does not appear to have an OOTB feature in PPS-P, and, will likely involve customization in the data layer.

Here, I would consider looking at using the data integration (DI) stored procedures that ship with PPS-P, in conjunction with either PPSCMD-controlled synchronization (from the application) and movement into a separate model (or your own custom cube/relational tables) dedicated to reporting.

You would need to review existing documentation on annotation and how they are stored, to ensure that the cell-intersection information for the annotation is retained, and referenced as needed.
A prime candidate in this respect would be the A_MODELNAME_MEASUREGROUP
annotation table.
Naturally, you would need to rely upon an interface other than the current PPS Add-in for Excel for such reports to show annotations.

Unfortunately, since we currently don't have a PPS-P SDK, pretty much any customisation effort at this time will be at the risk of the ISV (and downstream customers).

Hopefully we'll see better support for alphanumeric report retrieval in an upcoming release (SP, FP or RTM product).

- Adrian Downes