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.
Tag Archives: MDX
ASO Query Tracking Trivia
A Network54 Essbase board user asked a question today that I’ve heard a few times without ever being sure of the answer:
“If the MDX report run after query tracking is enabled returns no data… ….does it still aggregate?”
In other words, if you turn on query tracking in ASO and then run queries, but those queries only return #Missing instead of finding some data, will the queries still affect the aggregate views that Essbase chooses?
Useless Use of Crossjoin
Even fans of MDX like me will agree that it isn’t the most aesthetically pleasing language, so it’s painful to see it made more convoluted than strictly necessary. This post is a grumble about a piece of redundant syntax that appears far too often, and has really begun to grate…
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’.
MDX and ASO – Implied Share Metadata Oddities
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
MDX and Missing Suppression
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