Using Oracle TimesTen With Oracle BI Applications (Part 1)

Peter Scott -

Oracle BI Applications (OBIA) is a product for delivering industry best-practice dashboards and analysis over enterprise sources such as ERP, HR, SCM and CRM systems. OBIA includes the necessary functionality to extract data from the source systems, transform and load it to the target database's standardised data model and to provide a web-based analytic platform over the target database. The ETL functionality is (depending on OBIA version) delivered by either Informatica or Oracle Data Integrator (ODI). The reporting and dashboards are delivered through OBI.

Simplistically, Oracle BI Apps extracts data from the source system(s) and loads it into a custom data warehouse data model, this is then used as the data source for the pre-built OBI analyses and dashboards.

Over my next few blog postings I will be exploring the use of Oracle TimesTen as an additional data storage for OBIA reporting. If you are not already aware, Oracle TimeTen is an in-memory relational database that finds usage as in-memory caching technology for Oracle databases and as a standalone in-memory database, especially for rapid update transactional systems. It is also a key part of the Exalytics software platform (along with Oracle Business Intelligence and Essbase). For these articles I will be using the Exalytics edition of TimesTen. This gives access to some of the special Data Warehouse-style performance features that are not included with the standard TimesTen license. Some of the techniques I will cover are equally applicable to the standard TimesTen product and do not require the special Exalytics features. Pictorially, I will be using Oracle TimesTen as a second data source to OBI.

To simplify these blog postings I will consider a single fact table and its associated dimensions and use hand-coded TimesTen methods for data loading. I am writing my own data load code as I want to compare different loading techniques. In a production scenario I might consider using ODI or the code generated by the Exalytics BI Summary Advisor - both topics for another time.

Before I get into detail on loading TimesTen tables is may be useful to mention what Oracle TimesTen actually provides. TimesTen is a relational database that uses the server's RAM and not disk to hold the data. Although the database is "in memory" it does have a disk footprint in that the database is read from disk on startup and written back to disk on unloading in addition there are database log (checkpoint) files for use in database recovery from backup. Just as with Oracle 11g, TimesTen supports all the usual DDL of a relational database: CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE PACKAGE etc. Packages are written in PL/SQL. The Exalytics version of TimesTen adds two additional features we may find useful in building aggregate tables: in-memory columnar compression and the OLAP grouping operators (ROLLUP, CUBE and GROUPING SET). Query plans are generated by the TimesTen Cost Based Optimiser and we able to modify query plans by more accurate table statistics, optimal indexing and the use of database hints

I will look at some of these features in more detail as I develop the code in my next two blog posts.