Friday, September 7, 2018

OAC - Essbase Drill-Through Reports

Essbase drill through reports had various versions in the past years. There were studio drill-through reports, EIS drill through reports and GL drill through reports. All of them served well for viewing the supporting details but all had pros and cons.

OAC - Essbase has a new and easy way to maintain drill through reports and is entirely re-written to solve all the problems for supporting details. As this is based on connections and data sources abstraction, there are many opportunities to innovate and make it a lot better.  In this article, we are going to discuss the mappings involved in the drill through reports. In the next article, I will show you how to create one from scratch.

Here is the connections and data sources abstraction:

1. Dimension or Current member mapping
2. Generation Mapping
3. Level-0 Mapping

Lets discuss them in detail:

1. Dimension or Current Member Mapping

This is the easiest one and it will query on top of the current member in the selection. For example, if you selected Jan, it will query  like ".... where year in ('Jan') and if query on Qtr1, it will query like year in ('Qtr1').  As all levels data is unlikely in the supporting details, this makes sense when you are building detailed data for flat dimension or want to drill only on particular generation.

2. Generation Mapping 

This will query the members in the dimension from a mapped generation. For example if you have details for month and you want to show the result when quarter is selected, this mapping will work perfectly.
As this is a recursive in nature, you can allow this result on any upper generation. It will not allow work if you map upper level generation and allow to drill on lower level generation.  For example if your supporting detail data is at year level but you allow to drill through on month generation, it will not work.

As shown in the example above, you can either go from year or quarter to see the results at month generation. This will work on the same generation too. For example, if you select Jan, it will still work.

3. Level-0 Mapping

Level-0 mapping is for the ragged hierarchies. When generation mapping does not fit as supporting details are for different generations but for level-0 members then this is the best fit.

As shown above, Qtr3 has no children and if you think that there are supporting details for Qtr3 as well then this mapping suits your use case.

As this is based on common framework of data sources, we will get following advantages:
1. Join Excel and DB data for supporting details once "Join Data Source" is available.
2. Editable Data source - Make changes in the supporting details to fix some data.
3. Multi cell drill though reports - In future, you will be able to select multiple cells at once and execute the drill through reports.

This is enough of the theory and we will see this in practice in my next article.
Stay Tuned!

No comments:

Post a Comment