Dodeca has robust support for drill-through. You can drill from Essbase data to relational data, from Essbase to Essbase, and SQL to SQL. You can have multiple drill-through definitions in a single view, so that a user can choose one of many drill destinations. Today I want to look at the simplest form of drill-through in Dodeca, which is to simply enable a couple of the Data Drillthrough options on a source view, tell it what the target view is, and be done with it. I call this the “simple” version of drill-through because it just gives us the ability to double click on a data cell and drill from it.
The less simple, or rather, more elaborate, version of drill-through can be configured with custom context menus, multiple drill targets, and more configuration options than you can shake a stick at. I’ll be looking at an example of that in an upcoming article. But for now, here’s how “simple” drill-through can be quickly and easily configured in Dodeca.
The example I’m going to look at today is one where we’ll let the user drill from one Essbase-based view to another. While many people think of Essbase drill-through in terms of going from OLAP/Essbase/consolidated data back to the original source OLTP/relational/transactional data, drill-through between Essbase views is an incredibly useful feature as well. It gives us the ability for a user to pull up data they are interested in, such as by time period and location, then very quickly jump to a different or expanded view of data based on those same intersections. Given the fluidity and seamlessness we can achieve in terms of going between different views with any data on them, drill-through becomes even more powerful. Instead of swimming upstream to more granular data, we can think of drill-through more as “intelligent navigation” – and drilling to details is just one type.
That all said, let’s build some drill-through, our first stop is the template for the source view:
Here we have just an absolutely bread and butter Dodeca template for viewing Essbase data. The user will be able to select the time period (notice that the Year dimension has been tokenized as [T.Year]). Recall that in Sample/Basic, the dimension named “Year” actually contains time periods. Other than the single token in this view, I’ve applied some very light formatting. The fictitious purpose of this view is to simply show off the Sales for products for major regions. As with other Essbase views I have configured before, I have also defined the particular named range that will be retrieved in this view:
The retrieval range is important to set so that Dodeca can determine which cells in the view will be drillable and which are just normal cells.
For the target view, I have set this up as my template:
So, for this use case, we want the user to be able to drill from, say, their current time period, for a given market. Whereas in the source view the user was only seeing a single measure (Sales), the target view will give them expanded details (Sales, COGS, Margin, Total Expenses, etc.) for the market and time period they were on. Let’s take a look at the configuration of the source view:
Most of the configuration we need is in the
Data Drillthrough section. While we have very powerful features to map the POV as needed in order to translate between views/data sources if needed, since this is all “intra-cube” in this example, we can keep the configuration very simple. Notice first that I have set the
OpenViewForDrillthrough. This means that when the user double clicks a data cell, a new view will be opened. Another option might be to simply add a new tab to the existing sheet – it’s completely configurable. Next I need to specify the view to drill to with the
DataDrillthroughViewID setting. Here I have chosen the target view I want.
There’s no real configuration that needs to happen on the target view. For good measure, I”ll show you that there are two selectors defined on it, though:
I just want to point out that the target view didn’t strictly need to have two selectors on it, because the proper values for the market and time period will come from the POV on the source view. But by including the selectors, I can make the target view useful as a standalone view, and also allow the user to change the POV on the drilled through view. But it’s my choice – maybe I don’t want them to be able to navigate anywhere after they open the view for drill-through. It’s up to us. As I’ve said before, Dodeca gives us structure with flexibility.
As I mentioned at the beginning of this post, this is what I call the “simple” Dodeca drill-through use case. A more elaborate case I’ll cover in the future will show custom menus, multiple drill-through targets, and more. But for now I hope this gives you a sense of just how easy drill-through can be to setup.