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.
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