Drill-Through Revised

I recently wrote about setting up basic drill-through in Data Management.  As often happens, I’ve since learned more about the functionality and what I previously thought I knew was not entirely accurate.  Here is my attempt to correct any misinformation previously provided.

After setting up a new EPM planning application, we went about creating a target application in Data Integration.  Note: I am performing these tasks in the Planning Data Integration interface instead of in Data Management.  The steps should be pretty much the same, but I need to get used to using Data Integration since that is the path forward with Oracle. 

We wanted drill-through, so we were about to do what had been done previously to set up the target application.  But we wanted to understand the Enable Drill From Summary functionality.  Google returned much of the same information as the last time, but there was some new information in the results.  This new information was enlightening.  Instead of enabling specific dimensions, our new understanding was that all we had to do was check “Yes” on Drill Region and Enable Drill From Summary.  This is done from the Options tab.  By not selecting individual dimensions, the drill region definition will use the default dimensions of scenario, version, period, and year.   

The next steps were standard tasks to create the Import Format, Location, and Load Rule.  Once that was done, we were ready to test.  The load ran successfully.  A Smart View ad hoc was set up to view the data.  Green outlines on the cells indicate the data is drillable.  Both level 0 and summary level cells are drillable.

Right clicking the cell brings up the Smart View menu to select Drill-Through.

And a new sheet with the details behind the data.

A successful integration with drill-through enabled for both level 0 and summary level data is almost complete.  There is one last step to do.  Remember that we did not select individual dimensions for the drill region, we wanted it to be created with the default dimensions.  In Calculation Manager, open the Database Properties and then the Drill Through Definitions.

The definition has been created based on the members in the load. 

This needs to be modified so loads with different periods and years are included in the drill region.  Select Edit and update the members.  Scenario and version are not being modified since this load is specific to actual and final.  These members can be updated if needed.

Once the Drill Through Definition is updated, go back to Data Integration.  Open the target application.  The default dimensions are checked, these need to be unchecked.

Go to the Options tab and change Drill Region to “No”, but leave Enable Drill From Summary as “Yes”.

These changes to the target application are needed so the drill region definition is not changed with each load.  Now we will load another period to test that it is drillable.  We added Dec to our ad hoc and loaded the same data.  Looks good so far, green outlines on the cells indicate drill-through is available.

And drilling on a cell does in fact provide the details.

Drill-through can be done from Smart View like we’ve shown, or from a web form.  There are some differences in the details available on the web, and the web details can be opened from Smart View as well.  Reports should also be able to perform drill-through, but I have not tested that functionality yet. 

I also want to note that the first attempt at setting up this integration with drill-through at summary level failed.  I thought everything was being done in the correct order and with the correct settings, but something was not right.  I troubleshooted for a couple of hours trying to resolve with no success.  Last option I could think of was to delete the target application and start fresh.  All I can speculate as a reason for the problem is that Data Integration (and Data Management) can be finicky.

What’s the lesson of this post?  Never stop learning.  We had a process that worked to set up drill-through, but it was not the best.  So, we kept searching for more information and found a better way to do it, and to add functionality (summary level drilling).  I’m sure it won’t be long before we learn yet more useful aspects of drill-through.

As always, happy EPM’ng!

P.S. Special thanks to my fellow interRelite Mike Stevenson for working on this functionality with me.  It would have taken me much longer to figure out without his help.