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