On an MCITP in Performance Management

A couple of weeks ago, Microsoft announced a new Microsoft Certified IT Professional stream, MCITP: Enterprise Project Management with Microsoft Office Project Server 2007. What is interesting about this certification is the balance of content between Project 2007 / Project Server 2007 subject matter (exams 70-632 and 70-633 respectively, each leading to separate MCTS designations), as well as more advanced Project Server topics (exam 70-634) blended with The Project Management Institute's PMBOK (Project Management Body of Knowledge) Guide. What is more interesting is the increased emphasis on the business discipline of project management within what initially appears to be a technical certification.

Reading about this new MCITP brought me to wonder about the validity of a PerformancePoint-centric MCITP. Personally, I think it would be a great idea to promote an MCITP stream, dedicated to performance management (business performance management, corporate performance management, etc.). Such a designation would present a worthy challenge for BI versatilists to better appreciate the business discipline of establishing performance measurements, as well as the key role PerformancePoint Server 2007 would play in executing a performance management initiative. In my opinion, if both Microsoft and the Balanced Scorecard Collaborative put their heads together then an MCITP could emerge, addressing the growing demand for increased knowledge among BI practitioners as performance management and business intelligence draw ever closer.

In my imagination, such a certification (perhaps an MCITP: Performance Management with Microsoft Office PerformancePoint Server 2007 title) could include the following subject matter:

1. A Microsoft Certified Technology Specialist (MCTS) exam covering PerformancePoint Monitor and Analyze features


2. Another MCTS exam geared towards PerformancePoint Planning


3. A third PRO-level design exam (reminiscent of MCITP exam 70-446), focusing on solution architecture, cross-over knowledge on SQL Server 2005 "BI Services", ideal business models to implement for budgeting needs, as well as determining ideal objectives/metrics for a given problem domain.

What do you think? Is Microsoft on the right track with the MCITP: in EPM? Do you believe a full MCITP in performance management would be worthwhile for Microsoft to promote, or, for us to pursue?

- Adrian Downes

On "Mythbusting" Business Intelligence

It is always refreshing when someone steps up and challenges the status quo when it comes to identifying just what BI really is, and what it means to organizations of all kinds. Jamie Thompson's excellent blog (and a recommended read if you aren't subscribing to it already) sets out to bust up a few myths in BI, in a recent July 18th post.

Cheers, Jamie.... thanks for the healthy wakeup call on current BI (mis)perceptions!

- 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

On Waiting for SQL Server 2008

Amid the recently announced February 27th launch date for SQL Server 2008, Visual Studio 2008 and Windows Server 2008, Steve Jones over at SQLServerCentral.com shares his thoughts on why those of us still running SQL Server 2000 should actually forgo an upgrade to SQL Server 2005 and wait for the upcoming release.

Note: you may need to register on the site before reading Steve's article...

- 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

On Re-Evaluating the Traditional Budget

Recently I received a message from Pierre Naudé, Head of Performance Management for IS Partners (a Microsoft Gold Partner in South Africa) who is an advocate of the Beyond Budgeting method (from the Beyond Budgeting Round Table community) for adaptive planning.

Pierre has written an excellent and timely article on Re-Evaluating the Traditional Budget, which argues for new thinking that "makes strategy and planning an open, continuous and adaptive process".

In the lead-up to the release of PerformancePoint, I'm pretty sure a raft of whitepapers will surface from Microsoft; I believe such literature should include coverage of how PerformancePoint Planning may be used to satisfy different budgeting paradigms, from traditional budgeting to emerging methods like those proposed by BBRT and Kaplan & Anderson's Time-Driven Activity Based Costing.

Thanks for sharing the article with us, Pierre!

- Adrian Downes