Jeremy Kashel

Jeremy Kashel's Blog

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:

  [Account].[All Members].[Total Cost]
) ;
    //The scope of cells that will receive the data
    //The this keyword indicates the target is the same cells as defined in the scope
    //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
    //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)
  ) ;

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:

 //This part of the MDX comes from the 1st argument in the PEL statement
 '([Account].[All Members].[Account].&[5005],
 [Scenario].[All Members].[Scenario].&[1],
 Descendants([Time].[Monthly].[(All)].&[0], 1073741823, LEAVES),
 [Organisation].[Europe].[Level 02].&[11])' 
 //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)
 ([Account].[All Members].[Account].&[5005],
 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 
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.


Comments (2) -

  • Anonymous

    5/30/2008 11:17:43 AM | Reply

    Nice post geremy,

    I assume you can help me out with a little problem i am having with Allocate statement.

    I am using a Push Cross Model Allocation Rule to push data from one model to another. The dimensions and members sets for both the models are exactly same.

    Planning Business Modeler is also validating the rule correctly. However, when i try to execute the rule. I get the following error,

    "Some of the model's dimension members are invalid or missing. The model may need to be redeployed."

    I have also redployed the model several times but to no avail.

    Following is the PEL for the rule.

    SCOPE ([Business Driver].[All Members].Members,

      [Project].[All Members].Members,

    [Scenario].[All Members].[Actual],











    Any urgent reply will be most appreciated.

  • Jeremy Kashel

    6/12/2008 10:47:23 AM | Reply

    Hi Hameer,

    I've noticed that you have the statement [Project].[All Members].Members in your scope. If you change that to a single member, or to [Dimension].[Member Set].[All].LeafMembers, then you should find that your allocate statement runs without error.

    Take a look at the MDX query that gets generated when you have the Members function in the scope. When you specify the Members function in the scope, even though you can't see it in the member selector, the scope will include the All member. Although this is fine for the source of your query, the target cells of your ALLOCATE() statement is the 'this' keyword, which of course means that it's the same as the scope. The end result is that PPS is trying to write data to the All member, which is not possible.

    Hope that helps,