Snappy title, huh?
This post is a quick note on a powerful but hopefully non-obvious feature of MDX against Essbase, inspired by an interesting thread on the Network54 Essbase board. That discussion is about implementing custom groupings from the reporting layer – i.e. being able to retrieve roll-ups that don’t exist in the underlying cube. For example, the ASOsamp.Sample Products dimension looks like this:
Suppose that you want to do some reporting on the total of [Personal Electronics] and [Home Entertainment], for which there is no roll-up in the cube. MDX can do the summing, of course. But won’t that mess up any ratio- or variance-type measures, by adding the values for [Personal Electronics] and [Home Entertainment] together? As it turns out, the answer is ‘not necessarily’. Continue reading
Some Old News
One relatively unsung enhancement to Essbase in 126.96.36.199 was a change to CALCTASKDIMS behavior. Before 188.8.131.52, CALCTASKDIMS defaulted to a value of 1. From 184.108.40.206, Essbase selects a value for CALCTASKDIMS automatically unless overridden by the user with either the CALCTASKDIMS .cfg file setting or the SET CALCTASKDIMS calculation command.
So why am I blogging about this years after 220.127.116.11 came out? First, there is a good theoretical reason why the above Essbase ‘enhancement’ might have a seriously negative effect on calculation performance, which can be especially surprising when it occurs following a supposed upgrade. Second, this actually bit a coworker a few days ago, and it’s always satisfying (for me, if not my coworker) when empirical data and theory coincide.
Logging out users prior to performing an administrative task is such a common requirement that I give it barely any thought. This complacency bit me recently, and – since I suspect that I’m not alone – I want to share a simple observation about the MaxL operations involved. Continue reading
If you’re familiar with Dan Pressman’s work on ASO internals, you’ll know that he sometimes talks about the number of “bitmap passes” required to answer a particular MDX query. I don’t want to rehash everything Dan says here, but the following Kscope 2014 presentation (free “associate membership” required) contains a good explanation: How ASO Works and How to Design for Performance. Dan’s chapter in “Developing Essbase Applications” touches on the same concepts.
In brief, what Dan says is that a given MDX query may require Essbase to run one or more “stored queries” under the hood. He makes a pretty convincing case, using the analysis to show why (for example) a stacked hierarchy provides better query performance than pure MDX to calculate YTD. However, I don’t think anyone has pointed out that MaxL exposes a measure that helps prove that this is really what happens: “kernel_queries_tracked”. Continue reading
Essbase is Wrong
Once or twice I’ve got stuck troubleshooting an ASO query or MDX member formula, only to discover – after some head-scratching – that Essbase and I disagree about the appropriate value of a metadata property on an implicitly shared member. Whether the (very strange) behavior described below is by design I don’t know, but since ASO and BSO do not behave the same way and I can’t find any documentation describing a difference in this area, I have to suspect not. Continue reading
One feature of all Essbase query tools is the ability to suppress (i.e. remove from the result set) #Missing values, in most cases via a simple, on-or-off option that applies to full rows. In MDX, #Missing suppression is slightly more involved, but also has some interesting subtleties that make possible queries that would be harder or impossible (at least, in one step) to achieve using other tools. Continue reading
The final day of Kscope this year sees a change in format from ‘regular’ presentation slots to Thursday Deep Dives – two-hour sessions which all do something a little different to the norm, and are well worth sticking around for. Continue reading
This OTN thread reminded me of some unsuccessful work I did a while back trying to ensure that the appropriate version of Java was invoked when launching EAS from the web. After reviewing my notes and doing more digging I came up with a (hack-y, admittedly) workaround, so I decided to write it up. Continue reading
Block density – that is, the percentage of non-missing dense intersections at a non-missing sparse intersection – is an important performance parameter for BSO databases, but the Essbase documentation does not provide details of the algorithm used to derive the ‘Average Block Density’ statistic visible in EAS or via MaxL. Continue reading