Oracle Data Visualization with Advanced Analytics

In continuation to my previous blog Exploring Oracle Data Visualization Desktop in this post I am going to discuss on adding advanced Analytics with Oracle DVD ( and OBIEE 12c, Oracle BICS)

To start with first we have to enable the Advanced Analytics.

The installation is pretty simple and straight forward.

And it is simple click next next ....

It also enables us to create and use 'R' programming.

Once enabled we can use Analytics options. To test that let me create a simple bar chart.

And add a Trend line to that.

I can choose the method from Linear, Exponential and Polynomial, or can select the Confidence Intervals.

Let us try out one Cluster analysis with # of Orders vs Sales for Cities.

Next time I will proceed further on using R with DVD.

Exploring Oracle Data Visualization Desktop

Last year I came to know about this great product from Oracle, called Data Visualization Desktop (DVD). Which is similar to the VA of BICS or OBIEE 12c.

On other hand it seems Oracle's answer to Tableau and PowerBI Desktop. Anyways let's not get into the comparison between Oracle DVD, Tableau Desktop and Power BI Desktop. We will take that in a different post. The agenda for this post is to focus on Oracle DVD and explore various features of it.

To start with, to download Oracle DVD go to http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/index.html
and download 'Oracle Data Visualization 12c'.

The installation is fairly straight forward. Once installed you can run it, and the home screen will contain all the projects created / saved.

The first step will be connecting to a DB source.

There are primarily 2 options, File and Connection.

And there are quite a few options for Connection Types.

To keep it simple let me take an excel file as a source. I have taken 'Sample Order Lines'.

On the left had side you can see the Attributes and Measures, which are determined based on the data type. The right hand portion shows the sample data, and various operations can be performed on each of these columns.

You can add multiple data source together also. Let me add 'Sample States' excel also.

Once added we need to check the relationship between them, by clicking the 'Source Diagram' option just below 'Add Data Source'.

Once the data, columns, measures etc are finalized it is time to create some visualizations. And for that click on the 'Visualization' tab.

On the left side we have 3 options : 'Data Elements' (Columns), 'Analytics' and 'Visualizations'. In this case we would proceed with Visualizations. We can see the various chart types we can use.

We have the option to add a filter on the top.

I have selected a simple Bar chart. It is a simple drag and drop.

I add City in X-axis and # of Customers in Y-axis.

Furthermore I add a filter for Country = 'United States' for this chart, and Product Category in the Color, and change the chart to Stacked.

It needs to be saved as a Project.

We can play around with different visualizations, and it is pretty intuitive. The Sample Project provided with the tool can be good reference for that. A few samples from that :

Even the visualizations can be overlapped on each other also.

Next we visit the Narrate canvas, where we can include a story / description of the insight, and on the presentation mode the story / description is displayed one after another.

I have tried to explain the same using a small demo as below.

If you like this demo and interested in similar videos, you can subscribe my channel  LivingtheBI - Tilak

Alternative of Section Condition in Oracle BICS

Few days back , I was exploring Oracle BICS, and I noticed that Sections in BICS has different options than OBIEE

The Bug 25327498 has been raised to fix the issue.

But till the time Oracle add this option in BICS, is there any other alternative for Condition ?

In this blog I have tried to come up with an option for the same.

I have created a simple report and dashboard, for this POC.

Next I have created a simple prompt, with radio button.

This prompt has 2 specific values only 'hidden' and 'visible', with 'hidden' set as default selection. This prompt populates a presentation variable var_display.

I went back to to page and checked the source code, to find out the section id, which is like

I have used this section id in the javascript below :

<script type="text/javascript">
var sectionId = "d:dashboard~p:asgns923547hrc7n~s:ado2gidqeb7edpvr";
var sectionDiv = document.getElementById("Embed"+sectionId);
sectionDiv.setAttribute("@{var_display}", "true");
</script>

This section id portion needs to be changed based on the dashboards and sections. And @{var_display} is for passing the presentation variable from the prompt. Rest of the code will remain the same.

I add a text object in the same section as the existing report and put the code there and enable 'contain HTML markup' check box.

So the page looks like,

And when I run the page

With section as 'visible'.

The best part is this same piece of code can be used in OBIEE also.

Putting Horizontal and Vertical Records together in an OBIEE Report

Few days back one of friends came up with a very special requirement. He had 2 tables let's call them SALES_BUDGET_F, which stores the month wise budgeted sales, where the months are as the columns.

and SALES_ACTUAL_F, which stores the moth wise actual sales data, and this table is like a normal fact.

These 2 facts have joined by conformed dimensions Customer and Product. For the ease of our purpose in this case I have single dimension and let me call it CUSTOMER_PRODUCT_D. However having a single or multiple dimension will not cause any difference.

The objective is to show both the data together so that we can compare the Budget vs Actual.

Now before we proceed, I would like to mention this can be achieved through multiple ways, one of them can be creating an Opaque View in the  RPD or Materialized View. However in this case I will try to achieve the same through OBIEE.

In the logical layer I create a single fact with 2 LTS one for each of the physical facts. And the conformed dimensions are also mapped.

In the Fact - Sales, Year is mapped from both the facts. Actual Sales is computed from SALES_ACTUAL_F.

Month wise Budgeted Sales columns are calculated from month wise columns from the SALES_BUDGET_F. Like Jan Budgeted Sales = SALES_BUDGET_F.JAN

Now I start creating the report. First just to test if my tables are working fine, I create a simple report with Month ID (hard coded as 1 for Jan. I will be using it later for sorting) and Jan Budgeted Sales.

And it is working as desired.

Next I add columns for Month Name and Actual Sales (hard coded as 0)

I repeat it 11 more times in form of union for 12 months. And 1 additional criteria for Actual Sales, with Budgeted Sales column hard coded as 0.

Now let's check the result.

We need to get rid of these duplicate rows, and for that I use a Pivot view, with aggregation added on the measures.

And the net output looks like,

I put it in a dashboard with Customer, Product and Year prompt.

Before I conclude I would like to mention again that, this might be achieved through other ways too.