Drillbridge Plus has recently gained a new feature at the request of a customer. This one is kind of interesting and required a bit of deep thinking in terms of the best way to architect it. Here’s the deal: Smart View will let you drill-through on a data value where your grid is using attribute dimensions, but it won’t pass the attribute associations as part of the request. And as it turns out, there are instances where it’d be useful to have that attribute member so you can use it to dial in the SQL query that Drillbridge creates and executes.

What to do? Ask Drillbridge to go fetch those attribute member values for you anyway! In this post I’m going to walk through a use-case showing off the new feature, how to set it up, and I’m also going to show off some recent debugging enhancements that are really useful and have been around for awhile.

Let’s start. First, consider a normal Drillbridge report definition with a simple query:

A normal Drillbridge report definition (before adding attributes)

The preceding example is a typical Drillbridge definition that works with our good-ole’-friend-til-the-end Sample/Basic. Well, actually it’s a modified Sample/Basic that has a Years dimension, but you get the idea. In this example, Drillbridge will perform a pretty common conversion where the year member such as FY18 is converted into the value ‘2018’ to be used in the SQL query. This is accomplished with the handy built-in Drillbridge function, #removeStarting, which works really well here because it can safely handle values without throwing exceptions.

Let’s now go turn on the debugging feature on the options page:

Turning on debugging for the Drillbridge report

This is a relatively new feature to Drillbridge but is incredibly helpful for debugging and developing report definitions. When debugging is turned on, instead of executing the SQL query, Drillbridge just creates it as it normally would, but then just outputs the query without actually executing it. This is really great for development when you have a long-running query that you don’t want to wait for, or you just need to exactly compare your SQL syntax to a known-working example, and don’t want to dig it out of the logs. Turning on debugging for this example will let us see exactly if our attribute variables are working how they are supposed to be working.

Let’s go ahead and take a look at the debugging feature in action before we get going with anything attribute related. Over to the Test screen:

Testing the Drillbridge report with debugging turned on

Verifying Drillbridge report output with debugging turned on:

Viewing thew SQL output of the Drillbridge report with debugging turned on

Okay, so our “base case” is looking good. Now let’s get some attribute variables working. Let’s head back over to the options page and turn on the attribute fetching feature as well as specify which dimensions it should apply to:

Turning on and configuring new Attribute options on Drillbridge options page

Notice the Attributes category and how the attribute checkbox is now checked, and I have specifically told Drillbridge that it applies to the Market dimension. I can specify additional dimensions but adding them in to this definition and separating them with commas.

Now, back over to the report definition. If it wasn’t set already, I absolutely have to make sure that the Essbase Connection is set for the report, so that Drillbridge knows which outline to open up and pull attributes for. Here I have made sure to specify the Sample/Basic cube my local epmvirt11124 server as the associated Essbase server (these connections are configured on the Drillbridge Servers page):

Setting the Essbase Connection on the Drillbridge report definition

With all of that out of the way, I can now edit my Drillbridge query to reference the new variables that will be available. Let’s go ahead and add in some “short tokens” to represent the Market and Population dimensions:

Updating the Drillbridge report definition to use a new attribute variable reference (Population)

Short tokens are a sort of technique where we can just put in a simple definition of a Drillbridge token, then use the convenient token editor (note: token editor also only available in licensed version of Drillbridge) to dial in the configuration. The format for a short token is a dimension name with a pound sign (or octothorp, if you want to get fancy), surrounded by double curly braces. With the query updated with these short tokens, I can now navigate over to the token editor to edit their specific settings:

Navigating to the Token Editor

Then select the token:

Selecting a token to edit

Now edit its details:

Updating sample value to help with testing

For the Market token I’ll just say it has a default value of Washington, in order to facilitate testing. With the tokens all saved I can now go to the testing page again:

Testing the modified report

I don’t need to specify a value for Population since it’s dynamically populated as per the report configuration. Let’s build it:

Verifying the the proper attribute association shows up for the Population variable

Success! You can see that the variable #Population has been populated for us by Drillbridge, by fetching the value associated attribute value for the specified Market from the outline (in this case, that value is Small_6000000). I can treat it like I would any other Drillbridge query variable: I can prepend things, take substrings, or whatever other transformations I need in order to craft exactly the SQL query that will be most useful.

Just for fun, I’ll go add in that we now want to test to see how the “Pkg Type” attribute dimension is handled. This is an interesting case because as commonly happens with Drillbridge queries, we have a space in the dimension name. Because variables need to not have spaces, Drillbridge will convert them to use underscores instead. So we can refer to the Pkg Type attribute variable as #Pkg_Type, then test the query:

Testing with additional attribute variables

And here are the results (note that Drillbridge fetched the value “Can” for our package type from the corresponding attribute dimension):

Verifying the output with additional variables (“Can”) package type

Lastly, just for the sake of completeness, here’s what some of the relevant sections of the outline look like in EAS:

Comparing to outline in EAS

For testing purposes, do note that the raw member name (100-10) was used for the Product, rather than the alias of Cola, which actually wouldn’t pull back the needed attribute value. The member name/alias were the same in the case of Market/Washington, so it wasn’t an issue.

All For Now

That’s all for now – this new feature is available in the latest licensed version of Drillbridge. It’s a seemingly simple feature but I know it will be helpful in a handful of situations for various organizations, especially ones that use attribute dimensions to keep the size of a cube down. Please don’t hesitate to reach out if you have any questions about Drillbridge or this new feature.