Add a Dimension to ASO without Breaking Aggregate Views

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.

Continue reading

Adding a Dimension to an ASO Cube

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.

Continue reading

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…

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

UltraEdit with Essbase: Part 1 – Running MaxL

I’ve been using UltraEdit (actually, UltraEdit Studio but this technique works fine with ‘regular’ UltraEdit too) as a text editor / SSH client / FTP browser for a couple of years now, although I’ve barely scratched the surface of its capabilities. In this series of posts, I’m going to show some simple but helpful features that I use when working with UltraEdit and Essbase. First up will be turning UltraEdit into a bare-bones MaxL environment.

Continue reading

Use an Essbase Substitution Variable in a Shell Script

A couple of weeks back I was writing some automation scripts on a *nix system (using KornShell, in this case) and needed to do something I haven’t previously tried – grabbing the value of a couple of Essbase substitution variables to use elsewhere in the shell script. I’m sharing a generic version since (as a relatively inexperienced programmer on Unix-like systems) I was pleasantly surprised by how simple the toolset made meeting this requirement, and more generally, the solution I came up with demonstrates several useful techniques from which other *nix neophytes may benefit:

  • Command substitution
  • Inline redirection
  • Filtering multiple lines with grep
  • Extracting tokens from a single line with AWK

Constructive criticism and comments are most welcome!

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

Deleting Essbase Applications that Won’t Start

A very quick post on a perennial Essbase problem: the application that refuses to be deleted. Generally this happens when an application has been corrupted, due either to a bug, a crash or some clown’s1 too-smart-for-own-good file system monkey business.  The application then can’t be started, and deleting an application requires that it be started first.  I ran into this today with an application that had somehow been created as BSO before an ASO backup was restored into its folder structure.

Continue reading

Making Sense of Versioning and Support Policy

EPM Support Documentation

Thanks to Oracle’s well-worth-following Business Analytics – Proactive Support blog, I’ve been introduced to a couple of interesting support policy documents relating to the EPM stack:

These documents also reference the more widely applicable Lifetime Support Policy: Oracle Applications (‘LSP’).

With no disrespect intended, I’d guess that many EPM practitioners are unaware of some finer points of support policy. I know that I was! And without understanding the content of these documents, it’s impossible to give comprehensive answers to questions such as ‘For how much longer is Oracle committed to providing fixes for my 11.1.2 system?’ (the answer may be more complicated than you think). However, the documents themselves tend towards legalese, and they don’t always make completely clear what the real-world implications of the policies are. The GPEC document was updated on March 6th to include 11.1.2.4 information, so this is an opportune time for a plain-English review.

Continue reading