NativeMDXQuery Business Rules in PerformancePoint Planning

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.