Using the BI Server as a data source in OEID 3.1

In the previous post, we went through the OEID 3.1 installation and configuration using the new orchestration script. In this post we’re going to explore a new Oracle Endeca Information Discovery (OEID) 3.1 feature: the ability to specify Oracle BI as a data source and build an application using the Provisioning Service. Note than there was some level of integration between OBIEE and Endeca in version 3.0 and even in 2.4. This integration was done through the Oracle Endeca Integrator where you can define the BI server as a data source and create an ETL graph to load the data domain. The OEID 3.1 new integration gives the ability to directly connect to the BI server from the front end and report straight from the BI server, no need to run ETL graphs. I am using OEID 3.1 running under Windows 2008 R2 and I am using the OBIEE Sample Application v309R2.

We’ve already installed and configured OEID 3.1, the next task is to create a connection to the Provisioning Service in Studio. Log into Studio and Navigate to Control Panel->Information DiscoveryàProvisioning Service. Update the server name: “localhost”, having updated the default connection, we’re required to re-enter the caPassword (password for the truststore file for the SSL connection) and certPassword (password for the keystore file for the SSL connection). Save the connection.

Connect to Endeca Information Discovery portal->Control Panel->Data Source Library->New Data Source

Select Data Source type->Oracle BI and specify

• Oracle BI Server Host
• BI Server Port :9703
• Username and Password

->Next

The next screen allows you to select the Subject Area of Interest

I select A – Sample Sales and I am presented will a list of Presentation Tables

-> Select Base Facts for example and a list of the presentation fields are displayed. On this screen, one can select what attributes to include/filter by, change labels

The advanced tab enables more flexibilities for non-matching data types, missing values

Once you navigate to the New Discovery Application, you’ll notice the option to “Load Data from a Database”

A sample application is generated for us, but it’s basically useless because can only report based on a presentation table and typically a presentation table in OBIEE contains either attributes or facts. Presentation tables are just way of presenting tables and columns to the end-users for ad-hoc querying; as long as we have the proper modeling done in the BMM layer of OBIEE, you can create custom presentation with whatever column that you’re interested in. Given that fact, we’re going to create a new presentation table that contains both sales measures and product attributes to be used for Endeca Ad Hoc reporting and we called it Endeca Self-Service.

Then we create a new data source like before in Studio.

We can then create a dashboard with all the typical components such as a search box, a breadcrumb, a guided navigation and results table

The new self-service feature allowed by the Provisioning Service is a great step forward to a better tighter integration between OBIEE and OEID. One limitation is that you can only use one presentation table to report from; hopefully in the next release, you’ll be able to report using several presentation tables.

Oracle Endeca Information Discovery 3.1 Installation

So Oracle Endeca Information Discovery (OEID) 3.1 was released few days ago, Farnaz Mostowfi from Rittmanmead provided an excellent overview of the new features such as self-service BI, data mashup capabilities.

After Endeca was acquired by Oracle in October 2011, no time was wasted working on the integration with the BI tech stack in particular Oracle Business Intelligence. OEID 2.4 introduced among other features the ability to load data directly from the BI server through Integrator. OEID 3.0 enhanced the integration with the BI server and introduced new Studio features but the major change was the ability to deploy the Endeca server, Studio and Integrator on Weblogic, Tomcat being also supported still. In addition, OEID 3.0 introduced the “Provisioning Service” a web application in a Weblogic Server container that enables dynamic application creation within Studio from data (Excel files) uploaded from the desktop.

There were however several steps in the deployment process that had to be done manually which made the OEID 3.0 installation pretty dreadful. So getting back to OEID 3.1, with the flurry of new exciting features on Oracle slides, blogs and the excellent OEID channel on YouTube I was pretty excited and eager to experiment with it starting with the installation process. In this blog posting, I am going to look at the process of installing and configuring the latest OEID 3.1 release. The different components that will be installed are:

- Endeca Server 7.6

- Endeca Provisioning Service 3.1

- Endeca Studio 3.1

- Endeca Integrator 3.1

Software Download

The software packages can be downloaded from OTN or from Oracle Software Delivery cloud, you’ll need

From the Oracle Endeca Server (7.6.0) Media Pack for Microsoft Windows x64 (64-bit)

- Oracle Endeca Server (7.6) for Microsoft Windows x64                       V40519-01

- Oracle Weblogic Server 11gR1 (10.3.6.) Generic and Coherence           V29856-01

- Oracle Application Development Runtime 11g Patch Set 5 11.1.1.6        V29673-01

From the Oracle Endeca Information Discovery Studio (3.1.0) Media Pack for Microsoft Windows x64 (64-bit)

- Oracle Endeca Information Discovery Studio (3.1) for Microsoft Windows x64 (64-bit) and Weblogic V40543-01

- Oracle Endeca Information Discovery Provisioning Service (3.1) for Microsoft Windows x64 (64-bit) V40546-01

- Oracle Endeca Information Discovery Studio (3.1) Install Scripts for Microsoft Windows x64 (64-bit) V40547-01

Download JDK: the recommended version is Version 6 of the Oracle Sun SE (Standard Edition) Development Kit. We will be using jdk-6u43-windows-x64.exe, this JDK is available from

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html

It is important to make sure that the installation path doesn’t contain spaces; this is bound to cause issues with the Endeca components installation. I will be installing JDK in

C:\Java\jdk1.6.0_43

Orchestration Scripts

OEID 3.1 can be installed the “old” way: deploying the different components manually like in 3.0 or…or by running the new “orchestration script” script. This script will install the WebLogic, ADF, and Endeca Server, Endeca Studio and Endeca Provisioning Service components automatically while prompting you for information such as username and password. This is a big step forward to simplifying the OEID installation process like it used to be.

While Oracle delivered a set of functioning scripts that accomplishes what they were intended for, setting the scripts to work in a smooth way can be challenging. The key to getting started is reading/following the…Getting Started Guide!

The orchestration script can be run in 2 different modes:

INSTALL_MODE or non-configuration mode: In this mode, the different components: Weblogic, ADF, Endeca Server, Endeca Studio and Provisioning Service are install but not configured, in other words the respective domains are not created.

INSTALL_ALL_AND_CONFIGURE or configuration mode, you will be prompted for configuration values such as admin username, password, SSL paraphrase if installing in secure mode. The true benefit of this installation mode is that the software is fully installed configured and even the services are started for you. I will be using this installation mode using SSL.

Create a directory (for example c:\temp) to hold the unpacked version of the software

1) Unzip V29856-0.zip1 to get a single JAR file wls1036_generic.jar

2) Rename V29673-01.zip to       ofm_appdev_generic_11.1.1.6.0_disk1_1of1.zip, do not unzip it !

3) Unzip V40519.zip; this will be unzipped as:

• Endecaserver.zip do not unzip it!
• Windows_OC.zip, unzip it this file to get Windows_OC

4) Unzip V40547-01.zip to create EID_3.1_Studio_InstallWin.zip, unzip it to get one top folder with subfolders: eidOrch

5) Move the eidOrch to the top folder for example C:\temp

6) Move Endecaserver.zip from C:\temp\V40519 to C:\temp\eidOrch\installers\

7) Move the folder folder named Windows in Windows_OC from C:\temp\V40519\Windows_OC to C:\temp\eidOrch\orchScripts\ and rename it to ES_windows_OC, this is the folder that contains the Endeca Server installation Scripts.

We’re almost ready to start the orchestration script, let’s first do some cleanup and remove all files, folders from c:\temp except for C:\temp\eidOrch !!!

Let’s examine our directory structure C:\Temp

C:\temp\eidOrch

C:\temp\eidOrch\installers

C:\temp\eidOrch

Basically there are 2 main folders:

1) Installers: contains the software binaries

2) orchScripts: contains the installation scripts. The top level is run_EID_install.bat. This main script will in turn invoke child scripts in the following order

o ES_windows_OC\run_endecaserver_install.bat to install/configure the Endeca Server

o Studio_windows_OC\run_endecastudio_install.bat to install/configure the Endeca Studio

o PS_windows_OC\run_endecaprovisioning_install.bat to install/configure the Endeca Provisioning Server

Each of this script will read installation parameters from property files:

· orchScripts\config_EID_windows.prop for the main script

· orchScripts\ES_windows_OC\config_win.prop

· orchScripts\PS_windows_OC\config_PS_win.prop

· orchScripts\Studio_windows_OC\ config_Studio_win.prop

These property files have to be modified to specify installation values. Luckily, the changes to be done are pretty similar. The main parameters to be changed are:

JAVA_HOME, ORACLE_HOME and INSTALLER_LOCATION

The rest of the parameters can assume their default values (if you’re running a NON-SSL install). In my case, I will be running SSL therefore I am updating my property files as follows:

config_EID_windows.prop

JAVA_HOME= C:\Java\jdk1.6.0_43

ORACLE_HOME=F:\Oracle\Middleware

INSTALLER_LOCATION=C:\temp\eidOrch\installers

START_MODE=PROD (whether to start Weblogic in Production mode or in development mode

USE_SSL=TRUE

Config_win.prop

INSTALL_MODE= INSTALL_ALL_AND_CONFIGURE

START_MODE=PROD

JAVA_HOME= C:\Java\jdk1.6.0_43

ORACLE_HOME=F:\Oracle\Middleware

INSTALLER_LOCATION=C:\temp\eidOrch\installers

Config_PS_win.prop

INSTALL_MODE=INSTALL_SOLO_PS

START_MODE=PROD

JAVA_HOME= C:\Java\jdk1.6.0_43

ORACLE_HOME=F:\Oracle\Middleware

INSTALLER_LOCATION=C:\temp\eidOrch\installers

INSTALL_MODE in this context specifies whether to do an end to end installation with Weblogic, ADF and the Provisioning service (INSTALL_E2E_PS) or to just deploy the Provisioning service using an existing installation of Weblogic and ADF (INSTALL_SOLO_PS) which is the option that we’ve selected.

Since we’re doing an installation in secure mode, additional parameters have to be updated for the key stores locations.

KEYSTORE_IDENTITY_LOCATION=F:\Oracle\Middleware\user_projects\domains\ENDECA_SERVER_DOMAIN_NAME\config\ssl

KEYSTORE_TRUST_LOCATION=F:\Oracle\Middleware\user_projects\domains\ENDECA_SERVER_DOMAIN_NAME\config\ssl

Config_studio_win.prop

INSTALL_MODE= INSTALL_SOLO_STUDIO

START_MODE=PROD

JAVA_HOME= C:\Java\jdk1.6.0_43

ORACLE_HOME=F:\Oracle\Middleware

INSTALLER_LOCATION=C:\temp\eidOrch\installers

INSTALL_MODE flag works in a similar fashion, we’re selecting to only install the Studio Server into an existing middleware installation.

KEYSTORE_IDENTITY_LOCATION=F:\Oracle\Middleware\user_projects\domains\ENDECA_SERVER_DOMAIN_NAME\config\ssl

KEYSTORE_TRUST_LOCATION=F:\Oracle\Middleware\user_projects\domains\ENDECA_SERVER_DOMAIN_NAME\config\ssl

All the pieces are in place now to start the OEID installation! Let’s get to it!

To start the installation, open a DOS window as an Administrator, navigate to

C:\temp\eidOrch\orchScripts

Issue the following command to start the main script:

run_EID_install config_EID_windows.prop --temp-directory f:\temp

The script will request the following information need for the domain creation and configuration.

1) Enter the Weblogic Server Administrator username [default=weblogic]:

2) Enter the Weblogic Server Administrator password :

3) Re-enter the Weblogic Server Administrator password :

4) Enter the Endeca Server Administrator username [default=ESAdmin] :

5) Enter the Endeca Server Administrator password :

6) Re-enter the Endeca Server Administrator password :

7) Enter the SSL Pass Phrase :

8) Re-enter the SSL Pass Phrase :

Weblogic will be the first component to be installed, at the end of the installation: Installing Weblogic Server..........Successful

Next starts the ADF installation; if fails with the following message: Installing ADF..........Failed

Debugging this issue wasn’t easy, suffice to say after going through a couple failed installs, I found what the issue was. It had to do with the fact that the installation scripts are in a location different from where the temp directory is ( f:\temp as specified at the command line). There are 2 ways to solve this issue:

- Use a temp directory located on the C: drive

- Modify run_endecaserver_install.bat, run_endecastudio_install.bat and run_endecaprovisioning_install.bat. Look for the line cd %TMPFOLDER%\bin and enter the drive name for example, C:

Rather than altering the scripts, I am choosing the first option and point to a temporary location in C:

run_EID_install config_EID_windows.prop --temp-directory c:\temp_dir

Once the Endeca Server is installation is done, the Studio Install starts and you’re prompted for the Endeca Studio administrator username, password etc..

The last piece to get installed is the Provisioning Service; you will be prompted again for username, password, Endeca Server location, passphrase etc..

Following are screenshots of the install and the prompts

At last, the installation is successful, to confirm, navigate to the eid login page

As you can see, the installation process has been automated somewhat, however setting up the installation scripts can be a challenging task. As mentioned in the beginning of this post this is a great step in the right direction; OEID 3.2 will hopefully bring back the easy installation process.

Now that we’ve got our installation done, where do we go from here? In the next blog entry, I plan to explore a new and exciting feature that enables building Studio application quickly by pointing to the OBIEE BI server though thanks to the Endeca Provisioning Service.