Dodeca Drill-Through with OBIEE

We have implemented Dodeca only 6-8 months back and since then we have received many positive feedback about the tool. As an Essbase shop we were using drill through for long time. We have used EIS and later switched to OBIEE drill-through last year. Our users love drill through reports between SmartView and OBIEE. I have described here setup for SmartView to OBIEE Drill-through.

  Since the popularity of Dodeca increased within our user base, I started exploring the possibility of accessing same OBIEE drill-through reports from Dodeca by passing parameters(token) to OBIEE  GoURL. But one can ask, why would you do that when Dodeca drill though to relational database is easy to build? Well, I can think of two different scenarios where it can be very useful.

Scenario 1. Multiple sources: OBIEE has powerful connectivity to different type of source and complex data modeling capability. If data comes from different sources, OBIEE drill through will be useful way to leverage  OBIEE's power to make it one big logical transaction table.

Scenario 2. Graph & Chart: OBIEE has a different range of animated charting options. One can use same concept of passing parameter with GoURL  to OBIEE complex interactive charts. 
Here is a video how Dodeca drill through to OBIEE works...

Set up:

 Set up for this is really simple and requires very basic familiarity with Dodeca. First we will set up our target drill through report. This target report will show an OBIEE parameterized dashboard report in the built-in web browser view of Dodeca.

To develop the target report go to Admin->View tab. To create a new web browse  view click New. Provide a name for the report. For "View Type" Select WebBrowser from drop down list. 

On the Browser property click on URL editor and add your OBIEE go URL. In my case I had the following. 

I have highlighted the tokens that are used in the URL. These tokens will get its value from source report. Before using the link in the URL editor try it  with actual value to check if GoURL is working as intended. 

Next step is to set up the source view from where you want to drill down from. To do that create an Excel Essbase view. Make sure you have all the tokens used in target report declared in selector. Define the drill through properties in this Excel view to activate drill-through. 

 In Data Drill through property find DataCellDoubleClickMembersFilter. It sets the range of data where drill-through is valid. click the property field to open the Member filter window. Most of the options are very intuitive. Here is what I had in the Member filter window...

In dataDrillThroughViewID select target view name from the drop down list. Set OpenViewForDrillThrough as DataCellDoubleClickPolicy.

At this point save the view by committing the change. Preview the source report and double click on a valid drill through intersection to open OBIEE report in another tab. 

Detailed steps to create Excel Essbase view are well explained in YouTube by AppliedOLAP folks.

Perform Essbase drill through with OBIEE

Why OBIEE drill through when you have EIS or Essbase Studio? 

  Well, because EIS is marked for retirement and Studio has its limitations. Some of the studio limitations force you to have awkward work around in your environment. I have listed below some of those limitations that come to my mind immediately.

1. Need to deploy application through studio

2. Upper level drill down is not possible with ‘out of the box’ solution.

3. Does not work well with EPMA.

In this blog, I am trying describe how you can use Essbase drill through feature to connect to OBIEE via OBIEE parameterized dashboard. 

Step 1. Define drill through definition in Essbase cube.

Step 2. Write jsp code to decipher the list of object that you will receive when users drill down in smartview. Then redirect the connection to OBIEE parameterize URL to the OBIEE report dashboard. 

Step 3. Install jsp in a web server. It may be the OBIEE WebLogic server or external webserver like Tomcat.

Step 4: Build parameterized report dashboard in OBIEE. Its parameters must be based on Essbase cube’s member name.   

Step 1:Define drill through definition

Right click on essbase cube->edit-> Drill through definition.

You can add drill through definition directly via EAS. Control drillable intersection according to the requirement. Here is screenshot of drill through definition of one of my application.

You can copy the xml content from here

<?xml version="1.0" encoding="UTF-8"?>

<foldercontents path="/">

<resource name="OBIEE-TransactionDetails" description="" type="application/x-hyperion-applicationbuilder-report">

    <attribute name="name" type="string" xml:lang="es" value="Drill through" />

    <action name="Display HTML" description="Launch HTML display of Content" shortdesc="HTML">

      <url><![CDATA[http://YourOBIEEServer:7001/OBIEEDrillThrough/TransactionDetails.jsp?$CONTEXT$&$ATTR(id)$]]> </url>

    </action>

  </resource>

</foldercontents>

This xml above gives you an idea that we are connecting to jsp code TransactionDetails.jsp via http://YourOBIEEServer:7001/OBIEEDrillThrough/TransactionDetails.jsp with parameter CONTEXT$&$ATTR(id). Now TransactionDetails.jsp  will be responsible for reading these parameters and redirect your connection to your OBIEE dashboard. 

 Note: You can drill down from member name or Alias in smartview but Essbase always sends member name. so, your OBIEE drill through reports needs to have member name as parameter.

Step 2: JSP code

This is a sample jsp from my environment. Before you write this code, you should have your OBIEE drill through report ready. Try to access your obiee drill through report with parameterized URL. Once you successfully access reports via URL you are good to go. 

Note: Last step, response.sendRedirect(BIEEGoURL) redirects the connection to OBIEE URL to the report dashboard.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

    pageEncoding="ISO-8859-1"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<head>

<title>Redirecting to OBIEE</title>

</head>

<%@ page import="java.io.*" %>

<%@ page import="java.util.Map" %>

<%@ page import="java.util.Map.Entry" %>

<%@ page import="java.util.jar.Attributes" %>

<%@ page import="java.util.Iterator" %>

<%

    Attributes attribs = new Attributes();

    String OBIEEServer = "http://YourOBIEEServer:9704/analytics";

    String portalPath = "/shared/Transaction Details";

    String BIEEGoURL =OBIEEServer+"/saw.dll?PortalPages&PortalPath="+portalPath+"&Action=Navigate";

    String yr="";

    String period="";

//    System.out.println("Running... TransactionDetails.jsp");

    Map map = request.getParameterMap();

    Iterator iter = map.entrySet().iterator();

    while (iter.hasNext()) {

    Entry n = (Entry)iter.next();

    String key = n.getKey().toString();

    String values[] = (String[]) n.getValue();

    int i = 0;

    while(i < values.length)

    {

  

     if(values[i].contains(".id.")){

//      System.out.println(values[i]);

        if (values[i].contains("Account"))

               BIEEGoURL = BIEEGoURL+"&col1=%22Account%22.%22Account%22&val1=%22"+values[i].substring(17,values[i].length()).replaceAll("&", "%26")+ "%22";

//      If you have any special character like '&'etc , you need to replace them with ascii

        if (values[i].contains("Entity"))

               BIEEGoURL = BIEEGoURL+"&col2=%22Entity%22.%22Entity%22&val2=%22"+values[i].substring(13,values[i].length())+ "%22";

        

        if (values[i].contains("OperatingUnit"))

               BIEEGoURL = BIEEGoURL+"&col3=%22OperatingUnit%22.%22OperatingUnit%22&val3=%22"+values[i].substring(20,values[i].length())+ "%22";   

        if (values[i].contains("Intercompany"))

               BIEEGoURL = BIEEGoURL+"&col4=%22Intercompany%22.%22Intercompany%22&val4=%22"+values[i].substring(19,values[i].length())+ "%22";   

        if (values[i].contains("Years"))

               yr="20"+values[i].substring(11,values[i].length());

        if (values[i].contains("Period"))

               period=values[i].substring(10,values[i].length());

        if (values[i].contains("Product"))

               BIEEGoURL = BIEEGoURL+"&col5=%22Product%22.%22Product%22&val5=%22"+values[i].substring(11,values[i].length())+ "%22";   

        

      }// end if

     i++;

 

    }// end while(i < values.length)

      

    }// end   while (iter.hasNext())

       BIEEGoURL = BIEEGoURL+"&col6=%22Period%22.%22Period - Default%22||'-'||%22Years%22.%22Years - Default%22&val6=%22"+period+"-"+yr+ "%22";   

   

//    System.out.println(BIEEGoURL);  

%>

<%

response.sendRedirect(BIEEGoURL);

%>

Step 3: deploy jsp in web server

You can use external tomcat server or you can use obiee weblogic server for this. As Tomcat gel well with eclipse, I've used tomcat to develop it fully and later installed it in OBIEE Weblogic server. I have listed below the steps required to install your jsp code exported from eclipse as war file in Weblogic server. 

1. log in to weblogic server. Click lock and edit. 

2. Upload jsp file. 

3. Install the jsp code. Select default option, click finish.

4. Activate changes 

Find your newly installed application…and start it.

After successful installation it should look like this ...

Step 4: Build your OBIEE report

Logically it should be your first or second step. That way your connection information to the report will be readily available.