The newest release of Oracle Business Intelligence 11.1.1.7 shows Oracle's continued efforts in trying to integrate its Oracle Business Intelligence Platform with big data technologies such as Hadoop and Hive. Specifically, I'm talking about OBIEE 11g's ability to integrate with a Hadoop Data source.
What is Hadoop?
Hadoop is a framework that enables data to be distributed amongst many servers (nodes), commonly referred to as a 'distributed file system'. The data is not stored in a single database, rather it is spread across multiple clusters.
How does Hadoop process data stored in multiple nodes?
Hadoop uses a programming model called 'MapReduce' for parallel processing across multiple nodes. At a high level this is comprised of two steps:
Map step
The map step takes the data, divides it into smaller sets of data and distributes the result to worker nodes
Reduce step
The reduce step collects the data from all of the worker nodes and aggregates it into a single 'output'
What is Hive?
MapReduce functions are generally written in Java and generally require someone with deep knowledge in both Hadoop and MapReduce. The guys over at facebook created a technology called 'Hive' which is a data warehouse infrastructure that sits on top of Hadoop. More simply, Hive does the 'heavy lifting' of creating the MapReduce functions. In order to query a Hadoop distributed file system, instead of having to write MapReduce code, you generate sql-style code in a hive language called 'HQL'
Why does this matter in the Oracle Business Intelligence / Analytics space?
The analytics space is experiencing a shift in both technology and function. Traditional BI projects required a 'data warehouse' to store data in a series of star schemas (denormalized models) for quick query generation and data retrieval. The development and support of the data warehouse is achieved through a team of ETL developers whose main focus is to create the mappings that perform the data transformation from the source to the target.
Unless the functional requirements are clearly understood during this phase, value is usually lost in the data transformation and the potential to eliminate relevant data is certainly possible.
Using OBIEE 11g's Hadoop integration via a Hive ODBC, OBIEE can directly query distributed file systems via Hive. What does this mean? The potential now exists to eliminate or reduce the need for ETL as we now have the ability to directly query gigantic file systems.
The saving grace to ETL developers is that a need still exists for someone to create the HQL functions that populate the 'tables' that OBIEE uses. Ultimately, it could be a change in how ETL is developed.
Once you've created the ODBC Data Source Connection, you can configure the Driver set up under the 'General' tab:
Step 3: Configure Database Connection
Moving into the repository, you're going to create a new database connection like you would for any data source in the physical layer. Note that you need to specify the database type as 'Apache Hadoop' (this is important!).
Step 4: Create Connection Pool
Within the Apache Hadoop database connection you just created in step 3, create a data source with a call interface as type 'ODBC 2.0' or 'ODBC 3.5'. The data source call interface should not be 'Apache Hadoop' (you've already specified the database as type as Apache Hadoop!). If you specify the data source call interface as 'Apache Hadoop' you will receive the following error:
Your connection pool should be similar to the following:
You should now be able to import your tables and columns just like any other connection pool. The BI Server will generate normal SQL statements as if it were querying a traditional Oracle database, but the Hive ODBC driver in turn converts that to HQL which is used to execute mapreduce functions to query the Hadoop distributed file system across multiple nodes.
One of the great features of Oracle's Business Intellgience 11g foundation is the ability to integrate external applications via an API call or through the use of javascript libraries. In a previous article I discussed how to utilize javascript functions using OBIEE 11g's native UserScripts.js. Today we're going to expand on this functionality by integrating third party data visualization scripts. One popular javascript library used for data manipulation is 'Data-Driven Documents' . This open source scripting library gives users the ability to manipulate data using methods not available in OBIEE 11g. Kevin McGinley first wrote about this in 2012 and the guys over at Rittman Mead recently posted an overview of D3 / OBIEE integration. Below we're going to cover all the steps required to implement a D3 visualization technique.
Before we get started, you can view all of the D3 visualization methods at their github. In the example below we're going to use airline data to and D3's Calendar View to visualize average flight delays. You will need OBIEE 11.1.1.6.2 or higher (this example uses OBIEE 11.1.1.7.0) and IE 9+.
Step 0: Create an Answers Report
This report should contain a year dimension, a date dimension and an aggregate fact column. In the airline example I've selected 'Date', 'Year' and 'Average Departure Delay'. Take note of the column order as you will have to reference the column number in a narrative.
Step 1: Download the D3 Javascript Library from github
This is going to download a 'd3-master.zip' file that contains all of the javascript libraries needed for integration. You will unzip all of these files into OBIEE 11g's analytics ear deployment under Weblogic's Domain Home located at :
The Calendar view's javascript code is basically one script, with one function and one css file. These 'chunks of code' are all stored in the index.html using the example located on github, but in order for this view to play nice with OBIEE 11g, we're going to need to dissect components of the code into isolated narratives and css files. The first step is to take the css code:
#chart {
font: 10px sans-serif;
shape-rendering: crispEdges;
}
.day {
fill: #fff;
stroke: #ccc;
}
.month {
fill: none;
stroke: #000;
stroke-width: 2px;
}
and save it to its own css file (calendar.css) located at:
user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\b_mozilla\common\d3\examples\calendar\calendar.css (you will need to create the directory as this doesn't exist)
Step 3: Create an Answers Narrative to Execute the Javascript Library
Now that we've laid the groundwork for calling the D3 library, the next step is to integrate the Calendar View code into an Answers narrative.
First create the script headers and link type to call the javascript library. This code will be stored in the pre-fix of the narrative:
Next we're going to take the calendar view code and copy the entire code block from the start of the width variable delcaration to the end of the call to the selectAll function. Your code should look similar to:
Although this code does most of the heavily lifting and can be left unmodified, there are specific lines that can be changed and updated dynamically via the use of presentation variables.
Color Thresholds:
The color variable specifies the thresholds for red/yellow/green. In this case I deem the min and max ranges of an airline delay to be between 5 minutes and 30 minutes:
The height/width/cell size can be adjustable by changing the hardcoded values to presentation variables such as:
@{Width}
@{Height}
@{CellSize}
Date Formatting:
The 'day' variable responsible for date formatting:
var day = d3.time.format("%w"),
week = d3.time.format("%U"),
percent = d3.format(".1%"),
format = d3.time.format("%Y-%m-%d");
Requires that the format of the date be specified. The Calendar View script by default uses a 'YYYY-MM-DD' format. If your OBIEE data is a MM-YY-DD format or has a timestamp, you will need to modify the column data format to the following:
Modifying the Date Range:
The Calendar View code by default hard codes a date range of 1990 to 2011. You will most likely need to modify these values for your data set create a presentation variable that allows the users to change the date range dynamically:
var svg = d3.select("body").selectAll("svg")
.data(d3.range(1990, 2011))
Could be modified to:
var svg = d3.select("#my_chart").selectAll("svg")
.data(d3.range(year_range1, year_range2))
In the upcoming steps I will show how these variables can be called.
Step 4: Populate the Narrative and Post-Fix
In the narrative you will need to specify the Date and Metric you want to pass to the javascript function using the corresponding column number (see step 0 if you forgot!)
The Post-Fix should contain the remainder of the Calendar View code. This can remain unmodified:
Step 5: Create a Second Narrative for the Date Range
This narrative is optional, but assuming you want to give the user the ability to modify the date range, you would take the variables you referenced in the 'Modifying the Date Range' section (in my case year_range1 and year_range2) and set both of them equal to two presentation variables like below:
Step 6: View Narratives in Answers
Adding both narratives to a single view, your end result should look similar to:
This guide barely scratches the surface of D3-OBIEE integration but serves as a great example of how 3rd party APIs and javascript libraries can be integrated into OBIEE 11g. I encourage all BI Architects to look through the entire D3 library and see how D3 can be integrated into their current engagement.
Step 2: Create a Hive ODBC Connection via the ODBC Data Source Administrator
