Drillbridge Aquired by Applied OLAP and Community Edition 1.5.5 Released

Applied OLAP has officially completed the acquisition of Jason Jone’s Drillbridge product.  This is great news for those of us that are fans of the product as it gives Drillbridge the backing of a company with a long-standing record of great support and other great products.  Jason will now be working directly for Applied OLAP.  Congrats Jason!  We’re all excited to see what comes next for Drillbridge.  His official update.

Speaking of what comes next, there has also been a new release of the Community Edition.  Version 1.5.5 is available here once you register.  Stay tuned for another post on upper level drill-through with Drillbridge!

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:

Open 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:

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

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.

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

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:

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.

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

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:

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.

Enter your Essbase details and click Save.

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

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

Click on the New Cube Mapping button:

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

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.

Click on the New Deployment Spec button:

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.

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.

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.

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.

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

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

And here’s the sample:

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

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):

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

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:

That was my error until I installed this:

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.