cubeSavvy – Introducing MDX grids

Soon after releasing cubeSavvy, the very first comment asked why MDX queries weren’t available. Well, now they are. They are first-class citizens, along with report scripts. The reason they were not added at the outset was because, unlike report scripts, MDX queries are not stored on the Essbase server. Until grid storage was added in version 3.0, there was literally no place to put them.

I, personally, always choose MDX over report scripts. It’s the main reason I added the ability to export data from complex MDX queries to cubeSavvy Utilities. MDX is just more logical than report scripts, given my background with SQL before even learning Essbase.

So download cubeSavvy and get started writing MDX grids! To get you going, the following example MDX grid is included as a sample in cubeSavvy.

Sample MDX query used to create a cubeSavvy grid:

WITH
set [secProduct] as '{Descendants([100], Levels([Product],0))}'
set [secMarket] as '{Descendants([South])}'
set [secMeasures] as '{Descendants([Margin]),Descendants([Total Expenses])}'
set [Scen1] as '{[Actual]}'
set [Scen2] as '{[Budget]}'
set [secScenario] as '{[Scen1],[Scen2]}'
set [secYear] as '{Children([Qtr1])}'

SELECT non empty crossjoin([secScenario],[secYear]) properties [Long Names] ON COLUMNS,
nonemptyblock (crossjoin([secProduct],[secMarket])) PROPERTIES MEMBER_ALIAS ON ROWS,
[secMeasures] ON PAGES
FROM Sample.Basic

MDX Query notes:

1. Again, as with report scripts, the first thing to note is that this a completely valid mdx query. You can run this directly from EAS, MaxL, or anywhere else you’d normally execute an mdx query.
2. Dimension Properties. You can specify exactly what you want displayed on the grid. As  you can see above, selecting a specific alias table (Long Names) is straightforward – or you can just use MEMBER_ALIAS to get the default.
3. You can call your SETs whatever you want in the WITH statement. cubeSavvy will determine the dimensions they refer to and pass them to the calc script. The WITH statement is not a requirement, but I do think it makes MDX more readable.
4. Note the optimization keywords – non empty and nonemptyblock. These are more powerful than the limited commands (SUPMISSINGROWS and SPARSE) available in report scripts.