PPS-P: Where, oh where, does my data go?

A user enters a value into a PerformancePoint Planning form... so what happens next? And why are we the least bit interested?

Firstly, to answer the why do we care question, ask yourself this...

1. When do your extracts from PPS run (presuming you run it more regularly than say once overnight)?
   


2. Do users eg approvers expect to see updates immediately - particularly at certain times of the cycle ie month end?
   


3. What data do reviewers, approvers and report consumers expect to see? Approved? Currency Converted?

So we do care after all. So now to what actually happens. In fact, there are a few things that can happen. Let's examine each separately.

(1) The user selects the Save Privately action

• The data is stored on the local file system - known as the Offline Cache. This location is usually C:\Documents and Settings\%username%\Local Settings\Application Data\Microsoft\PerformancePoint\OfflineCache
• In the Offline Cache, the Catalog.xml file stores a list of all assignments stored locally
• In the Offline Cache, each assignment is stored in a .dat file. The dat file an xml file which contains binaries.
• Data is NOT sent to the SQL server or the cube
• The status of the assignment is still 'started'

(2) The user selects the Submit Draft action

• The data is stored in the SQL table called MG_ModelName_Default_Partition
• The resulting values from Definition Rules are stored in the MG table also however they're linked to the 'No Assignment' assigment
• Once the pre-configured polling period has passed, the cube called ApplicationLabel_ModelSiteLabel will be refreshed
• The status of the assignment is 'Partial'

(3) The user selects the Submit Final action.

• The data is stored in the SQL table called MG_ModelName_Default_Partition
• The resulting values from Definition Rules are stored in the MG table also however they are linked to the 'No Assignment' assigment
• Once the pre-configured polling period has passed, the cube called ApplicationLabel_ModelSiteLabel will be refreshed
• The status of the assignment is 'Submitted'

(4) A data movement job occurs

• The data is copied from the source SQL table called MG_ModelName_Default_Partition to the destination table called MG_ModelName_Default_Partition
• Data is aggregated when the scope of a dimension (in the data association) has been set to "All" on the source model.
• All data is copied: Partial, Submitted, Approved
  

Then of course the question is, how long does all this take? Sadly, there's no simple answer to that question, as so aptly discussed by Peter Ebley. To paraphrase Peter, there are a few steps that occur:

1. Send it to the server (Status = Pending)
   Depends on the size of the submission (How many rows? Is the workbook attached? How many dimensions?) and network bandwidth.
   
   
2. Process the Queue (Status = WaitProcess)
   Depends on the Planning Process Poll Interval (recommendation is 1 minute) and how many items are in the queue and of course, how long it takes to validate the submission.
   
   
3. Cube Processes
   Depends on the OLAP Cube Refresh Interval property in the Workflow tab of PAC.