Drill-Through in PBCS and Hyperion Planning Without FDMEE

Brian Marshall

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.

Author:

9 Comments

  1. Srx
    SrxReply
    May 3, 2016 at 4:57 am

    Great great great post! While reading I was thinking about why is drillbridge drill to be prefered over native drill to FDM but it was clear reading at the options below. Reading that Planning drill to FDM requires extra licen$e drives me mad.
    I implemented drillbridge by curiosity in Devt environment, it took me 2 hours +/- still have to investigate the paging feature.

    • Brian Marshall
      Brian MarshallReply
      May 3, 2016 at 6:09 am

      I think you have to consider that FDM and FDMEE are data management applications. They happen to also provide you with drill-through, but that is almost an afterthought compared to their real purpose. Conversely, Drillbridge is solely designed for that purpose and as a result, it is awesome at it. The paging features in the free version, are functional, but they can stress your relational source. If you are really interested in paging, I hear the 3.0 release of the Enterprise Edition of Drillbridge has made massive leaps in performance and functionality in that particular area.

  2. May 8, 2016 at 12:19 am

    […] This week I posted about Drill-Through in PBCS and Hyperion Planning Without FDMEE. […]

  3. Nitin
    NitinReply
    August 11, 2016 at 1:01 pm

    Thank you Brian. Amazing post! I have been looking for exactly the same thing. Trying to setup custom drill through. Working great through Essbase connection but no luck using Planning forms or Planning connection in SV. I right click and select drill through, an empty small pop-up shows up without the drill through URL.

    You had to restart any services or do anything special after you created drill region? This is how mine looks:

    Thank you.

    • Brian Marshall
      Brian MarshallReply
      August 11, 2016 at 4:39 pm

      Is this an ASO Plan type? You may want to check this out:
      epmmarshall.com/drill-through-with-aso-plan-types/

      This is a bug from the original 11.1.2.3 introduction of ASO plan types. It is fixed in 11.1.2.4, but I don’t know if it will be in 11.1.2.3.

Leave a Reply

Name*
Email*
Url
Your message*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>