Adatis

Adatis BI Blogs

Multiple Assumption Models in PerformancePoint

PerformancePoint gives you the flexibility to have multiple assumption models to use as you please in your main models. Although this is great, I've found a problem when the two assumption models have different member sets for the same dimension, and so in an extension to my last assumption model post, this post provides a workaround for the issue. Consider the following example. I've got a main model where I want to use 2 assumption models, namely: HR Assumptions - Uses the HR member set from the BusinessDriver dimension; Strategic Assumptions - Uses the Strategic member set from the BusinessDriver dimension. If you go and add the two assumption models to the main model at the same time, then everything looks normal, as shown in the screen shot below: Once you deploy successfully, you will of course want to write a business rule to pick up the assumption model data. However, when writing the rule and trying to pick from the BusinessDriver member selector, you will see that you can unfortunately only select from one member set, as shown below: If you need to write rules that reference specific members in both member sets, then you will be out of luck. It's not even possible in any kind of native MDX rule, as the main model cube that gets created in Analysis Services only contains the dimension that has been created from the 'HR' member set. It would seem that PerformancePoint just picks the member set that is first alphabetically. The workaround for this issue is simply to create a single member set that combines the two original member sets. Therefore, each assumption model will contain more members than required, but that's far better than not being able to write the rules that you need. So just something to be aware of, and catch, at design time - rather than in the middle of your build.  

Assumption Model Design Techniques and Business 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.