OBIEE 11G and Oracle Times Ten 11.2.2 Integration

OBIEE 11G and Oracle Times Ten 11.2.2 Integration

Oracle Business Intelligence Enterprise Edition support Oracle Times 11.2.2 as a physical data source.  As Times Ten is a memory-optimized relational database that delivers very low response time and very high throughput to performance-critical systems, it is very useful for in-memory analytics with improved performance.

A TimesTen database may be used as the database of record, and/or as a cache to an Oracle database. The purpose of this blog is to discuss the how  to enable Oracle Times Ten as a data source for Oracle BI Server followed by step by step guide for creating/testing BI Server metadata for Times Ten database.

Prerequisites

TimesTen 11g server should be installed. If the TimesTen Server and OBIEE are installed on two separate machines then Times Ten 11g client should be installed on the OBIEE install machine, which is used to make required communication with TimesTen server.

 TimesTen can be downloaded from http://www.oracle.com/technetwork/database/timesten/downloads/index.html

For the simplicity I have installed the Times Ten and OBIEE 11g on a Windows machine. I am using BISAMPLE Times Ten database and data

Creating Times Ten database

For creating the Times Ten database, the first step is to create the necessary DSN,

Windows - Go to Control Panel >> Administrative Tools >> Data Sources (ODBC) >> System DSN, Choose a driver for Times Ten Data Manager 11.2.2 and put in details required for DSN

Data Source Name – BISAMPLE_TT

Description - BISAMPLE_TT

Database Character Set – AL32UTF8

Data Store Path + Name – Point this to a folder where you would want to install the BISAMPLE_TT data store.  (Size Estimate 80 Mb)

Permanent Data Size – 40

Temporary Data Size - 32

Linux - Open sys.odbc.ini file (Installation folder for times ten - /Times Ten/tt1221/info)

Add a new data source name in the section labeled [ODBC Data Sources]

[ODBC Data Sources]

plsqldb=TimesTen 11.2.1 Driver

cachedb1=TimesTen 11.2.1 Driver

cachedb2=TimesTen 11.2.1 Driver

BISAMPLE_TT=Times Ten 11.2.1 Driver

The database attributes can be added anywhere after [ODBC Data Sources], the possible entries are as follows

[BISAMPLE_TT]

Driver=/home/oracle/app/oracle/product/TimesTen/tt1121/lib/libtten.so

DataStore=/home/oracle/app/oracle/product/TimesTen/tt1121/info/DemoDataStore/BISAMPLE_TT

PermSize=40

TempSize=32

PLSQL=1

DatabaseCharacterSet=AL32UTF8

Create Dimensions and Fact in TimesTen Database  

Connect to the BISAMPLE_TT by using ttiSQL

CONNECT BISAMPLE_TT;

Create BISAMPLE_TT user by issuing the following command

CREATE USER BISAMPLE_TT IDENTIFIED BY BISAMPLE_TT;

GRANT ADMIN TO BISAMPLE_TT;

Now disconnect and connect again using the BISAMPLE_TT user created

CONNECT "DSN=BISAMPLE_TT; UID=BISAMPLE_TT;PWD=BISAMPLE_TT";

For the demonstration purpose I have created following two dimension table and one fact table in BISAMPLE_TT database schema.

W_OFFICES_D – office dimension table

W_DAY_D – day dimension table

W_HEADCOUNT_F – Head Count Fact table with fte

These tables have been loaded with the sample data.

Enabling the use of Times Ten as Data Source for the Oracle BI Server

Oracle BI Server connectivity to Times Ten is through ODBC. The following ODBC settings need to be done based on installation of OBIEE and Times Ten Server. If OBIEE and Times Ten server are on two different machines, then Times Ten Client need to be installed on OBIEE machine.

Based on configuration and operating system whether it is a Windows setup or Linux, please follow steps listed below.

OBIEE and Times Ten Server on same Machine

Windows – DSN Creation explained in previous section will be used in the OBIEE environment settings. No other DSN needs to be created.

UNIX – Edit odbc.ini file from /InstallHome/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Under the [ODBC Data Sources] section make following entry

[ODBC Data Sources]

BISAMPLE_TT=Times Ten 11.2.1 Driver

The database attributes can be added anywhere after [ODBC Data Sources], the possible entries are as follows

[BISAMPLE_TT]

Driver=/home/oracle/app/oracle/product/TimesTen/tt1121/lib/libtten.so

DataStore=/home/oracle/app/oracle/product/TimesTen/tt1121/info/DemoDataStore/BISAMPLE_TT

LogDir=/home/oracle/app/oracle/product/TimesTen tt1121/logs

PermSize=40

TempSize=32

PLSQL=1

DatabaseCharacterSet=AL32UTF8

Note: - The DataStore and LogDir are based on the installation and subject to change.

OBIEE and Times Ten Server on different machines

Windows – Times Client is installed on the machine where OBIEE is installed. Once the Clients installed, create a DSN called BISAMPLE_TT on this client machine to connect to the TimesTen server.

To create the DSN using the ODBC Data Source Administrator, choose TimesTen Client 11.2.1 as the driver and create a DSN with the following properties

Name – BISAMPLE_TT

Server Name or Network Address – Enter the name/ip of the TimesTen server machine

Server DSN – BISAMPLE_TT (This name should appear in the drop down as this is the DSN created on the TimesTen server machine)

Connection Name – BISAMPLE_TT

User ID – BISAMPLE_TT

Password – BISAMPLE_TT

Leave the other parameters as default. Click on Test Data Source Connection and Test Oracle TimesTen Server Connection and confirm that it connects to the server successfully.

UNIX – Times Client is installed on the machine where OBIEE is installed. Once the Clients installed, edit odbc.ini file from /InstallHome/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Under the [ODBC Data Sources] section, make the following entry

BISAMPLE_TT= TimesTen Client 11.2.1

For this DSN entry, there is a corresponding set of database properties and attributes that need to be entered. The database attributes can be added anywhere after the [ODBC Data Sources] section.

[BISAMPLE_TT]

Driver=/home/oracle/app/oracle/product/TimesTen/tt1121/lib/libtten.so

TTC_SERVER = TimesTenServerName

TTC_SERVER_DSN = BISAMPLE_TT

TTC_SERVER should point to the machine where TimesTen Server is installed.

 TTC_SERVER_DSN should be BISAMPLE_TT.

OBIEE Environment Settings

In order to enable OBI Server to communicate to the TimesTen server, following settings need to be done within the OBIEE install location for Windows/Unix install of OBIEE.

Open user.sh from the following location on your OBIEE install

/InstallHome/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

At the end of the file, add the following to include TimesTen folder location to the LD_LIBRARY_PATH variable.

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH: =/home/oracle/app/oracle/product/TimesTen/tt1121/lib

 Change the path to the appropriate location on your TimesTen installation.

- If both OBIEE and TimesTen server are installed on the same machine, then this path will refer to the TimesTen server install location.

- If OBIEE and TimesTen server are installed on different machines, then this path will refer to the TimesTen client install location.

Don’t forget to restart OBI Server for these changes to take effect.

Creating BI Server Metadata 

 Import Metadata

The “Import Metadata” menu option in the Oracle BI Administration tool is used to create BI Server Physical layer metadata necessary to access times ten database for reporting and analysis. The following illustrations outline the process of creating Oracle BI Sever metadata for Times Ten.

In the “Import Metadata’’ wizard, following

Select Data Source

Input Import Type, Connection Type, Chose DSN, User Name and Password as shown below,

 Select Metadata Types

  Select metadata type as Tables and Click on Next.

Select Metadata Objects

Select metadata objects – i.e. Time Ten Database (BISAMPLE_TT), Schema User (BISAMPLE_TT), Tables to Import and click on import to repository view and click on Finish.

Modeling Physical Layer

Database

Make sure that we have chosen the appropriate database features, it is very important to ship function down to database rather than at BI Sever level.  

Connection Pool

Choose ODBC 2.0 driver to connect to the Times Ten database, maximum connection=10 to start with.

Imported Tables and Aliases

Imported Physical Table 

W_DAY_D, W_OFFICES_D & W_HEADCOUNT_F

Aliases –Best Practice is to have aliases of physical tables

D01 – Time Day Grain (W_DAY_D)

D12 – Offices (W_OFFICES_D)

F0 – Facts Headcount (W_HEADCOUNT_F)

Set keys for the dimension tables and following joins,

"12 - Sample Data (Times Ten)".""."BISAMPLE_TT"."D12- Offices"."OFFICE_KEY" =

 "12 - Sample Data (Times Ten)".""."BISAMPLE_TT"."F01 Facts Headcount"."Office_Key"

"12 - Sample Data ( Times Ten)".""."BISAMPLE_TT"."D01 Time Day Grain"."Day_Key" =

"12 - Sample Data (Times Ten)".""."BISAMPLE_TT"."F01 Facts Headcount"."Day_Key"

Modeling Logical Layer

Make sure that we have chosen the appropriate database features, it is very important to ship function down to database rather than at BI Sever level.

Logical Business Model

13 – Sample Headcount

Dimension

·         D01 Time Dim – Level Based Dimension

Set as Time Dimension, it has two hierarchies Fiscal and Calendar 

o    Fiscal – Fiscal Year – Fiscal Half –Fiscal Qtr – Fiscal Month – Fiscal Week – Day Detail

o    Calendar- Hierarchy 2 – Year – Half Year –Qtr – Month – Week – Day

·         D02 – Office Dim – Level Based  Dimension

o    D02- Offices Total - Offices Company - Offices Organization - Offices Department - D02- Offices Detail

·         Content Level for Logical Source Table

Logical Table

·         D01 Time - Office Key ( Logical Table Key)

·         D02- Offices – Calendar Date, Day Key ( Logical Key)

·         Content Level  - Set it to  Office Detail

Fact Table

·         F01 Facts Headcount

·         Measure - Full Time Empl ( End Period)  = fte

·         Aggregation

o    For Time Dimension

Last("13 - Sample Headcount"."F01 Facts Headcount"."Full Time Empl  (End Period)" )

o    For Other Dimension

Sum ("13 - Sample Headcount"."F01 Facts Headcount"."Full Time Empl  (End Period)" ) 

·         Full Time Empl ( Beg Period)

o    For Time Dimension

Last("13 - Sample Headcount"."F01 Facts Headcount"."Full Time Empl  (End Period)" )

o    For Other Dimension

Sum ("13 - Sample Headcount"."F01 Facts Headcount"."Full Time Empl  (Beg Period)" ) 

The logical content level for fact for Time and Office dimension are set to detail.

The business model diagram

Presentation Layer

Created subject area for logical star for times ten database

Note – After completing the development, don’t forget to check consistency of the repository.

Load Repository and Generate a Simple Report

Loaded repository by using Enterprise Manager, a sample report generated for Times Ten database by using OBIEE 11g.

A simple report with Time and Office Hierarchy and Full time Empl (End Period) fact based on exposed Time Ten database tables is as below.