Microsoft® Office PerformancePoint™ Server 2007 Des= ign Basics

Applies to: Microsoft Office PerformancePoint Server 2007

This document provides guidance and best practice= s for designing a Microsoft Office PerformancePoint Server 2007 Planning applicat= ion.

Contents

In= troduction..........................................= ...........................................................................= .............. 3

Audience.........= ...........................................................................= ...........................................................................= ............................ 3

Purpose..........= ...........................................................................= ...........................................................................= ............................. 3

Performance Point Server Planning Basics..........................................= .................................... 3

Modeler and Overall Design..................................................................= ...........................................................................= ............ 3

Model Sites vs. Applications..........................................= ...........................................................................= ..................................... 3

Decision Point: Multiple Model Sites vs. Multiple Applications........................= ...................................................................... 3

Models............................................................= ...........................................................................= ........................................................ 5

Decision Point: One Large Model vs. Several Smaller Mod= els..........................................= ................................................... 5

Decision Point: Using an Assumption Model..........................................= ...........................................................................= ....... 7

Model Properties..........................................= ...........................................................................= ........................................................ 7

Decision Point: Enable Input at All Levels?..........................................= ...........................................................................= .......... 8

Decision Point: Method for Moving Data Between Models..........................................= .......................................................... 8

Dimensions.................................................................= ...........................................................................= .......................................... 9

Decision Point: Setting the Time Dimension..........................................= ...........................................................................= ........ 9

Decision Point: Which Dimensions to Use?..........................................= ...........................................................................= ......... 9

Decision Point: Use the Account Dimension..........................................= ...........................................................................= ..... 10

Decision Point: Managing Hierarchies.................................................................= ................................................................... = 11

Decision Point: Aggregation vs Rules.................................................................= ..................................................................... 11

Input Forms and Output Reports..............................................................= ...........................................................................= ....... 12

Input Forms.................................................................= ...........................................................................= ........................................ 12

Decision Point: Design the most efficient & effecti= ve Input Form for the users............= ..................................................... 12

Decision Point: Use Excel formulas sparingly in Input F= orms...........................................= .................................................. 13

Decision Point: Use Excel macros sparingly...........................................= ...........................................................................= .... 13

Output Reports.................................................................= ...........................................................................= ................................... 13

Decision Point: Choose the most efficient & effecti= ve Delivery Method.....................= ........................................................ 13

Decision Point: Use Excel formulas sparingly in Output Reports.............................= .......................................................... 14

Rules............= ...........................................................................= ...........................................................................= .............................. 14

General Guidance..........................................= ...........................................................................= .................................................... 14

Decision Point: Choose the rule type..................................................................= ..................................................................... 15

Rules Guidance.................................................................= ...........................................................................= ................................. 15

Decision Point: Scoping rules..................................................................= ...........................................................................= ....... 15

Security.........= ...........................................................................= ...........................................................................= ............................ 16

General Guidance..........................................= ...........................................................................= .................................................... 16

Data Integration.= ...........................................................................= ...........................................................................= ...................... 16

General Guidance..........................................= ...........................................................................= .................................................... 16

Migration Guidance..........................................................................= ...........................................................................= ................. 17

General Guidance..........................................= ...........................................................................= .................................................... 17

Conclusion.......= ...........................................................................= ................................................. 18

top of page

Introduction

Audience

Developers, architects and others decision makers invo= lved in designing and building PerformancePoint Planning solutions, and have:

▪Purpose

This document is intended to provide:

▪ Guidance to aid in making key design decisions along the architectural decision path that will affect how robust, extensible and efficient your PerformancePoint Server implementation will be.

▪PerformancePoint Server Planning Basics

Modeler and Overall Design

Model Sites vs. Applications

Decision Point: Multiple Model Sites vs. Multiple Applications

▪ t= op of page

Figure 1 – Example Architecture for Mu= ltiple PerformancePoint Server Application Model Sites

▪

Figure 2 – Example Architecture for Mu= ltiple PerformancePoint Server Applications

t= op of page=

Models

Decision Point: One Large Model vs. Several Smaller Models

Models you should consider include:

▪

Figure 3 – Example of Multiple Smaller Models

Table 1.1 – Model Types

= Avail= able Model Types	Defau= lt Dimensions Never= remove these – your model will break
Assumption Model	Scenario, Time
Exchange Rate Model	Currency, Entity, Exchange Rate, Scenario, Time
Generic Model	Scenario, Time (recommendation: add Accoun= t)
Financial Model with Shares Calculation	Scenario, Time, Account, Entity, Business Process, T= ime Data View, Consolidation Method, Flow
Financial Model without Shares Calculation	Scenario, Time, Account, Entity, Business Process, T= ime Data View

top of page

Financial Models= (with or without shares) are used to leverage native PerformancePoint Server functionality:

▪ top of page

Table 1.4 – Time Granularity

Deali= ng with Differences in the Time Dimension
Monthly plans require attributes that are planned at quarterly and annual levels. à<= /span>Use three separate models.	Monthly Mod= el: Monthly data input.
Quarterly M= odel: Quarterly data input.
Annual Mode= l: Annual data input.
Data being input at a higher time granularity is req= uired in calculations for planning at a lower time granularity. à<= /span> Use two separate models.	Assumption = Model: Higher time granularity.
Planning Mo= del: Lower time granularity.

Table 1.5 – Disparate Planning Methodology

Deali= ng with Multiple Groups Inputting Data for Multiple Attributes
The Revenue group inputs at the Customer/Product lev= el; The Expense group inputs at the Cost Center level (no Customer/Product).<= br> à<= /span> Use three separate models.	Revenue Mod= el: Revenue planning processes.
Expense Mod= el: Expense planning processes.
P&L Mod= el: Combines the two input models.

Table 1.6 – Currency Conversion

Deali= ng with Currency Conversion
Data inputs are in a local currency; Reporting in US= D. à<= /span> Use two separate models.	Financial M= odel: Fact Data includes both local currency and amounts converted to USD.
Exchange Ra= te Model Type associated as an Assump= tion Model.
Data inputs are in a local currency; Reporting in US= D. à Use a single model.	Generic Mod= el: Create a custom rule for currency conversion, making use of the Account Types from the Account Dimension.

Decision Point: Using an Assumption Model

Utilizing assumption models as cube measures to the pl= anning model may eliminate the need for form macros, improve performance and make = the data more accessible in the form.

top of page

Model Properties

Guidance for best practices is only being issued on on= e of the Model properties; Enable Input at All Levels, all other properties may = be used with the product guidelines.

Decision Point: Enable Input at All Levels?

PerformancePoint Server Planning Business Modeler Vers= ion 1 (RTM): It is recommended that the = model is restricted to input at leaf levels only (Enable Input at All levels is s= et to false). Input at all levels generally requires customization in order for the system to function as desired.

▪Decision Point: Method for Moving Data between Models

Table 3.1 – Data Movement

Chara= cteristics of Member Data Sets to be moved	Sugge= sted Method for Moving Data
Static during the planning process	Associations
Will change during the planning process	SSIS Package
Must be moved in a certain order	Custom written SSIS Package
Must be moved with calculation dependencies	Custom written SSIS Package

top of page

Dimensions

Decision Point: Setting the Time Dimension

Create the time dimension first.

▪Decision Point: Which Dimensions to Use?

Identify necessary dimensions by describing, in detail, various pieces of data within your plan.

Example: Reve= nue for March for red bicycles.

Table 4.1 – Dimension Planning

Descr= iption	Dimen= sion
Revenue	Account
March	Time
Red	Dimension =3D Color (many:many) or Dimension =3D Product & Property =3D SKU (1:1) (see relati= onship discussion, below)
Bicycles	Product

Relationships:

▪ top of page

Dimension Properties:

▪Decision Point: Use the Account Dimension

Think of the Account Dimension (Chart of Accounts, COA= ) as the dimension which stores the label, value, quantity, ratio or rate (Reven= ue, Headcount, Expense, Price, Units Sold, Growth Rate). Within the Account dimension, the Accou= nt Type Property controls aggregation behavior and should be used appropriately based on the Account Type. In ‘traditional’ MS Analysis Services design, measures are sometimes used rather than the ‘Account’ dimension. That is not the approach to take with Planning. The Account dimension is not just for traditional financial accounting members such as ‘Marketing Expenses’ etc.<= /p>

▪Consolidated =3D TRUE
Converted =3D TRUE

Income

Sum

Credit

Consolidated =3D TRUE
Converted =3D TRUE

Header <= /span>

Header account for = a group of related accounts

None

Headcount

Headcount included = in allocation calculation targeted to other accounts

End

Consolidated =3D TR= UE

Rate

Rate account that w= as included in calculations

Average balance of = member children

None

Ratio

Ratio account that = was included in calculations

None

Tax expense

Tax expenses

End

Consolidated =3D TR= UE

Unit

Price/unit account = that was included in calculations

Sum

None

Decision Point: Managing Hierarchies

Hierarchies may be used within model dimensions and/or mapping tables. Large hierarchies = will dramatically affect form performance. Smaller is better.

When the source data is more granular (hierarchy perspective) than the level at which you plan, consider using two separate models, an Input Model and a Planning Model, rather than a large hierarchy.=

Plan in advance for Hierarchy maintenance.

▪Decision Point: Aggregation vs. Rules

Considerations in favor of Aggregation:

▪o Product Structure of the Source System member set

o Product Structure of the Forecasting member set.

T= he organization differs between each set; the detail value of each member rema= ins constant.

Aggregation Tips:

▪ top of page

Member Set View Tips:

▪Input Forms and Output Reports

Input Forms

Decision Point: Design the most efficient & effective Input Form for the users.<= /a>

Recreating existing forms may not be the best decision= . Proactively consider the best design.

Table 6.1 – Form Design

Matri= x	Tips<= /p>
Using Filters	Keeps your matrix area small & manageable
Eliminates the need for multiple worksheets that are= identical except for the filtered member
When the TimeDataView is included as a filter, setti= ng the Default to Periodic may positively impact performance.
Using Columns and Rows	A form should be “tall”, not “wide”. Select column members carefully.
Model Dimensions	Include each model dimension in every form (input &a= mp; output). When the user does not = have a need to see a particular dimension, include it as a filter, disable the option to “Show in Workbook”.

Use a Planning Input Dimensionality Matrix

Table 6.2 – Form Design

Inputs	Entity	Currency	Account	Customer	Scenario	Product	Time
Price<= /o:p>	Filter	Filter	Row		Filter	Filter	Column
Costs<= /o:p>		Filter	Row		Filter	Row	Column
Freight	Filter		Filter		Filter	Row	Column
Volume=	Filter	Filter	Filter	Row	Filter	Filter	Column

top of page

Decision Point: Use Excel formulas sparingly in Input F= orms.

Use Excel formulas sparingly.

▪Decision Point: Use= Excel macros sparingly.

Table 7.1 – Excel Macros

Macro= usage to be avoided
Insert Columns or Rows into the Grid	Use the Authoring Pane to set columns & rows.
Data movement into the Grid	Consider using dynamic formulas either within or out= side of the grid.
Macro= tips (see also PerformancePoint Add-in for Excel Help)
AfterRefres= h and BeforeAssignmentAction<= /o:p>	Planning events available to trigger macros.
Module Titl= e =3D PerformancePoint	Required if you are using either Planning Event.
Digital Sig= nature	Required if you are using either Planning Event.
Application.EnableEvents =3D True	Required if the macro is entering amounts that will = be submitted (i.e. Aggregation & Submit Leaves macros discussed above).<= span style=3D'mso-spacerun:yes'> Alternatively, the matrix Property “C= apture Changes before Workflow Action” may be set to “True”. This setting has a significant negati= ve impact on performance.

<= span style=3D'mso-bookmark:_Toc197839950'>

<= span style=3D'mso-bookmark:_Toc197839950'> top of page

Output Reports

Decision Point: Choose the most efficient & effective Delivery Method.

Table 8.1 - Reporting

Delivery Method	Pros / Cons
Excel Pivot Tables	Pro: Effective for providing data for formal reports= .
Pro: Provid= es expand and collapse capability.
Con: Limited flexibility in formatting and style.
PPS Reports	Pro: Easy to maintain & work with.
Con: Limited flexibility in formatting and style in = Excel 2003; Excel 2007 provides all of the Excel formatting features.
Con: Formulas within the grid may not be changed.
PPS M&A	Pro: Useful for ad-hoc reporting, exports to Excel
Con: User training may be required.
Custom reports using Excel Cube Functions (2007 only= )	Pro: Ability to produce asymmetrical reports with fl= exible formatting and style.=
Con: Level of expertise & effort required to dev= elop new reports.

PPS Reports:<= /b> Performance considerations

▪Decision Point: Use Excel formulas sparingly in Output Reports.
Use Excel formulas sparingly.

▪o    “Pretty sheet”: Stylized report that pulls= from the “ugly sheet”

Co= nsider making the formulas dynamic (vlookup, match, offset, index) so that they continue to return accurate values when changes are made to the matrix.

o    “Ugly sheet”: The matrix.

=

top of page

Rules

General Guidance

▪Decision Point: Choose the rule type.
Overview of what type of rule to use when…

Table 9.1 - Rules

Rule Type

Uses

Definition Rule

Use when a rule will always be true and you do not n= eed to see the data in a fact table.

Assignment Rule with MDX Query

Use when a rule will write to the Fact Table and eff= ects a small number of cells (<500,000)

Assignment Rule with SQL

Use when a rule will write to the Fact Table, effect= s a large number of cells (≥500,000) and does not require the use of CA= SE or IF statements.

Assignment Rule with nativeSQL

Use when a rule will write to the Fact Table, effect= s a large number of cells (≥500,000) and requires the use of CASE or IF statements.

SSIS

Use when a rule is a very complex, multi-stage calculation.

Combination:

Definition Rule & Assignment Rule

Use when you want to see the results immediately and= also want to store the results in the Fact Table.

Automatic Rules

Runs when the cube is processed; can be a good subst= itute for a definition rule.

Rules Guidance

▪ top of page

Decision Point: Scoping rules.

▪o    Multiply the number of dimension members for each dimension in your model to get your maximum rule scope.

For = example, if you have 3 dimensions: Time (12= months), Scenario (Budget, Actual), and Account (Revenue, Expenses, and Profit). Your maximum scope would be 12 x 2 x 3 = or 72 tuples.

▪o    Business Process, set the dimension scope to “Input”

o    TimeDataView, set the dimension scope to “Periodic”      =

o    Scope all other dimensions to the member that you wish the rule to affect.

Security

General Guidance

▪o    One method for locking down visibility/data entry is by limiting the members revealed on your form.

o    Workflow controls the Scenario(s) to which you may write data; Role may not require a Scenario restriction.

Data Integration

General Guidance

1.     Create and use the StagingDB as y= our data import destination. The AppDB= is never to be updated directly.

2.     Use a configuration table or configur= ation file to store environment settings such as connection strings, server names, variables, etc. to source systems. In general, store all environment specific settings here. You do not want to have any hard coding= in your packages. If you choose a SQL table, it will be readily available for reporting, easy to backup and run scripts against.

3.     Have your SSIS packages log to a centralized logging database so that log data may be exposed to reporti= ng, and readily available for troubleshooting, etc. This is configurable in SSIS packages by enabling SQL logging.

top of page

4.     Have explicit code to recreate indexe= s on Label Tables for packages that import large amounts of information into= the StagingDB should. Specifically, re= create indexes for the ID and Label columns.

5.     Use standard, generalized naming conventions for SSIS Task Names and Data Flow Components, Connection Manager, etc. This will make loggi= ng information easier to analyze, report on and mine for valuable information (document from Chip).

6.     Add annotations to your SSIS packages= describing its purpose.

7.     Turn off package validation while debugging your SSIS packages.

8.     Use a data validation strategy for yo= ur packages. Things to consider:<= /p>
a.&n= bsp;    Ensure validation is turned on for the Perfo= rmancePoint Planning Command Utility (PPSCMD.exe). This will provide information about invalid records (for example, invalid foreign keys, duplicates, nulls, updates to the non-member, informa= tion on failures of business rules)

b.&n= bsp;    Upon completion of your SSIS package(s) for = data loading, ensure the BizSystemFlag in Label Table(s) has no values greater t= han 900.

c.&n= bsp;    Consider checking row counts (assuming no aggregation) and check totals between source and destination tables.

9.     Steps to be followed in every package= used for loading data into PPS;

a.&n= bsp;    Synch the model (Planning Command Utility parameter: /Operation SynchronizeData)

b.&n= bsp;    Delete any old data (Perform SQL inserts wit= h a value of 400 in the BizSystemFlag)

c.&n= bsp;    Perform a load operation (Planning Command Utility parameter: /Operation LoadDataFromStaging)

d.&n= bsp;    Synch the model again (Planning Command Util= ity parameter: /Operation SynchronizeD= ata)

e.&n= bsp;    Create label table (Execute bsp_DI_CreateLabelTableForModel stored procedure)

f.&n= bsp;     Bring in the fact data from the external sou= rce to the _Label version of the destination table (Perform SQL inserts)

g.&n= bsp;    Convert the labels (Execute bsp_DI_ConvertLabelColumnToMemberIdForModel stored procedure

h.&n= bsp;    Load the inserts (Planning Command Utility parameter: /Operation LoadDataFromStaging)

10.   Use Expressions to construct argument= s within your SSIS packages. Example= :

Figure 4 – Expressions to Construct Arguments

top of page

Migration Guidance

General Guidance

Table 10.1 - Migration

Migra= tion Method

Mirrored Migration =

Appropriat= e only when all Planning applications from one environment are to be moved to a destination environment where no other Planning applications will coexist with those being migrated.

Component Migration

Appropriat= e when the Planning applications are to be migrated and merged with other Planni= ng applications into a single environment. This method is significantly more complex.

Conclusion

Numerous decisions will be made during the design and construction of a Planning solution. A successful implementation, one that is robust, extensible and efficient, wi= ll only result from a series of thoughtful decision at each of the numerous decision points.

=

<= /p>
top of page



Comments or suggestions on this article?

Provide feedback by clicking one of the links at the bottom of the Office Online article.

Rule Type	Uses
Definition Rule	Use when a rule will always be true and you do not n= eed to see the data in a fact table.
Assignment Rule with MDX Query	Use when a rule will write to the Fact Table and eff= ects a small number of cells (<500,000)
Assignment Rule with SQL	Use when a rule will write to the Fact Table, effect= s a large number of cells (≥500,000) and does not require the use of CA= SE or IF statements.
Assignment Rule with nativeSQL	Use when a rule will write to the Fact Table, effect= s a large number of cells (≥500,000) and requires the use of CASE or IF statements.
SSIS	Use when a rule is a very complex, multi-stage calculation.
Combination: Definition Rule & Assignment Rule	Use when you want to see the results immediately and= also want to store the results in the Fact Table.
Automatic Rules	Runs when the cube is processed; can be a good subst= itute for a definition rule.

Migra= tion Method
Mirrored Migration =	Appropriat= e only when all Planning applications from one environment are to be moved to a destination environment where no other Planning applications will coexist with those being migrated.
Component Migration	Appropriat= e when the Planning applications are to be migrated and merged with other Planni= ng applications into a single environment. This method is significantly more complex.