Drillbridge’s powerful mapping abilities handle most common transformations with ease – stripping a prefix, converting a month into digits, or letting you use the Java language itself to parse a string however you need. Every dimension that gets drilled from is represented as a variable that a Drillbridge administrator can use to build their own expression. For example, if there is a dimension called Years in the cube, and the user drills on a member named FY15, then on the report side of things, there will be a variable referenced as #Years that will have the value of “FY15”. From here it would be typical to use the #removeStarting function to strip off the FY, and perhaps prepend the string “20”, then use that in a database query.

Something interesting happens, however, when the dimension has spaces in it. Let’s say that the dimension name is “Time Periods”. For perhaps obvious reasons, Drillbridge can’t create a variable with a space in it, because it’d look like a variable followed by a string (like #Time Periods). Instead, Drillbridge will replace spaces with underscores for purposes of creating a variable. In this case the variable would be #Time_Periods.

So #Time_Periods is the variable that is used inside the token expression, but what about the name of a token? The name of a token should still be the name of the dimension, using spaces, not underscores.

Consider the following tokenized query:

SELECT *
FROM Transactions
WHERE MONTH(PostDate) IN {{
"name" : "Time Periods",
"expression": "#monthAbbreviationToDigit(#Time_Periods)",
"drillToBottom": "true", "quoteMembers": "false"
}}

As you can see, the name parameter has the space, but the expression uses the underscored version of the variable name. In this case we’re also using the very handy #monthAbbreviationToDigit built-in function that can convert month names like Jan, Feb, and so on to 1, 2, or the proper month number.

It took a little longer than anticipated (I took some genuine time off over the holiday break), but I am very happy to make Drillbridge version 1.5.0 available to download.

A lot of work has gone into this release. For example:

• You can now drill from columns (!)
• Use your own custom mappings to resolve member names (this is a super cool feature I wrote about earlier and it knocks the pants off of Essbase Studio (as far as I know) by allowing you to write in mappings for your own members that might not have children (for example, a YTD member in the Time dimension)
• Streaming output performance enhanced
• Now generates XLSX files with much better output (not everything is treated as a String – if the column type is DATE or TIME or TIMESTAMP, it gets treated and formatted accordingly)
• Now run Drillbridge from Linux!
• All files included that should allow for running on Windows, Linux, AIX, Solaris, and even Mac OS X
• Java 7 or newer is required! I know this might be an inconvenience but I pretty much had to, and it’s good to get current with Java. Java 1.8/8 should work just fine as well in case you want to go all out

I have done my absolute best to test this version and make sure it doesn’t have any showstopper bugs but there are bound to be issues. There are a few things I know need to be worked on for the inevitable 1.5.1 release. So if you upgrade, please be sure to make backups so you can flip back to 1.3.3 or 1.3.4 or whatever your current version is, in case there’s something broken that you need.

If you have any questions about the new features and how to use them, don’t hesitate to hit up the forums or email. In the coming days I will be adding on to the Drillbridge Wiki with some info about how to set things up in terms of new features. There was recently a lot of spam activity on the wiki that I’m trying to sort out but for now it looks okay.

That all being said, this is the best release of Drillbridge ever and it now contains every feature I originally set out to put in, and then some. There will undoubtedly be some quirks but I look forward to a few point releases to stamp them out. Many or most of the features are from direct user requests, feedback, and ideas, so thanks to everyone.

Happy Drilling.

The ODTUG webinar for Drillbridge yesterday seemed to go pretty well (more to come soon!) but one of the questions that came up is if Drillbridge works with Teradata and/or Netezza for implementing Hyperion drill-through to relational. My answer: it should, but I don’t know for sure. Drillbridge supports Microsoft SQL Server, Oracle, and MySQL out of the box. Drillbridge also allows you to put your own JDBC driver into it’s /lib folder and you should be able to use any other flavor of database that you can write SQL for: be it Informix, DB2, Teradata, Netezza, or whatever.

So that being said, if you are interested in implementing Drillbridge and using one of these backend relational databases, please don’t hesitate to reach out to me if I can help with it. I’d love to be able to confirm compatibility rather than to just say “I suspect it will work, JDBC is awesome, right?”

Such is the case with software, but there were a few other issues pointed out to me, this time with Excel file generation. So, the good news is that I fixed it (I think) and actually fixed another small issue that I noticed could pop up (and edge case with certain types of reports).

Excel files will now be generated in streaming mode, as with the much enhanced report generation, so that’s cool. Also, unlike before, the Excel file generation will now honor the Smart Formatting option if you have that turned on, so that’s even more awesome.

One bad thing I just discovered, though, that I hadn’t though of, is a quirk with trying to download Excel files from Drillbridge pages that are paged. This is a bit problematic due to how to write the query, so for the time being I must say: no Excel file download on reports with Enable Paging turned on. I have tweaked the HTML so this option won’t even show.

Paging turns out to be a somewhat problematic issue due to the complexities of supporting multiple database backends, performance, and other things. So for now it’s just considered experimental.

A new Drillbridge 1.3.3 package has been uploaded to the download site with the aforementioned fixes.

Thanks again to those of you providing feedback and living on the bleeding edge of this thing. Version 1.3.1 is still the gold standard for now but 1.3.3 is just about there, I think. In any case, things should settle down from here on out in terms of major code changes.

Happy Drilling.