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’.
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.
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!
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.
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’s 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.
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 22.214.171.124 information, so this is an opportune time for a plain-English review.
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 126.96.36.199 environment.
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!
188.8.131.52 is Here
Oracle EPM 184.108.40.206 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.
I seem to be spending a lot of time in My Oracle Support (MOS) recently, and I’m going to say this is due to the vast quantity of helpful information contained therein, not because everything I touch has broken for past three months. It has been an opportunity to explore some MOS functionality. One nice feature I hadn’t seen before is the configurable ‘Hot Topics’ email, which provides a great way to keep up to date with new support information. Continue reading
No MDX, Essbase or ASO internals in the post (you may be relieved to read).
Instead, I want to highlight a very simple but wonderful feature of the Firefox browser – using Smart Keywords (aka ‘Smart Bookmarks‘) to perform custom searches. In brief, ‘Smart Keywords’ allow you to associate the search function of a web page with a text keyword.
If you’re already familiar – as many ‘tech’ types will be – you can stop reading now. If you aren’t, I think you’ll be very pleasantly surprised by how easy this makes accessing your go-to EPM online resources. Continue reading
Snappy title, huh?
This post is a quick note on a powerful but hopefully non-obvious feature of MDX against Essbase, inspired by an interesting thread on the Network54 Essbase board. That discussion is about implementing custom groupings from the reporting layer – i.e. being able to retrieve roll-ups that don’t exist in the underlying cube. For example, the ASOsamp.Sample Products dimension looks like this:
Suppose that you want to do some reporting on the total of [Personal Electronics] and [Home Entertainment], for which there is no roll-up in the cube. MDX can do the summing, of course. But won’t that mess up any ratio- or variance-type measures, by adding the values for [Personal Electronics] and [Home Entertainment] together? As it turns out, the answer is ‘not necessarily’. Continue reading