Drill-Through in PBCS and Hyperion Planning Without FDMEE

While recently debugging an issue with FDMEE, I needed to test drill-through in Hyperion Planning without using FDMEE.  But wait…can you even do that?  I had always planned on showing how to use Drillbridge with Hyperion Planning, but as I was talking with Jason, he mentioned we could even get it working in PBCS.  So how does this work?

Let’s start easy with Hyperion Planning.  If you happen to read Francisco’s blog, you may have already read this post about FDMEE drill-through.  It actually tells us that FDMEE just uses the Essbase drill-through definitions.  This happens to be the exact functionality that Drillbridge uses.  As it happens, if we set up drill-through on an Essbase cube that supports Planning, it just works.  See…easy.  But let’s try it with the Vision cube:

I’ll spare all of the details of the Drillbridge setup, but we’ll cover a few specifics.  First we’ll set up a test deployment specification:

PlanningPBCSDrillthrough01

So you can use all of your regular functions here, but I wanted to keep it super simple for testing purposes.  Next, we need to setup a connection to the Essbase database:

PlanningPBCSDrillthrough02

Once we deploy the report, we can take a peek at what it actually produces in Essbase:

PlanningPBCSDrillthrough03

So what happens in Planning?

Here we can see that the cell is enabled for drill-through:

PlanningPBCSDrillthrough04

When we right-click we have to click drill-through to see our choices:

PlanningPBCSDrillthrough05Once we click Drill Through we should see a list of all of the valid reports for that intersection.  Just like in Essbase, we can see multiple reports if multiple reports are defined:

PlanningPBCSDrillthrough06

Finally, we can click on the link and we are redirected to our report in Drillbridge:

PlanningPBCSDrillthrough07

So there we have it…drill-through without the use of FDMEE.  The coolest part is that this works everywhere in Planning.  Planning Web Forms, Planning Ad Hoc Grids, Planning Web Forms in Smart View, Planning Ad Hoc in Smart View, and Financial Reports.

But what about PBCS?  As it happens, PBCS works basically the same way as Planning.  The difference is, we can’t directly deploy the drill-through definition through Drillbridge.  So how do we do this?  If we look at the drill-through region defined, all we really need to do is create one manually that will point back to Drillbridge.  We’ll fire up PBCS and find a way…

Without EAS, Oracle has moved many of the features we would normally find there to Calculation Manager.  Open calculation manager:

PlanningPBCSDrillthrough08

Click on the small Essbase Properties icon:

PlanningPBCSDrillthrough09

Find the application to which you wish to add drill-through and click on Drill Through Definitions:

PlanningPBCSDrillthrough10If the database isn’t started, you will get a dialog like this:

PlanningPBCSDrillthrough11

Once the Drill Through Definitions dialog is displayed, click the plus sign:

PlanningPBCSDrillthrough12

We’ll start by entering a name and the XML contents.  I copied and pasted my on-prem XML from EAS.  Then click Add Region:

PlanningPBCSDrillthrough13

Next we add our region (copy and pasted from EAS, changing year to FY14 for PBCS Vision) and click Save:

PlanningPBCSDrillthrough14

Now let’s go see what happened:PlanningPBCSDrillthrough15

Enabled for drill-through!  Now let’s right-click and take it for a spin:

PlanningPBCSDrillthrough16

And let’s click Drill Through:

PlanningPBCSDrillthrough17

And just like on-prem we see our drill-through report name.  Now let’s click on it and…oh no!

PlanningPBCSDrillthrough18

Okay, that might be a little dramatic.  The one downside to this approach is that we are leaving PBCS to come back to an on-prem server.  So it let’s us know that this might not be secure.  Let’s just click continue and see our data!

PlanningPBCSDrillthrough19

And there we have it…working drill-through to on-prem using something other than FDMEE.  Our very own Hybrid approach.  The one thing to note from above is that the circled area will not function properly.  I’m re-using a report I created for my on-prem version of Vision, so it does show numbers, but in PBCS you will need to turn off anything that references the API.  This also means that upper level drill-through won’t work…yet.  The REST API does give us what we need to enable upper-level drill-through, so I expect this feature to be added in the future.

At the end of the day, we have basically three options for Planning drill-through:

FDMEE

Pros:

  • 100% Oracle Product with Oracle Support
  • Standard integration tool for the EPM stack
  • Loads data, audits loads, and provides drill-through

Cons:

  • Requires an additional license from Oracle
  • Does not support drill-through above level 0
  • Can’t bolt it onto an existing application without reloading data
  • The drillable content must exist in FDMEE
  • No ability to change the way the drill-through looks

Drillbridge

Pros:

  • Bolts onto any existing application
  • Insanely fast time to implement
  • Allows for full customization of the drill-through report
  • Data does not technically have to live in Essbase
  • Drill at any level, not just level 0

Cons:

  • Not an Oracle product, though supported by Applied OLAP, this can be a deal-breaker for some companies

Custom Drill-through

Pros:

  • You get total control over how you enable drill-through

Cons:

  • You get to do a mega-ton more work

For Planning, I think Drillbridge is a great alternative to FDMEE.  This is especially true for companies that don’t actually own FDMEE.  And for those of you that need upper-level drill-through, it really is the only choice short of hiring a developer to build you a custom solution.

PBCS is a little bit trickier.  There are still three options available:

FDMEE

Pros:

  • 100% Oracle Product with Oracle Support
  • Standard integration tool for the EPM stack and you likely loaded data to PBCS using it
  • Loads data, audits loads, and provides drill-through
  • You can use on-premise now, or the built-in version

Cons:

  • Does not support drill-through above level 0
  • The drillable content must exist in FDMEE
  • FDMEE on PBCS has a limited number of fields, and those fields have a character limit
  • No ability to change the way the drill-through looks

Drillbridge

Pros:

  • Bolts onto any existing application
  • Insanely fast time to implement
  • Allows for full customization of the drill-through report
  • Data does not technically have to live in PBCS

Cons:

  • Not an Oracle product, though supported by Applied OLAP, this can be a deal-breaker for some companies
  • Does not yet support upper-level drill-through (more on this later)

Custom Drill-through

Pros:

  • You get total control over how you enable drill-through

Cons:

  • You get to do a mega-ton more work

For PBCS, right now, I would generally stick with FDMEE.  Most of us are using it to load data into Planning anyway, so adding additional detail to the import format for drill-through isn’t much in the way of additional work.  However…if you need upper-level drill-through, you are out of luck…for now.  I fully expect that we will see a future release of Drillbridge that includes REST API integration.  This means that at a minimum, it should allow for upper-level drill-through.  When that happens…Drillbridge becomes a more powerful tool for drill-through for PBCS than even FDMEE.


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.


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:

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.