Drillbridge: Introduction

***Updated 2016/03/10 for Firefox Issue Fix***

So you have an existing Essbase model and you are finally ready to provide your end-users with drill-through.  But wait…EIS is dead and buried and while Essbase Studio can be made to function with an existing cube, it is somewhat painful to actually make it work properly.  On top of that, it just isn’t very flexible in the way that it provides drill-through.  So what do we do?

I guess we could technically go build our own custom drill-through definitions and figure out a way to make it work.  There’s always FDMEE, but that’s not cheap and doesn’t offer upper-level drill-through.  Enter Jason Jones (a fellow Oracle ACE) and his amazing tool just for drill-through.  Drillbridge is one of the coolest third-party tools I’ve used in the EPM space.  So how do we get started?

First, let’s head over to the Drillbridge website and download the Community Edition (1.5.4).  This is the free version and a great place to start.  You can download 1.5.4 here.  You will need to register and login to the site to actually see the download link.  Once we have the ZIP file downloaded, we can move on to installation.

Before you install Drillbridge, make sure that you have Java installed and in the PATH.  You can verify this by opening up a command prompt and typing java -version.  Once we verify that Java is working properly, the installation is pretty straight forward.  We just unzip the file that we downloaded into a directory on our server.  I chose E:\Drillbridge.  You should end up with something looking like this:

Drillbridge01Open up a command prompt change directories to the bin folder and test Drillbridge by typing Drillbridge.bat console.  Assuming everything work, you should see something like this:

Drillbridge02

You can go ahead and stop the service and now we are ready to install the service by typing in Drillbridge.bat install.

Drillbridge03

This should create a new service.  We can run Services.msc to make a quick change to the service.  I like all of my services using my Hyperion Service account, so I right-click on the new service and click Properties.  Once on the properties screen, click on the Log On tab and enter your credentials.

Drillbridge04

Then click OK and you should be ready to start up your service:

Drillbridge05

Now we can log into the system and start our configuration!  Let’s fire up the website and take a look.  You should be able to navigate to [Servername]:9220 which should provide you with the splash screen:

Drillbridge06

We’ll start by creating a connection.  Click on the Connections button at the top of the screen and you should be prompted for a username and password.  The default username is admin and the default password is drillbridge.

Drillbridge07

Once you are logged in, you are ready to click the New Connection button:

Drillbridge08

In case you want to test this with everyone’s favorite Essbase application, I’ve created a SampleBasic SQL Server database for you to play with.  I’ve also included a SQL Essbase Load Rule to load data our Sample.Basic Essbase database.  The database includes a Fact table with 1.25 million records of data to load along with level 0 meta-data tables and a full extract of the meta-data from Sample.Basic courtesy of the Advanced Outline Extractor from AppliedOLAP.  You can get both here.  If you end up using my sample, your connection should look something like this:

Drillbridge09

Now that we have a SQL data destination for our drill-through, we are ready to configure the Essbase side of things.  First, we’ll add an Essbase server.  Click on the Servers button at the top of the screen and then click on New Server button.

Drillbridge10

Enter your Essbase details and click Save.

Drillbridge11

Now that we have our Essbase server configured, we can add our actual Essbase database.  Click on the newly created server:

Drillbridge12

Once you click on the server, you should see a new tab.  Click on the Cube Mappings tab:

Drillbridge13

Click on the New Cube Mapping button:

Drillbridge14

Enter the details for your Essbase application and click save.  I’ll be using Sample.Basic to go with my sample data:

Drillbridge15

Now that we have our database configured, we need to configure the deployment specs.  What’s a deployment spec?  Basically we need to specify the intersections in our Essbase database that will be enabled for drill-through.  We specify the deployment spec once because we may have multiple drill-through options for each of our intersections.  To add our deployment spec, click on Admin in the top right corner and then click on Deployment Specs.

Drillbridge16

Click on the New Deployment Spec button:

Drillbridge17

Enter a name and a set of intersections in the specification.  Also, check the box indicating Level-0 only.  We’ll start simple in this blog post and in our next post we’ll head to the upper levels where the fun stuff happens.

Drillbridge18

The code that I used basically gives us level 0 for all of our dimensions except the Scenario dimension.  I’ve just specified Actual and Budget as the Variance members are calculated anyway.

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

We have all of the foundation ready to go, now all we need is a drill-through report.  Click on Reports and then click on New Report.

Drillbridge19

We’ll give it the report a name, choose a connection, and provide a query.  We’ll also select an Essbase connection and choose a few options at the bottom.

Drillbridge20

And here’s the code for the query:

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  = '{{ "name" : "Market",    "expression": "#Market"      }}' AND 
    DimMeasures = '{{ "name" : "Measures", "expression": "#Measures" }}' AND
    DimProduct = '{{ "name" : "Product", "expression": "#Product" }}' AND
    DimScenario  = '{{ "name" : "Scenario",  "expression": "#Scenario"  }}' AND
    CONVERT(CHAR(3), CAST([DimYear] AS DATETIME), 0)  = '{{ "name" : "Year",  "expression": "#Year"  }}'

Once we create the report, we still have a few things left to do.  Click on the report name to modify the report.

Drillbridge21

Click on the Validate tab to verify that the connections to both Essbase and our Relational database are good along with the report definition.

Drillbridge22

Click on the test tab and enter an intersection and click Build It to see a preview of the drill-through report.

Drillbridge23

And here’s the sample:

Drillbridge24

Finally, we’ll choose our Deployment Spec and  Server and click the Deploy button to finish off our first drill-through with Drillbridge.

Drillbridge25

Now we can fire up Smart View and try it out.  We’ll drill into a level-0 intersection and make note of our value ($249,683):

Drillbridge26

And here’s our nice drill-through report courtesy of Drillbridge:

Drillbridge27

Our numbers up top tie (since this is pulled from Essbase…it should).  You can click on the Excel link and tie out the numbers.

If you are using Firefox as your default browser, beginning in 11.1.2.4, it requires the Oracle Smart View for Office 11.1.2.5.x extension.  If you don’t have this installed, you will likely receive something like this:

FirefoxFail

That was my error until I installed this:

FirefoxWin

And that’s it for today.  This is one very long post, but there’s no great way to break it apart.  Go grab Drillbridge and give it a try.  Exciting things are coming and I can’t wait to use this product at several of my clients.  As we continue to explore this tool, we’ll move onto upper level drill-through and then move onto some of the more advanced features.

The EPM Week In Review: Week Ending March 5, 2016
Drillbridge Aquired by Applied OLAP and Community Edition 1.5.5 Released

Comments

  1. Chris Rothermel
    March 7, 2016 - 2:43 pm

    Great product and the easiest EPM product I’ve ever installed. One big advantage Drillbridge has over FDMEE is drill-through at upper levels.

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.