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.
