Friday, 9 May 2008

The Flow Dimension for PerformancePoint Server Planning

One of the internal dimensions for PerformancePoint Server Planning is the Flow dimension. When creating a Financial Model with Shares Calculations it is standard and cannot be deleted from the model. You must however add it for a Financial Model without Share Calculations. A number of people new to the product do not understand what this dimension is used for and initially it does not really seem to be relevant. It's relevance however becomes transparent when you try and report on your financials. The standard dimensions cannot be deleted and here is a list of them on the left from the Planning Business Modeler (PBM).

To make it easier to understand just focus on 4 of the dimension members being NONE (None), OPE (Opening Balance), MVT (Movement) and CLO (Closing Balance). Now put them into perspective when looking at your trial balance. All income statement type accounts start each fiscal year with no value and accumulate until the end of the year. Each fiscal period has movement and one normally views them as monthly, year to date, quarterly etc etc. Balance sheet items on the other hand start every fiscal year with the closing balance from the prior fiscal year. They also have monthly movement and are normally viewed as at a point in time which in accounting terms is the closing balance. So to summarise, profit and loss (income statement) accounts have periodic movement and balance sheet accounts have an opening balance, periodic movement and a closing balance.

The next question you ask is the how do they tie up to the Flow Dimension and how does one load your current General Ledger (GL) information into PPS using them. Simple, load all income statement accounts with a Flow of NONE and load all balance sheet accounts with the closing balance (CLO) for each period. PPS then needs to calculate and populate the opening balances and periodic movement for the balance sheet accounts.

If you go to the business rules for the model and look under "System Defined Procedural Rules" you will find several rules that do just this. The first rule is called "Chart_CarryForward" and the second "Chart_GAP_Movement". These rules require parameters in order to run which are:
  • Scenario
  • Entity
  • StartTimePeriod
  • EndTimePeriod
  • BusinessProcess
There is also a batch rule that will calculate the movement for all entities and scenarios called "Chart_Batch_GAP_Movement".

These calculations create records in the MG table for the model and when the cube is refreshed they appear for reporting purposes.

One may then question how do you arrive at YTD or CompareToLastYear totals for income statement accounts if you only have a Flow on NONE. This is calculated in the cube only under the definition rules called "TimeDataView Calculation". This rule will distinguish between balance sheet and income statement accounts and properly calculate what the YTD or comparative value are.

By using the combinations of the flow and the timedataview dimensions from within a tool such as Management Reporter one can create some complex reporting solutions.

- Paul Steynberg

No comments: