Recent Stories

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.

SeaHUG officially off and running

Hi all, the Seattle Hyperion User Group is officially up and running! More details to come but right now we are rounding up all the various Seattle and Greater Seattle Hyperion folks from all of the various nooks and crannies. Since not EVERYONE reads this blog (but should), if you are in Seattle or have some colleagues in Seattle that might be interested, will you please let them know about SeaHUG?

Calling all Seattle-based Hyperion enthusiasts!

Are you in the Seattle or greater Seattle region? Are you a Hyperion, Essbase, EPM, OLAP, BI, or whatever-you-want-to-call-it enthusiast? There is a new Hyperion User Group starting up that I am helping out with. We would love your feedback to see what kind of meetups and content YOU, the user, would be most interested in. If you’re in this area (or even a little further to the south in Oregon!) can you please take a few minutes to fill out a quick survey?

Thanks for your time!

Gadget Review: The Jiggler

So, theoretically speaking, let’s say you are doing some work for a client and they give you one of their laptops. Said client is super secure and employs all of the latest and greatest tricks and tips to minimize their risk. And one of those security techniques is to auto lock the laptop after a certain number of minutes of no input. And this setting can’t be changed (because that’s locked down too). And you are working between that laptop and another laptop (say, a nice MacBook Pro…). What to do? Well, hypothetically speaking, you could plugin a little device to pretend your mouse is jiggling. In fact, it might even be called the Jiggler. Yeah, this might do the trick… just saying.

OAUG Collaborate 2014 is a wrap

I went to my first OAUG Collaborate last week. To date I have almost entirely been a Kscope person. Kscope definitely has a much larger Hyperion presence/footprint, but there’s a little bit of Hyperion at Collaborate. Nicely enough, my favorite Hyperion consulting firm had a booth on the main floor.

I was at Collaborate in a mostly non-Hyperion capacity, however. I gave my presentation on Oracle Data Integrator that is a case study/success story of refactoring a lot of boilerplate, hard-to-maintain, and buggy code into clean ODI packages. I expanded the presentation from when I gave it at NWOUG last year. It now includes a screenshot of ODI Studio for each piece of ODI functionality I talk about, then towards the end I have broken down a SQL to SQL Oracle interface with journalization so we can see exactly how ODI accomplishes this particular job in such an awesome way. It’s a bit of a deep dive but when the audience is full of SQL experts I think it makes sense. The presentation clocks in at almost exactly an hour now so I think I’m going to find some ways to trim it and polish it a little bit.

With Collaborate under my belt, this now makes the list of unique conferences I have presented at three items long: Kscope, NWOUG, and Collaborate. Not too shabby.

Thanks to all those that attended – there were many more people that I possibly would have thought (although I suspect few if any of you read this blog… :)

A quick trick to avoid hard-coding folder names in batch files

I’m still surprised (although I guess I shouldn’t be) how often I come across batch automation files that have a first line of setting the current folder – using a hard-coded folder name. For example, if the automation is located in D:\Essbase\Automation, then the first line of the script looks like this:

cd /d D:\Essbase\Automation

99% of the time, this line is the same as the folder containing the batch file. Instead of hard-coding this, you can actually just use a handy shortcut on Windows:

cd /d %~dp0

The %~dp0 token/variable gets replaced at runtime with the current folder containing the executing file. The /d parameter simply tells the cd command to change drives, if necessary (so that the change directory command works if it’s going from the C drive to the D drive, for example).

Using the above technique, you can write batch files that are more standard looking, portable, and more flexible. It’s a good thing (said in the voice of Martha Stewart).

Winter Update

It has been a busy fourth quarter, to put it mildly. As luck would have it, I have found myself on this Saturday with a few minutes on my hands and thought I would do a quick post.

I’m doing a lot of work in ODI, Essbase automation, and some fun things involving the intersection of mobile, the cloud, and Essbase. So it’s exciting times as always. Earlier in November I did a presentation in Portland at NWOUG (Northwest Oracle User Group) on an ODI success story. I’ll also be giving that presentation in Las Vegas next year for OAUG, and just to get even more mileage out of it I will be presenting it at next year’s Kscope, by ODTUG. Man, I am getting some serious mileage out of this thing. I keep refining it and making it a little bit better each time. The way it’s currently structured, it’s more of a business-friendly approach why ODI is so awesome, but I’d like to make it more technical, so by the time Kscope rolls around I will have hopefully morphed it into a nice blend of high-level and low-level information.

Speaking of Kscope, another abstract of mine was accepted. It’s ostensibly my Practical Essbase Web Services presentation from last year, but refined a bit. The number one feedback I got from last year’s presentation was that a live demo would have been good. Well, you asked and you shall receive. Next year’s presentation will have a live demo (probably on a local VM but I may have a few other tricks up my sleeve).

That’s kind of what I’ve been up to. Oh, and along the way in my spare time I accidentally created an ad hoc app on the iPad for Essbase. So, there’s that too.