Using TRACE_MDX with Planning

Introduction

As I showed in my last post, TRACE_MDX can be utilized with Planning ad hoc forms. Oh, terrific, but do I really and truly have to go into an ad hoc form to see the layout? Besides, going into an ad hoc form changes its structure and maybe I want to see what that form’s MDX looks like from the word “go”. Is there a way to do it? You betcha.

It’s just this little chromium switch

All you need to do is to go into Planning form design and select “Suppress missing blocks”.

Simply save the form and open it back up.

 

 

And take a look at the (by now) good old mdxtrace.log file and see…

===============================================================

Following MDX query executed at Sun Apr 14 14:05:15 2013

===============================================================

SELECT {[Period].[Jan],[Period].[Feb],[Period].[Mar],[Period].[Q1],[Period].[Apr],[Period].[May],[Period].[Jun],[Period].[Q2],[Period].[Jul],[Period].[Aug],[Period].[Sep],[Period].[Q3],[Period].[Oct],[Period].[Nov],[Period].[Dec],[Period].[Q4],Hierarchize(Descendants([Period].[YearTotal]),POST)} ON COLUMNS,

NONEMPTYBLOCK {Hierarchize(Descendants([Account].[IncomeStatement]),POST)} ON ROWS

FROM SampTest.Consol

WHERE ([Segments].[BAS],[Entity].[E01_0],[Year].[FY13],[Scenario].[Forecast],[Version].[Working])

=== MDX Query Elapsed Time : [0.068] seconds ===================

And what do we get from this?

A whole bunch of things:

1. There’s that NONEMPTYBLOCK statement again. You know, the thing that makes BSO MDX queries so fast. Yup, it sure is interesting that it has been around in MDX for such a long time, and used for such a long time in Planning (I am going to guess since 11.1.1.1 as that’s when I remember Suppress Empty Blocks becoming available). And yet it wasn’t ever documented. Why?
2. The MDX portion of the form took only 0.068 seconds.
3. Did you see the Hierarchize function? And the POST option? Check out the Essbase Technical Reference topic on Hierarchize – do you see how Oracle could make expansion work either way (up or down) if they wanted to? Although I suspect there is a reason that this is not exposed as I’ll explain/guess at in a bit.
4. Columns get treated differently than rows. What do I mean? If you look at the form layout screen shot, you’ll see that the Planning form command to get all of the periods is IDescendants(YearTotal). That’s how Accounts are defined as well. And yet the MDX clearly shows individual selections for each period and a Descendants of YearTotal where Accounts are simply a Descendants function. Why?

What do I mean by that? I took the MDX and stuck it into Smart View using the Execute MDX command and got the following columns:

Jan

Feb

Mar

Q1

Apr

May

Jun

Q2

Jul

Aug

Sep

Q3

Oct

Nov

Dec

Q4

Jan

Feb

Mar

Q1

Apr

May

Jun

Q2

Jul

Aug

Sep

Q3

Oct

Nov

Dec

Q4

YearTotal

Interesting, eh? Apparently (well, definitely, actually as we can see) Planning needs the columns twice, once through explicit selections and then again through Hierarchize(Descendants([Period].[YearTotal]),POST). Isn’t that just odd?

This must be something internal to Planning as this simpler MDX gives me exactly what I would expect wrt columns, i.e., non-repeated months.

SELECT {Hierarchize(Descendants([Period].[YearTotal]),POST)} ON COLUMNS,

NONEMPTYBLOCK {Hierarchize(Descendants([Account].[IncomeStatement]),POST)} ON ROWS

FROM SampTest.Consol

WHERE ([Segments].[BAS],[Entity].[E01_0],[Year].[FY13],[Scenario].[Forecast],[Version].[Working])

 

Dear Oracle Planning Product Management (or more likely Development) – what the heck is going on? Why oh why oh why does Planning need almost double the columns? Weird.

And what’s really weird

As I stated in the beginning of this post, one needs to flip the Suppress Empty Blocks switch to make MDX fire on form retrieval. And that implies that only this setting (I suspect it is the only way to easily get to the functionality NONEMPTYBLOCKS provides) makes Planning use MDX. I am further guessing, per what My Man In California, Glenn Schwartzberg, stated in the comments section of last week’s blog re default retrieves in Smart View, Planning must use the Grid API to do standard retrieves. I find that fascinating because it has been “common” knowledge that Planning uses MDX to retrieve forms. TRACE_MDX tells us quite clearly that in fact that is not true.

And so that then suggests that maybe MDX still isn’t the fastest or best way to retrieve data from Essbase. I guess I shouldn’t be super surprised that nothing beats a native API, but I do wish this stuff was documented. Wait, it just was. :)

Be seeing you.