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.

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 ~.

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.