Having posted about NativeSql business rules a while back, I though that I might as well cover NativeMdxQuery business rules also, especially as there isn’t too much documentation available on the web for this subject.
NativeMdxQuery is a rule implementation type that can be used with assignment rules in PPS Planning. Rather than writing PEL and having the compiler convert your PEL into SQL or MDX, the idea is that you write the raw MDX directly if you pick NativeMdxQuery.
Why Native Rules?
When when I posted about NativeSql, I mentioned a scenario or two where using NativeSql was very useful. The advantages to writing a NativeMdxQuery, however, are less obvious. This is especially the case when you consider that a)standard MDX rules in PPS Planning are less restrictive that the SQL rules, and b) that PEL is very efficient – you can write a very concise MDX PEL rule vs writing the equivalent in MDX itself.
So what advantages are there? Although a fair amount of MDX functions are included in PEL, it’s not possible to use all MDX functions/statements/operators, e.g. Case(), Item(). This is one situation where you might want to write a NativeMdxQuery.
Also, the ability to filter and restrict data in a raw MDX statement is far more powerful than the options available in PEL. For example, you can use the Filter()/IIF functions in PEL, but they’re quite slow and you’re restricted as to where you can put them. If you use MDX, you can use both a WHERE clause to slice the query and/or a HAVING clause for filtering.
Finally, when just writing queries from an SSAS cube, a technique that’s sometimes used is to create query-scoped calculated members, by using the MDX statement WITH MEMBER. This allows you to have calculation steps in your MDX query, and can effectively be used in one sense as a temporary variable store for calculations. If you’re trying to do complex calculations in PEL, you have to assign everything to the ‘this’ keyword. It’s much cleaner to have a block of code where you can define any calculations that the main query depends on. This is what WITH MEMBER will let you do.
How to Write a NativeMdxQuery
The requirements for a business rule using NativeMdxQuery are that you need to write an MDX select statement, which specifies the target cells that will be written to. Unlike NativeSql statements, you do not need to handle how the data gets inserted – PerformancePoint handles all that for you, as long as you produce the MDX select statement.
An example select statement that makes up the entire content of a NativeMdxQuery assignment rule is shown below. The comments show what the query is doing:
--[Measures].[Last Year] just makes the rule a bit more readable WITH MEMBER [Measures].[Last Year] AS ([Measures].[Value], [Time].[Monthly].CurrentMember.Lag(12)) CELL CALCULATION queryCalc FOR --This specifices the cells that we are overwriting with an expression or value '([Measures].[Value], [Scenario].[All Members].[Scenario].&[1], {[Time].[Monthly].[Month].&[200901],[Time].[Monthly].[Month].&[200902]}, {[Account].[Profit and Loss].[Level 07].&[5010],[Account].[Profit and Loss].[Level 07].&[5009]}, [BusinessProcess].[Standard].[Level 06].&[8], [TimeDataView].[All Members].[TimeDataView].&[1], Descendants([Entity].[Divisions].[(All)].&[0], ,leaves), Descendants([Currency].[All Members].[(All)].&[0], ,leaves), Descendants([Product].[Product Category].[(All)].&[0], ,leaves))' AS --The 100 is the value that we are giving the cells above 100 --This is the select statement which cells will receive the value above SELECT NON EMPTY ([Measures].[Value], ({[Scenario].[All Members].[Scenario].&[1]}, {{[Time].[Monthly].[Month].&[200901],[Time].[Monthly].[Month].&[200902]}}, {{[Account].[Profit and Loss].[Level 07].&[5010],[Account].[Profit and Loss].[Level 07].&[5009]}}, {[BusinessProcess].[Standard].[Level 06].&[8]}, {[TimeDataView].[All Members].[TimeDataView].&[1]}, {Descendants([Entity].[Divisions].[(All)].&[0], ,leaves)}, {Descendants([Currency].[All Members].[(All)].&[0], ,leaves)}, {Descendants([Product].[Product Category].[(All)].&[0], ,leaves)})) --Ensure we only write to cells with a certain value by using HAVING HAVING ([Measures].[Last Year] > 100000) properties [Scenario].[All Members].Key , [Time].[Monthly].Key , [Account].[Profit and Loss].Key , [BusinessProcess].[Standard].Key , [Entity].[Divisions].Key , [TimeDataView].[All Members].Key , [Currency].[All Members].Key , [Product].[Product Category].Key ON COLUMNS FROM [Strategic Planning] --Filter on a dimension member property by using a WHERE CLAUSE WHERE ([Entity].[Region].&[North])
The points to note about the above statement are:
- You must connect to the correct cube for the current model;
- You don’t need to include a cell calculation – but it’s the way that Microsoft implement business rules that use MDX, and it’s hard to see how you would get a rule to be of any use without it;
- You must include the member keys as properties, otherwise the rule will error.
Conclusion
Writing MDX is clearly not for all users of PerformancePoint, but does provide the ultimate in flexibility when compared to PEL. Most MDX queries written in PPS will use cell calculations. If you’re not used to how these work, or you just want to save some time, remember that an easy way to get started is to use the debug button on an MDX PEL rule in PBM – this will output a query that is similar to the one shown above.
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