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.
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr