Wednesday, September 9, 2015

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. 







Tuesday, August 25, 2015

How to extract fast from BSO Planning to ASO Reporting

Recently we have implemented a new BSO planning cube (for input purpose) and corresponding ASO cube for reporting. We have a process that Extract from BSO cube and load data to ASO cube. We are running this process every 15 min interval to sync our reporting cube with planning cube.
Well, I will not discuss here the merit of using BSO planning with ASO Reporting. But here in this blog, I would like to discuss certain way by which you can extract large chunk of data fairly quickly from BSO. Then you can load that data to ASO cube or do nothing and be happy that you did it and did it fast!
This process is much quicker than dataexport calc script extract (as per testing done in our environment). The process here is based on MDX extract to flat file from BSO with Java API. The same flat file can be loaded to ASO/BSO cube if needed. Key to your MDX query success? Keyword 'NONEMPTYBLOCK' in the query. It is complex to parse MDX output because of its weird format. But once you do that ‘Good thing will happen’...

Here is the code sample that we have used in our job that runs every 15 min to extract from BSO to ASO cube.



//Note1: Provide full path to your app/DB folder of target database as the value of filePath below. In case you are loading the extracted data back to ASO/BSO cube.

// Note2: We will extract data from BSO in flat file fileName

//Note3: Extracted data will be written in a format that do not require any load rule to load back into ASO or BSO. That is each row will have one dimension member from each dimension and a measure value. We will not load 0's or #Missing.

//Sample:

pln= IEssbase.Home.create(IEssbase.JAPI_VERSION);
// Sign On to the Provider
IEssDomain plnprovider = pln.signOn(s_userName, s_password, false, null, s_provider);
try {
cv = plnprovider.openCubeView("Mdx Query Extract", s_PlanningSrvName,s_plnAppName, s_plnCubeName);
System.out.println(new Timestamp(System.currentTimeMillis()) + " Connected to "+ s_PlanningSrvName +" "+
                                 s_plnAppName +" "+ s_plnCubeName);
mdxExtract(cv); // This subroutine will extract data from planning cube to flat file
               
} catch (Exception x) {
                System.out.println("Error: Extract to flatfile failed " + x.getMessage());
                x.printStackTrace();
                statusCode = FAILURE_CODE;
} finally {
                // Close cube view.
                 try {
                    if (cv != null)
                        cv.close();
                   } catch (EssException x) {
                    System.err.println("Error: " + x.getMessage());
                    x.printStackTrace();
                    statusCode = FAILURE_CODE;
                   }
}


//========================================================
// Subroutine mdxExtract
//========================================================

        private static void mdxExtract(IEssCubeView cv) throws Exception {
        boolean bDataLess = false;
        boolean bNeedCellAttributes = false;
        boolean bHideData = true;
       // String lastData = null;
        String mdxquery = null
        file=new FileWriter (filePath+fileName); 
        filePrint=new PrintWriter(file);
        System.out.println("Beginning MDX Extarct and Load...");
       

        mdxquery ="SELECT " +
                    " {MemberRange([Period].Jan,[Period].Dec)}" +" ON COLUMNS," +
                    " NONEMPTYBLOCK Crossjoin"+
                    " (Crossjoin ([Account].Levels(0).Members, [Operating Unit].Levels(0).Members),"+
                    "   (CrossJoin "+
                    "           (Crossjoin ([Scenario].Levels(0).Members,[Entity].Levels(0).Members),"+
                "                       (CrossJoin "+
                "                               (CrossJoin ({USD,CAD},[Version].Levels(0).Members),"+
                "                                       (CrossJoin "+
                "                           ( CrossJoin([Product].Levels(0).Members, [Intercompany].Levels(0).Members), "+
                "                                                               CrossJoin([Source].Levels(0).Members, [Year].Levels(0).Members)   ) ))))))"+
                " ON ROWS "+
                        " FROM [APP].[DB]";
        System.out.println(mdxquery);
        IEssOpMdxQuery op = cv.createIEssOpMdxQuery();

        op.setQuery(bDataLess, bHideData, mdxquery, bNeedCellAttributes,IEssOpMdxQuery.EEssMemberIdentifierType.NAME);
        op.setXMLAMode(false);

        op.setNeedFormattedCellValue(true);
        op.setNeedFormatString(false);
        op.setNeedMeaninglessCells(false);

        cv.performOperation(op);
        System.out.println("MDX Extarct complete...Writing in file ");
        IEssMdDataSet mddata = cv.getMdDataSet();
        IEssMdAxis[] axis = mddata.getAllAxes();

        String s_pad="Amount"// We have one more dimension in ASO cube than BSO. We are padding a value here for the member name where data needs to get loaded. 
        int totalRec=0;
        int k=0;
    
       for(int j=0;j<axis[1].getTupleCount();j++)
        for(int i=0;i<axis[0].getTupleCount();i++){
         data=null;
         IEssMdMember[] row = axis[1].getAllTupleMembers(j);
         IEssMdMember[] datacol = axis[0].getAllTupleMembers(i);
         data="\""+row[0].getName()+"\" \""+row[1].getName()+"\" \""+row[2].getName()+"\"     \""+row[3].getName()+"\" \"";
         data=data+row[4].getName()+"\" \""+row[5].getName()+"\" \""+row[6].getName()+"\" \""+s_pad+"\" \"";
         data=data+row[7].getName()+"\" \""+row[8].getName()+"\" \""+row[9].getName()+"\" \""+datacol[0].getName();

          loadCellValue(mddata,k);
                
          k++;
        if(data!=null){
                filePrint.println(data);
                totalRec++;
           }
        }//end of for loop
       
       
        filePrint.close();
        System.out.println(new Timestamp(System.currentTimeMillis()) +" Data Export file is ready... ");
        System.out.println("Total number of Data Extracted:"+k);
        System.out.println("Total number of Data Loaded:"+totalRec);
        System.out.println("------------------ ------------------------- ----------------");
    }

    private static void loadCellValue(IEssMdDataSet mddata ,int k)
            throws Exception {
       
       
                if (mddata.isMissingCell(k)) {
                        data=null;
                } else {
                    String fmtdCellTxtVal = mddata.getFormattedValue(k);
                    if (fmtdCellTxtVal != null && fmtdCellTxtVal.length() > 0) {
                        data=null;
                    } else {
                        double val = mddata.getCellValue(k);
                        data=data+"\" "+val;
                    }
                }
       
                k++;
            }
   

//========================================================
// Load ASO
//========================================================

// Easy 

String[][] error=cube.loadData(IEssOlapFileObject.TYPE_TEXT, null, IEssOlapFileObject.TYPE_TEXT,s_exportedFileName , true, null,null);
if (error!=null){
 System.out.println("There is kickout ....Check for it");
// print it in File if needed
}