PEL Allocate Statement Vs Associations

PerformancePoint provides you with a few options when you want to move data between models, the most obvious of which is Associations. However, there is also the powerful PEL Allocate statement….

So which method should you use to move data? Associations are very easy to setup and will transfer your data for you – all out of the box. However, they do have a few limitations, namely:

• You can only apply a minimum, maximum or total aggregation to the source data;
• You can’t have parameters to dynamically select members;
• If you delete a dimension that’s used in an association, then you can’t view or edit the association. Although there should hopefully be no need to do this in production(!), it can be a bit annoying during development.

By writing a PEL Data Movement rule of type PushCrossModelAllocation or PullCrossModelAllocation, we get bit more flexibility. With the PEL Allocate statement you can:

• Use PEL functions or operators to alter the source value that gets written to the target model;
• Similarly, apply a ratio to the source value so that we only transfer the relevant portion of the whole data value. E.g. we can transfer 20% of head office costs;
• Use rule parameters to dynamically select members.

So if you find that Associations don’t quite do what you need, then how do you use a PEL Allocate rule? The following is an example of a simple PushCrossModelAllocation PEL Allocate statement:

scope 
(
  $TransferScenario$,
  [Time].[Monthly].[All].LeafMembers,
  [Organisation].[Europe].[France],
  [Account].[All Members].[Total Cost]
) ;
     
  Allocate
  (
    //The scope of cells that will receive the data
    //The this keyword indicates the target is the same cells as defined in the scope
    this,
    //The source value. If its the same as the scope then we just use ()
    //Or we can apply a simple calculation
    [Account].[All Members].[Volume] * [Account].[All Members].[Cost Per Unit],
    //The scaling ratio that gets applied to the source value
    //0.2 indicates that we only want to apply a fraction of the source value
    0.2,
    //The last two arguments are only relevant for Push and PullCrossModelAllocation
    //They specify the mappings between the source and target members
    //Here I want France (in the source) to be mapped to France (in the target)
    (
      [Organisation].[Europe].[France]  
    ),
    (
      [Organisation].[Group].[France]
    )
  ) ;

end scope;

There are a fair few arguments to the Allocate statement, but I’ve found that a good way to learn what its doing is to debug the rule in order to see the MDX statement that gets generated. The MDX query that gets generated for the above PEL statement is as follows:

WITH CELL CALCULATION queryCalc 
FOR
 //This part of the MDX comes from the 1st argument in the PEL statement
 '([Account].[All Members].[Account].&[5005],
 [Measures].[Value],
 [Scenario].[All Members].[Scenario].&[1],
 Descendants([Time].[Monthly].[(All)].&[0], 1073741823, LEAVES),
 [Organisation].[Europe].[Level 02].&[11])' 
AS
 //This is arguments 2 and 3 of the PEL Allocate statement
 //Namely the simple A*B calculation and then multiplying the value by the ratio
 ((([Account].[All Members].[Account].&[5003],[Measures].[Value]) *
 ([Account].[All Members].[Account].&[5004],[Measures].[Value])) * 0.2)
SELECT NON EMPTY 
 ([Account].[All Members].[Account].&[5005],
 [Measures].[Value], 
 NonEmpty(({[Scenario].[All Members].[Scenario].&[1]},
 {Descendants([Time].[Monthly].[(All)].&[0], 1073741823, LEAVES)},
 {[Organisation].[Europe].[Level 02].&[11]}))) 
 properties [Scenario].[All Members].Key ,
 [Time].[Monthly].Key ,
 [Organisation].[Europe].Key ,
 [Account].[All Members].Key 
ON COLUMNS 
FROM [Europe]

Here we can clearly tell (as marked in the above MDX code) how the first, second and third PEL arguments translate into MDX. If you find that the allocate statement is not quite doing what you want, then its quite useful to execute the MDX within SSMS to figure out exactly how the source model is being queried.

Once you’re finished coding your business rule it can be executed via a scheduled job, manually, or via Excel.

To be fair to Associations, you’re probably more likely to use them for bulk transfer of data, perhaps to facilitate cross model consolidation – and they’re extremely useful for this purpose. The Allocate statement is perhaps more relevant for specific pieces of data that need to be adjusted in some way before being transferred to the destination. Despite this, the Allocate statement is the more flexible method and will allow you to support more sophisticated cross model data transfer – if it’s needed.