Recent Stories

End of the road for EIS, start of the road for Drillbridge

Essbase Integration Services (more commonly referred to as EIS) has been officially end of lifed. I have always been a huge EIS fan since I first learned about it, and can’t help but feel a little nostalgic about my early days with it. It was even the basis of a number of my early blog articles going all the way back to 2009 or so, and to this day those articles remain some of my most popular, if only because there are precious few articles on EIS out there.

Most of you readers are probably familiar with EIS and its position in the greater Essbase landscape, but for those who aren’t familiar, here’s a rundown: EIS is can create and update Essbase outlines and data using data stored in a relational database. Don’t be fooled into thinking this is just an Essbase load rule that is pointed to a SQL table. EIS is a veritable Swiss army knife in terms of building outlines, with much flexibility with generating the levels, parent/child builds, setting member properties (formulas, UDAs, consolidation operator), and then loading the data.

EIS really shines when you have a well-designed database schema to use as your basis: primary keys, foreign keys, proper data modeling, and so forth. This might seem pretty obvious but the fact of the matter is that many of the environments I have seen using EIS have implemented it with a poorly constructed data source. EIS still works in this scenario, of course, but sometimes it’s just not as great.

You may already know that Essbase Studio is the successor (perhaps more spiritually than technically) to EIS, in that it provides the ability to create cubes/outlines and load data from relational tables. Essbase Studio improves upon EIS in many ways, but also significantly changes things around. I am an expert with EIS but by no means an expert with Essbase Studio (just to provide a somewhat narcissistic reference point for how much of your EIS knowledge might transfer over to Studio).

One of the big things that EIS and Studio bring to the table is the ability to implement drill-through. So perhaps you’re loading some transactional data that has been consolidated somehow, implementing drill-through with EIS/Studio enables you to provide the ability for users to see a number in the cube and then drill through to the individual rows that make up the value for that cell.

This drill-through ability is one of the bigger use cases for EIS/Studio, in fact, it’s such a compelling feature that I have seen a number of environments that have done a skeleton EIS/Studio implementation so they could get drill-through, but then keep managing the outline with EAS and use their normal automation. I have seen and used both HAL and ODI to populate metadata tables for EIS/Studio to use in these instances.

That all being said… Don’t build a pretend EIS/Essbase Studio environment just to get drill-through.

If the only thing you want from EIS or Studio is its drill-through abilities (in other words, you wouldn’t otherwise have relational tables to source data from), then Drillbridge is an incredibly compelling alternative.

Drillbridge, as I have extensively written (maybe I should call this Jason’s Drillbridge Blog), is a drop-in solution for drilling from Essbase to any relational data you have. Drillbridge works with SQL Server, Oracle, DB2, MySQL, and should work with any database for which a Java driver exists (hint: practically every database out there).

I’ll be writing more about this in the coming weeks, but besides being a good solution in its own right, Drillbridge could significantly improve and simplify the architecture and automation of any environment with a “fake” EIS/Studio setup. In other words, if you are feeding data to relational tables only because you have to in order to get the EIS/Studio outline “overlay” to work and provide drill-through, you could instead drop the use of EIS, the automation for extracting dimensions/populating tables/updating the outline, and instead just drop in Drillbridge and create a mapping from your metadata (outline) to your relational data.

For the record, I like Essbase Studio and this is nothing against it, particularly when solutions that involve it are constructed properly. But this particular use case is just such a slam dunk for Drillbridge that could improve numerous environments I’ve seen, I just have to point it out, especially in light of EIS being end-of-lifed.

Drillbridge 1.3.1 available for download

Just a quick post. Like the subject says, a new point release of Drillbridge is now available in the normal downloads location. Drillbridge continues to receive polish and fixes. Nothing too earth shattering this time around, just more DRILL-THROUGH AWESOMENESS:

  • Queries can now be 8000 characters instead of 4000
  • Custom connection settings to issue ALTER or SET commands on your database if needed
  • Workflow around connections and reports improved
  • Drill-through deployment improved for Planning and Financial Reporting
  • Duration of report execution now logged! (Check out how long those SQL queries are taking)
  • Various bugs fixed
  • Polish, polish, polish

I’m really happy with this release. The codebase is looking really solid, things are working great, feedback from users has been ROCKIN (thank you Peter, Sebastien, Julien, and many others). Drillbridge is being used in French, German, Russian, and English-speaking countries, much to my amazement. I haven’t spent too much time on internationalization (making software work well in different languages and locales) but Drillbridge has some cursory support for formatting numbers and dates in a nice way, so that’s a good start. Down the road I’d like to offer Drillbridge in various languages – all in good time.

Version 1.3.2 is officially underway. As with this release, it will offer improvements, fixes, and enhancements. I have a list of about 20 things to improve that I will slowly be working through. I’ll talk about the improvements in future posts but in general they will center around enhancements to make creating drill-through reports easier, improvements to performance, aesthetics, and a couple of other tricks I have up my sleeve…

Thanks again to the literally DOZENS of people out there offering suggestions, offers to help, feedback, kind words, compliments, and more. Much to my amazement this tool has gone from a proof of concept to a labor of love to an actual bonafide deployable tool that plugs a little or not so little gap in the current Hyperion ecosystem.

Keep calm and… drill on.

Drillbridge 1.3.0 available!

Drillbridge 1.3.0 was quietly released last week, actually, and YOU’D KNOW ALREADY if you were on the Saxifrage Systems LLC Tools mailing list and got to be part of the cool kids club already (seriously, it’s non-spammy. Please take a second to add your email address).

This release contains new features and fixes, including new smart formatting features, result paging, custom stylesheets, enhanced security options, better error handling/messages, easier deployments, and more. In fact, this software is so awesome now that I’m going to use it for a webinar in just two short months where your humble blogger/programmer/cube nerd will install, configure, and deploy Drillbridge in 10 minutes or less (!).

Oh, and did I mention that the reference guide is now a 40-page beautifully typeset PDF full of detailed information and examples? Well, it is.

As always, Drillbridge can be found in the Saxifrage Systems download section. If you need support, please email or better yet, head over to the support forums. I monitor those like a hawk so it’s just as good as email.

Speaking of support, there are still a couple of little issues I am addressing and polishing up, so version 1.3.1 is underway. If you instill 1.3.0, however, 1.3.1 will be a drop-in upgrade, so don’t let that hold you back. Overall the Drillbridge codebase has really settled down and is holding up very, very well. I don’t anticipate any major new features for a bit which means that further releases are just going to be polish and small enhancements (gotta get things dialed in for the webinar on October 28th!).

You’re going to love this.

I don’t usually talk about things I’m planning on doing or haven’t done yet, but I’m going to make an exception. I’m putting together a webinar for ODTUG (you are a member, right?) that I’ll be presenting in late October (October 28th to be exact, MARK YOUR CALENDARS!). The webinar will be on – you guessed it – Drillbridge (maybe I need to change this to Jason’s Drillbridge Blog…).

Thankfully, this won’t be my first presentation or webinar. Your humble author has presented on all manner of topics, including load rule optimization, Oracle Data Integrator tips and tricks, Dodeca, Essbase Web Services, and a few other things for good measure. I am delighted to report that this will be my first webinar on a piece of software I have created, though, so I’m pulling out all the stops and designing what I’m tentatively calling the Drillbridge WOW demo.

The overall webinar will be about an hour long, but what I want to do is have a section or segment where I kind of race to deploy drill-through functionality to a cube and show off how powerful the Drillbridge features and expression language can be.

So here’s what I’d like to do, all in one fell swoop: download and install it, configure a datasource, define a report, deploy the definition to a cube, AND use complex mappings/features, namely that the report itself will feature drill to bottom where the lowest level of the dimension needs to be mapped (such as from Jan to ’01’ or similar) and the other dimensions need some sort of similar mapping such as removing a prefix or otherwise altering the member from the point of view. On top of that, we’ll then flip on Smart Formatting with a different locale (French, anyone?), re-run the report, then download it to Excel. And to top it all off, I’ll do this all in 10 minutes or less.

That’s right – 10 minutes (or less!). That’s crazy. To think, I once spent hundreds of hours achieving this same result using different tools.

Sound good? Sound crazy? Well, the good news, as I said, is that this functionality all exists already and is ready to go in 1.3.0. As I mentioned in an earlier post, I’m just cleaning things up and testing them to ensure that Drillbridge is as robust as possible before releasing this major release.

I’m pretty excited, but more importantly I am excited to be able to help the larger Hyperion community provide drill-through to its users in an evolutionary and incremental way with this great little tool. Stay tuned for more.

Upcoming Drillbridge features, part 2: Custom Formatting

One of the requests I got from an earlier version of Drillbridge was to put less space between rows. Fair enough – we all have different stylistic tastes. I could have just changed the spacing and called it a day. But what about other formatting, colors and tweaks? I decided, ya know what would be cool? Styling things however you want.

To that end, there are now a handful of styling options that can be used to achieve any result you want:

  1. Global CSS
  2. Report-specific CSS
  3. Report header
  4. Report footer

These should be pretty straightforward, but there’s a couple of nuances I want to point out.

Global CSS

Global CSS is a server-wide setting that allows you to define your own Cascading Style Sheet (CSS) that will be included in every single report. This is a good place to set some global spacing, fonts, colors, and perhaps some settings inspired by your company colors.

Report-specific CSS

Individual reports can have their own CSS. This useful, for example, if you want/need to adjust things (perhaps a column width or two) for a specific report but not all reports. You might even want to customize settings for different divisions that have different styles (the possibilities are endless!). Additionally, for ease of use reports now have certain CSS classes appended to the data table, rows, and each column, so you can easily apply settings to the 1st, 2nd, 10th, or whatever column specifically. You might want to align some text to the right (text-align: right), for example.

Report Header & Footer

Reports can each have a custom header and footer that is your own custom HTML enclosed within a particular <div> tag at the top and bottom of a report. I thought about making this a global setting but decided against it. You might want to point to some company logo or otherwise include some standard company text or links. There is no limit to what you can put in these areas.

That’s it for these new features – fairly straightforward – but should provide exactly the kind of flexibility that people want, without having to resort to one-off changes in each version of the software.

Upcoming Drillbridge features, part 1: relational paging

As of Drillbridge 1.3.0, paging is now supported. More specifically, tokens
that help write paging queries in SQL are provided – paging is not done
automatically. The reason that automatic paging is not supported is that while
this approach ostenisbly means writing a bit of SQL code to perform the paging, it’s
the most flexible and most performant.

If paging is turned on for a given report, then when that report is built, in
addition to the normal Point-of-View parameters that are made available to the
query, there will be three additional parameters:

  • PAGE
  • ROWS_PER_PAGE
  • OFFSET

Drillbridge will start the PAGE variable off with 1. This token can be used like any other token. The ROWS_PER_PAGE variable can also be used like any other token and can be configured on the report itself. Typical values might be 20, 50, 100, 500, or 1,000.

The OFFSET variable is not specified in the report request, it is calculated as a convenience variable to be used in queries. OFFSET is provided since in some SQL dialects, the total number of rows to skip is needed rather than a page or other option. The formula for OFFSET is:

(page - 1) * rowsPerPage

For example, if a report is meant to page on every 20 rows, meaning that page 1 is rows 1-20, page 2 is rows 21-40, and so on, the following would be true on page 2:

  • PAGE = 2
  • ROWS_PER_PAGE = 20
  • OFFSET = (2 – 1) * 20 = 20

If your data source was a MySQL database for example, then a paging query to this technology might be this:

SELECT column FROM table LIMIT 10 OFFSET 10

As you can see, we will want to plug in the ROWS_PER_PAGE for the 10 after LIMIT, and then the OFFSET for the 10 after the OFFSET keyword. Different SQL technologies have different ways of paging data (some are more complex than others…) but this one is straightforward, thankfully.

Just plug in the given variables like any other, turn on paging in the report, and presto, instant paging – with good performance.

Next week I’ll talk about some of the other cool features coming soon…

Good things coming in Drillbridge 1.3.0

I had originally been planning just a few fixes and tweaks for the next point release of Drillbridge which was to be 1.0.3. But after adding quite a handful of new and improved features, I have decided to bump the version more significantly. I’m happy to say that everything I’m about to describe is fully implemented and just undergoing some documentation and testing on my part. It’s going to be a super cool release, hence bumping the version up a bit more.

Of particular note, the following features have gone in:

  1. Paging and results per page settings
  2. Faster Essbase outline queries and a new outline caching mechanism
  3. Smart Formatting option for locale-specific formatting of dates and numbers (French, German, and Russian users rejoice!)
  4. Row limit and query timeout settings available on reports
  5. Global CSS formatting, report-specific CSS, header, and footer
  6. Documentation improvements and more examples
  7. Tons and tons of small improvements and fixes (too many to enumerate here but full notes will be in the changelog)

I’m going to try and get 1.3.0 wrapped up in the next couple of weeks. If you’d like to try it early, let me know and I’ll send a build your way. After this release there’s just one major feature left that I think this product needs, then it should be a matter of bug fixes and minor improvements from there will I pivot to my next big project.

Over the coming days prior to an official release, I’ll be posting a series on some of the new features that are coming up, so please feel free to email me if you have any questions or comments and I’ll be sure to address it in the blog!

Drillbridge 1.0.2 package updated

Just a quick tweak to fix a last minute thing I apparently broke. This release saw the introduction of a super cool mechanism for updating the Drillbridge repository automatically when Drillbridge gets updated, but I made a last minute change that slightly broke it so it was messing up the start of the service. I put things back all looks good again.

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:

Curr vs. Prior: @VAR("FY2014", "FY2013");

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

Curr vs. Prior: @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, as well as the actual versus prior:

Prior: @PRIOR("Actual", 1, @CHILDREN("Years")
Actual vs. 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.