Friday, December 9, 2016

Using Drill Through Reports in Hyperion Smartview add-in (for metadata)

Extra metadata information needed please

On a regular basis following question pops up with Essbase users:
"We want to show metadata linked to our clients (or suppliers, employees,..) in Essbase. How can this be done? But do we need to load all of this into Essbase to do so?"
The answer is yes, it's possible to lookup  - while analyzing data in your Smartview sheet - any additional information for (a) specific dimension(s) from external tables by means of  "drill through" report. 
Interested? You might want to check out this video to see how the end-user experience looks like in Smartview before you dive into the below specified technical details.

Essbase Studio

There are a couple of possibilities to link data residing in relational tables (e.g. Oracle Database, txt files,..)  with the data or metadata stored in Essbase. This posting explains the use of Essbase Studio Console for querying additional metadata residing outside of the Essbase cubes.
Essbase Studio is the updated version of the old Integration Services but combines this concept with the possibility to do more "classic" admin actions, as we know them from the Essbase Administration Console.   You might already have heard that Oracle positions the Studio as the future standard toolbox for the Essbase administrator, so it's worth spending some time on this.
A typical setup within Studio is to make a connection to a number of tables containing either data and/or metadata. Studio can then automatically propose a star schema that can be used to populate your dimensions and to load in the data.

To Drill-Through or not, that's the question

Cube Setup

In our example we would like to see the phone-number for Adam, one of our employees. We have following information available in a table of our sample HR system:

Content of the relational table from Oracle Database visualized in an Excel sheet
We only loaded in the Employee-id, using the combined First and Last Name field as the alias.  One could off-course decide to additionally include these fields into the Essbase database and query them natively, but there might be some advantages in not doing so:
  • infrequent use of the data
  • ownership of the data
  • or you just don't want to make this part of the process
Essbase Studio - view of the setup of the hierarchy/dimension

In our outline editor (Essbase Administration Console) this looks like this:

Essbase Administration Console - Outline Editor

Drill Through Report Setup

One of the objects that can be added in the Studio Editor is the "Drill Through Report" option we are looking for.

Essbase Studio
On the 'Context' tab, you choose the dimension(s) the report should work with..
Essbase Studio - Content Tab

 .. and in the 'Report Contents'- tab, select the columns from the table that should be shown when the drill is executed.
Essbase Studio - Report Content

VoilĂ , this will make our example work. The result can be seen in the video on top of this posting (if  you haven't done so).
More advanced setups may require the tuning of the SQL query with the necessary variables to link table and dimension(s).
Essbase Studio - Report Contents - Template SQL