As I mentioned in my post a few weeks a ago, the PEL Allocate statement is a powerful method of moving data between PerformancePoint models.
Although it's powerful, the end result of an Allocate statement is simply that the destination model will contain data that has been queried from the source model. What if the destination model already contains data for the target scope? If this is the case, then you will have double counting. When creating the Allocate rule, there is unfortunately no option that lets you decide what you want to do with existing data. Ideally you need to have the 'Existing Data' option that you get when running an association, which gives you the 'Append', 'Scoped Replacement' and 'Full Replacement' options.
To get around this, I tend to place two rules inside a rule set. The first rule is a SQL implementation assignment rule with the same scope as the allocate rule, and deletes existing data by using the PEL statement this = NULL inside the rule. Then the second rule is the allocate statement, which appends the data. This is shown below:
The idea is then to execute the rule set, which will execute each of its rules in order. The advantage to doing this is that your whole data movement process can be executed in one clean step (perhaps scheduled or via Excel), without the hassle of executing several individual rules.
PerformancePoint assumption models are very useful if you want the data in one model to drive and affect another model. However, if you’ve been using assumption models, then the chances are that you may have noticed that they can sometimes be a bit awkward to use. This post highlights a couple of issues that I’ve experienced with assumption models, and provides a few workarounds.
Firstly, when you initially link the assumption model to the main model, you may get the error message ‘the assumption model uses different member sets for the following dimensions’, as shown below:
Lets presume that you ignore this warning and continue to link to the assumption model. Further down the line, when you come to write your PEL rule to pick up the assumption model data, you may find that you get an error when validating the rule. This will occur if you want to populate a member in one member set by referencing a member in another member set, which resides in the assumption model.
To illustrate this, if we want to populate an account called ‘Total Sales’ in the ‘Operational’ member set of the main model by using an account called ‘Price’ in the ‘Drivers’ member set in the assumption model, then we would have the following PEL rule:
scope (
[Scenario].[All Members].[Forecast],
[Account].[Operational].[Sales]) ;
this = [Account].[Operational].[Volume] * (MODEL([Sales Assumptions]), [Account].[Drivers].[Price]);
end scope;
However, when validating this rule we get told “No hierarchy Drivers exists in dimension Account”. The only account member set that PerformancePoint will let you use in the above rule is 'Operational'. Note: you'll be ok if 'Price' exists in the main model, but you will just need to refer to it as [Account].[Operational].[Price]. But if 'Price' doesn't exist in the main model, then a different approach is needed.....
The simple way around it is to use an approach found in the Alpine Ski House sample application. Add the accounts (if that’s the dimension causing you problems) into another dimension called Business Driver. You can then use the Business Driver dimension in your PEL rule, as PerformancePoint is more than happy for you to reference assumption model specific dimensions. This is shown below:
scope (
[Scenario].[All Members].[Forecast],
[Account].[Operational].[Sales]) ;
//The rule is almost identical, but validates ok as we're using the BusinessDriver dimension.
this = [Account].[Operational].[Volume] * (MODEL([Sales Assumptions]), [BusinessDriver].[Drivers].[Price]);
end scope;
Another problem that you may experience is when you have a difference in granularity between member sets used in the assumption model and the main model. For example, you may have an Entity member set called ‘Regional’ used in the assumption model, but a more detailed member set used for the Entity dimension in the main model.
So in the above example, we want to populate members in the more detailed 'Company' member set by using a value from the member's parent in the higher level 'Regional' member set. In order to do this, we can just use the Parent function on the current member of the main model's entity member set. Even though we've referenced the main model member set inside the MODEL function, if the current member's parent exists in the assumption model, then it will pick it up without any problems:
scope (
[Scenario].[All Members].[Forecast],
[Account].[Operational].[Sales],
Descendants([Entity].[Company].[TOT], 1073741823, leaves)) ;
this = [Account].[Operational].[Volume] *
//Its ok here to use [Dimension].[Main Model Member Set].CurrentMember.Parent
(MODEL([Sales Assumptions]), [Entity].[Company].CurrentMember.Parent,
[BusinessDriver].[Drivers].[Price])
end scope;
So both very simple design techniques, but it’s worth being aware of them before you actually start building your models.