Category Archives: drillbridge

Kscope15 Presentation Preview: Drillbridge

I am very pleased to be able to say that I have two presentations at Kscope15. One of my presentations is on Drillbridge, the other presentation is a deep dive on how interfaces work in ODI. I’m going to really have a chance to geek out on both of these, especially the ODI presentation.

As for the Drillbridge presentation, I want to give a brief overview not just of what I am going to cover, but what I hope to accomplish for those in attendance.

This Drillbridge presentation will be an elaboration of my original webinar. I’m going to introduce the attendee to what Drillbridge is and how it works (of course). Then, as with the webinar, I will download and deploy Drillbridge in real-time, showing off just how easy it is to get drill-through up and running. The original webinar had a stated goal of deploying drill-through in 10 minutes or less. Not only was drill-through deployed to a cube in less time (five minutes!), I used the spare time to redeploy drill-through on the same cube but with drill-to-bottom enabled in the Time dimension (and still had some time left over). Yeah, it’s that good.

Since I’ll have more time than the webinar, I’ll also be able to show off the handful of awesome features that have been added in the last year: custom mappings, support for Linux, custom plugins, server-side result paging, smart formatting, drill-from-drill, custom stylesheets, and more.

After the presentation, attendees should have a firm grasp of how Drillbridge works, how it can be used, and how implement drill-through in simple situations. More importantly, they should be heading back to their respective companies excited about how they can now deliver a solid win to their users without breaking the bank or having to re-architect their systems.

There are a lot of great presentations this year, but I hope if you’re interested in drill-through or Drillbridge you will be able to attend!

Drillbridge 1.5.4 Available

Nothing fancy in this release, just a rollup release with a collection of bug fixes, enhancements, and small improvements. There were some issues with null values in certain situations and Excel files that are now resolved. Thanks to those that provided feedback and testing. Please email for Dropbox link for now, otherwise this new version will be available from the normal downloads site later today.

Issue starting Drillbridge on Windows 2008

I’m still tracing this through, but there’s an occasional issue I’m seeing on Windows 2008 servers when it comes to starting Drillbridge. If you get an error when you try and start the service, check your logs and try to find the cause, as you normally would. If you see an error along these lines:

INFO   | jvm 1    | 2015/04/07 10:09:39 | org.springframework.context.ApplicationContextException: Unable to start embedded container; nested exception is org.springframework.boot.context.embedded.EmbeddedServletContainerException: Unable to create Tomcat tempdir

It could be a permissions problem such that the account running Drillbridge does not have high enough privileges to create a temporary folder for some web server files. If this is the case, you can actually specify the temporary folder yourself.

Open up /config/wrapper.conf and search for the text “additional”. You should find a commented our line like this:

#wrapper.java.additional.1=

You can leave that uncommented but just add in the following line:

wrapper.java.additional.1=-Djava.io.tmpdir=D:/Drillbridge/tmp

Then go make sure that you create the /tmp folder so that the folder path exists. You don’t have to use this folder name, drive, path, or anything. It just has to be a folder that exists and that the Drillbridge user has the ability to write to. Save the file and then start (or restart if Drillbridge was already running) Drillbridge. Everything should now start fine.

I’m still tracing down the root cause of this to try and make it so that users would never have to apply this fix themselves but until then, this fix works well enough. I haven’t seen this issue on Linux editions of Drillbridge – just Windows Server 2008 in environments that are relatively locked down.

Custom Functions in Drillbridge

The upcoming version of Drillbridge has some really exciting features in it. I’d like to talk about one of those features right now. Drillbridge now has support for custom functions. Drillbridge has a very powerful and flexible mapping system for easily translating between the members in your cube and related detailed data in a relational table. This is a big part of what makes Drillbridge such a compelling solution for quickly implementing drill-through.

One of the more popular functions is #removeStarting. This function strips a prefix off of a string (member name) if it has a certain prefix. For example, let’s say that the names of members in the Years dimension are FY14, FY15 and so on. You need just the numeric value in order to perform a query for the detailed data. You can use #removeStarting like this:

#removeStarting("FY", #Years)

And the value returned by the function will be 14, 15, or whatever the proper year is. The newest version of Drillbridge now supports adding custom functions by way of dropping in a Java file that contains them. This way if you have a complex mapping that needs to happen, you can use the full power of the Java language to accomplish it. For example, consider a function that needs to do special processing with respect to a combination of the year and month, in order to get a particular time value stored in a GL system.

public static int getJulianLastFromYear(String fiscalYear, String shortMonthName) {
  Integer year = Integer.valueOf("20" + DrFunctions.removeStarting("FY", fiscalYear));
  Integer month = Integer.valueOf(DrFunctions.monthToTwoDigits(shortMonthName, "en"));
  return getJulianLastOfMonth(year, month);
}

Then just compile this class as normal, drop it in the Drillbridge drivers folder, restart Drillbridge, and you can now use this function inside of your own expressions, just by calling it with whatever name you give it (in this case, it might be #getJulian).

This is a really exciting feature that will offer tremendous flexibility, particularly in cases where complex mapping is needed that might be difficult or impossible to perform with the relational database. Stay tuned – there are a few more very interesting features coming down the pike…

Drillbridge drill-to-bottom token parentheses note

Just a quick note on the syntax for Drillbridge tokens that use drillToBottom. One of the more compelling Drillbridge features is the ability to “drill to bottom”. What this means is that a user can drill on an upper-level member such as “Quarter 1”, then Drillbridge will open up the outline, get the level-0 descendants, transform them according to the token expression, and then replace the whole token with the list of members combined into a string.

The string that Drillbridge generates in this case is almost always used in a SQL “IN” clause, like this:

SELECT * FROM Transactions WHERE Month IN ('01', '02', '03')

In this case, the tokens ’01’, ’02’, and ’03’ were perhaps generated because they were transformed with the #monthToTwoDigits function and processed the member names that are the children of Quarter 1 (“Jan”, “Feb”, “Mar”).

Earlier versions of Drillbridge required you to supply the starting an ending parentheses, such that your query text looked something like this:

SELECT * FROM Transactions WHERE Month IN ({{token definition}})

This changed back around version 1.5.0, however. Now Drillbridge by default will supply the starting and ending parentheses. This change was made to accommodate a few use cases where for performance reasons you might need or want to generate the query a little bit differently (such as using a sub-select or something fancy).

Given that current and future versions of Drillbridge supply these parentheses, you would instead write the query before more like this:

SELECT * FROM Transactions WHERE Month IN {{token definition}}

If you really want to supply the parentheses yourself for some reason, you can set the “suppressParentheses” parameter on your token to “true” and Drillbridge won’t supply them – meaning that you will write the query more like the first style. While I’m on the subject, also note that by default, Drillbridge surrounds your member values with single quotes (see the first example in this post where 01, 02, and 03 are all surrounded by single quotes). You can also turn this off if you need to (for example, if the items in the IN clause must be numeric) by setting the “quoteMembers” token parameter to false.

Advanced Drillbridge tokens: Protecting against unexpected members

Here’s another quick tip for setting up Drillbridge tokens. I recently created a Drillbridge report that needs to map a member from the Cost Centers dimension over to multiple columns in a relational database table. For example, a cost center may have the format 111-222-333-44, and the code 222 corresponds to a column in a table, 333 corresponds to a column in a table, and 44 corresponds to a column in a table (these are all in the same table, by the way).

Because the incoming member on the POV comes across as a Java string, we can perform normal Java string operations on it. In this case we can easily extract the “222” by doing the following (assuming the cost center dimension is named “Cost Centers”:

#Cost_Centers.substring(4, 7)

Recall that in Java, string character offsets start at 0, and that the substring we extract does not include the character at the ending offset (this is all spelled out nicely in the Java String documentation). So effectively, in the above function we are saying “give me the substring of the #Cost_Centers variable from characters 4 through 7 (but not including 7).

A problem with this approach is that members with fewer characters could potentially cause a StringIndexOutOfBounds Java exception. For example, a level one member or parent to our cost center might be “Other”. Therefore when the substring method is called on “Other”, we are asking Java to give us characters that do not exist, and an exception is thrown.

I thought for awhile on what the best way to handle this is. There are a lot of ways I could go in terms of the code, but I decided that the best approach is one that won’t really affect the Drillbridge code much at all right now.

The decision to use a full-fledged expression language as the basis of Drillbridge token expressions has turned out to be quite fortuitous, and one of the things it affords us inside of Drillbridge expressions is the ability to write complex scripts that can handle this situation for us. One such way to guard against member names that are too short is to use the ternary operator.

Many programming languages support the ternary operator. It’s a compact way of representing an “if-else” construct. For example, consider this simple code:

if (stopped) {
    return "Red";
} else {
    return "Green";
}

In this case, the “stopped” variable is a boolean variable that is always one of either true or false. If the variable is true, our function returns the text “Red” and if the variable is false, our function returns the value “Green” (assume that the if block is contained within a function that returns a String value.

This construct is so prevalent in computer programming that many languages support a way to compactly write this, using the ternary operator:

String colorText = stopped ? "Red" : "Green";

In the above example, the variable stopped gets evaluated and if true, the whole expression will evaluate to “Red” and if false, it’s “Green”. Then the String variable named colorText will get the value.

We can use this exact same construct inside of a Drillbridge token expression, thanks to the powerful expression language being used. Now consider this enhanced query example:

SELECT * FROM TRANSACTIONS
WHERE
    SEGMENT2 = '{{
        "name":"Cost Centers", "expression" : "#Cost_Centers.length() >= 13 ? #Cost_Centers.substring(4, 7) : '~~ Cost Center name not long enough to parse ~~'", "sampleValue":"111-222-333-44"
    }}'

Now we are checking the value of an expression first – checking the length of the incoming cost center. If it is at least 13 characters, then the return value of of the expression is safe to be figured out using the substring method. If the member fails the length test, then we return an arbitrary string, which in this case we have determined cannot match anything at all in the database, and therefore return nothing.

This little trick can be used in situations where we need to parse out sub-strings from member names but might have some members that don’t fit the format we need. Also, thanks to Drillbridge’s inventive handling processing of multiple members with the drill-to-bottom feature, we can also use this to effectively filter out members from a list of members, such that we keep the ones we want and discard any others that we don’t need (for example, it’s not uncommon to have “non-conforming” member names for input members).

Drillbridge White Paper now available

I am excited to say that the official Drillbridge white paper is now generally available. It is titled “Drillbridge: Easy Hyperion Drill-through with No Redevelopment”. In my first official white paper, I have hoped to concisely capture the essence of this useful software tool that is now amazingly  approaching 20 production deployments.

In other news, the next version of Drillbridge is still in progress, slowly but surely. The focus continues to be on polish, performance, and incremental feature additions. I have a few small but exciting tidbits that I will hopefully have a chance to post later this week.

Drillbridge White Paper now available

I’m crossing another bucket list to-do item off of my list: writing my first white paper. And what’s it on? Drillbridge, of course. I’ll be making it available to a wider audience next week, but if anyone is interested in a concise, succinct paper on how Drillbridge works, and how it can be used to quickly implement drill-through with a minimum of resources, please email me and I’ll send it right over. I’d love to get your thoughts.

Drillbridge Token Parameter Overview

Tokens in Drillbridge are a large part of the secret sauce that makes everything work so well. They are simple, flexible, and powerful. If you haven’t noticed, they are essentially JSON strings that have a range of allowable values. The most common of these are “name” and “expression”, but there are actually a handful of other keys that are less well known but incredibly useful. I’m going to cover the current state of Drillbridge tokens in this post.

As of the current version of Drillbridge (version 1.5.2), the following parameters on tokens are recognized:

  • name
  • expression
  • drillToBottom
  • sampleValue
  • quoteMembers
  • suppressParentheses
  • overflow
  • overflowAt
  • flags

Parameter: name

The name parameter has been around since day one. It identifies which dimension the token is replacing text for. For example, if the expression for the token will be generating the names of members in the Time dimension, then the value of name would be Time. The name here should exactly match the dimension, even including spaces. For example, if the dimension is named “Time Periods” then the value for this token should be “Time Periods” and NOT “Time_Periods”. This is a common issue that pops up (I’m guilty of it myself).

Parameter: expression

The expression parameter is where most of the magic happens. A lot has been written about expressions in Drillbridge on the Drillbridge Wiki so I will save some examples of expressions for a future article.

Parameter: drillToBottom

Possible values for this parameter are either “true” or “false” and nothing else. The default for this token value is false, so if you aren’t drilling to the bottom then you don’t even need to include this. This option is used to indicate that the incoming member should have its level-0 descendants pulled from the outline, then have transformation in the expression applied to them. For example, if you drill on Qtr1, the values that get processed by Drillbridge from the outline are Jan, Feb, and Mar, and if you have the #monthToDigit function as part of your expression, these will be converted to 1, 2, and 3. Note that when using this setting, you almost always need to have an associated Essbase cube on your report (so it knows which cube outline to search).

Parameter: sampleValue

Used purely to help testing. Putting a value here will simply pre-populate the text box on the report test screen so that you don’t have to keep putting values in by hand. It is really, really, handy.

Parameter: quoteMembers

By default, this is set to true. On queries where multiple values are pulled from the outline, you will typically use an IN SQL clause. For example, the generated SQL might be something like “WHERE Time IN (‘1’, ‘2’, ‘3’)”. This happens if Drillbridge generated the values of 1, 2, and 3. As you can see, each of these values has a single quote before and after it – Drillbridge put that in automatically. There are some cases where you won’t want Drillbridge to automatically put quotes in, such as advanced queries where you need to define the structure a little differently (such as for performance reasons). Possible values for this parameter are true or false. If false then the generated code in this example would be like this: “WHERE Time IN (1, 2, 3)”.

Parameter: suppressParentheses

Similar to the previous example, when Drillbridge generates a list of members, it will automatically enclose it within parentheses. For example, the parentheses surrounding 1/2/3 here: “WHERE Time IN (‘1’, ‘2’, ‘3’)”. Most SQL dialects seem to enclose their lists with parentheses, but for ones that don’t or where you need more control over the generated code, you can set suppressParentheses to true, and Drillbridge will not add in the parentheses for you. If you need to get creative with how you write your query (again, probably for performance reasons) then you might find yourself using this parameter.

Parameter: overflow

The overflow parameter is a relatively new addition. Some SQL languages and versions have a limit to the number of things you can put in the IN clause. For many versions of Oracle, this is 1,000. The normal way of programming around this (and what most versions of Drillbridge do) is just clip anything over 1,000, so that your query won’t fail. But what if you just absolutely need those 1,500 things or 10,000 or whatever it is? Modern Drillbridge versions have your back. The overflow parameter let’s you specify an additional template to append to the query, then replace it with additional members from the list of generated members.

Here’s an example: let’s say that there are an arbitrary number of accounts in the Account dimension, named Account 0000001 through Account 1000000. Let’s also say that drillToBottom is set to true and that the member that the user drills on would result in some 5,000 members being queried. Again, the normal behavior to handle this would be to clip the member list to 1,000 or whatever the default setting is (it can be configured in Drillbridge to whatever you want), then fire off the query. So the relevant portion of the query would look something like this:

WHERE Account IN ('Account 0000001', 'Account 0000002', ... , 'Account 0001000')

Using the overflow parameter, you could specify an overflow value of the following:

overflow: " OR Account IN %%OVERFLOW%%"

What Drillbridge will do is use the first 1,000 entries as it normally would, then cycle through the rest, generating subsequent lists that are placed into the %%OVERFLOW%% text inside the overflow token, then the whole thing is appended to the whole token replacement. This let’s us sidestep the natural 1,000 or whatever item limit on many RDBMS.

Parameter: overflowAt

This token expects a numerical value and it just specifies how big the groups should be that members are broken up into for the overflow parameter. The default is 1,000 but depending on your needs you may wish to set it lower or higher.

Parameters: flags

Flags is meant to be a sort of catchall bucket for various configuration settings that might be specific to the underlying technology without having to create official parameters. It is a comma-delimited list of flags. The only recognized flag at the moment is “~”, such that your flags parameter would look like this:

"flags": "~"

This is a special flag that comes into play on a Essbase Member Resolver. For tokens using drillToBottom, this tells the Essbase routine that fetch the descendants of the drilled member to ignore any members that have a consolidation operator of ~.