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.
How Artificial Intelligence and Data Add Value to Businesses
Knowledge is power. And the data that you collect in the course of your business
May
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
1 Comment
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar