Drillbridge 3.4.6 Token Enhancements: Join Character and Line Removal

Drillbridge 3.4.6 will be publicly released soon. It contains a couple of enhancements to give developers even more control over the way their queries are generated.

For each token, you can now optionally specify the “token join text” as well as certain “line removal members”.

Token Join Text

Drillbridge has supported upper-level drill for many years – the ability to drill on an upper level such as YearTotal or Q1 or Total Entities and generate a SQL query based on all of the members below it. This has even worked with PBCS for years as well, providing an easy way to implement drill-through from upper-levels from PBCS too. How this generally works is that Drillbridge will fetch the level-0 descendants of the drilled member and then concatenate them together to use in you SQL IN clause. For example, consider the following Drillbridge query:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = '{{"name":"Market","expression":"#Market","sampleValue":"Washington"}}' 
    AND MEASURE = '{{"name":"Measures","expression":"#Measures","sampleValue":"Sales"}}'
    AND SCENARIO = '{{"name":"Scenario","expression":"#Scenario","sampleValue":"Actual"}}'
    AND PRODUCT = '{{"name":"Product","expression":"#Product","sampleValue":"100-10"}}'
    AND SUBSTRING(TX_DATE, 6, 2) IN {{"name":"Year","expression":"#monthToTwoDigits(#Year, 'en')","drillToBottom":true,"sampleValue":"Year","overflow":"OR TX_MONTH IN %%OVERFLOW%%","overflowAt":3}}
ORDER BY
    TX_DATE

There’s nothing too fancy going on in this query. Well, okay, a little bit. This query is based on our favorite database, Sample/Basic. You can see that the query from the SAMPLE_BASIC_TRANSACTIONS table takes into account the value from the Market, Measures, Scenario, Product, and Year dimensions (recall that in Sample/Basic, Year is the time dimension and its children are quarters).

This query has really simple mappings for all of the dimensions except the Year (time) dimension, which is where things get interesting. In this example, the contents of the TX_DATE column are values such as ‘2019-10-22’ – that is, a pretty standard year-month-day format with hyphens. The incoming values from the cube, however, will be month names such as Jan, Feb, Mar. This example uses the built-in Drillbridge function #monthToTwoDigits to convert the month names to two digits. This is a function that has been built-in since the earliest versions of Drillbridge. Yes, you can convert dates using SQL, but every database has its own particular syntax. Having this built-in makes report development much easier, particularly in this example where we are also doing a drill to bottom operation. Let’s take a look at what happens when we drill on some intersection that includes Jan for the member in the Year dimension:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01')
ORDER BY
    TX_DATE

Since drillToBottom is enabled for this token, Drillbridge gets the level-0 descendants of the drilled member (in this case it simply yields the drilled member itself as it has no children and is itself a level-0 member), applies the expression/function to each item (converting Jan to 01), surrounds each item with quotes, then surrounds the whole thing with parentheses. We end up with a valid SQL query that combines native SQL (the SUBSTRING function) on the TX_DATE column (extracting the two digit month) and checks if it’s IN the specified values. Now let’s drill on an upper-level member (Qtr1) and see what we get:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01', '02', '03')
ORDER BY
    TX_DATE

Drillbridge was given Qtr1, went out to the Essbase outline, saw that the level-0 descendants were Jan, Feb, Mar, applied the expression/function, then joined them all together. With the new custom join text option turned on (and some custom join text specified, such as a semicolon in the following example), we get this:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01'; '02'; '03')
ORDER BY
    TX_DATE

Historically, Drillbridge has always assumed the text to join things together with was a comma, because that’s what all SQL IN clauses take. The custom join text gives you full control over this now, though. This probably won’t be useful in SQL queries, but it is useful for the Forwarding Link report type in Drillbridge, which generates a URL to send the user to rather than a SQL query. This report type is generally used to send a request over to an image server, OBIEE, or some other system. Now that you can specify custom text, many more options are opened up and accommodated.

Line Removal Members

Building on the previous example, let’s say that you drilled on Year instead of Qtr1. You would get the following query:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12')
ORDER BY
    TX_DATE

As with before, Drillbridge went out to the outline, found the descendants, mapped them with the expression, and generated the query for us. The interesting thing about this case, though, is that now in this context, the filter on date is essentially useless, since all of the months are included in 01 through 12. I’ll point out that this example is for demonstration purposes and while it’s not incredibly contrived, I have seen many datasets with a 00 or 13 month (to represent starting/ending balances) that this wouldn’t work on – but the principle still applies. In any case, Drillbridge had to go to the work of consulting the outline for the members (not a huge deal), and then made the SQL database do the work of applying the IN clause. For only 12 members, this isn’t necessarily a huge performance consideration. But many organizations performing drill to bottom on doing it on dimensions with thousands of members.

Drillbridge now supports a new option per token called “line removal members”. The way it works is that if a member is drilled on is in the list of line removal members, then Drillbridge will remove the entire line with the token on it, as well as skip the member resolution process. For instance, considering the previous example, if the user drills on the Year member, then we just don’t even care about that particular clause in the WHERE statement. For instance, let’s say that the line removal members in the Year token is the comma-delimited list of “Year, Test, All Periods” – meaning if the drilled member in the Year dimension is either a member named Year, a member named Test, or a member named All Periods, then kill the whole line. And sure enough, if we drill on Year we get this query:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
ORDER BY
    TX_DATE

Properly setting up this feature may involve crafting your query in a slightly different way than you might otherwise. Since the whole line is removed, you may need to consider the placement of your AND clause. In this case, since AND was the first part of the line, we’re good remove it (as opposed to AND being on the end of the prior line). Also, if we only had one predicate in the WHERE clause then it might be necessary to add the “dummy” clause (1=1) – this is a pretty common thing to see in SQL as it always evaluates to true.

The line removal members feature can only be used in certain circumstances but in cases where your users may be drilling from the “top of the house” it could offer potentially enormous performance benefits for your query execution time.

Drillbridge 1.4.0 Feature Preview: Custom Mappings

Drillbridge 1.4.0 is coming out later this month and it contains some really cool features. Today I am going to go over one of them. This feature is called “Custom Mappings” and while it’s ostensibly simple, it is a huge win for drill-through and cube design.

Consider the scenario where you are drilling from a member in the Time dimension. Drilling from January (Jan), February (Feb), and so on are straightforward (especially with Drillbridge’s convenience methods for mapping these to 01, 02 and such). Even drilling from upper-level members is a snap – Drillbridge gets handed the member Qtr1, for example, then opens the outline to get the three children, then applies the mappings to those and plugging it into the query (so the relevant query fragment might be WHERE Period IN ('01', '02', '03') or something.

Everything is great, right? Well, what about those YTD members you often see in ASO cubes as an alternate hierarchy? Something like this:

  • YTD (~)
    • YTD_Jan (~) Formula: [Jan]
    • YTD_Feb (~) Formula: [Jan] + [Feb]
    • YTD_Mar (~) Formula: [Jan] + [Feb] + [Mar]
    • etc.

The problem with these members is that they are dynamic calcs with no children. So if you try to drill on this, then Drillbridge would literally be querying the database for a period member named “YTD_Feb”, for example.

I have sort of worked around this before in Studio by instead putting shared members under these. Under YTD_Jan you have a shared member Jan, under YTD_Feb you have shared members Jan and Feb, and so on. This works, although it’s a bit cumbersome and feels a little clunky.

Custom Mappings to the Rescue!

Custom Mappings is a new Drillbridge feature that allows you to specify a list of member names to use when drilling on certain member names. If a Custom Mapping is added to a report, Drillbridge will consult that first for child member names. If a mapping isn’t found then Drillbridge will just use the normal provider of mappings (e.g. it’ll open the cube outline and use that).

All that’s needed to create custom mappings is to put them in a file. Here’s an example:

YTD_Jan,Jan

YTD_Feb,Jan
YTD_Feb,Feb

YTD_Mar,Jan
YTD_Mar,Feb
YTD_Mar,Mar

YTD_Apr,Jan
YTD_Apr,Feb
YTD_Apr,Mar
YTD_Apr,Apr

YTD_May,Jan
YTD_May,Feb
YTD_May,Mar
YTD_May,Apr
YTD_May,May

YTD_Jun,Jan
YTD_Jun,Feb
YTD_Jun,Mar
YTD_Jun,Apr
YTD_Jun,May
YTD_Jun,Jun

YTD_Jul,Jan
YTD_Jul,Feb
YTD_Jul,Mar
YTD_Jul,Apr
YTD_Jul,May
YTD_Jul,Jun
YTD_Jul,Jul

YTD_Aug,Jan
YTD_Aug,Feb
YTD_Aug,Mar
YTD_Aug,Apr
YTD_Aug,May
YTD_Aug,Jun
YTD_Aug,Jul
YTD_Aug,Aug

YTD_Sep,Jan
YTD_Sep,Feb
YTD_Sep,Mar
YTD_Sep,Apr
YTD_Sep,May
YTD_Sep,Jun
YTD_Sep,Jul
YTD_Sep,Aug
YTD_Sep,Sep

YTD_Oct,Jan
YTD_Oct,Feb
YTD_Oct,Mar
YTD_Oct,Apr
YTD_Oct,May
YTD_Oct,Jun
YTD_Oct,Jul
YTD_Oct,Aug
YTD_Oct,Sep
YTD_Oct,Oct

YTD_Nov,Jan
YTD_Nov,Feb
YTD_Nov,Mar
YTD_Nov,Apr
YTD_Nov,May
YTD_Nov,Jun
YTD_Nov,Jul
YTD_Nov,Aug
YTD_Nov,Sep
YTD_Nov,Oct
YTD_Nov,Nov

YTD_Dec,Jan
YTD_Dec,Feb
YTD_Dec,Mar
YTD_Dec,Apr
YTD_Dec,May
YTD_Dec,Jun
YTD_Dec,Jul
YTD_Dec,Aug
YTD_Dec,Sep
YTD_Dec,Oct
YTD_Dec,Nov
YTD_Dec,Dec

With this Custom Mapping in place on a report, drill-to-bottom can be provided on a cube’s Time dimension YTD members. You get all of this functionality without having to tweak the outline, add a bunch of shared members, or anything. And what’s even better – you’ll even save a whole trip to the outline. If there is some member that is problematic to resolve, for some reason, or you just wanted to override the member resolution process, you could also stick it in the custom mapping.

Just for completeness, let’s take a look at the admin screens for editing and updating Custom Mappings. Here’s an overview of all of the different Custom Mappings that have been created:

Drillbridge Custom Mappings List

Here’s a look at editing a Custom Mapping:

Drillbridge Edit Custom MappingAnd here’s previewing the list of individual mappings available for a given mapping (that have been uploaded by importing a text file):

Drillbridge View Entries in a Custom MappingThere you have it. As I mentioned, this feature will be available in upcoming release version 1.4.0, which should be out later this month. This feature is really, really cool, and there are a few more things this release adds that I will be talking about over the next week up to the release.

Drillbridge, the simple way to implement drill through

I implemented a drill through solution for a company earlier this year. We used a combination of Essbase Studio and ODI. I always felt right at home with Essbase Integration Services (EIS) but hadn’t played too terribly much with Studio – until now.

The solution came out working beautifully. Along the way, however, my research into the solution options led me down some interesting paths. In particular is that Essbase has supported drill-through definitions on cubes for quite some time. In practice this means you can define intersections on the cube that should be drillable, and then provide a URL to drill to. Generally this will be some Oracle software but the door is open to drill to anything – provided you can implement the backend properly.

What started out as a proof of concept has evolved into software that I am calling Drillbridge. Drillbridge is a turnkey solution for implementing drillthrough to a relational database. All you need is the following:

  1. An Essbase cube
  2. A relational database with data you want to drill through to that can be mapped somehow from the source data
  3. SmartView (sorry Excel add-in users)

I should add a little bit more about that second point: if the member in your cube is Jan or January but you have a table somewhere that has a field with a value of ’01’ or similar, then you need to construct a view/query that can map from one to the other somehow. Similarly, if you prefix your Account members with A or A_ or something, then you just need to construct the query to transform the data accordingly. In practice I am finding that this isn’t too hard.

Drillbridge works out of the box with Microsoft SQL Server, Oracle, and MySQL, with potential support for pretty much anything that JDBC can connect to (hint: that’s almost anything on the planet).

I’ll have some more info in the coming weeks but for now I threw together a pretty quick video showing the basic Drillbridge admin interface and a working demo from the venerable Sample/Basic database.