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.
Category Archives: BSO
Data Cache Size Recommendation – Why?
A quick post prompted by a recent OTN thread. The Essbase Database Administrator’s Guide tells us that the recommended size of the Data Cache is 1/8 the size of the Data File Cache. Even granted the usual caveats on these recommendations, I think this one is really odd.
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.
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’.
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.
Automatic CALCTASKDIMS and Empty Tasks
Some Old News
One relatively unsung enhancement to Essbase in 11.1.2.2 was a change to CALCTASKDIMS behavior. Before 11.1.2.2, CALCTASKDIMS defaulted to a value of 1. From 11.1.2.2, 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 11.1.2.2 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
Reverse-Engineering Block Density Statistics
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