SmartView and MDX, its a thing.

MDX for querying Essbase is something I’ve recently began to invest time into, and I’m not looking back.  The thing that got me to start looking for alternative data extract options from Essbase was my users constantly requesting an ‘extract’ of the data. They love using that level and building Excel based reporting on it; please hold your comments on how wrong that is, I agree, but don’t have a choice here.  So we were trying to pull these extract using report scripts, and ad-hoc views in SmartView, but it just wouldn’t work. There are several large sparse dimensions and the number of potential intersections for each is very high, greater than 1 Million rows.  But the actual rows with data are closer to 50-60k so this is not really that big of a pull. When you use an Ad-Hoc report in SmartView, even with suppression enabled, the entire data set is first retrieved then suppressed, often exceeding line limits in Excel or SmartView, and causing systems everywhere to lock up and freeze.

In comes MDX, I had worked with MDX in the past, but as anyone who has run MDX can attest, the format is near useless without post processing, and I did not have the time for that, and I was hoping to make this something the users could do on their own. So I took a look at the option in SmartView for running MDX, and I love it, formatting is perfect.  Now I had to work on the query to make it efficient. Since this is an ASO not a BSO, ‘NONEMPTYBLOCK’ is not an option. But we can use ‘NONEMPTYSUBSET’ with CROSSJOIN to great effect. The actual query, structure ends up looking something like this  ( for an ASO cube whose dimensions are  year, scenario, version, currency, entity, location, product, account)

SELECT

{DESCENDANTS([YearTotal],2)} ON COLUMNS,

NONEMPTYSUBSET(CROSSJOIN(

NONEMPTYSUBSET(CROSSJOIN(

NONEMPTYSUBSET(CROSSJOIN(

NONEMPTYSUBSET(CROSSJOIN(

NONEMPTYSUBSET(CROSSJOIN(

NONEMPTYSUBSET(CROSSJOIN(

NONEMPTYSUBSET(CROSSJOIN(

{[2016]},

{[Budget]})),

{[Working]})),

{[USD]})),

{LEAVES(ENTITY)})),

{LEAVES(LOCATION)})),

{LEAVES([ALL_Products])})),

{DESCENDANTS([Account],3)}))

ON ROWS

FROM [Application.Database]

As the query resolves the ‘Cross Joins’,  it removes the blank subsets, in effect doing multiple layers of suppression to remove the empty rows, before doing the next cross join.  Using this method I am now able to pull a report in SmartView in <1 minute, where in the past using report script, it would take hours . Not only is it faster, but users can run this from their own system, whenever they want. We are also using the LEAVES function to drill into our members so that we can go straight to L0, and only pull members contributing to our roll up, this is an optional but effective addition to the query.

So let’s look at a real example, in this example users wanted about 10 accounts on all combinations. The structure of this cube was such that most of the dimensions (29 standard dimensions) would only have 1 intersection per cost center, but some of these contained 1000+ members, potential intersections here are in the millions, but we know only 20k have data. To give you an idea here are how the dimensions break down:

• Scenario, Version, Year, Period, Currency, Account– all as you would expect in a planning application, since this is a reporting cube for a planning application

• Reporting split dimensions – used like attributes, but very by period/year/scenario there are 20 of these with between 2 and 30 members

• Customer (1000 members), Entity (4000 Members), Location (300 Members) – data will only intersect at a couple combinations (1-5), and each of these 1-5 will only intersect with 1 reporting member/dimension/year/period

So our possible intersections here are obviously very large, but like I said, we know data is only in about 20k intersections (including accounts selected) so using the MDX template I just displayed, we expand the number of cross joins and write our new query.  We could test this in EAS but then our output is useless, so let’s open SmartView. In SmartView connect a sheet as an ad-hoc query to the database you want to query and you will see these options in the Essbase ribbon.

Clicking on Execute MDX will open a dialog, drop in your query, for some reason SmartView does not like query’s ending with a semi-colon so remove that if you have to.  

Click on Execute and be prepared for excel to appear frozen for a couple minutes. If you really want, you can check in EAS and see that the query is running, but with some faith, you will get your results in SmartView.  After waiting the required time for Essbase to run your query,  for the network to send you the results, and SmartView to process the results, in my test case about 3 minutes for 19556 rows 40 columns,  you will have something that looks like this: (data randomized for example)

And that’s all there is to it, MDX in SmartView, it really is not evil, and the users I have shown this to have fallen in love with it. There are a few things to call-out about this method:

• Your data must aggregate to top level, if not, it will be suppressed before the cross joins get to that dimension, if you have one dimension that does not, make sure it’s in the first cross join to execute and you will be okay
• Once you start a query, unless you have access to EAS, there is no way to stop it.
• This will work in a BSO as well, but query would need to be modified
• This query will be dependent on the users access to the cube
• Would recommend that tested as working queries are shared with users, you can use the SmartView MDX Library extension to do this with ease

I hope this helps someone, somewhere with their data pulls, if you have any questions feel free to leave a comment and I’ll get back to you.

-Dan