Wednesday, December 17, 2014

OBIEE VISUALIZATION - D3J Charts Part 2

In this blog entry, we will take the D3 to the next level where I will showcase its implementation in BOM(bill of materials).

what is BOM?
The bill of materials (BOM) is a list of the parts or components that are required to build a product. In simpler terms, it is a hierarchical structure having parent/child relationship where the end item is the final product and intermediate items are its components that are used to build this end product.

What we are implementing?
In this entry, we will try to build parent/child relationship irrespective of the real BOM so as to get the idea what in future we can explore from this relationship.
The example demonstrated is taken from here, but with slight variance, instead using predefined parent/child heirarchy and using table as the view, we will directly implement D3 graph.
Goal is to implement employee/manager hierarchy as shown below:

To create such hierarchy, we follow the steps here:

Step 1:
Create the table in warehouse schema with the following script:

Step 2:


Import the table XW_EMPL_D and create two aliases in physical layer of OBIEE with the name Hier_Employee and Hier Employees Manager as shown in the figure above.
Step 3:


Create the physical join between employee id and manager id of two aliases as shown above.

Step 4:
Pull the tables in BMM layer.

Step 5:



Ensure the logical join is created as show above and take care of the type of join i.e. Left Outer as we want all the employees name irrespective of whether they have manager of not.

Step 6:
Drag the two tables in Presentation layer, save the rpd and host it on the server.
Now, create analysis from narrative veiw using employee name from both the tables.Employee name will serve as employee name and manager name from two tables respectively.
Step 7:
Add D3 javascript code in narrative view, which is taken and modified from here, as per our requirements as given below.

Prefix:

Narrative:
Postfix:


The report looks like the one above. One can add other visualizations using this feature.
Read More »

Tuesday, December 16, 2014

OBIEE VISUALIZATION - D3J Charts Part1

In this entry, I will be showing you the process of embedding a D3 visualization in OBIEE 11g. I've had the pleasure of working on this technology where i have to display some kind of BOM hierarchy using the D3 charts. As a part of POC on this one, i started on simple virtualization taking examples from various websites. I will be sticking on OBIEE development rather that D3, so without further adieu lets start.

What is D3?
D3.js is a JavaScript library for manipulating documents based on data. D3 helps you bring data to life using HTML, SVG and CSS. D3’s emphasis on web standards gives you the full capabilities of modern browsers without tying yourself to a proprietary framework, combining powerful visualization components and a data-driven approach to DOM manipulation.
reference: d3js.org

Embedding Concept
OBIEE has the concept of narrative view in which you can embed your own javascript and view the end results as per your design definition.

Lets' take a simple example given at rittman's blog to develop similar application.

Target:
Based on the total sales for each customer each month, customers are placed in the scoring groups

  • Group 1: Customers for which revenue exceeded £10,000
  • Group 2: Customers for which revenue exceeded £5,000
  • Group 3: Customers for which revenue exceeded £2,000
  • Group 4: Customers for which revenue exceeded £0
At the end of each month we want to compare each customers current scoring group with the scoring group they occupied the previous month. It’s the movement of customers between groups each month that we are interested in analysing.


Lets' start by creating the above model.

Step 1:
Lets start by creating the appropriate table and data in database;






Step 2:

Import the table in Physical Layer

Step 3:
Create the Business Model and drag the CUSTOMER table twice from physical layer and name one as Customer-T. Drop all the columns except Cust-ID which will serve us in creating a join in BMM.

Step 4:

Define the relationship as defined above in BMM layer.

Step 5:
Pull the 2 tables in Presentation layer, save the rpd and host it.

Step 6:
Create the report using Customer, Group_last_month, Group_This_Month and Movement from the Customer Table.
Now to create the D3 view we have to add the narrative view in obiee. After adding, specify:
Prefix:
Narrative:
Postfix:
The report looks like the one above. One can add other visualizations using this feature.
Read More »