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 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.
