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.
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?
At long last, Oracle Analytics Cloud – the Oracle Cloud offering that includes Essbase Cloud along with BICS and DV – is on sale. I’m going to have a lot to say in more detail about this product over the coming weeks, but as a long-time “Essbase guy”, my first blog post will be aimed at people who already use Essbase on-premises, and some of the questions I think they’ll have: Why would you want to consider Essbase Cloud? How does it differ from the on-premises product? If Essbase Cloud is compelling, how would you move existing applications to test it out?
I’ve been working on tuning aggregate views with a test copy of a large ASO cube to which I added some additional dimensions. Tuning aggregate views can be tricky, because aggregate views make query performance heavily dependent on the exact combination of levels being queried. And there’s always some user that comes along with an unusual query that happens to hit a combination of levels that performs particularly poorly. So when I handed the system over for front-end certification by users, I enabled query logging. By parsing the log for “worst case” query times, I could proactively monitor and then investigate any particularly nasty cases the users encountered. Unfortunately, I was being a little bit too smart for my own good.
This post is a quick follow-on to my last, inspired by the same piece of client work. Fair warning: it’s only going to make sense if you are already somewhat familiar with aggregate views and view definition scripts (.csc). If you’re not already familiar with the concepts but want to read this anyway, I’d refer you to a presentation given at Kscope11 as an excellent (ahem) primer on the topic (free associate membership of ODTUG required).
But in summary: Many people maintaining larger or complex ASO cubes have developed very carefully crafted sets of aggregate views to optimize query performance. They also know that, unfortunately, some structural changes can invalidate those view definitions – adding levels to stored dimensions and adding new stored dimensions to name two. This can necessitate a lot of painstaking, trial-and-error optimization to generate a new set of aggregate views that provide equivalent performance to the original set.
In the course of adding a new dimension to an existing cube, I realized that there was a straightforward way to preserve the validity of my existing set of aggregate views.
A long gap since my last blog entry – for which I’m going to mostly blame Kscope…
I’ve mentioned before that I really enjoy the Unix tools and am working at becoming more competent with them. In this post, I’m going to explain why adding a new dimension to an ASO cube while retaining existing data is slightly harder than doing the same to a BSO cube, and then show how I used a very simple script (ksh, but these are such basic commands that I’m sure it can be translated easily if not used exactly as is) to modify native export files and solve the problem.
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
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’.