Category Archives: Essbase

Format Strings – Who Knew?

by TimG | 9 Comments

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

by TimG | 2 Comments

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

by TimG | 8 Comments

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

by TimG | 2 Comments

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

by TimG | 4 Comments

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

by TimG | Leave a comment

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 …

Firing Up 11.1.2.4 with John A. Booth’s Amazon Image

by TimG | 14 Comments

Update 9th October, 2015

John has decided to remove the Amazon Image discussed in this post because of Oracle licensing concerns. This post remains for reference and may still be of use with respect to opening an AWS account etc., but without the AMI it is not possible to follow the full process to set up an EPM 11.1.2.4 environment.

Busman’s Holiday

I haven’t posted to this blog for six weeks or so, because most of my free time has been taken up with a slightly different Essbase project: I’ve been writing a chapter (and co-writing another) for volume two of ‘Developing Essbase Applications’. It has been a great, if intense, experience and the book is shaping up to be a worthy successor to the first volume with a combination of new and returning authors. The print version will be available later this year. Follow a Twitter search on the hashtag #DevelopingEssbase or check back here for more updates!

11.1.2.4 is Here

Oracle EPM 11.1.2.4 appeared for download on OTN earlier this week. The arrival of a new EPM version is always exciting, with a flurry of social media activity as people install and test it (my initial observations were posted on the Qubix blog). The testing is the fun part, but the prospect of a from-scratch install never fills me with joy. Fortunately for me John A. Booth has already done the hard work, and released a Metavero AMI (Amazon Machine Image – essentially, a virtual machine snapshot from which any number of identical instances can be spun up on Amazon Web Services). Thanks John! In this post I’m going to explain how to make use of the AMI.

Continue reading …

Automatic CALCTASKDIMS and Empty Tasks

by TimG | Leave a comment

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 …

An Insight into ASO MDX Execution

by TimG | 1 Comment

If you’re familiar with Dan Pressman’s work on ASO internals, you’ll know that he sometimes talks about the number of “bitmap passes” required to answer a particular MDX query.  I don’t want to rehash everything Dan says here, but the following Kscope 2014  presentation (free “associate membership” required) contains a good explanation: How ASO Works and How to Design for Performance.  Dan’s chapter in “Developing Essbase Applications” touches on the same concepts.

In brief, what Dan says is that a given MDX query may require Essbase to run one or more “stored queries” under the hood.  He makes a pretty convincing case, using the analysis to show why (for example) a stacked hierarchy provides better query performance than pure MDX to calculate YTD.  However, I don’t think anyone has pointed out that MaxL exposes a measure that helps prove that this is really what happens: “kernel_queries_tracked”. Continue reading …