Microsoft PPS: Dead.

I was reading a blog that I thought is worth sharing with all of you.


Microsoft PPS: Dead.

Nigel Pendse reports the death of Microsoft Performance Point Server:

Despite all the positive auguries, it seems that PerformancePoint Planning has flopped.
Microsoft announced on January 23 that it was to be discontinued. There will be a final SP3 Planning release in mid 2009 to deal with immediate customer issues, but no further enhancements. It is possible that some planning functionality will be added into other Microsoft products, such as Analysis Services and Excel, but this will not be based on the abandoned PerformancePoint Planning product.
The Biz# project will have run for almost six years from commencement to abandonment, with little to show for it apart from some disappointed customers and partners.


Link to the blog is mentioned below:


PPS Planning is being discontinued – What could have went wrong?

Although little late, I thought of putting my views on the overall episode of PPS Planning being discontinued. Whatever happened, doesn’t seem right, however the question is why it happened and the reason behind it. For us we don’t have any other option than putting our views unless we get some specifics (which I don’t think will happen) from peoples from Microsoft.

As per the information available the development of PPS started during 2003 timeframe under the code name of Biz# and it was finally released during end of 2007. So here we are talking about development time frame of around 4-5 yrs which is a considerable effort.

Now keeping in view the trend of delivering the Software as a Service (SaaS), Planning Module of PerformancePoint Server’s underlying architecture was not targeted for SaaS (you might have already heard of ‘Oracle Launches Oracle Hyperion On Demand‘). However the the complete Monitoring & Analytics was delivered through Sharepoint Server which I guess MS has already spent some effort to bring into SaaS structure (the base of my assumption is the recently announced ‘Microsoft Sharepoint Online‘). So integrating the Monitoring & Analytics part to Sharepoint as ‘PerformancePoint Services’ looks to be safe step towards ensuring the availability of the application in SaaS model.


I have few more content that needs to be added to this post, I will do that as soon as I get some time.


These are my personal thoughts and I would welcome the comments correcting me if my understanding is not correct in this regard.



Business Intelligence VPC Release 7.1 Download

Yesterday I got the information that ‘Business Intelligence VPC Release 7.1’ is available for download. The links can be found from the blog entry in PerformancePoint Blog.



There are many demos included in this VPC. You can get the list in below screenshot.


The VHD image comes with below listed Products installed:


One more thing that needs to be informed about is that this VHD is time sensitive and will expire on 15th Apr 2010. Plenty of time to play with the software but not to keep any important data.

The only drawback is the amount of RAM that will need to be allocated to this VPC in order to work properly. I guess 2GB of RAM is the minimum that will be required for this VPC. Or my suggetion would be to create 2-3 copies of this VHD and categories the demos across these VHD’s and distribute the load and remove the duplicate databases / applications across these copies.

Also I doubt whether this image has PPS SP2 installed, so that is one more step that we will need to do once it has been downloaded. Also it will be good to have a look at ‘Speeding up the All-up BI VPC’ blog posted at

Understanding Administrative Roles

Understanding Administrative Roles by reading the help files or reference documents can be sometime confusing. However by creating appropriate windows users for each role and using those windows accounts to access each component of PPS will provide good understanding of each role.

My suggestion would be to configure minimum of below mentioned users in PPS and assign it to appropriate roles:


Now assuming that the initial Global Administrator identified while installing the PPS was ‘Administrator’, login to PAC using ‘Administrator’ user and assign user ‘Global Administrator’ to Role ‘Global Administrator’.


Use the ‘Run As’ option to start the PPS component in different user security context than the logged in user.

image image

Once we log in to the PAC using ‘Global Administrator’ role we can see that this user can add additional users to ‘Global Administrator’ role and ‘User Administrator’ role.



However he can not add users to ‘Data Administrator’ role or ‘Modeler’ role.



The next here will be that ‘Global Administrator’ will need to identify the ‘User Administrator’ at Application or Model Site level and those User Administrator will identify the ‘Data Administrator’ and ‘Modeler’ for the Application(s) / Model Site(s) within their scope.

There is table in help file which makes tasks that can be performed by each Administrative Role much clear.


There are some small concepts that can be easily understood by doing this exercise e.g. Modeler can create Business Roles however they can not assign users to these Business Roles whereas ‘User Administrator’ cannot create Business Roles but they can assign users to these roles.

InterCompany Reconciliation

Here is another informative document on InterCompany Reconciliation from the ‘BI VPC 6.0’.


Details on IC Reconciliation

This document is indented to provide instructions on setting up, executing and validating intercompany reconciliation.

IC Reconciliation from 10,000 feet

The purpose of IC reconciliation is to ensure that errors in recording intercompany transactions to not create erroneous debt or revenue in an intercompany entity. For example, if Americas sells a product to International, there will be an entry in America’s receivables account and an entry in International’s payable account for the transaction. However, if America records the transaction as $100 but International records the transaction at $120, we’ve got a problem. Intercompany reconciliation resolves this problem by adding -$20 to a special account that belongs to the seller. So, when everything gets rolled up during consolidation, America and International’s intercompany balances will be even.

Prerequisites for IC Reconciliation
  1. You’ll need Modeler permissions in Business Modeler and direct access to the SQL app databases for your application.
  2. You’ll need a financial model or financial model with shares. This is critical; IC reconciliation rules won’t run on any other type of model.
  3. You’ll need an entity dimension. This is important for obvious reasons, specifically because you need to have entities associated with your transactions (intercompany or not!).
  4. You’ll need an intercompany dimension. It’s usually just like the entity dimension except the intercompany dimension is a flat hierarchy. When you write your intercompany reconciliation rules, you’ll need to reference members in the intercompany dimension.
  5. You’ll need a time dimension.
  6. You’ll need a flow dimension if your financial model has shares. If not, the flow dimension is optional.
  7. If you have entities that use different currencies, you’ll need an exchange rate model. You’ll also need to populate your exchange rate data for the time periods you care about.
  8. You’ll need at least 3 accounts for a decent IC reconciliation. First you’ll need 2 intercompany accounts to hold the transaction from the buyer side and seller side. The third account will be the balancing account. I’ll cover this in more detail later.
  9. You’ll need intercompany transaction data.
  10. You’ll need intercompany reconciliation rules.
Getting started
Configuring model properties

First we’ll need to set the following model properties. Open Business Modeler and connect to the model site you’re working with (this is where all of our Business Modeler work will take place). Check out your financial model of interest and go to the Model Properties tab. Scroll down to the Reconciliation Balancing Account. Your screen should look something like this.


Set your Reconciliation Offset and Reconciliation Balancing accounts to their proper values.

Loading Intercompany Transactions

Intercompany transaction data is stored in the default measure group table in the. After you have loaded your data, you can run the following query to see your data more easily:

select s.[name] as Scenario, time_month, b.label as [Account], b.[name] as Account, at.[name] as [Account Type], ac.[name] as [Account Classification], dc.[name] as [Debit/Credit], c.[name] as BusinessProcess, currency.[name] as Currency, d.[name] as Entity, g.[name] as IntercompanyMember, a.[value], t.Label as [Time Data View], r.rulesetorrulename as [Rule], RuleID, ContextID, AssignmentID, JournalID, a.createdatetime, l.comments, a.changedatetime
from mg_managementreporting_measuregroup_default_partition a
left outer  join [d_scenario] s on a.[scenario_memberid] = s.memberid
left outer  join [d_account] b on a.[account_memberid] = b.memberid
left outer  join [ag_accounttype] at on b.[accounttypememberid] = at.memberid
left outer  join [ag_debitcredit] dc on at.[debitcreditmemberid] = dc.memberid
left outer  join [ag_accountclassification] ac on at.[accountclassificationmemberid] = ac.memberid
left outer  join [d_businessprocess] c on a.[businessProcess_MemberId] = c.memberid
left outer  join [d_currency] currency on a.[currency_memberId] = currency.memberid
left outer  join [d_entity] d on a.[entity_memberid] = d.memberid
left outer  join [d_entity] g on a.[intercompany_memberid] = g.memberid
left outer  join [rulesetsorrules] r on a.ruleid = r.rulesetorruleid
left outer  join [d_timedataview] t on a.TimeDataView_MemberId = t.memberid
left outer join LoadingControlId l on l.LoadingControlID = a.LoadingControlID
–a.time_month = 200301
–and g.memberid != -1

Creating Business Rules
  1. Now that we have some IC reconciliation data, we need to create a business rules to actually run our reconciliation. Open Business Modeler, check out the Consolidation model and click on the “Business Rules” tab. You should see a root ruleset of type “InterCompanyReconciliation” called “Intercompany Reconciliation Rules”.
  2. Inside this ruleset, create a new rule. As an example, let’s look at a reconciliation rule for Tahoe and Vermont (from AdventureWorks data). This rule will reconcile transactions between Tahoe and Vermont for the accounts we specify. In the rule expression pane, type the following:

[Intercompany].[All Members].[Tahoe],
[Intercompany].[All Members].[Vermont],
[Account].[Consolidation].[Cons 1013000],
[Account].[Consolidation].[Cons 2015500],
[Flow].[All Members].[ADD]

This rule states that we will reconcile balances treating Tahoe as the seller and Vermont as the buyer for transactions where Tahoe used account 1013000 and Vermont used the account 2015500. We will only reconcile transactions in the Addition flow. As you can see, the entities and accounts here match the inputs we created in the measure group table. That’s good. Your screen should look something like this:


  1. Validate, save and deploy your rules. Now we’re ready to run!
Running the Job
  1. Open an Excel client with the Performance Point add-in installed. Connect to the PerformancePoint server. We’ll run the IC reconciliation job by choosing PerformancePoint > Jobs and Assignments > Launch Job. Here are the wizard steps to follow:
    1. Choose Create New Job and click Next.
    2. From the Job Type dropdown, select “ICReconciliation Job” and click Next.
    3. Specify your job parameters.
    4. Click Next and Finish to launch the job.
  1. The job shouldn’t take much time to run; you can monitor its status with the Job Status tool (found under PerformancePoint > Jobs and Assignments > Job Status).

Note that you can also run the job directly from Business Modeler by clicking the Process Management link and selecting “Jobs” from the View dropdown. Then click “Schedule Job…” and follow the wizard. Save the model site then right-click on the job you just created to launch it.


Verifying the Results
  1. Once the job has finished successfully (you can check on its success in the Job Status tool; we’ll need to go back to the measure group table to verify that the results were written out correctly. The easiest way to do this is to re-run the IC Reconciliation MG query that we used earlier. This time, you’ll notice that more rows exist in the measure group table. These rows were created by the IC Reconciliation job (look at the Rule column), which will automatically launch the currency translation job as needed.
The Tao of Reconciliation

You may be asking yourself, “Self, what is an Offset Account? What is a Balancing Account for that matter?” Well, when an IC reconciliation job runs it looks at the difference between the values recorded by the buyer and the seller. In this case, the seller recorded the transaction at $50.00 higher than the buyer. IC reconciliation always assumes the buyer’s value is correct and so we must reduce the seller’s value by 50 bucks. The offset account (that we set in the model properties) is considered a “seller’s account” so adding -$50.00 will bring the whole shebang into balance. But, our ancient Greek accounting rules tell us that we need to record a double entry for this transaction. Enter: the balancing account. The balancing account will always get a double-entry for the same amount and opposite sign as the offset account.

We said earlier that the offset account must be of type intercompany and the balancing account must not. When we perform a consolidation on this set of entities and accounts, all of the transactions in the offset account (specifically the ones created by intercompany reconciliation) will be eliminated automatically because they are intercompany transactions. However, the transactions we created in the balancing account will not be eliminated and their value will roll up into the balance sheet when consolidation happens. This is how we restore balance in the universe when there are general ledger discrepancies.


The diagram above illustrates the relationship between the offset and balancing accounts and is especially helpful for people who like buckets.


Consolidation Without Shares

Here is another document from ‘BI VPC 6.0’ related to Consolidation Without Shares.


Overview of Finance Intelligence

Finance intelligence provides built in calculation to do financial forecast, financial consolidation, currency conversion, reconciliation and other various finance related tasks.

Companies have both external (financial) accounting system and internal (cost) accounting system. Budgeting, forecasting, management reporting all fall into the internal accounting. Those reports are produced and analyzed by internal accountants, financial analysts, Human Resources or top executives within the company. Statutory reporting is what company publishes to the outside world so that they can see the financial status of the company. This needs to follow specific GAAP (US only) accounting rules. Our financial intelligence feature supports both accounting systems by providing numerous rules templates.

There are three types of models one can create using performance point server:

  • Financial Model with Shares: A financial model that requires shares calculation to determine the ownership of entity in a hierarchy. One can use this model to create statutory financial reports for consolidation, doing currency conversion and reconciliation.
  • Financial Model without Shares: A financial model that doesn’t require shares calculation. Following scenarios will benefit using such a model:
    • Creating stand alone statutory reports without consolidation.
    • Any internal account reporting such as forecast, budgeting and management reporting.
  • Generic Model: A model that usually doesn’t include any financial related information. This normally is used by the Human Resource department to create non financial related reports such as headcount, salary and review reports.

There are two types of assumption models one can create using performance point server:

  • Global Assumption Model: An assumption model that can be used to link with the various models.
  • Exchange Rate Model: An exchange rate assumption model that is linked with various models for currency conversion purpose.

Overview of Consolidation

What is Consolidation?

Companies have subsidiaries and ownership of other companies need to run consolidation for both internal and external reporting. The general concept of consolidation is to consolidate all children entities to the parent entity, and any transactions took place between companies should be eliminated. If company uses financial model with shares model, FI’s consolidation rules will be used to handle elimination, minority interest and investment related transactions. If company uses financial model without shares, any inter company transaction is 100% elimination and all transactions for children entities will be rolled up to the parent to view.

How Consolidation works in Performance Point Server:

Financial model with shares consolidation: This uses financial model with shares since it requires ownership information which derives from the shares calculation. There are five rules templates for statutory consolidation in FI. They are Inter-company rule for Profit/Loss, Inter-company rule for investment, Inter-company rule for Equity and Inter-company rule for Balance Sheet. One can map a leaf account to the model property that used by the rule. The leaf account will then be used for calculating minority interest, investment and elimination related transactions. Users can also write their own rules to accommodate more statutory requirements. The financial model with shares consolidation only supports one level entity hierarchy, and shares calculation will generate all the ownership information for each entity relative to the parent entity which in term generates a flat hierarchy.

If company A owns 90% of company B, there will be two leaf entities (company B and company A) and one consolidated entity Consol. After shares calculation company A has holding consolidation method and company B has Full consolidation method. Once the method is determined for each entity within the hierarchy, we need to load fact table data for each leaf entity. Consolidation process involves the following three steps:

  1. Shares Calculation: To calculate the ownership and control for each sub entity.
  2. Reconciliation (optional): To record any difference occurred between inter companies.
  3. Consolidation: To record elimination for inter-company transactions based on the type of the accounts, consolidation methods and flow type (for BS accounts only).
  4. Currency Conversion (optional): Consolidation can also do currency conversion but it’s optional.

Financial model without shares consolidation: This uses financial model without shares since we assume 100% ownership between parent and child entities. The model property Consolidation Balance Account is used to record the elimination for each account. Non statutory consolidation supports staged hierarchy so that parent entities can have multiple levels. The 100% elimination will take place for all the intercompany transactions.

Consolidation Business Process:

Following is a diagram of Business Process Diagram for leaf level entity (from PM doc):


First customer loads their data into fact table via Input process. If there is any adjustment needed, customer can either manually load data via Manadj process or system can post adjustment via Autoadj process. Reconciliation writes back to fact table via Autoadj process. Consolidation writes back to fact table via Elimination process. Allocation writes back to fact table via ALLOC process. All consolidation rules write back to fact table. After all the adjustments and elimination write back, cube level aggregation will take place via PREALLOC, POSTALLOC and Consolidated process. One can only see cube level aggregation via either excel report or cube browser.

Validation Requirements for Consolidation:

The following validation needs to take place prior to consolidation:

  • Ensure time dimension has the member set other than all associated with it
  • Ensure Account dimension is associated with the correct hierarchy
  • Ensure Entity dimension is associated with the correct hierarchy:
    • Financial model without shares can have staged hierarchy
    • Financial model with shares only supports one level
  • Ensure Entity type for consolidation is defined as Legal or Management but not corporate
  • Entity types need to following definition:
    • Legal, basically a parent. Use this type if they own any part of another entity
    • Management, basically a child that is not also a parent. Use this type if they do not and cannot own any part of another entity
    • For each of the consolidation points, setup an additional entity, appending “Consol” to the entity name. I.e. “AW Resorts” new entity would be “AW ResortsConsol”. These new Entities will be of TYPE “Corporate”. When consolidations are performed at this level, the system would store the new consolidated data in this new “AW ResortsConsol” entity.
  • During data loading, following business rules apply:
    • Only intercompany accounts can be used for intercompany transactions
    • Entity can only load transactions with entity currency
    • One can’t load transaction to non leaf accounts
    • One can’t load transaction to non leaf entity
    • Income Statement accounts should have flow of none, and Balance Sheet accounts should have any flow but none.

Financial Model without Shares Consolidation:

Step by Step:

  1. Create Exchange Rate Model
  2. Create Financial Model without Shares
  3. Set up the following Model Properties
    1. Default Currency – Default currency for currency triangulation.
    2. Reconciliation Balancing Account – Needed for reconciliation
    3. Reconciliation Offset Account – Needed for reconciliation
    4. Consolidation Balance Account – Needed for consolidation
  4. Load dimension data.
  5. Load exchange rate fact data
  6. Load Consolidation fact data
  7. Deploy model site.
  8. Run Consolidation job.
  9. Create excel reports to view the final consolidated results from parent.

Things to verify:

  • All model properties should be set.
  • If using exchange rate model, the time granularity of the financial model should be higher than or equal to the exchange rate model.
  • Account and Entity should have consolidation hierarchies specified for the model
  • Be very careful when loading consolidation related data. Validation rules will apply.
  • If there is intercompany transaction, intercompany column should be filled for the entity.


Model to Model mapping- Associations

As mentioned in my previous post, I will be sharing the documents I found in ‘BI VPC 6.0’ VPC.



An association defines the relationship between the source and destination model when models are mapped in Planning Business Modeler. When an association is created, it enables the movement of fact data when a data movement job is performed. Fact data is a measure that exists in reference or in context to dimensions. For example, if there is a customer, time, and product dimension, actual sales will result from these dimensions. A measure is a summarizable numerical value that you use to monitor your business.

Associations created between models will include corresponding dimensions and members. Models can be mapped between two separate model sites.

How Associations are used

An association may be created from an existing model’s data to reuse data in order to create a model in its own right. Purposes for this kind of association may be to segment a corporate model for each of its divisions. This allows the divisions to work on the model to meet financial planning requirements. This association is also used for taking a snapshot of data for what-if analysis. The following diagram visually demonstrates a larger model contributing to a segment model.


An association may be created to contribute to a larger model as a method to feed data from a business segment or division into a corporate model. For example, by creating a model for analysis that can contribute to a larger model, each division can do its own planning and feed its final numbers into a corporate model. The following diagram visually demonstrates a segment model contributing to a larger model.


An association may also be created from an existing model’s data to reuse data in order to contribute dimensions and members to a segment of another model. The new model may be used for planning, reporting, or hypothetical analysis. The following diagram visually demonstrates an association between segment models.


Create an Association


An association defines the relationship between a source and destination model that are mapped in Planning Business Modeler.  Once the data between models is mapped, an association is created and then enables the movement of fact data when a data movement job is executed.


To create a new association

1. In the Site Browser pane, select Associations.

In the Association Tasks pane, click Create a New Association. The Create New Association dialog box is displayed.

In the Name text box, enter a name for this new association. Association names must be fewer than 100 alphanumeric characters long, and must be unique.

2. In the Label text box, enter a label for this new association. Association labels must begin with a letter, must be fewer than 40 alphanumeric characters long, and must be unique.

3. Optional. In the Description text box, enter a description of this new dimension. The description can be up to 256 characters long.

4. In the Aggregation Function drop-down list box, select the aggregation type. For disproportionate model mappings where the number of members in the source is greater than the number of members in the target, you can identify the method that should be used for aggregation.

Note   One or more source members may be associated (mapped) to only one destination member. Selecting one source member to many destination members is not supported.

Select one of the following:

· AVG: Performs an average on the set of numbers.

· MAX: Returns the highest value from a set.

· MIN: Returns the lowest value from a set.

· SUM: Returns a total of the numeric values in a set.

5. Select the model from the Source Model drop-down list box. The Source Model Site will default to the model site in which the user is currently working.

6. Select the destination model site from the Destination Model Site drop-down list box.

7. Select the destination model from the Destination Model drop-down list box.

8. Click OK to create the new association. The Association Summary workspace is displayed.


About Model, Dimension, and Member Associations


Associations are created when models are mapped to enable the movement of fact data between source models and destination models. Fact data constitutes the actual values when dimension members act as reference points for which those values exist. Creating an association involves mapping dimensions and members from one model to another. Associations also involve mapping sets of source members to a target member. This allows you to map to objects that store valuable corporate data and reuse the data for other purposes such as contributing to another model or creating a model for hypothetical analysis.

After the associations exist, data can be moved from the source fact table to the destination fact table when data is moved in Planning Business Modeler during a data movement job.

Note   Models can be mapped between a local model site and a model located in another application.

Model Associations

The model association is the first step taken to make sure the correct data is moved. The source model site and source model are selected to be associated with the selected destination model site and destination model.

The source model is assumed to be in the current model site from which the association is created. A selection for aggregation is provided with AVG, SUM, MIN, and MAX values so that you apply the appropriate aggregate function to the source data while it moves to the selected new model.

Dimension Associations

When the source and destination model site and models are selected, a model association is created and the next step may be to create associations between dimensions and members. There are two options for selecting and moving data: dimension mapping and dimension scoping. Dimension mapping allows you to map between dimension reference data in the Member Associations tab after creating a dimension mapping. Reference data is the context in which fact data exists and is a result of having dimensions and members (reference points to the fact data).

Dimension scoping allows you to map between members of dimensions in the Dimension Associations tab by clicking the member-picker button. The member-picker button opens a dialog that allows you to scope or filter what source dimension members are selected for data movement and to scope which destination dimension members will contain those values.

The following further defines the difference.

Dimension Mapping

Planning Business Modeler allows you to configure mapping between member sets to specify the movement of data between models. When selecting a dimension in the source destination as a reference point for the destination model and resulting fact data, you may choose to map all the members to the destination, where no scoped dimensions exist. This makes the new model identical to the source model.

Dimension Scoping

Scoping refers to filtering dimension members or member sets. A scoped dimension is a dimension where members have already been selected. The following table helps illustrate the difference between a scoped source dimension and a scoped destination dimension.

Scoped Source Dimension

Scoped Destination Dimension

\ clip_image008

If the source dimension is scoped, only the scoped members are included in the data selection for data movement, as this diagram shows.


If the destination dimension is scoped, the data movement should assign the moved values to that defined scope, as this diagram shows.

You select set of members to include in the mapping from source to destination from the button with the ellipsis that is used as a member-picker. The member-picker exists for each dimension in the source and target models.

Example of Dimension Scoping

The following are examples of source and destination dimension scoping.

Source dimension scoping

If a model association is created to move data into a new model for analysis purposes and only data from a given year’s quarter should be included, you should scope (or filter) for the source dimension. You may scope or filter data from the source model on any selectable Time dimension member. As a result of scoping the Time dimension, the fact data transformed will reflect only the members you selected. In other words, only those measure group rows that match the set you scoped for, in a particular dimension, will be considered in the map movement.

Destination dimension scoping

If a model association is used to feed currency data from the business segment model to the corporate model, you should create an association between the Currency dimensions in the source and destination models. If the source model data uses EUR currency and the destination model requires USD, you must scope the destination Currency dimension to reflect the change during a data transformation. In other words, by setting the scope in the destination dimension, you dictate which member is used for the resulting data. That resulting data becomes the single value that will appear for all moved and aggregated rows of data.

Note   Source dimension scoping and destination dimension scoping are different because there is no filtering of data in the latter.

In short, source dimension scoping is a form of filtering which members to include while destination dimension scoping establishes the member to which source members aggregate or map.

Member Mapping

Member mapping is an alternative method for customizing the dimensions that correspond to the model association you created. You may prefer it over member scoping as it allows for more detailed mapping between members.

Data Movement Job

When models are mapped to the metadata objects in preparation for data movement, the association is made to perform the data movement across models in Planning Business Modeler.