Dodeca: Create and use a new Essbase MDX Selector List

The most recent version of Dodeca brought several exciting enhancements for MDX-related functionality. One of these is a new selector list based on a reusable MDX script object. Although MDX queries are probably most often associated with queries that return numerical data from a cube, they also have incredibly useful metadata capabilities that can be employed for various purposes. In Dodeca, it’s common to use a report script or member query specification to return members from an outline. For example, you might want to provide your users with a selector such that they can choose a particular product (or products) from your Product dimension in order to customize a report that they will build dynamically.

I see MDX scripts as being a natural, clean, and flexible way to populate these selectors, and moving forward I will recommend them whenever possible over the more arcane report scripts that have been around for years.

That all said, what I want to show today is the following: I’m going to edit an existing Dodeca view so as to replace one of its existing selector lists with a new list based on an MDX query.

Continue Reading…

New MDX Examples Page

MDX has been around for many years, but it seems to be enjoying something of a renaissance right now. I think there are various reasons for this. Dodeca has supported MDX in various ways for quite some time, and even dramatically enhances MDX support in its latest 7.3 release, including an MDX editor with advanced syntax highlighting and autocomplete (!), support for member lists generated from MDX queries, and more. I really prefer MDX over report scripts especially when it comes to generating member lists. The equivalent MDX queries always seem a little cleaner and succinct.

To that end, I thought I would start collecting various MDX examples that process dimensions/members in certain ways put them up on a page. There are examples for the Sample/Basic database that show fetching members from a dimension at various levels, with a UDA, sorted forwards/backwards, removing duplicates, and more. It’s nothing earth shattering (considering the super complex things that MDX can achieve) but in the future I foresee MDX being used even more for things like this.

Running MDX queries through a JDBC driver (for fun?)

So there I am, sitting in front of the Alaska Airlines gate at Boston Logan airport, waiting for my flight home to Seattle. It’s not a particularly glamorous terminal – the divorce from Delta hasn’t been too kind to Alaska at BOS; Delta seems to have kept the house and kids while Alaska microwaves Lean Cuisine on a futon in its bachelor pad…

As I’m pondering why there are white rocking chairs in the terminal, my phone rings with a familiar name: Mr. Brian Marshall. We catch up and exchange pleasantries before pivoting over to more important matters (all things EPM of course!).

Brian: “So… Vess.”

Jason: “Oh boy…”

So we get to talking about accessing Essbase data through a Java database driver, á la Vess. And we get to talking about running MDX queries and dumping the output – á la Camshaft.

And as the talk goes on I end up saying something stupid like this: “You know what might work? Jjust pass an MDX query through the driver over to Essbase and map the output to a fake table… It’d be like an unholy combination of Vess and Camshaft. You could probably knock it out in a day or two.”

And at that moment I knew I wouldn’t be able to resist opening my laptop for the five plus hour flight home. Continue Reading…

Camshaft (Essbase MDX query tool) 1.0.2 released

Apparently I’m having quite the productive Friday, what with showing how easy it is to setup drill-through with Dodeca and that I’m heading to Oracle Open World 2017 to contribute to a presentation on cool Essbase tools.

To these articles I’ll add that I just released a Camshaft point release. This release has a couple of fixes and enhancements. Thanks to André Märki and others for providing feedback.

This version of Camshaft fixes an issue where some data with many digits after the decimal would be rendered in scientific notation. Along with this fix I have added a new command-line switch, --maximum-fraction-digits (used on the command-line such as --maximum-fraction-digits=2) to set the max number of digits to render after a decimal.

Additionally, there was a bug with running a query from a file that is now fixed. You can now specify something like --query=somefile.mdx and Camshaft will look for the given file. If found, it’ll read its entire contents for an MDX query, then execute that. This option can help make command invocations with big gnarly MDX queries a little easier to manage.

Please keep that feedback coming and I’ll add enhancements/fixes to the best of my ability. I have some interesting Camshaft news coming in the near future that some people will really like!

As always the latest Camshaft documentation and download can be found linked from the Camshaft page.

Camshaft MDX tool updated and available

Some of you may recall a tool I released quite some time ago (seemingly to beta-testing purgatory) called Camshaft. Camshaft is a simple Java utility that executes a given MDX query against an Essbase cube and outputs the results. The original version of Camshaft came out around two years ago. This version is built on the same framework but includes various updates and new options. In the interim, the output abilities of the MaxL interpreter have been improved a bit, and with the right incantation it can now output pretty useable data.

The name Camshaft is actually a portmanteau of who the tool is named for, and the feeling that he gets when writing a load rule (especially one loading in MDX data). It’s not every day that a tool is named after a tool, but I digress (I kid, I kid!).

Anyway, Camshaft offers a fairly wide array of options to customize the output from an MDX query. You can suppress headers, choose your column delimiter, how to format #Missing/#NoAccess cells, and more. There’s even an output option to generate an HTML table if you want.

You could run this query, for example:

SELECT
        CROSSJOIN({[Jan], [Feb], [Mar]}, {[Curr Year], [Prev Year]}) ON COLUMNS,
        {[Measures].Levels(0).members} ON ROWS

And you might get this output (depending on options):

	                        Jan, Curr Year          Jan, Prev Year          
	Original Price          #Missing                #Missing                
	Price Paid              #Missing                #Missing                
	Returns                 #Missing                #Missing                
	Units                   #Missing                #Missing

Of course, maybe you want Jan, Curr Year to be on multiple lines. Just pass in the --line-per-header command-line argument and get that output:

	                        Jan                     Jan                     
	                        Curr Year               Prev Year               
	Original Price          #Missing                #Missing                
	Price Paid              #Missing                #Missing                
	Returns                 #Missing                #Missing                
	Units                   #Missing                #Missing  

It’s fairly flexible. You can output to the console or a given text file, and more. You can suppress the whole header if you want. The latest version of the documentation for Camshaft is online (and will be updated from time to time as refinements are added), as well as inside of the Camshaft downloadable file. The Camshaft download site is here (also available on the small Camshaft info page).

Camshaft is a free utility offered with no support or warranty (although feature ideas are welcome), and is closed source (for now), although sometime in the future I may just open the source code up so that some intrepid developers can do what they want with it.