Loading Data from Oracle to TimesTen in Exalytics Environment - Options

As an Oracle Exalytics Implementation Advisor, I have been working with number of customers and helping them for smooth transition from non-Exalytics to Exalytics world. BI Apps migration makes it more interesting due to ETL component of the solution, Informatica or ODI.

One of the important question customer would like to understand is about the how to load data from Oracle to Times Ten in Exalytics environment? Are there more than one option?  Which options is better than other option?  Which option should be used in specific situation?  Are there any licence implications? Is incremental load is supported?  

This is an attempt to discuss the various options to load data into times ten from Oracle in general and Exalytics in particular at higher level.

Oracle Cache Facility

Oracle In-Memory Database Cache (IMDB Cache) is an Oracle Database product option ideal for caching a subset of an Oracle database using Oracle TimesTen In-Memory Database (TimesTen) and deploying in the application tier for multi-user and multi-threaded applications for improved response time. Applications perform read/write operations on the cache tables using SQL and PL/SQL with automatic persistence, transactional consistency, and data synchronization with the Oracle database. Cached tables operate like regular relational tables inside the TimesTen database and are persistent and recoverable. 

The IMDB Cache architecture is shown below.

Applications using IMDB Cache may choose to configure a combination of caching options

• Read-only caches - transactions are performed in the Oracle Database and the changes are refreshed to the TimesTen cache database.
• Read-write (or write-through) caches – transactions are performed in the TimesTen cache database and then propagated to the Oracle Database.
• On-demand and preloaded cached - data may be loaded on-demand or preloaded, and may be shared across the cache grid members, or reside only in a specific cache node.

Data synchronization with the Oracle Database is performed automatically.

• Asynchronous write-through cache leverages the speed of TimesTen by first committing the transactions locally in the cache database, and asynchronously sending the updates to the Oracle Database. Asynchronous write-through cache groups provide faster application response time and higher transaction throughput.
• Synchronous write-through cache will ensure that if the Oracle Database cannot accept the update(s), the transaction is rolled back from the cache database; with synchronous write-through, the application must wait for the commits to complete in both the Oracle Database and the TimesTen database.
• For read-only caches, incremental updates in the Oracle Database are asynchronously refreshed to the in-memory cache tables in the application-tier at user-specified intervals.

Note that at this time, the Oracle Cache facility -  which allows dynamic loading, refresh and propagation of data between Times Ten and Oracle - is not supported on Exalytics. 

OBIEE Summary Advisor

OBIEE Summary Advisor tool should be used to define and generate aggregate structures used by Business Intelligence applications. It is designed and intended specifically to allow aggregation of objects of types frequently used by Business Intelligence applications.

Please refer Support Note 1472652.1 for step by step guide to enable and configure Oracle Bi 

Summary Advisor does following during initial build

·         Generate the SQL which would be used to populate the aggregation tables

·         Update the RPD with the aggregates in the Physical and BMM layers

·         Create the physical tables in TimesTen and do the initial load

It does not support the incremental data load, though repository creation and database scripts can be separated from data load scripts.

Times Ten Utilities

There may be a situation where result of a SQL query from back-end Oracle database into single table on TimesTen with creating a cache grid, cache group, and cache tale to contain the result or also not using BI Summary Advisor scripts.

TimesTen provides the tools that will execute a user-provided SELECT statement on Oracle and load the result set into a table on TimesTen. Both the TimesTen and Oracle databases involved must be configured with the same national database character set.

The following are the major steps that are performed to accomplish this task:

• Create a table with the correct columns and data types on TimesTen.
• Provide a SELECT statement that will be executed on Oracle to generate the desired result set.
• Load the result set into the table on TimesTen.

TimesTen provides two methods to accomplish these tasks:

A.  The ttIsql utility provides the createandloadfromoraquery command that, once provided the TimesTen table name and the SELECT statement, will automatically create the TimesTen table, execute the SELECT statement on Oracle, and load the result set into the TimesTen table.

Connection to Times Ten

ttisql "DSN=cachedb1_1122;UID=oratt;PWD=timesten;OraclePWD=oracle"

 Copyright (c) 1996-2011, Oracle.  All rights reserved.

Type ? or "help" for help, type "exit" to quit ttIsql

connect "DSN=mydb;UID=oratt;PWD=timesten;OraclePWD=oracle";

Connection successful: DSN=mydb;UID=oratt;

DataStore=/timesten/install/info/DemoDataStore/mydb;DatabaseCharacterSet=WE8DEC;

ConnectionCharacterSet=US7ASCII;DRIVER=/timesten/install/lib/libtten.so;

PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=inst1;

(Default setting AutoCommit=1)

Create table and load data based on using select statement 

Command> createandloadfromoraquery employees 2 SELECT * FROM hr.employees;

Mapping query to this table:

    CREATE TABLE "ORATT"."EMPLOYEES" (

    "EMPLOYEE_ID" number(6,0) NOT NULL,

    "FIRST_NAME" varchar2(20 byte),

    "LAST_NAME" varchar2(25 byte) NOT NULL,

    "EMAIL" varchar2(25 byte) NOT NULL,

    "PHONE_NUMBER" varchar2(20 byte),

    "HIRE_DATE" date NOT NULL,

    "JOB_ID" varchar2(10 byte) NOT NULL,

    "SALARY" number(8,2),

    "COMMISSION_PCT" number(2,2),

    "MANAGER_ID" number(6,0),

    "DEPARTMENT_ID" number(4,0)

     )

Table employees created

107  ws loaded from oracle.

B.   The ttTableSchemaFromOraQueryGet built-in procedure evaluates the user-provided SELECT statement to generate a CREATE TABLE statement that can be executed to create a table on TimesTen, which would be appropriate to receive the result set from the SELECT statement. The ttLoadFromOracle built-in procedure executes the SELECT statement on Oracle and loads the result set into the TimesTen table.  

E.g. 

$ ttisql "DSN=mydb;uid=oratt;pwd=timesten;

OraclePwd=oracle;OracleNetServiceName=inst1"

Copyright (c) 1996-2011, Oracle.  All rights reserved.

Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=mydb;uid=oratt;pwd=timesten;

OraclePwd=oracle;OracleNetServiceName=inst1";

Connection successful: DSN=mydb;UID=oratt;

DataStore=/timesten/install/info/DemoDataStore/mydb;

DatabaseCharacterSet=WE8DEC;ConnectionCharacterSet=US7ASCII;

DRIVER=/timesten/install/lib/libtten.so;PermSize=40;TempSize=32;

TypeMode=0;OracleNetServiceName=inst1;

(Default setting AutoCommit=1)

Command> call ttTableSchemaFromOraQueryGet('hr','employees',

 'SELECT * FROM hr.employees');

< CREATE TABLE "HR"."EMPLOYEES" (

"EMPLOYEE_ID" number(6,0) NOT NULL,

"FIRST_NAME" varchar2(20 byte),

"LAST_NAME" varchar2(25 byte) NOT NULL,

"EMAIL" varchar2(25 byte) NOT NULL,

"PHONE_NUMBER" varchar2(20 byte),

"HIRE_DATE" date NOT NULL,

"JOB_ID" varchar2(10 byte) NOT NULL,

"SALARY" number(8,2),

"COMMISSION_PCT" number(2,2),

"MANAGER_ID" number(6,0),

"DEPARTMENT_ID" number(4,0)

 ) >

1 row found.

Command> CALL ttLoadFromOracle ('HR','EMPLOYEES','SELECT * FROM HR.EMPLOYEES');

< 107 >

1 row found.

Command> SELECT * FROM hr.employees;

< 100, Steven, King, SKING, 515.123.4567, 2003-06-17 00:00:00, AD_PRES, 24000, <NULL>, <NULL>, 90 >

< 101, Neena, Kochhar, NKOCHHAR, 515.123.4568, 2005-09-21 00:00:00, AD_VP, 17000, <NULL>, 100, 90 >

...

< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 2002-06-07 00:00:00, AC_MGR, 12008, <NULL>, 101, 110 >

< 206, William, Gietz, WGIETZ, 515.123.8181, 2002-06-07 00:00:00, AC_ACCOUNT, 8300, <NULL>, 205, 110 >

107 rows found.

Limitations

The load process does not check that the column data types and sizes in the TimesTen table match the data types and sizes of the result set

The load is automatically committed every 256 rows. If an error is encountered during the load, it will terminate the load, but will not roll back any committed transactions.

It does not support data load into system tables, dictionary tables, temporary tables, detail tables of views, materialized view tables, materialized view log tables, or tables already in a cache group. 

In addition, you cannot use a synonym for the table name.

Foreign key constraint will not be validated during the load process

Oracle Data Integrator

Oracle Data Integrator provides Knowledge Modules that implement optimized methods for loading data from a source or staging area into a TimesTen database.

Source or Staging Area Technology	KM	Notes
SQL	LKM SQL to TimesTen	Loads data from an ANSI SQL-92 source to a TimesTen staging table using the TimesTen JDBC driver.
File	LKM File to TimesTen (ttBulkCp)	Loads data from a file to a TimesTen staging table using ttBulkCp utility.

In addition to these KMs, you can also use the Generic SQL KMs or the KMs specific to the other technology involved.

For extracting data from a TimesTen staging area to a TimesTen table, ODI provides IKM TimesTen Incremental Update (MERGE).

KM	Notes
IKM TimesTen Incremental Update (MERGE)	Integrates data from staging area into a TimesTen target table using TimesTen JDBC driver in incremental update mode. For example, inexistent rows are inserted; already existing rows are updated.

A good news is that ODI is supported on Exalytics.

SQL Developer Export Facility

SqlDeveloper can be used to connect to both Oracle databases and TimesTen data stores and access the schemas and data of both. The SqlDeveloper Export facility allows exporting data in ascii csv ("column separated values") format so that tables from Oracle can be exported in ascii format and subsequently loaded into TimesTen using the TimesTen ttBulkCp loading utility. As opposed to the other tools listed in this Note, SqlDeveloper can be used in the other direction as well, such that tables can be exported from TimesTen into ascii formatted .csv files which can then be loaded into Oracle using Sqlloader. Check the "SqlDeveloper TimesTen InMemory Database Support User's Guide" for more information about using SqlDeveloper against TimesTen.

There are multiple options to populate aggregate times ten table on Exalytics and Non-Exalytics machine. The decision should be based on project requirements, toolset and resource capabilities on the project.