Do this, not that: Current vs. Prior Year dynamic calc in Scenario

Here’s just a quickie I saw the other day. Imagine a normal cube with a Years dimension, a Scenario dimension, and any other normal dimensions. Years contains FY2012, FY2013, FY2014 or similar and so on. Scenario contains Actual, Budget, and all the other normal stuff you’d expect to see.

Naturally, the Scenario dimension will contain all sorts of handy dynamic calcs, starting with our trusty Actual to Budget variance:

Actual vs. Budget: @VAR("Actual", "Budget");

So far so good.

How about a scenario that gives us the current year versus the prior year? Don’t do this:

@VAR("FY2014", "FY2013");

Or this (which is I guess slightly better but still not quite great):

@VAR(&CurrentYear, &PriorYear);

Why shouldn’t you do this? One, it requires maintenance – the kind of maintenance that is easily forgotten about util a user calls up and says that something doesn’t look quite right.

Second and more importantly, it’s semantically wrong. Hard-coding the year effectively breaks the inter-dimensional promise that our cube is ostensibly making – which is that the Scenario value we’re looking at should be based on the current Year member – not some arbitrary member irrespective of the POV.

(This all being said, yes, there could be a legitimate design reason to code a dynamic calc in Scenario that is always the current year irrespective of the POV, but I digress).

A simple formula can get us the prior value:

@PRIOR("Actual", 1, @CHILDREN("Years"))

As well as the actual versus prior:

@VAR("Actual", @PRIOR("Actual", 1, @CHILDREN("Years")));

Note that this assumes there is nothing else in the Years dimension and that it’s got a typical “ascending” sort (2010, 2011, 2012, in that order). If you have a years dimension going in descending order you could put -1 in for the @PRIOR command or just switch to @NEXT.

There you have it – a simple cleanup that saves maintenance, doesn’t rely on outline variables being updated, is intuitive, and more importantly, is doesn’t break the semantics of the cube.

Drillbridge 1.0.2 now available

Hot off the digital press is an updated Drillbridge. Version 1.0.2 brings several fixes, enhancements, and more. There’s still a few rough edges but I’m trying to keep a fast cadence with releases so I don’t get too bogged down. Of particular note is the following:

  • New infrastructure in place that should make applying upgrades much easier. So when 1.0.3 comes out you should be able to pretty smoothly update. Note on this version: you CANNOT upgrade from 1.0.0 or 1.0.1 to 1.0.2. Sorry. You should copy and paste the contents of your connections/reports and recreate them. This should be the last time you have to do this.
  • You can now create and modify server mappings, cube mappings, and deployment specifications. So you can create an entry for your main Essbase server, then a mapping for Sample/Basic, then a list of members that the drill-through report is valid for in Sample Basic, and then just deploy the report directly to the cube (no more need to copy and paste code using EAS!) Of course, you might still want to crack open EAS so you can validate that everything got setup properly.
  • Very powerful expression syntax and convenience functions (please see previous post about how it looks). This should eliminate the new to setup mapping tables and views in a huge number of use cases. For example, if your cube has members like Jan, Feb, Mar, and the database has 01, 02, and 03, then you can use a new handy #monthAbbreviationToTwoDigits function to convert without a mapping table. This is super cool.
  • The initial version of drilling from upper level members in an Essbase cube is now implemented (!). For example, drilling on Qtr1 will create a query with Jan, Feb, and Mar because Drillbridge will open the outline up real fast and read the members. Please note that this feature works but has not been extensively tested. I am thinking that future versions will need an outline cache module in front of this in case it just takes too long to ransack the outline. Please let me know how it goes for you.
  • Note that row limit and debug mode are not implemented yet, they just have GUI elements so far.

The road to 1.0.3

This release packs a lot of great stuff that I’m proud of. As time permits I will be working on the next version, 1.0.3, which will further enhance and refine things. In particular I am looking to implement some or all of the following:

  • Row limit
  • Debug mode
  • Create/edit/delete server variables
  • Ability to use variables in queries
  • More information when errors occur
  • Example connections built-in to help get things started
  • Result pagination

For now the only major feature on the horizon will be drilling further from an drill sheet – this is partially implemented right now but it’s a little tricky to get right so I’m taking my time on this. From there on out I predict that the majority of changes to Drillbridge in the future will be fixes and simple enhancements.

As always, please head over to the Saxifrage Systems Drillbridge page to get to the downloads. Please don’t hesitate to check out the support forums which I monitor regularly.

Thank you again to everyone out there that has downloaded and played with this thing. The feedback has been incredibly useful. Any problem or issue you run into I am happy to take a look at and fix up if possible. No issue is too small – literally anything you can report to me that I’m unaware of will result in me being able to make this tool better for everyone in the community. Also, if you deploy this to production, please tell me about it! Drillbridge is officially in production at a major restaurant chain (more info coming as soon as possible) and I would love to know about more.

New functions and fixes in Drillbridge 1.0.2 and first enterprise deployment!

Drillbridge 1.0.2 should be ready sometime next week. I’m putting some extra time into testing to make sure that some of the sharp edges are not so sharp. I successfully deployed Drillbridge to an enterprise client this week and learned a few things in the process (oh, and by the way, Drillbridge is now running in the enterprise!).

Some of the unique or new challenges that I bumped into were these:

  1. Need to use integrated security on connection to Microsoft SQL Server
  2. Source dimension in cube has space in name (“Business Unit”)

Solving the SQL Server integrated security thing isn’t too bad. I just had to include a DLL from the Microsoft SQL Server JDBC distribution that enables this, and setup the JDBC URL accordingly. Fortunately I had already relaxed the requirement on JDBC connections so that they aren’t required to have a password (which is the case for integrated security).

Secondly, a space in the dimension name was an edge case I hadn’t considered before. Because the new variable interpolation mechanism makes the source member from each dimension available as a variable (like #Years or #Scenario), it simply wouldn’t parse with s space. So now spaces are converted to underscores, so in this case you can use the #Business_Unit variable to get at the submitted value for the business unit.

New Functions

To this point it has been necessary to setup a view or a mapping table on the relational side that can help translate from the members in the outline to the data in the source table. In order to ease this process for some common transformations, some custom functions have been added to the expression parser:

  1. #monthAbbreviationToTwoDigits – converts Jan to 01, Feb to 02, etc
  2. #monthAbbreviationToDigit – converts Jan to 1, Feb 2 to, Dec to 12, etc
  3. #removeStarting – strips a prefix off a member, if present, like changing BU001 to just 001

On top of these functions, the new expression language being used under the covers gives access to a huge library of Java functionality that can convert most members to their proper equivalents in the relational table. This should enable mapping from members to the source names in 90% of cases, if not more.

Speaking of the new expression language, let’s see what queries look like now:

SELECT PD, YR, AMT FROM Transaction WHERE PD IN ({{"name" : "Business Unit", "expression" : "#Business_Unit + 'BU'", "drillToBottom" : "true"}})

What’s going on here? A few things have changed. As with before, the token is enclosed in double curly braces ({{}}). Now inside is a simple JSON expression – for those not familiar this is an industry standard notation for writing complex data structures.  Between the curly braces is the following information:

  1. name = “Business Unit”
  2. expression = “#Business_Unit + ‘BU'”
  3. drillToBottom = “true”

What’s going on here? This indicates that the name of this token is Business Unit (for reference purposes). Also, the value of the expression is the value of the #Business_Unit variable, then suffixed with the text ‘BU’. Note another twist though: drillToBottom is set to true. In this case, the associated Essbase outline to this report will have the member from Business Unit looked up, then get it’s level-0 descendants, then suffix them like in the normal expression. A little confusing to explain so here’s what the finished query sent off to the database looks like:

SELECT PD, YR, AMT FROM Transaction WHERE PD IN ('BU123-10BU', 'BU123-20BU', 'BU123-30BU')

Note that the children of the queried member (BU-123 in this case) are BU123-10, BU123-20, and BU123-30, then our expression suffixed them with the BU as from above, so that the query is mapped properly for our relational data source. Not all expressions will necessarily be this complex, but this shows the simple power of a few options combined together.

People have said they are really excited to drill to the lower-level members from upper-level members and I’m happy to say it’s working quite well. Version 1.0.2 of Drillbridge will come out next week. Unfortunately you cannot upgrade from 1.0.1 or 1.0.0 to this version. The simplest thing to do would be to copy all of your connection info to a text file and then re-input it. Sorry for the inconvenience, but the database schema migration stuff is just now being added which will make upgrades simpler in the future.

The MaxL “Repeated calls to startMaxl.bat stops working” on Windows issue

I’ve run into this before and a few colleagues have recently run into this. Sometimes you’ll write some MaxL automation that loops a lot, for example, looping over a bunch of input files (perhaps you have a single text file per day of data or something).

So in your batch file you have a for loop or just a bunch of repeated calls to startMaxl.bat (note that startMaxl.bat is a wrapper of sorts for essmsh, the MaxL interpreter). The first X number of runs work just fine, then all of a sudden new calls to the script stop working. Closing the command prompt window and reopening it “fixes” the issue.

In the past when I bumped into this it was very tricky to troubleshoot. Let’s look at the code for startMaxl.bat:

@ECHO OFF
@REM This file is created to startMaxl
rem Set ESSBASEPATH
call "%~dp0\setEssbaseEnv.bat"
set PATH=%ESSBASEPATH%\bin;%PATH%
%ESSBASEPATH%\bin\essmsh.exe %*

Nothing too fancy, but notice that second to last line – where we are setting the PATH to be the ESSBASEPATH variable PLUS the existing PATH. This is a fairly common pattern. The problem, though, is that Windows has a limit to how long the value of a variable can be. The repeated calls to startMaxl are increasing the size of the PATH variable in that environment (i.e., the changes aren’t permanent) and once the length limit is hit, the job fails.

The workaround? There’s a couple. If you can call essmsh directly then use that. Sometimes you get a “MaxL initialization error” or similar  – this is due to environment variables not being set (check the setEssbaseEnv.bat file for the variables that need to be set). You can add these to your system variables and call essmsh directly. Alternatively we could change startMaxl or create a new file next to it that will only append the PATH once (say, by tracking this in another temporary variable).

Just wanted to pass this on since it ca be a little tricky to troubleshoot. Good luck!

 

Easy Hyperion Drill Through and the road to Drillbridge 1.0.2

I feel like I’m repeating myself but I have to say again that the response to Drillbridge has been absolutely incredible. A lot of you are providing some awesome feedback. Perhaps one day this little tool will be up there with the venerable Outline Extractor.

In any case, I’ve actually been incredibly (actually, unbelievably) busy with other projects, but Drillbridge is like a hobby for me so I have a few updates planned for the next release. This will be version 1.0.2:

  1. Paginate returned results if desired
  2. Create, edit, and update server and cube mapping definitions, and deployment specifications (the members that are drillable)
  3. Maybe (I thought this wasn’t in the Java API but apparently it is, at least for modern Essbase versions): deploy drill-through definitions to a cube mapping

I’m trying not to bite off more than I can chew for a single release. There are a number of things that are planned for an as-of-yet unnamed future release:

  1. Drill to descendants of upper-level  member (i.e., drill on Qtr1 to do a drill using Jan, Feb, Mar)
  2. Custom image/header/footer on results pages

And looking way down the road:

  1. Drill from a drill – this is actually fairly complicated so I want to make sure I get this right. But basically you’d be able to keep drilling through data. A lot of people are interested in this for drilling to invoice PDFs, other tables, and so on.

Please keep the awesome feedback coming and know that I’m dedicating all the energy I can to making this a useful tool!

Drillbridge 1.0.1 out the door

Things are a little fast and furious with the Drillbridge software right now. I can’t believe the overwhelming response to this free little tool so far. In the last week alone, Drillbridge has been successfully deployed and by the end of the month it’s looking like Drillbridge will be deployed in an official capacity for a prestigious restaurant chain. Who’d have thought this little proof of concept would turn out to be a nice tool for scratching the drill-through itch that so many organizations have?

Based on the immense, thoughtful, and positive feedback, this release includes fixes/enhancements along the following areas:

  • Update SQL Server JDBC driver to latest version
  • Fix for when query has no parameters in it
  • Notes about Java being required on the PATH
  • Added EULA
  • Query size can now be 4000 characters (was 255)
  • Parsing of payload from SmartView drill operation is now more robust
  • Cleaned up logging to try and reduce clutter a bit
  • Removed several unused/test dependencies, shaving 13MB off download
  • Now includes Oracle driver (thin client)
  • Show examples of JDBC URLs on connection creation screen
  • Note: Essbase Servers and Deployment Specs are still unused/unusable in this release.  Reports need to be deployed manually
  • Upgraded several dependencies to newer versions

As usual, this download is available from the Saxifrage Systems Drillbridge downloads page. Please read the release/upgrade notes and head over to the forums if you have any issues!

Drillbridge weekend update

I am incredibly humbled and impressed with the feedback that Drillbridge is getting already – on a holiday weekend no less! I am very happy to say that Drillbridge has been successfully deployed by someone other than myself – on a different continent no less!

There are a few bits of very early feedback that I will be incorporating soon (in the upcoming 1.0.1 release). Namely the following:

  1. At this time, Drillbridge requires Java 1.6+ to be in the system PATH
  2. Drillbridge runs as a service but is only tested on 64-bit Windows machines
  3. The Microsoft SQL Server JAR file will be updated to the more modern version in 1.0.1
  4. Various bugfixes/adjustments/improvements (of course)
  5. Drill from SmartView only works with Internet Explorer as the default browser. This appears to be a limitation/requirement in how SmartView implements opening a browser. Drilling from Planning should work with any supported browser there but I haven’t had a chance to test yet.

Please feel free to head over to Saxifrage Systems, or if you want a convenient forwarding link you can use a new Drillbridge URL I just picked up. Also, Saxifrage Systems now has forums in case you want to post there. I will do my best to monitor them and be timely with responses.

Drillbridge, the simple way to implement drill through

I implemented a drill through solution for a company earlier this year. We used a combination of Essbase Studio and ODI. I always felt right at home with Essbase Integration Services (EIS) but hadn’t played too terribly much with Studio – until now.

The solution came out working beautifully. Along the way, however, my research into the solution options led me down some interesting paths. In particular is that Essbase has supported drill-through definitions on cubes for quite some time. In practice this means you can define intersections on the cube that should be drillable, and then provide a URL to drill to. Generally this will be some Oracle software but the door is open to drill to anything – provided you can implement the backend properly.

What started out as a proof of concept has evolved into software that I am calling Drillbridge. Drillbridge is a turnkey solution for implementing drillthrough to a relational database. All you need is the following:

  1. An Essbase cube
  2. A relational database with data you want to drill through to that can be mapped somehow from the source data
  3. SmartView (sorry Excel add-in users)

I should add a little bit more about that second point: if the member in your cube is Jan or January but you have a table somewhere that has a field with a value of ’01’ or similar, then you need to construct a view/query that can map from one to the other somehow. Similarly, if you prefix your Account members with A or A_ or something, then you just need to construct the query to transform the data accordingly. In practice I am finding that this isn’t too hard.

Drillbridge works out of the box with Microsoft SQL Server, Oracle, and MySQL, with potential support for pretty much anything that JDBC can connect to (hint: that’s almost anything on the planet).

I’ll have some more info in the coming weeks but for now I threw together a pretty quick video showing the basic Drillbridge admin interface and a working demo from the venerable Sample/Basic database.