Take your OBIEE graphs and charts to new level with D3 (Part 2)

Collapsible Tree

Inspiration: https://bl.ocks.org/mbostock/4339083

Please read my first blog here for set up needed to start your OBIEE D3 visualization.

In this blog you will see,  how you can have interactive Essbase hierarchy(or any other hierarchy) displayed through OBIEE like above.

We will do it with hierarchical data added as parent / child in analysis and then D3 will do the rest. We will use "union all" in the analysis to achieve this.

The analysis will look like this ...

sort order column will make sure data is sorted by generation. We will put simple 'A', 'B','C' to identify each generation.

Generation 1...

Generation 2 ...

 Generation 3...

And so on ...

We also need to make sure that Leaf nodes are not present in parent column.... to do that we will add filter to each generation query. Assuming at least root node exists for the tree to built. 

So Filter will look like ....  

Gen 1 filter

none

Gen 2 Filter

Gen 3 Filter

And so on. ....

The above steps will provide you data in the narrative view that you can use in your D3 code.

Lets look at the D3 code... 

Prefix

<!DOCTYPE html>

<meta charset="utf-8">

<style>

    

.node {

  cursor: pointer;

}

.node circle {

  fill: #fff;

  stroke: steelblue;

  stroke-width: 1.5px;

}

.node text {

  font: 10px sans-serif;

}

.link {

  fill: none;

  stroke: #ccc;

  stroke-width: 1.5px;

}

</style>

<svg width="1200" height="1500"></svg>

<script src="/analyticsRes/libraries/d3.v3.min.js"></script>

<script>

var data=[];

Narrative

data.push({name:"@2",parent:"@1"});

Postfix

var dataMap = data.reduce(function(map, node) {

 map[node.name] = node;

 return map;

}, {});

var treeData = [];

data.forEach(function(node) {

 // add to parent

 var parent = dataMap[node.parent];

 if (parent) {

  // create child array if it doesn't exist

  (parent.children || (parent.children = []))

   // add node to child array

   .push(node);

 } else {

  // parent is null or missing

  treeData.push(node);

 }

});

var margin = {top: 20, right: 300, bottom: 20, left: 300},

    width = 1500 - margin.right - margin.left,

    height = 900 - margin.top - margin.bottom;

var i = 0,

    duration = 750,

    root;

var tree = d3.layout.tree()

    .size([height, width]);

var diagonal = d3.svg.diagonal()

    .projection(function(d) { return [d.y, d.x]; });

var svg = d3.select("svg")

    .attr("width", width + margin.right + margin.left)

    .attr("height", height + margin.top + margin.bottom)

    .append("g")

    .attr("transform", "translate(" + margin.left + "," + margin.top + ")");

  root = treeData[0];

  root.x0 = height / 2;

  root.y0 = 0;

  function collapse(d) {

    if (d.children) {

      d._children = d.children;

      d._children.forEach(collapse);

      d.children = null;

    }

  }

  root.children.forEach(collapse);

  update(root);

d3.select(self.frameElement).style("height", "800px");

function update(source) {

  // Compute the new tree layout.

  var nodes = tree.nodes(root).reverse(),

      links = tree.links(nodes);

  // Normalize for fixed-depth.

  nodes.forEach(function(d) { d.y = d.depth * 180; });

  // Update the nodes…

  var node = svg.selectAll("g.node")

      .data(nodes, function(d) { return d.id || (d.id = ++i); });

  // Enter any new nodes at the parent's previous position.

  var nodeEnter = node.enter().append("g")

      .attr("class", "node")

      .attr("transform", function(d) { return "translate(" + source.y0 + "," + source.x0 + ")"; })

      .on("click", click);

  nodeEnter.append("circle")

      .attr("r", 1e-6)

      .style("fill", function(d) { return d._children ? "lightsteelblue" : "#fff"; });

  nodeEnter.append("text")

      .attr("x", function(d) { return d.children || d._children ? -10 : 10; })

      .attr("dy", ".35em")

      .attr("text-anchor", function(d) { return d.children || d._children ? "end" : "start"; })

      .text(function(d) { return d.name; })

      .style("fill-opacity", 1e-6);

  // Transition nodes to their new position.

  var nodeUpdate = node.transition()

      .duration(duration)

      .attr("transform", function(d) { return "translate(" + d.y + "," + d.x + ")"; });

  nodeUpdate.select("circle")

      .attr("r", 4.5)

      .style("fill", function(d) { return d._children ? "lightsteelblue" : "#fff"; });

  nodeUpdate.select("text")

      .style("fill-opacity", 1);

  // Transition exiting nodes to the parent's new position.

  var nodeExit = node.exit().transition()

      .duration(duration)

      .attr("transform", function(d) { return "translate(" + source.y + "," + source.x + ")"; })

      .remove();

  nodeExit.select("circle")

      .attr("r", 1e-6);

  nodeExit.select("text")

      .style("fill-opacity", 1e-6);

  // Update the links…

  var link = svg.selectAll("path.link")

      .data(links, function(d) { return d.target.id; });

  // Enter any new links at the parent's previous position.

  link.enter().insert("path", "g")

      .attr("class", "link")

      .attr("d", function(d) {

        var o = {x: source.x0, y: source.y0};

        return diagonal({source: o, target: o});

      });

  // Transition links to their new position.

  link.transition()

      .duration(duration)

      .attr("d", diagonal);

  // Transition exiting nodes to the parent's new position.

  link.exit().transition()

      .duration(duration)

      .attr("d", function(d) {

        var o = {x: source.x, y: source.y};

        return diagonal({source: o, target: o});

      })

      .remove();

  // Stash the old positions for transition.

  nodes.forEach(function(d) {

    d.x0 = d.x;

    d.y0 = d.y;

  });

}

// Toggle children on click.

function click(d) {

  if (d.children) {

    d._children = d.children;

    d.children = null;

  } else {

    d.children = d._children;

    d._children = null;

  }

  update(d);

}

</script>

Move data "On-Save" from Planning BSO to Reporting ASO (Part 1)

Moving data from BSO planning application to ASO reporting application ? 

Want to have this data movement really fast ? 

Want to move that data as soon as users save web forms ? 

     

      Well, you can do that in many different ways. The time it takes to move the data between 2 cubes depends mostly on how fast you can extract level 0 data from the Planning BSO cube. 

    To do that I have used MDX export from planning BSO cube with 'NONEMPTYBLOCK‘ keyword. I have tested this against 3 different BSO planning cubes and found that MDX with 'NONEMPTYBLOCK' keyword export level 0 data faster than data export calculation script. But I imagine, it won't be the case always. So, test test and test before you select your data export process. 

We have built this solution last year using Essbase Java API and CDF. I agree this is not really out of the box solution. You need to use bit of Essbase Java API to format MDX output to get this to work.  But if you are in Essbase version 11.1.2.4, this can be a solution where you don't have to write any Java code. I will write about that in my next post.

Here is the solution steps:

    

1. Get your level0 export MDX query ready. Don't forget to   add 'NONEMPTYBLOCK‘  keyword. 
2. Now you have to create Java Custom defined function(CDF). With in the CDF use Essbase Java API to parse and load MDX output to ASO database. If you need more details, I have blogged earlier about this here. 
3. Run CDF from a calculation script in the planning cube using RUNJAVA command.
   
4. Add this calculation script in your web-form to run it on save.
   
5. Success of this 'On-Save' load depends on speed. You don't want your user to watch their frozen web-form for 5 min while data is getting transferred. Try to have this data movement more targeted by passing POV information to your CDF from planning web-form via calculation script. By doing that you can minimize amount of data movement and restrict it only to POV of the web-form. 

 Demo time ... 

Truncating DATAEXPORT target relational table with CDF

First of all, I am a big fan of Essbase DATAEXPORT calc script to relational database. It provides flexible way of communication between cubes. You can export data to a relational table then manipulate through SQL load rule and load it back.  Certain type of mapping / data management is much easier in SQL. Moreover entire script can be called from Maxl. 

    Well, can it be? One problem though, Essbase DATAEXPORT calc does not allow you to truncate / delete from the underline table. Bummer!! How hard it could be for oracle to provide that function? It would have save my time of writing this blog and yours reading it. 

We will solve this issue with CDF (custom defined function). If you have not created your first CDF yet, then this is a good place to start. Once you have that CDF ready you can use it in your calc script like this…

Steps to install CDF

1. Install JDK. In my case I have JDK 1.7 
2. Install Eclipse (Java IDE). 
3. In Eclipse create a new Java project. (File->New->Java Project). I have named it CDF.
4. Expand the project and right click on src folder, create new -> package. I named it  com.williams.cdf
5. Right click on com.williams.cdf and select Build path -> Configure Build Path. Click on the Libraries tab then add 2 External JARs essbase.jar and odbc14.jar. File essbase.jar is available in Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseServer/java directory. you can download odbc14.jar from internet. It is required to connect to your underline oracle database. If your relational database is not oracle then you need corresponding java jar file to connect with that database.
   

Now the set up is done,  it is time for coding.

 6. Right click on package com.williams.cdf and create new -> Class.  I named it relationalDDL. Once done, it should look like this...

Here is the code that I have for relationalDDL.java ...

package com.williams.cdf;

  

import java.sql.*;

import oracle.jdbc.pool.OracleDataSource;

public class relationalDDL {

    static Connection sqlcon=null;

    static Statement sqlstmt = null;

    static OracleDataSource ods = null;

  public static void main(com.hyperion.essbase.calculator.Context ctx,String args[]) {         

         truncateTable(args[0],args[1],args[2],args[3]); 

    }

   private static void openConnection( String URL, String userid, String passwd) {

                    try {

                                                ods = new OracleDataSource();

                                } catch (SQLException e1) {

                                                System.out.println("New Connection Object creation Failed: " + e1.getMessage());

                                }

                ods.setURL(URL);

                ods.setUser(userid);

                ods.setPassword(passwd);

                                try {

                                                sqlcon = ods.getConnection();

                                                System.out.println("Oracle database connection established to "+ URL);

                                } catch (SQLException e) {

                                                System.out.println("Connection Failed to "+ URL);

                                                System.out.println("SQLException: " + e.getMessage());

                                }              

           

    }

    private static void closeConnection(Connection oraConn){

                if(oraConn != null)

                                {

                                                try

                                                {

                                                                oraConn.close();

                                                }

                                                catch (SQLException x)

                                                {

                                                                System.out.println("SQLException: " + x.getMessage());

                                                }

                                }

    }

    public static void truncateTable(String table, String URL, String userid, String passwd) {

     

                System.out.println("CDF Started");

                openConnection( URL,userid, passwd);

                try {

                                                sqlstmt=sqlcon.createStatement();

                                                sqlstmt.execute("TRUNCATE TABLE "+ table);

                                                System.out.println("Trancated table: "+table);

                                } catch (SQLException e) {

                                                System.out.println("SQLException: " + e.getMessage());

                                }

                closeConnection(sqlcon);

    }

}

If you have everything set up properly then you should be able to save the code above without any error in eclipse. Once saved right click on relationalDDL.java in package explorer to create a run configuration. 

We will configure something like shown below but we will not run it. Just hit apply and close.

Once configuration is saved, right click on relationalDDL.java in package explorer and click Export. Select Runnable JAR file

Provide the path where you want to export in your local machine. Ignore any warning that says main class not found. I named the jar file as DDL.jar. 

copy this jar file to your EPMSystem11R1/products/Essbase/EssbaseServer/java/udf folder.

update udf.policy file in EPMSystem11R1/products/Essbase/EssbaseServer/java

add following lines.

// Grant all permissions to CDF DDL.jar 

grant codeBase "file:${essbase.java.home}/udf/DDL.jar" {

 permission java.security.AllPermission; 

};

Now as this CDF is ready to  run, invoke it from calc script with RUNJAVA command. For any error check Essbase.log.

ETL inside ORACLE EPM workspace

There are quite a few options available when it comes to choice of an ETL tool for Oracle Hyperion Essbase and Planning data load. In this blog, I will discuss how you can select EPM workspace for the same. This is an excellent  platform to use to launch jobs that has batch / shell scripts wrapped over MaxL or SQL.  But Wait! Why should I use workspace ? Why not just running batch scripts or shell scripts in the server? Because in workspace you will have audit trail with ability to restrict access for users. Also one can schedule job, check run history, debug logs etc.(yeah yeah...we can do that in server too...) Finally, better user experience!

To set it up in workspace, first you need to make sure MaxL, SQL , batch files are running properly in workspace server. i.e. path related to ARBORPATH, ESSBASEPATH etc set in the server and Essbase is installed.

1. Create a generic Application or job launcher by Navigate -> Administer->Report and Analysis ->Generic job Applications. Then click '+' sign to create a new job.  Lets call it Run_CMD as it will run our batch job. Product host is your workspace server. Have the command template set up like below. To read more about the command template setup click the help button in the popup window. Lastly provide the full path to your cmd.exe in windows as executable. click ok.

2. Now we need to provide the batch job information that we want to run with this generic job launcher. To make it more interesting, lets consider our batch job uses both Maxl and SQL in it. Which will be supplied to the script as an input file. To do this we can put our .sql and .mxl files in the server and use that path in the batch script. But by doing that we will not be able to check those files from workspace. To make it more debug friendly we will import these files.

Lets see the next steps....

3. Navigate ->Explore. Create a folder where you want to store these jobs. My folder structure looks like this ...I redirect all my outputs under the log folder.

4. Now you have to import your batch file. I had batch file called MyBatchFile.bat that looks like ...

Note: that I do not have any path specified for files InputSQL.sql and InputMaxL.mxl. We will import them into workspace. 

5. Select File->Import->File as Job

Check Import as Generic Job and click next

Select Run_CMD from drop down list and click next

Click 'Go'

Add InputSQL.sql and InputMaxL.mxl files from your PC and click ok.

At this point you may get an error if you don't have mime type set up for .sql and .mxl file type. To do that go to Navigate -> Administer->Report and Analysis -MIME Types. Click go at the bottom of the page and add .sql and .mxl type

Finally run the job by double clicking on it or by using Run job option. Use Logs folder as your output directory.

OBIEE: Generate Level 0 members dynamically for an Essbase upper level member

Occasionally you might have come across a requirement where you needed all the list of level zero members under a selected member. Specially it is helpful when you need to create a detailed transaction report for an upper level entity or cost center. For example, If you can generate level 0 members under certain roll up , you would be able to use it against your transaction table. The solution can be achieved different ways. Let us discuss few of them. 

SQL Solution: "Start With connect by" With function Connect_by_isleaf

If your hierarchical back-end data is flatten , then your task is much easier. Just join it with transaction table or fact table to get the details. But if your metadata is in a table in parent child format then you need to use Oracle  "start with connect by" to get your level 0 data. The query could look like this:

Select Member_name

  From (    Select Member_name, Connect_by_isleaf Is_leaf

              From Period_dimension

        Start With Member_name = :Member_name

        Connect By Prior Member_name = Parent)

 Where Is_leaf = 1

Clearly, for this to work you need to have your database table in sync with Essbase hierarchy. Which always may not be the case.

OBIEE Solution:

In OBIEE, one can actually connect to Essbase to get the metadata information and then pass it to relational database  with the help of OBIEE feature "is beased on another Analysis"

Here in the screenshot EntityLev0 is an analysis that uses presentation layer variable. That variable will pass our upper level member to EntityLev0 analysis and will produce all level 0 members below it. 

Now how to generate such report ? 

Solution 1: Generating Level 0 members in OBIEE dynamically with MDX and 'Evaluate' Function. 

The following will generate all the level 0 members of Entity dimension. Notice how you can manipulate Evaluate function by commenting out parameter requirement. 

Now you can easily make it parameterized by using a presentation layer variable. like 

EVALUATE('Descendants([@{PV_Entity}], Levels([Entity],0))/*%1*/' ,"Entity"."Gen1,Entity")

But there is a problem. This will give you member alias not member name in our current set up.

Unless you set your cube to display member_name like below.

Is there a way to get member name not alias when display column is set to display Alias? Most likely not with Evaluate. Mainly because member name is an intrinsic property of a member. Evaluate works with MDX functions and there is no mdx function available to get the member property. I will be happy to be proved otherwise. 

So, if you need list of level 0 member name , next one is the solution that you are looking at....

Solution 2: Dynamically get list of level 0 members for any higher level member with MEMBER_UNIQUE_NAME

 Step 1) Update RPD to get a flatten OBIEE column with essbase member name (not default alias)

I have wrote about this in my last blog. Read it here

Step 2) Create an analysis which would look like this: 

* "Period" above is the flatten member name available for all members. 

** {YearTotal} is default value. I normally put my generation 2 member as default. It helps to test the report. If you don't put a default value it fails in the result section of the analysis but works fine in dashboard. 

Clearly column 1 above will provide you level 0 members always based on what you have in your presentation variable. 

So, Finally use it in your transaction details report .....