Lately I have been working on new materials and demo ware to help show off the power, flexibility, and sophistication of both the Dodeca Spreadsheet Management System and Drillbridge/Drillbridge Plus. I came across a really great Drillbridge mapping example today that I hadn’t specifically solved before, but with a little creativity I was able to write the proper Drillbridge query and get exactly what I wanted.

Consider an Essbase cube with the following dimensions:

• Years: FY15, FY16, etc
• Periods: Periods/Quarters/Months
• Scenario: Actual, Budget
• Departments: balanced hierarchy with four levels
• Location: Total/Division/Store
• Measures: Ragged hierarchy with accounts at level-0

For this post I am going to design a Drillbridge query that maps from this cube back to its related relational data, with the additional wrinkle that we want upper-level drill in several dimensions, including one where the dimension in the cube is represented by two different columns in the source data.

Here’s a screenshot from EAS of what this looks like:

Overview of dimensionality for retail cube

Let’s now turn our attention to the underlying transactional data that we want to drill to:

Example of upstream transactional data for retail cube

The upstream transaction data adds a few things that aren’t present in the cube, such as a transaction ID, transaction date, amount, and a memo. In terms of mapping from the cube members to the columns of data, things are fairly straightforward. The Scenario values are exactly the same, so that’s easy. The period is represented numerically as well as with a three-letter month (“Jan”), so that’s easy as well (if we didn’t have the three letter month name in the relational data, we could just easily use a built-in Drillbridge function that converts month names to numbers). The account is also a straight mapping. The division and location are a little interesting, however.

Notice in the cube that each entity/location is made up of its three digit division, a hyphen, and a three digit location number, such as 701-101. This is actually really easy to accommodate in Drillbridge because the Location will be passed in and it’s easy enough to use the substring function to get the first three digits and the last three digits.

Where things get interesting, however, is if we want to provide upper-level drill in the Location dimension. As an example, this is what we might need/want a generated query to look like:

SELECT 
	* 
FROM
	SUPERMARKET_TRANSACTIONS 
WHERE 
	YR = '2014' AND
	PD_NAME IN ('Jan', 'Feb', 'Mar') AND
	SCENARIO = 'Actual' AND
	DEPT IN ('001') AND
	ACCOUNT = '0170100' AND
	(DIVISION, LOCATION) IN (('701', '101'), ('701', '102'))

Note that this code example is for MySQL, the syntax and technique varies a bit for Oracle or Microsoft SQL Server. Pay particular attention to the last line. Remember, in this table, the division and location are separate columns but are derived from a single incoming value or values. In order to properly check for the proper values (in the case of multiple locations) we actually need to verify that a given tuple (such as ('701', '101') of values exists. If the user drills from an upper level member in the Location dimension, you can kind of imagine that multiple level-0 members area going to be processed for Location token, such as 701-101 and 701-102 (to be clear, Drillbridge only handed a single member that was actually drilled on, Drillbridge takes care of figuring out the descendant members, whether it is from Essbase or PBCS).

So, knowing that, we can carefully construct the proper Drillbridge token to generate the query that we need. And here’s what that token expression looks like:

\"('\" + #Location.substring(0, 3) + \"'\" + ', ' + \"'\" + #Location.substring(4) + \"')\"

This looks kind of gnarly, but let me break it down for you. When you drill from upper level members in Drillbridge, Drillbridge gets all of the levef-0 descendants of the drilled member, applies them one at a time to your expression, then joins them all together (such as with a comma) in order to generate the final expression that is dropped in to the query. So first of all, the expression above, when applied to a single incoming member name (like 701-101) will generate this: ('701', '101').

The reason for the backslashes is to escape the double quotes. So from left to right we have this:

• Start with an opening parenthesis followed by a single quote: ('
• Apply the substring function to the value in the #Location variable. Substring is the Java String function of the same name, which is given a starting character (strings start with character 0, and an ending character (exclusive), such that the substring of the incoming value 701-101 will be 701.
• Add on a single quote and, comma, a space, and a single quote: ', '
• Now use substring on the location variable again but this time to get the last three characters, so that 701-101 becomes just 101, for example
• Follow that all with a closing parenthesis: )

Drillbridge will automatically join all of the individual values with commas and surround the whole thing with parentheses. Also note that the reason we are treating the division/location as strings (and quoting them) is because in this case, they are strings (more specifically, they are CHAR(3) columns in this table).

All that said, here’s our final Drillbridge query, complete with turning on drill to bottom for various other tokens:

SELECT * 
FROM
	SUPERMARKET_TRANSACTIONS 
WHERE 
	YR = '{{"name":"Years","expression":"'20' + #Years.substring(2)","sampleValue":"FY14","overflow":"","overflowAt":0,"flags":""}}' AND
	PD_NAME IN {{"name":"Periods","expression":"#Periods","drillToBottom":true,"sampleValue":"Qtr1","overflow":"","overflowAt":0,"flags":""}} AND
	SCENARIO = '{{"name":"Scenario","expression":"#Scenario","sampleValue":"Actual","overflow":"","overflowAt":0,"flags":""}}' AND
	DEPT IN {{"name":"Departments","expression":"#Departments","drillToBottom":true,"sampleValue":"001","overflow":"","overflowAt":0,"flags":""}} AND
	ACCOUNT = '{{"name":"Measures","expression":"#Measures","sampleValue":"0170100","overflow":"","overflowAt":0,"flags":""}}' AND
	(DIVISION, LOCATION) IN {{"name":"Location","expression":"\"('\" + #Location.substring(0, 3) + \"'\" + ', ' + \"'\" + #Location.substring(4) + \"')\"","drillToBottom":true,"sampleValue":"701","quoteMembers":false,"overflow":"","overflowAt":0,"flags":""}}
ORDER BY TRANS_ID

One of the really great features in Drillbridge Plus is the token editor GUI that makes editing token parameters in queries a snap. Here’s a screenshot of editing our kind of complex Location token in the editor:

Details of token for Locations in Essbase cube

Back over to the report configuration itself, we also need to make sure to set the Essbase Connection for this Drillbridge report definition, because Drillbridge needs to know which cube to inspect in order to determine what the level-0 members are:

Drillbridge query configuration and related Essbase connection

Just for fun, let’s also tweak a couple of the options available on this report type. Of particular interest is that I want dates to be formatted nicely (Smart Formatting) and I want to turn on auto numbering, which will cause a column to be sliced in automatically with the current row number (I could achieve this in SQL but it’s really easy to just turn on the Drillbridge feature):

Adding a couple of convenience options to drill-through configuration

With all of that configuration out of the way, we are now ready to test things out. Nicely enough, I have defined “sampleValue” parameters for all of the tokens, which means when I go to test the report configuration out, the boxes are all filled in for me. This is one of my favorite features because it saves a ton of time during development:

Testing the drill-through definition

Of particular interest in this test configuration is that you can see I am drilling on a division (because I want to make sure my upper-level drill is working properly) as well as an upper level time member (Qtr1) so I can also make sure that that’s working. Here are the results of executing the report:

Data comes back exactly how we wanted it

I’d be lying if I said I got this to work on the first try (I always forget a comma or quote somewhere), but here we are, with our auto number column, automatic formatting on the dates and amounts, and more importantly, all of the proper data for all locations under the division we wanted to see. After this, I defined a quick deployment spec (not pictured) to allow for upper level drill in the Location dimension (and others), deployed it to the cube, and fired up Smart View to verify the drill-through to be working.

After quickly getting my ad hoc sheet configured (and using the bright neon lime green to highlight drillable cells), I set things up just right in order to test the same intersection from Smart View (meaning drilling on a quarter and a full division). Here’s the sheet as well as the results of the drill-through request:

Testing the drill definition in Smart View

Summary

I really enjoyed being able to use Drillbridge to map data from the cube to relational for this non-trivial mapping example. I think this was a really nice, clean approach that retrieved the exact data that we wanted. Another way we could have solved this and kept the Division/Location mapping even simpler would have been to actually just use a view to combine the division and location with a hyphen, then we could compare the values from the Location dimension directly. This technique is often employed when you have control of the target database and you just want to keep your Drillbridge query as simple as possible (as in all tokens are just passed right through with no adjustment). Either approach works just fine, although often there’s no ability (for technical/security reasons) to put in custom views. You can also use an ETL tool to extract out the data you need (and map it in to a format that’s conducive to the cube) but then you are introducing more moving parts and more development effort. One of the reasons so many companies are enjoying Drillbridge is that it keeps the development time (and moving parts) down to a minimum.