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.
A highlight of day 3 for me was seeing Roberta Vork & Alyson Powell Erwin presenting a session titled 'Integrating Microsoft Dynamics Data for Planning, Analysis and Reporting in Office PerformancePoint Server 2007'. This started off with a demo of how to create PPS M&A dashboards on top of the AS cubes that come with a the various Dynamics products. Alyson mentioned that there are currently some sample M&A Dynamics dashboards available here, with more to come in the near future. Check the PerformancePoint Downloads page to get hold of them.
Roberta's part of the presentation focused on how to get data into PerformancePoint via the Data Import Wizard. Roberta demoed importing data from AX, before discussing a few of the issues that you're likely to encounter when importing data from Dynamics. One of those issues may be that your PPS models are at a higher level than the data coming in, meaning that you will have to carry out a many to one mapping in some way before the data is usable in PPS. Another interesting point from the presentation is that PPS will be able to connect to Dynamics SL in mid 2009.
The final session that I made it to was 'Mastering Data Cleansing in SQL Server Integration Services' by Rushabh Mehta. This was well presented, explaining why data cleansing is an important part of ETL. Although some of the techniques & SSIS tasks covered weren't all that new, Rushabh did show the new Data Profiling Task, which will allow you to carry out a detailed analysis of your source data, which is then viewable in the XML output or the Data Profile Viewer tool.
Both presentations are available for download:
So that's it for another year, although the date has already been set for the next conference - 6th-8th October again. Overall the conference was a success in my opinion, so I'll be doing my best to get out again next year.
Just another quick update from me, this time on day 2 of the Microsoft BI Conference in Seattle.
One of the most important sessions for me was 'Mastering Master Data Management', delivered by Colin White & Kirk Haselden. The summary for the session quite openly states that 'there is a lot of confusion surrounding Master Data Management (MDM)'. I would agree with this - but hopefully this presentation goes some way to addressing people's questions on the subject. The presentation covers some really good theory, showing that there are several different ways that MDM can be approached, although unfortunately we didn't get a look at the Microsoft MDM solution just yet.
There was also more on Gemini yesterday, including a new stand in the exhibitor section where it was possible to talk to the development team. I got to see the new 'slicer' feature of the Gemini pivot tables in a bit more detail, which is a fairly similar to the slicers/filters in QlikView.
Speaking about exhibitors, I also spent a bit of time at the Orange Peel stand, where I saw a demo of their Business Intelligence Studio product, which has actually been out for a while now. Business Intelligence Studio is an Excel add-in for PerformancePoint that allows the creation of PPS form templates with a number of interesting features, including the ability to add new dimension members from Excel, cascading filters, and the ability to run rules from Excel buttons to name a few.
Finally there was also a PPS Planning session titled 'Implementation Tips And Tricks', delivered by Celia O'Kane & Julio Triana. This session covered some useful solutions around performance, business rules and security. Again, the presentation can be downloaded here.
I'm just back at the hotel here in Seattle, so just some time for a quick post about day 1 of the 2nd Annual Microsoft BI Conference.
The big news of the day was undoubtedly the sneak peak of "Project Gemini" - the code name for the core of a BI focused SQL Server release that will happen in the first half of 2010. The impressive part of the presentation for me was seeing an early version of a new Excel add-in to work with Gemini. We witnessed Donald Farmer manipulate 20 million (yes million!) rows of data within Excel, before creating a very slick report via a new version of Excel pivot tables, which he then published to SharePoint at the click of a button. Mosha Pasumansky explains a more about Gemini here.
Once the introductory events were over, it was a chance to attend the more focused sessions, which explained particular tracks in more detail. Although there was a huge amount of choice, I naturally went for the PerformancePoint sessions:
Michael Bower and Scott Sebelsky from Microsoft presented an introduction to Financial Consolidation with PerformancePoint Server 2007. This session gave an insight into what's involved in carrying out financial consolidation with PerformancePoint, providing some useful tips and tricks. There's more to come, as Michael will be releasing a white paper on financial consolidation with PerformancePoint Server at some point in the near future. Watch this space....
Finally, Srini Nallapareddy and Steve Hoberecht presented a detailed presentation on the various different types of business rules that you can use in PPS Planning. Srini explained the pros and cons of using different rule types, as well as providing some useful tips on how to optimise rules for better performance. You can download the presentation here.