We all know that traditional BSO-based Hyperion planning forms have the best performance when you can keep your sparse dimensions in the Page and Point of View, and use dense dimensions in rows and columns. Particularly if you’re using relationship functions in the row or column expansion, such as Descendants or Children. But there may be times when user requirements result in the need to include sparse members in the rows or columns of the form. Luckily, by using the Suppress Missing Blocks form option, you can usually keep form retrieval time within acceptable parameters, even when calling level zero descendants of a sparse dimension in your rows.
However, what happens if you have a combination of dense and sparse members in your rows? Turns out the order in which you include them can make a significant difference in form retrieval time.
Form Performance Mystery
I was recently working on an input form where the business requirement involved putting two sparse dimensions and the accounts dimension (dense) in the rows. I started with the most intuitive layout from a user perspective – reading from left to right in the same order they would think about the data: Account, Sparse 1, Sparse 2. However, retrieving the form routinely took between 45 seconds to almost a minute! From a user perspective, that’s likely too long to sit, waiting for their form to render.
At first I thought it was the simple fact of performing row expansions on two sparse dimensions, in addition to retrieving two different scenarios as comparison columns. (In this application, Scenario is also sparse). However, the more I dug into it, I found other forms in this same application with similar sparse expansions in the rows, but with far better retrieval times. In reviewing the Essbase log for any additional clues, I notice a high number of blocks being calculated in the Dynamic Calc Cache when retrieving my problem form.
When comparing the block count to a similar form, the log showed a significantly smaller number of blocks being calculated. Not to mention the runtime on that other form was only a few seconds. What was going on?
Row Dimension Order Matters!
I tried changing the order of the dimensions in the rows to Sparse 1, Sparse 2, Account. Voila! Both the form retrieval time and number of blocks being dynamically calculated dropped significantly.
By having accounts as the first row dimension, the query being sent to Essbase was being written in a way to calculate 23 times more blocks than it really needed. Note – the account members included in the form were very straightforward, with most using simple addition to add up in a hierarchy. There were a handful of members with formulas, but nothing including cross-dimensional pointers to other dimensions.
MDX Trace confirms the numbers
I took it one step further and ran an MDX Trace on the slow & fast forms, which confirmed the order of the query going to Essbase.
Slow: NONEMPTYBLOCK {CROSSJOIN({[Account].[Cost]},CROSSJOIN({[Department].[Manuf]},{[Product].[Widgets]}))} ON ROWS
Fast: NONEMPTYBLOCK {CROSSJOIN({[Department].[Manuf]},CROSSJOIN({[Product].[Widgets]},{[Account].[Cost]}))} ON ROWS
So unlike the BSO FIX command, which reorders itself automatically based on outline order, regardless of what order your fix statement is in, the MDX query issued to Essbase from a Planning form evaluates blocks based on the explicit order on the form. By starting the MDX query with the sparse dimensions, you are limiting the number of blocks in which the dense member is evaluated.
Try, then try again
While the Sparse First then Dense rule worked for most of the forms, there were a few where the performance did not always improve, or worked better with some other order combination. Row layout is, of course, only one of many variables when designing well-performing forms. The lesson is – experiment with lots of options on your form design! And use all the tools at your disposal, particularly the Essbase log – it’s loaded with information.
Free eBook Download
In the eBook “Having a Conversation with Data”, learn what the current BI infrastructure has been and associated challenges with the traditional approach. How important the user experience is in order to best maximize data’s value (think visualizations!!) to your organization and how to gain a competitive advantage with modern analytics platforms.
