MDX – CrossJoins, Filters and “Perspective”

This post expands on a concept I touched on a few years back in the context of #Missing suppression (see the section on NONEMPTY in this post) and specifically the “perspective” from which any kind of filter – for #Missing or not – is evaluated. This question on the Essbase Users board got me thinking about the topic again, and the potential MDX ‘gotcha’ it presents.

Continue reading

Why BSO Disk Space doesn’t follow Block Density

When choosing which BSO (or Hybrid) dimensions to make dense and which to make sparse, a higher block density is generally considered ‘better’. This is a simplification, as the densest combination isn’t always the best performing, but bear with me. The point I want to make and explain in this post is this:
When using bitmap compression, squeezing a given set of data into a denser arrangement doesn’t always result in a space saving of the same magnitude
This isn’t totally intuitive, and it can come as surprise to see that your huge increase in block density has only made a modest dent in the size of your .pag files.

Continue reading

Format Strings – Who Knew?

Yet again, a post prompted by a question on OTN (this one from my UK-based co-worker Russell). And yet again, a question on OTN that results in me appreciating a feature of which I was almost completely ignorant. This one has some remarkably useful, and, as far as I know, unique applications. With format strings you can, for example:

  • Return values from Essbase including a — potentially variable — unit of measure (e.g. USD99.99, JPY250,000)
  • Return values in specific or complex formats (e.g. 22 September 2015)
  • Return values using mixed measures (e.g. 3’6″ for 42″, 12lb 5oz for 197oz)

Even better than advanced formatting, however, is the fact that Format Strings can be hacked to do some completely non-format-related but very useful things. The documentation does not discuss this possibility at all, and searching for information on this I could find only one example online in a blog post from Brian Marshall. Because Format Strings allow the use of any MDX function, we can choose to ignore data altogether and return information from metadata or other functions:

  • Retrieve a datestamp from the Essbase server on to a report
  • Show both a member and its parent (and / or grandparent, and / or great-grandparent, etc.) on the same row
  • Show the attribute associations of a member without time-consuming drill and suppress shenanigans
  • Show UDA associations

This post describes the basic Format String functionality, shows some simple and then more complex format examples, and concludes with the fun non-format ‘hacks’.

Continue reading

Essbase Security Filters and ‘Background’ Access Levels

Security filters can be difficult beasts at the best of times, especially when it comes to the interaction of multiple filter rows / multiple filters (I glaze over on reading the DBAG statement that “a filter that defines a more detailed dimension combination list takes precedence over a filter with less detail”). In this post I’m going to discuss a particularly confusing behavior involving the interaction of calculation privileges with filter access. I’m not the first to discover or comment on the following phenomenon, but I don’t think it’s been written up comprehensively. The DBAG entry for filters certainly doesn’t make any mention of it, which seems like an oversight.

Continue reading

Automatic CALCTASKDIMS and Empty Tasks

Some Old News

One relatively unsung enhancement to Essbase in was a change to CALCTASKDIMS behavior.  Before, CALCTASKDIMS defaulted to a value of 1.  From,  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 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.
Continue reading