Drillbridge: Enabling Upper Level Drill-through

In our last post about Drillbridge, we did the basic introduction and then ran through the steps to get it up and running. But, in the interest of simplicity, we kept it simple. Now we are ready to get a little more complex.

For today, a little more complex will be upper level drill-through.  This is one of those things that can make Essbase admin’s and DBA’s run from a building like it’s on fire.  Why?  Because upper level drill-through relies upon the end-users ability to not do something stupid.  As we all know…it’s a virtual certainty that they will.  This is where Drillbridge really shines.  We have several mechanisms to control the level of stupidity that we allow our users to engage in before we shut them down.

So let’s get started.  Log into your Drillbridge server and start by changing up the Deployment Spec.  If you remember from our first post, we started with this:

@RELATIVE("Year", 0),@RELATIVE("Measures", 0),@RELATIVE("Product", 0),@RELATIVE("Market", 0),"Actual","Budget"

We also checked the box indicating that this was Level-0 only.  Now let’s change that specification to this:

@IDESC("Year"),@RELATIVE("Measures",0),@IDESC("Product"),@IDESC("Market"),"Actual","Budget"

We have switched our Year dimension to use the IDESC function.  We have left the Measures dimension at level-0, since many times we can’t tie numbers out at upper level due to upper-level aggregation operators.  We have also switched Product and Market to IDESC as well.  We’ve left Actual and Budget alone since there is no upper level in the Scenario dimension.  We’ll also un-check the Level-0 members only option.  At the end, it should look something like this:

DrillbridgeUpper01This is our first method of preventing stupid user tricks.  We only allow drill-through that makes sense.  Now that we have our deployment spec configured, we can move on to the report.

Before we get too deep into the report, let’s first talk about how upper-level drill-through really works.  If a user chooses to drill-through on Qtr1 for instance, what does that mean?  Essbase is simply going to pass that value through, Qtr1.  But Drillbridge takes that member and uses the Essbase JAPI to determine the level-0 descendants of the member.  In our simple case, this is just Jan, Feb, and Mar.  But wait, we can’t use an equals sign in our SQL statement and evaluate multiple values, can we?  No, no we can’t.  So let’s look at a sample SQL statement that will enable our upper-level drill-through:

SELECT
	 DimMarket
	,DimMeasures
	,DimProduct
	,DimScenario
	,CONVERT(VARCHAR(10),CAST([DimYear] AS DATETIME),101) as [Date]
	,CONVERT(CHAR(3), CAST([DimYear] AS DATETIME), 0) AS DimYear
	,TransactionNumber
	,Amount
FROM
	[SampleBasic].[dbo].[FactSample]
WHERE
    DimMarket  IN {{ "name" : "Market",    "expression": "#Market", "drillToBottom" : "true" }} AND 
    DimMeasures = '{{ "name" : "Measures", "expression": "#Measures" }}' AND
    DimProduct IN {{ "name" : "Product", "expression": "#Product", "drillToBottom" : "true" }} AND
    DimScenario  = '{{ "name" : "Scenario",  "expression": "#Scenario"  }}' AND
    CONVERT(CHAR(3), CAST([DimYear] AS DATETIME), 0)  IN {{ "name" : "Year", "expression": "#Year", "drillToBottom" : "true" }}

In our last post, we didn’t really talk about the query, we just used the simple sample.  Let’s take some time now to actually understand how the query engine in Drillbridge works.  First, you’ll notice double curly braces in our where clause.  This is what tells Drillbridge to evaluate an expression.  Inside of our curly braces, we have three settings that we need to define to get back upper-level members.

First we specify the dimension that we want to pull back from Essbase.  This is the Name property.  Next we have the actual value and how we want to format it.  This is the Expression property.  If you take a look at the PDF that included with Drillbridge, you can get an idea of all of the possible expressions, but for today we’ll keep this part simple and just use #DimensionName.  Our last property is optional if we don’t care about upper-level data.  But, because we care, we must set the drillToBottom property to true.

This is the setting that tells Drillbridge to take Qtr1 and find all of the level-0 descendants.  Now all we need to do is modify our SQL statement to use an IN instead of an =.  And with that we should be ready to deploy our new report.  So let’s try it out.  Let’s start by drilling into level-0 for everything but our Year dimension.  For the Year dimension we’ll try an upper-level query:

DrillbridgeUpper02

Once we drill-through, we see that we have Qtr1 in our POV and multiple months of data:

DrillbridgeUpper03

Excellent!  We have upper-level drill-through.  But wait, what about stupid user tricks?  Let’s take a look back at our report:

DrillbridgeUpper04

Here we have two ways to prevent long-running queries executed by users.  First, we can set a row limit.  This is generally enough, but sometimes, a user can find a way to execute a query that returns a low record count, but it still takes forever.  So as a safe-guard, we can also add a query timeout.  This way we are protected on both fronts.  So let’s start with the row limit.  If we enter a row limit of 10 and try out the drill-through again, we get this:

DrillbridgeUpper05

Great!  Only 10 rows!  But wait…what about the other rows?  The downside to the Community edition is that it does not offer a message to users to let them know that additional rows were suppressed.  Bummer.  But don’t worry too much, as we have a way around this that I’ll cover in my next Drillbridge post.  We’ll also cover query time limits in a later post as I still have to resolve the issue I’m having.

And with that, you should now have working upper level drill-through with at least a row limiter.  Our next Drillbridge post will cover some of the advanced features.

PBCS April 2016 Update: FDMEE On-Premise Is Here, Kinda…
The EPM Week In Review: Week Ending April 2, 2016

Comments

Leave a Reply

Your email address will not be published / Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.