Drillbridge is a tool with an ostensibly narrow focus – drill from Essbase/Hyperion data to somewhere else. Typically that “somewhere else” is the relational data that has been summarized to load into the cube. While the concept of drill-through is very simple in principle, Drillbridge has been extensively engineered to make take this simple process and augment it with dozens of features that enhance its usefulness.
That said, in no particular order, I thought it might be fun to point out my ten favorite Drillbridge features.
Get Around the 1,000 Item Limit in Oracle
Many traditional drill-through implementations run into an issue when drilling to more than 1,000 items. This generally happens when drilling from an upper-level member, such as high up in the Accounts of Entity dimension. Typically how this is implemented is that all of the level-0 descendants of the drilled member are placed into a SQL SELECT statement using the IN clause. For example, drilling on Qtr1 in an outline could yield a query like the following:
SELECT * FROM Transactions
WHERE MONTH_NAME IN ('Jan', 'Feb', 'Mar')
Most versions of the Oracle relational database, however, have a hard limit of 1,000 entries in the IN clause. Drillbridge provides an advanced syntax for getting around this by letting you specify how to break the items into multiple groups to get around the 1,000 item limit. For example, imagine that the limit was actually three items in a single IN clause and you had five items that you needed to check for:
SELECT * FROM Transactions
WHERE (MONTH_NAME IN ('Jan', 'Feb', 'Mar') OR
MONTH_NAME IN ('Apr', 'May'))
Paging
Drillbridge reports can be configured to break the results into several pages and allow the user to page through them. This can provide numerous benefits. The biggest benefit is to the user experience. Instead of waiting for 100,000 rows to load in, a particular report might be configured to break the results up into pages of 10,000 rows each. This can improve the user experience by making the browser more responsive (since there is less data in memory), and make it so the user can start viewing data before all of it has returned from the relational database. The user will be presented previous/next buttons in the interface to quickly navigate between pages:
Drillbridge paging feature
Drillable Columns
Drillable columns are one of the ways that Drillbridge allows for providing even more ability for the user to get context around their data. With drillable columns, any column or set of columns in a report can be configured to automatically link to another report. When the user clicks the link, Drillbridge will run the new report using the current POV combined with the POV of the row. For instance, if the user originally drilled into transactional detail and then wanted to drill on a particular transaction, the original POV (month, account, etc.) and the transaction number would be combined to launch the new report.
Drillable columns are also one of primary reasons why I like drilling into a web page instead of just plain/raw data in an Excel tab. Here’s an example with drillable columns:
Drillable columns in Drillbridge
If you look carefully you can see that the link at the bottom of the browser window actually includes all of the parameters from the POV and that particular row, enabling the target report to use the source parameter as it would any other parameter.
Drill to URL
The fundamental concept behind Drillbridge is that it flexibly creates a SQL query based on a given POV and it provides incredibly enhanced functionality for crafting that query. Drillbridge provides an additional report type out of the box that instead of generating a SQL query, generates a hyperlink, and contains all of the same functionality for mapping from the POV to a link that you’d expect in a normal report. For demonstration purposes, here’s an example query template:
Editing a Drillbridge URL forwarding report
Notice that instead of a query we are editing the “Link Template” and that it starts with http rather than the more traditional SELECT we are used to seeing. I’m able to do this because I set the type of the report to a Forwarding Link report instead of JDBC Drill-through Report (the default):
Changing the class of a report
Changing the report type also changes the available options that we have. The Forwarding Link report is a little simpler in terms of its options, but it lets us specify if we want the link to be submitted as a GET or a POST request:
Modifying options on the URL report type
Lastly, after testing this simple report where it submits a search request to Google, take a look at the URL that gets generated and shown in the browser:
Drillbridge URL report
You can see that the parameter I tested (“DrB_Test”) is up in the URL. Many organizations are using this functionality in order to drill into internal systems that show PDFs and images of invoices, receipts
Drill from Upper Level in PBCS
I think this is unique in the world right now, but Drillbridge drill-through from PBCS to on-premise (or cloud) relational databases and supports upper-level drill-through from PBCS. Again, by way of simple example, you can drill into member Qtr1 from PBCS and run a query on-prem that is something like SELECT * FROM Transactions WHERE MONTH_NUM IN ('01', '02', '03). Typically the children of Qtr1 would be Jan, Feb and Mar. This is really easy to convert to a month number using the built-in Drillbridge function #monthToDigits. This function is also really useful because it can handle full month names, different locales (month names in different languages). More importantly, though, is that Drillbridge uses the PBCS REST API to communicate with the cube in the cloud to figure out what those children are.
Essbase Upper Level Drill Ignore Non-Consolidating Option
This is probably a lesser known feature and a small one at that, but it’s a really great example of paying attention to the details. Occasionally you’ll have a hierarchy with some non-consolidating members, such as the following:
Total
Account1 (+)
Account2 (+)
Memo Account (~)
In this case, Account1 and Account2 roll up to the total, but Memo Account does not. It still has data, however, that would be retrieved from running an upper-level drill from the Total member. This may be and often is what you want. However, sometimes it’s better or you really want to ensure that the total from the drilled member matches the total of entries in the drill-through request. So by setting the “~” custom flag on the token, you can tell Drillbridge that when it expands the upper-level member to the list of descendants to query, it should ignore any non-consolidating members. This will ensure that the total you drill on should match up with the sum of the entries. Again, a small feature but it is used out in the real world surprisingly often.
Excluded Members
Similar to the previous option/flag to ignore non-consolidating members, sometimes there are members in the outline that you do not want to drill on at all. Frequently this seems to be sensitive salary/HR data that is fine to show in the aggregate but you may not want people to be able to pull up detailed transactions and find out details. With excluded members, you can set a list of members that should always be removed from the SQL query, even if it would normally be part of the upper-level drill.
Custom Mappings
In a way, custom mappings are kind of the opposite of excluded members. They give you the chance to specify a list of arbitrary members in place of an existing member. This is most commonly used for YTD members on ASO cubes. For example, you might have a YTD_Feb member in the Time dimension as part of an alternate hierarchy. There are multiple ways to implement this but it’s common to do with a formula ([Jan] + [Feb] or something). Upper-level drill-through wouldn’t otherwise work here because their aren’t children of this member to provide to the SQL query. This is where custom mappings come in. We can specify via a simple text file or a SQL query that a list of members should be swapped in for a given member. So we can define a mapping such that Drillbridge treats YTD_Feb as if “Jan” and “Feb” were children of YTD_Feb, and perform the query accordingly. This allows for more flexibility in the outline and to provide uniform drill-through experience to the user (as opposed to making them go from the top of the house dimension or manually piece different queries together).
Editing Custom Mappings in Drillbridge
Custom Plugins
Drillbridge supports the ability to add new report types via custom plugins. The report types that Drillbridge ships with (JDBC/SQL, Forwarding Link, MDX, Composite, etc.) are just normal plugins (albeit ones that ship with Drillbridge). Custom plugins are the most interesting and powerful features of Drillbridge, although some custom coding is needed. Plugins are written in Java but have full control over the drill-through experience and output, while also being able to leverage the powerful query processing capabilities and framework built in to Drillbridge.
One of the more interesting uses of custom plugins I have seen in Drillbridge is where companies have an Essbase cube that is based on a GL, but the GL is constantly being updated. This can mean that if you’re drilling from the cube directly to the GL, you can have issues tying out because there may new newer entries throwing the totals off compared to what is in the cube. In this case, the company wanted to be able to see the additional detail though, but not confuse it with what was loaded to the cube. So they built a custom plugin that would color-code the data and put it at the bottom so that the normal data was shown first, then toward the bottom of the report, newer journal entries were shown in a dark grey color that denoted they were newer than what was in the cube. I thought this was a really great use of the custom plugins.
Themes!
The most recent version of Drillbridge Plus comes with the ability to design custom themes as well as a completely new theme called Prophecy. Ever since the earliest versions of Drillbridge it has been possible to modify the HTML/CSS used to render a page, but with the new theme capability, it’s possible to get a different look on your reports with just a single option change. Users of the new Prophecy theme will feel right at home with their other Oracle tools. Here’s a sample image from a normal report with the new theme turned on:
Drillbridge report with Prophecy theme selected
That’s All For Now
I think drill-through has never been more important or useful to users of Essbase/Hyperion products, as it provides very useful and rich context for the OLAP systems organizations spend so much time using. Ever since day one, Drillbridge has tried to do one very specific thing but do it very, very well. I hope you enjoyed this overview of some of my favorite Drillbridge features and welcome your questions and comments!