Using Apache Drill as a JDBC data source in OBIEE 12c

Apache Drill is an open-source SQL query engine for Big Data exploration. Robin Moffatt from Rittman Mead has written two excellent blog posts about Apache Drill:

• Introduction to Apache Drill
• Using Apache Drill with OBIEE 12c

I will be experimenting with an approach based on a new feature of OBIEE 12c: the ability to have a JDBC/JNDI based data source in the repository; unfortunately documentation about this feature is limited. The goal of this post is to document the main steps for connecting to Apache Drill via the new JDBC/JNDI interface in OBIEE 12c. One of the advantages of JDBC over ODBC is that there is no need to install and configure client drivers a step that can be tricky especially in *nix systems.
Following are the main steps to be completed for the JDBC/JNDI based data source configuration:

• Copy the Apache Drill JDBC library to the WebLogic class path
• Create a JDNI data source in WebLogic
• Load the Java Data source in the OBIEE Administration Tool
• Create a database and connection pool for the new data source
• Import the data source metadata and start building stuff!

Note that for this experiment, I will be using the MapR sandbox already configured with Apache Drill among other tools.

Apache Drill JDBC library file

Once you download/unzip the Apache Drill package file, the JDBC library file to be copied is:

[Apache Drill]\jars\jdbc-driver\drill-jdbc-all-[version].jar

Copy the file to the OBIEE 12c domain directory, lib subfolder

[ORACLE_HOME]\user_projects\domains\bi\lib

and restart the managed server.
Start Drill in cluster mode in the MapR sandbox

Create a data source in WebLogic

• Log into the WebLogic console and create a new data source

Lock & Edit the configuration then create a new Data Source

Specify a name for the new JDBC data source, a JNDI name, set the database type drop-down to “Other” then click Next/Next. In later steps, we will provide connection details for the database.

Next

Next

Set the values for Driver Class Name field and the JDBC URL
Driver Class Name: org.apache.drill.jdbc.Driver
URL: jdbc:drill:zk=[hostname and port]/drill/cluster-id
where [hostname and port] is the ZooKeeper quorum hostname and port (5181 in this example)
to find out the cluster-id, take a look at the configuration file for running Apache Drill in cluster mode: drill-override.conf (see screenshot below). In my current setup, I am pointing to the MapR Apache Drill sandbox.


Test the connection to Drill

Set the schema to connect to by default in the “System Properties” section

Click Next then select the target to deploy the new JDBC data source to

Finish.

Load the Java Data source in the OBIEE Administration Tool

Open the OBIEE Administration online then select Load Java Datasources

Specify the WebLogic hostname, the managed server port, user and password

If the Java data source is loaded successfully

Create a database and connection pool for the new data source

Now that the JNDI data source has been successfully loaded into the Administration Tool, we can create a new database and a connection pool in the OBIEE physical layer.

We now need to update of the database feature IDENTIFIER_QUOTE_CHAR from the default double quotation mark ( “ ) to a back tick ( `) as required by Apache Drill.

Now the exciting part, let’s create a new connection pool:

• Call Interface: JDBC(JNDI)
• Data source name: http://[Hostname and Managed Server Port]/obiee/javads/[JNDI name] for example: http://localhost:9502/obiee/javads/jndi/drill/

One way to make that your data source is accessible is to open the URL in your web browser

Import the Drill data source Metadata

Next

The JNDI data source created in WebLogic points to the “hive” schema.

A new physical catalog “DRILL” is created along with a physical schema “hive.default”, the schema contains one table “orders”.

Let’s view the data

Create a simple “Business Model” and analysis et voila!

Being able to import a JDBC data source into the OBIEE Administration Tool is a very powerful capability that will make it possible to import new types of data sources into OBIEE and perform analyses, I will be exploring new JDBC based data sources in the near future.
Fiston
a+

OBIEE 12c is GA!

Oracle Endeca Information Discovery (OEID) 3.1 quick install

As you may know, Oracle Endeca Information Discovery OEID was released November 7th, I decided to download the product and go through the installation. The great news is that the installation process has been greatly improved compared to OEID 3.0. With OEID 3.1, the overall installation can be scripted using an “orchestration script” to install and configure

o Weblogic 10.3.6

o Oracle ADF runtime 11.1.1.6

o Endeca Server 7.6

o Endeca Studio 3.1

o Endeca Provisioning

The purpose of this post is to document at a high level the steps while I am going through the installation using the provided orchestration script. My OS is Microsoft Windows Server 2008 R2. This post will cover the software staging, scripts modifications and install. A future post will cover the OEID Integrator installation and sample data load and new features.
The software package is available from Oracle Software Delivery Cloud. 
1) Download the following media packages:

Oracle Endeca Server (7.6.0) media pack folder

- V40519-01: Oracle Endeca Server (7.6)

- V40521-01: Oracle Endeca Server (7.6) Sample data

- V29856-01: Oracle Weblogic 10.3.6

- V29673-01: Oracle ADF Runtime 11g Patch set 5

Oracle Endeca Information Discovery Studio (3.1.0)

- V40547
You’ll notice also the media pack V40522-01 (IKM SQL to Endeca Server for integrating with ODI) which is pretty exciting.

JDK version 6 is also a pre-requisite for the OEID 3.1 installation, I will be using JDK1.6.0_43.
2)  Create a folder that will hold the installation scripts and the binary installation files, for example C:\stage
3)  Unzip V40547—> EID_3.1_Studio_InstallWin.zip—>eidOrch into c:\stage
The eidOrch folder contains 2 subfolders:

- installers: actual software binaries for Endeca Studio and Endeca Provisioning

- orchScripts: orchestration script for Endeca Studio and Endeca Provisoning

4) Unzip V40519-01 into C:\stage to get

- endecaserver.zip

- windows_OC.zip

5) Unzip windows_OC.zip and move the resulting subfolder “windows” to
C:\stage\eidOrch\orchScripts\
6) Rename the folder that you just moved ES_windows_OC
7) Move the endecaserver.zip file to c:\stage\eidOrch\installers
8) Rename V29673-01 to ofm_appdev_generic_11.1.1.6.0_disk1_1of1.zip, that’s the name expected by the installation script and move it to C:\stage\eidOrch\installers. Note that you can download ofm_appdev_generic_11.1.1.6.0_disk1_1of1.zip from OTN.
9) Unzip the V29856-01 media pack, this is the Weblogic 10.3.6 jar file and move it to c:\stage\eidOrch\installers
10) Folder structure before installation

C:\stage\eidOrch\Installers\

C:\stage\eidOrch\orchScripts\

11) Run_EID_install.bat is the main script, it will call the following scripts

- eidOrch\orchScripts\ES_Windows_OC\run_endeca_server_install.bat (Weblogic, ADF runtime, Endeca server installation)

- eidOrch\orchScripts\Studio_windows_OC\run_endecastudio_install.batm(Endeca studio installation)

- eidOrch\orchScripts\PS_windows_OC\run_endecaprovisioning_install.bat (Endeca Provisioning)

The installations scripts will get the predefined configuration parameters from property files :

- eidOrch\orchScripts\config_EID_windows.prop

- eidOrch\orchScripts\ES_Windows_OC\config_win.prop

- eidOrch\orchScripts\Studio_windows_OC\config_Studio_win.prop

- eidOrch\orchScripts\PS_windows_OC\config_PS_win.prop

Following are screenshots property files, note that JAVA_HOME, ORACLE_HOME and INSTALLER_LOCATION are the main parameters that have to be adjusted based on your local settings. 
You also have the option to set additional parameters such as START_MODE, USE_SSL etc… One notable parameter for the Endeca_Server, Endeca_Studio and Endeca Provisioning product property files is INSTALL_MODE. This parameters specifies whether we’re performing an install only or an install followed by configuration. There are additional values to specify that there is an existing middleware installation. Refer to the documentation for more details. Note that this install will be NON-SSL, if using SSL ports, there are additional setting to be changed on the property files.

config_EID_windows.prop

config_win.prop

config_Studio_win.prop

config_PS_win.prop

Open a DOS command prompt as Administrator and cd into c:\oracle\eidOrch\orchScripts\
Then launch the main orchestration script:
run_EID_install config_EID_windows.prop --temp-directory c:\temp
and watch the installation in progress. You will be prompted to enter parameters such Admin usernames/passwords (you can potentially modify the scripts to automate this step).
Caution:  if you're using a disk drive (for the temporary directory) that is different from the one that holds your scripts, the installation script will fail. In my case I had to modify the installation scripts as follows: search for the line
cd %TMPFOLDER%\bin
enter a new line below it with
C:
This has to be done for run_endecaprovisioning_install.bat, run_endecastudio_install.bat and the endecaserver_install.bat




At the end, you’ll have the Endeca Server, Endeca Studio, Endeca Provisioning fully installed/configured and up and running in no time!
A much smoother install process for OEID 3.1 indeed!
p.s OEID software is now available for download on OTN

update: Several users are experiencing an issue when they try to invoke web services to import data (OES-000169: Could not connect to Endeca Server at 10:7001). The work around is to make sure that the Windows hosts has an entry with IP address, machine name.domain example:
192.168.56.1  endeca.demo.org endeca

a+
Fiston

Creating BIP Gantt Charts using JFreeChart (cont.)

In my previous post I showed the majors steps for creating a simple Gantt Chart in BI-Publisher using the JFreeCharts API. The purpose of this post is to provide the technical details of the implementation. 
We first need to download the following JAR files:
· jfreechart-1.0.4.jar
· jcommon-1.0.8.jar
· Commons-code-1.5.jar
 
Extension code:
Create a Jdeveloper generic application
Application Name: GanttChart
 



















Click Next to create a project:
Project Name: bip
 


Add a new Java class to the bip project 



I’ve created a Java class that will be packaged as a jar file and copied to the Weblogic folder. The purpose of this file is to:
1) Read in the XML from the BI-Publisher data model:
2) Organize XML data in the format expected by the JFreeChart API
3) Plot the Gantt Chart using the API
4) Return the generated image
5) BI-Publisher renders the image on the report
Update the code in Jdeveloper with the following is the code listing: this is a very crude code intended for testing only
 
/* ======================================
* JFreeChart : a free Java chart library
* ======================================
*
* Project Info: http://www.jfree.org/jfreechart/index.html
* Project Lead: David Gilbert (david.gilbert@object-refinery.com);
*
* (C) Copyright 2000-2003, by Object Refinery Limited and Contributors.
*
* This library is free software; you can redistribute it and/or modify it under the terms
* of the GNU Lesser General Public License as published by the Free Software Foundation;
* either version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public License along with this
* library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
* Boston, MA 02111-1307, USA.
package oracle.bip.extensions;
import java.awt.image.BufferedImage;
import java.io.IOException;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.codec.binary.Base64;
import javax.sql.rowset.serial.SerialException;
import javax.xml.parsers.ParserConfigurationException;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.ChartRenderingInfo;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.entity.StandardEntityCollection;
import org.jfree.data.category.IntervalCategoryDataset;
import org.jfree.data.gantt.Task;
import org.jfree.data.gantt.TaskSeries;
import org.jfree.data.gantt.TaskSeriesCollection;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
public class bipExt {
public static String createGantt(NodeList bipXML, String taskName,
String taskStartDate,
String taskEndDate, String title) throws IOException,
SerialException,
ParserConfigurationException, SQLException, SAXException,
ParseException, NullPointerException {
final TaskSeries s1 = new TaskSeries("GANTT");
//Read in the start dates, end dates, task names as lists
ArrayList<String> startDate = new ArrayList<String>();
ArrayList<String> endDate = new ArrayList<String>();
ArrayList<String> taskname = new ArrayList<String>();
// First parameter list is the list of task start dates
for (int i = 0; i < bipXML.getLength(); i++) {
Node node = bipXML.item(i);
if (node.getNodeType() == Node.ELEMENT_NODE) {
Element element = (Element)node;
NodeList nodelist = element.getElementsByTagName(taskStartDate);
Element element1 = (Element)nodelist.item(0);
NodeList startDateNode = element1.getChildNodes();
String c1=startDateNode.item(0).getNodeValue().toString();
// Extract the canonical dates
startDate.add( c1.substring(0, 9));
}
}
// Second parameter list is the list of task end dates
for (int i = 0; i < bipXML.getLength(); i++) {
Node node = bipXML.item(i);
if (node.getNodeType() == Node.ELEMENT_NODE) {
Element element = (Element)node;
NodeList nodelist = element.getElementsByTagName(taskEndDate);
Element element1 = (Element)nodelist.item(0);
NodeList endDateNode = element1.getChildNodes();
String c2=endDateNode.item(0).getNodeValue().toString();
// Extract the canonical dates
endDate.add(c2.substring(0, 9));
}
}
// First parameter list is the list of task labels
for (int i = 0; i < bipXML.getLength(); i++) {
Node node = bipXML.item(i);
if (node.getNodeType() == Node.ELEMENT_NODE) {
Element element = (Element)node;
NodeList nodelist = element.getElementsByTagName(taskName);
Element element1 = (Element)nodelist.item(0);
NodeList labelNode = element1.getChildNodes();
String label = labelNode.item(0).getNodeValue();
taskname.add(label);
}
}
BufferedImage chartImage;
bipExt gantt = new bipExt();
// Create the dataset expected by JFreeChart for the Gantt Chart Type
IntervalCategoryDataset dataset =
gantt.createDataset(startDate, endDate, taskname);
// Create the JFreeChart
final JFreeChart chart = ChartFactory.createGanttChart(
title,
"Task",
"Date",
dataset,
true,
true,
false
);
chart.setBackgroundPaint(new GradientPaint(0, 0, Color.white, 1000, 0, Color.blue));
// Render the Chart as an image
final ChartPanel chartPanel = new ChartPanel(chart);
int width=450;
int height=270;
chartPanel.setPreferredSize(new java.awt.Dimension(width, height));
ChartRenderingInfo info = null;
info = new ChartRenderingInfo(new StandardEntityCollection());
chartImage = chart.createBufferedImage(550, 350, info);
byte[] buffered_image = ChartUtilities.encodeAsPNG(chartImage);
String image = new String(Base64.encodeBase64Chunked(buffered_image));
return image;
}
public bipExt() {
}
public static IntervalCategoryDataset createDataset(List sdate,
List edate,
List taskName) throws ParseException {
Iterator iterator = sdate.iterator();
final TaskSeries s1 = new TaskSeries("Schedule");
for (int i = 0; i < sdate.size(); i++) {
String c1;
String c2;
c1=sdate.get(i).toString();
c2=edate.get(i).toString();
s1.add(new Task(taskName.get(i).toString(), new SimpleDateFormat("yyyy-MM-dd").parse(c1),
new SimpleDateFormat("yyyy-MM-dd").parse(c2)));
}
final TaskSeriesCollection collection = new TaskSeriesCollection();
collection.add(s1);
return collection;
}
}

Add the previous 3 jar files to the project 





And create the JAR Deployment file



Once the JAR File is generated, copy it to the place where Weblogic  server expects it 



Log into to BI Publisher and “Change the Disable external references” flag to “False”, it is “True” by default. 

BI Publisher Report:
Create the BI-Publisher data model. For this example, download the ACTIVITIES table from the amis site and create a simple BIP SQL query:
select label,start_date,end_date from activities with the respective XML tag names: LABEL, START_DATE, END_DATE
 


Following is a sample XML data set from the data model 




Create an RTF template using BIP template builder with the following 3 form fields:
1) Namespace:
 


2) Create Gantt:
Call to the Java extension to pass in the XML data from BIP, the XML tag names and title, the result from the call is stored in a parameter called GanttIMG 



3) Display Gantt:
Display the image on the report 



Create a new BIP report using this RTF template and the previous data model.
Run your report, if all goes well: ta-da! 


 
a+
Fiston

Creating BIP Gantt Charts using JFreeChart

An overlooked feature of BI-Publisher is the ability to create custom extensions in Java in order to add capabilities beyond the out of the box features.  Tim Dexter blogged about this undocumented feature several years ago.
Using this feature, I was able to integrate BI Publisher with JFreeChart an Open Source plotting API in order to generate plots that are not provided by BI-Publisher such as the Gantt chart type. The main steps are as follows:
1) Create the BIP data model: as an example I used the sample Gantt data set from the amis website.


2) Create the BIP extension code in Java, I used Tim Dexter’s example as a starting point. I plan on sharing the custom code once I get a chance to clean it up.
Creating Gantt chart using JFreeChart is very straightforward and there are several examples on the web, other more sophisticated types of plots (box plots etc…) require more work. The only difficulty with JFreeChart is that it doesn’t support XML dataset as input, there are ways around that limitation
You’ll need the following JAR files available on the web:
· jfreechart-1.0.4.jar
· jcommon-1.0.8.jar
· Commons-code-1.5.jar
The resulting JAR file will have to be copied to the location expected by Weblogic, on my PC it is:
C:\fusion\user_projects\domains\bifoundation_domain\lib
 


3) Create the BIP template, I used Template builder to create an RTF template with 3 fields
 


Namespace:
Create Gantt Chart: is the call to the custom Java code. My custom code call is:

I am basically feeding the XML data from BI-Publisher to the JFREECHART API as well as the XML tags
Display Gantt Chart: renders the resulting image (Gantt Chart) from JFreeChart
;

5) Change the “Disable External References” flag to “FALSE” (it is TRUE by default) in BI-Publisher
 


6)  Upload your RTF template, bounce the services and voila!
 

Pretty basic but the look and feel of this can be greatly improved by tweaking the JFreeChart API.
a+
Fiston