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