In-Memory Analytics with Oracle BI Apps and Oracle Exalytics

(1)

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

In-Memory Analytics with Oracle BI Apps and Oracle Exalytics

UKOUG Analytics Event, London, July 2013

Mark Rittman, Technical Director, Rittman Mead

(2)

About the Speaker

•

Mark Rittman, Co-Founder of Rittman Mead

•

Oracle ACE Director, specialising in Oracle BI&DW

•

14 Years Experience with Oracle Technology

•

Regular columnist for Oracle Magazine

•

Author of two Oracle Press Oracle BI books

•

Oracle Business Intelligence Developers Guide

•

Oracle Exalytics Revealed

•

Writer for Rittman Mead Blog : http://www.rittmanmead.com/blog

•

Email : [email protected]

(3)

About Rittman Mead

•

Oracle BI and DW platinum partner

•

World leading specialist partner for technical excellence, solutions delivery and innovation in Oracle BI

•

Approximately 50 consultants worldwide

•

All expert in Oracle BI and DW

•

Offices in US (Atlanta), Europe, Australia and India

•

Skills in broad range of supporting Oracle tools:

‣ OBIEE

‣ OBIA

‣ ODIEE

‣ Essbase, Oracle OLAP

‣ GoldenGate

‣ Exadata

‣ Endeca

(4)

Oracle Business Intelligence 11g

•

Oracle’s business intelligence platform, 11.1.1.7 release came out in April 2013

•

Fourth major release of OBIEE 11g, with many new features + updated look and feel

•

Enterprise BI platform centered around the Common Enterprise Semantic Model (RPD)

•

Mobile BI apps, MS Office integration, ad-hoc, dashboard and published reporting

•

Built around Oracle Fusion Middleware

•

Deployable on Windows, Unix, Linux

•

Accessing a range of enterprise data sources

‣ Oracle and other RDBMSs

‣ Essbase and other OLAP servers

‣ Files, XML, web services

‣ ADF and SOA sources

(5)

Oracle BI Applications

•

Packaged version of OBIEE that includes a data warehouse, and ETL mappings, from E-Business Suite, Siebel, SAP and Peoplesoft

•

Covers areas such as Financial Analytics, HR Analytics, Sales Analytics etc

•

Built on the same technology as OBIEE 11g, plus ETL and administration tools

(6)

Pre-Defined ETL Routines from Oracle EBS, Peoplesoft, Siebel, JDE, SAP

•

Integrated, conformed dimensional data warehouse

•

Deployable on Oracle, MS SQL, IBM DB/2 and Teradata

•

Uses Informatica PowerCenter for ETL, or now ODI11g

•

Staging tables and presentation tables

•

Allows modular deployment

•

Lowest grain of information

•

Prebuilt aggregates

•

History tracking

(7)

BI Apps Data Warehouse Limitations

•

Designed for “lowest common denominator” DB features

‣ No “out of the box” partitioning, MVs, compression optimization for PQ

•

Based on traditional disk-based RBDMS technology

•

Can often lead to slow reports, dashboards, limiting user acceptance

•

Common issue - what can we do about it?

(8)

Oracle Exalytics : First In-Memory Engineered System for Analytics

• Relational, Multi-Dimensional and Unstructured data analysis available as a single engineered system

• Combination of in-memory hardware and optimized software versions

• Supports the Exadata and Big Data Appliance data management systems

Exalytics In-Memory

Machine

Spans Relational, Multi-Dimensional, and Unstructured analysis, combined with Financial & Operational Planning

‣ In-Memory Optimized Hardware

‣ In-Memory Oracle BI, TimesTen, Essbase, and Endeca

‣ Many In-Memory Software Innovations

Tightly-Integrated with Exadata, and Big Data Appliance

(9)

Oracle Exalytics Benefits vs. Standard Hardware + Software

•

Single supported stack of hardware + software : patching synchronized and tested across all components

•

OBIEE, Essbase, TimesTen etc optimizations that are only available when deployed on Exalytics hardware

•

Optimal selection of CPUs, RAM (DRAM), network connectors for a BI application tier

•

Automatic in-memory caching of commonly-used aggregates - no manual tuning and selection

•

Future platform for all Oracle BI products - EPM Suite, BI, Endeca, BI Apps

(10)

Exalytics as the Exa-Machine for OBIEE

•

Runs the BI layer on a high-performance, multi-core, 1TB server

•

In-memory cache used to accelerate the BI part of the stack

•

If Exadata addresses 80% of the query performance, Exalytics addresses the remaining 20%

‣ Consistent response times for queries

‣ In-memory caching of aggregates

‣ 40 cores for high concurrency

‣ Re-engineered BI and OLAP software that assumes 40 cores and 1TB RAM

ERP/Apps DW

Oracle BI

(11)

Exalytics Under the Covers - How Does it Work?

•

Exalytics brings together different technologies, which are still standalone products in their own right

•

To harmonise and optimise their use within Exalytics, it utilises the following techniques:

‣ In-Memory Adaptive Data Mart - Using Oracle TimesTen for Exalytics, an in-memory RDBMS

‣ In-Memory Intelligent Result Cache

‣ In-Memory Cubes

•

Some of these are genuine "secret sauce"

•

New functionality and algorithms

•

You can only get them through licensing Exalytics

•

Others are descriptions of DW/BI strategies, or existing product functionality, extended to take advantage of the capacity for processing in memory that Exalytics has

(12)

In-Memory Adaptive Data Mart

•

Commonly-used aggregates are copied into Oracle TimesTen for Exalytics

•

Past query patterns are analyzed and suitable aggregates recommended

•

Oracle BI Server then uses these aggregates to make queries run faster

•

Aggregates change over time in response to changes in query patterns

•

Tools are provided for managing and populating these aggregates TimesTen BI Server

Ex a ly ti c s Aggregates Data Warehouse Detail-level Data

(13)

Oracle TimesTen for Exalytics

•

New version of TimesTen specifically for Exalytics (and only available with Exalytics)

•

Support for analytic functions

‣ Perform all the processing at source

‣ Combine with being in-memory = should be very fast

•

Column compression

‣ Whitepaper cites 5x

‣ Given the hardware capacity, we could seriously contemplate loading the whole Data Warehouse into memory

‣ Opens up lots of interesting design potential

•

We can load aggregates into TimesTen, leave base data at source, and use OBIEE’s Vertical Federation capability to seamlessly report across both

‣ All hidden from the end-user, all they will know is that their reports run fast!

(14)

TimesTen and OBIEE Architecture

•

Oracle BI Server communicates with TimesTen through TimesTen Client

•

Summary Advisor, and nqcmd use Oracle BI Server to access TimesTen

•

Typical single TimesTen database per Exalyics machine

‣ Max TimesTen database size around 300MB

- Due to need to set aside equal

Temp size for the Perm size selected

•

Clustered Exalytics boxes can be daisy-chained together using InfinBand connections

‣ For HA scenarios, does not increase available RAM

‣ Summary advisor scripts write to both TimesTen databases, replicating aggregates

‣ TimesTen databases can be “wired together” for failover/HA purposes

TimesTen Memory-Resident Database Checkpoint Files Log ODBC Oracle BI Server nqcmd Summary Advisor

(15)

Summary Advisor for Aggregate Recommendation & Creation

•

Utility within Oracle BI Administrator tool that recommends aggregates

•

Bases recommendations on usage tracking and summary statistics data

•

Captured based on past activity

•

Runs an iterative algorithm that searches, each iteration, for the best aggregate

•

Could we use this to cache commonly-used BI Apps

aggregations in TimesTen, automatically based on usage patterns?

(16)

Alternative Approach - Copy “Hot Data” into TimesTen for Exalytics

•

Standard approach is to store aggregates in the TimesTen datamart

‣ Aggregated by the source DB, aggregates then cached in TT database

•

Other approaches could be used, however

‣ Store whole detail-level dataset in the TT database

‣ Store just recent detail-level data in TT, and use OBIEE’s fragmentation feature

‣ Store aggregate layer from BI Apps DW entirely in TimesTen

(17)

Another Option - Oracle In-Memory Database Cache (IMDB)

•

Automatically replicate “hot” transactional data from Oracle BI Apps DW tables into TimesTen for Exalytics

•

Use OBIEE fragmentation to enable automatic navigation between sources

•

Aggregation performed by both TimesTen, and by source DB (as appropriate)

•

However - fairly intrusive approach, Oracle-only, probably not attractive to most

BI Apps customers and DBAs

(18)

Running BI Apps In-Memory - Is it Possible and Practical?

•

Can we use the Summary Advisor to automatically cache commonly-used aggregates in-memory?

‣ Similar to regular OBIEE caching, relies on query repeatability + use of aggregation

•

Could we copy all, or part, of the BI Apps data warehouse directly into TimesTen?

•

How would we update the RPD to point to the in-memory tables?

•

How fast would TimesTen be to load, and to query, vs. Oracle/SQL Server/DB2 etc?

(19)

Using the Summary Advisor on BI Apps 7.9.6.4 Step 1 : Start Advisor

•

Server has to be an Exalytics server, in this example is patched-up to 11.1.1.6.9

•

Workstation has the 11.1.1.6.9 BI Administration tool installed

•

Select Tools > Utilities, then Oracle BI Summary Advisor from utility list

1

2

(20)

Using the Summary Advisor on BI Apps 7.9.6.4 Step 2 : Scope Source Queries

•

By default, all queries registered in the usage tracking and summary statistics tables are in-scope

•

Refine the recommendations by limiting timeframe, and setting minimum accumulated time threshold

•

Still an opportunity later on to pick and choose from recommended aggregates

•

Once selected, then select the TimesTen connection pool and database as the aggregate table target

3

(21)

Using the Summary Advisor on BI Apps 7.9.6.4 Step 3 : Create Aggregates

•

Summary Advisor then recommends a set of “candidate” aggregates, which you can choose to implement

•

Select all, none or some of the recommended aggregates

•

Then run the resulting logical SQL script using the nqcmd utility

•

Note - may need to clean-up BI Apps DW data to remove duplicates etc before script completes OK

(22)

Using the Summary Advisor on BI Apps 7.9.6.4 Step 4 : Review RPD and Data

•

Aggregate Persistence process called by the “create aggregates” process also maps tables in RPD

•

Physical layer contains entries for the TimesTen tables

(23)

Summary Advisor for BI Apps : Pros and Cons

•

Pros

‣ Automatically analyzes query patterns and recommends aggregates to accelerate them

‣ Handles the registering of the TimesTen tables in the RPD, including mapping back into business model

‣ Supports any data source that the BI Server supports

•

Cons

‣ Queries have to have run before they’ll be considered for loading into TimesTen for Exalytics

‣ Relies on subsequent queries being able to use those aggregates

‣ Could get unwieldy if many aggregates are registered in the RPD

‣ Summary Advisor process does not automatically clear down tables that don’t feature in future recommendations

‣ Inefficient refresh process, unless you use a process such as

http://www.rittmanmead.com/2013/04/incremental

-refresh-of-exalytics-aggregates-using-native-bi-server-capabilities/

(24)

Alternative Approach - Copy “Hot Data” into TimesTen for Exalytics

•

Standard approach is to store aggregates in the TimesTen datamart

‣ Aggregated by the source DB, aggregates then cached in TT database

•

Other approaches could be used, however

‣ Store whole detail-level dataset in the TT database

‣ Store just recent detail-level data in TT, and use OBIEE’s fragmentation feature

‣ Store aggregate layer from BI Apps DW entirely in TimesTen

(25)

Loading data directly from Oracle into TimesTen with ttLoadFromOracle

•

The procedure ttLoadFromOracle uses OCI to load data directly from Oracle into an existing TimesTen table

•

Specify a whole table (SELECT *), or part (SELECT ... WHERE)

•

Target table must existing on TimesTen already

‣ Create it automagically using ttTableSchemaFromOraQueryGet or CreateAndLoadFromOraQuery

‣ However both these use Oracle data types and no compression, so size in memory is going to be greater

REVENUE_F_TS

REVENUE_F_LARGE

ttIsql --ConnStr

"DSN=BISAMPLE_TT;UID=SH;PWD=SH;OracleNetServiceName= orcl;OraclePWD=SH"

Command> call ttLoadFromOracle('A_TEST',

'REVENUE_F_TS', 'SELECT SHIPTO_ADDR_KEY, OFFICE_KEY, EMPL_KEY, PROD_KEY, ORDER_KEY, REVENUE, UNITS,

DISCNT_VALUE, BILL_MTH_KEY, BILL_QTR_KEY,

BILL_DAY_DT, ORDER_DAY_DT, PAID_DAY_DT, DISCNT_RATE, ORDER_STATUS, CURRENCY, ORDER_TYPE, CUST_KEY,

SHIP_DAY_DT, COST_FIXED, COST_VARIABLE, SRC_ORDER_NUMBER, ORDER_NUMBER FROM

BISAMPLE.SAMP_REVENUE_F_LARGE WHERE BILL_MTH_KEY=201012');

< 7750 > 1 row found.

-- this has loaded 7750 rows for a given month

TimesTen is loaded from the results of a query

on Oracle _{ttLoadFromOracle}

(26)

Generating TimesTen load DDL and scripts with ttImportFromOracle

•

For ttLoadFromOracle to work, the target table must exist

•

The utility ttImportFromOracle is useful here.

‣ It can map Oracle data types to optimal TimesTen ones

‣ Optionally, it can aggressively limit column sizes based on data to reduce TimesTen footprint

‣ It can evaluate compression effectiveness and apply it only where most useful

‣ Given a set of tables, it will generate:

- TimesTen DDL for requires schemas/tables/indexes

- A script to load all the tables into TimesTen in parallel (ttPDL.sh)

CreateIndexes.sql ttImportFromOracle REVENUE_F CreateTables.sql CreateUsers.sql DropIndexes.sql DropTables.sql LoadData.sql TableList.txt ttPDL.sh ttSizing.sh UpdateStats.sql

(27)

Generating TimesTen load DDL and scripts with ttImportFromOracle

•

ttImportFromOracle is new in TimesTen 11.2.2.5

‣ Not an official production utility - best efforts support only

‣ But it’s only generating scripts, which contain standard (supported) TimesTen functionality

•

The executable is located in $TT_HOME/support

•

It uses OCI, so make sure LD_LIBRARY_PATH is set to include Oracle DB lib

‣ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

•

Feature-rich syntax, but at its simplest can just be invoked for a single table, with compression:

$ ttImportFromOracle -oraconn SH/SH@orcl -tables REVENUE_F_TS -compression 1 Beginning processing

Resolving any tablename wildcards Eliminating any duplicate tables Getting metadata from source Generating database user list Assigning TimesTen datatypes Analyzing source tables

Analyzing table 'SH.REVENUE_F_TS' ... Estimating table sizes

Evaluating parallel data load Generating output files

Finished processing

(28)

Generating TimesTen load DDL and scripts with ttImportFromOracle

•

ttImportFromOracle writes a set of scripts that are subsequently executed to :

‣ Create the target tables and indexes on TimesTen, using optimised data types and compression

‣ Load the target tables on TimesTen, still via ttLoadFromOracle

REVENUE_F ttPDL.sh REVENUE_F_TS ttLoadFromOracle REVENUE_F_TS CREATE TABLE ... CreateTables.sql

(29)

Comparing Oracle and TimesTen as data sources

•

Our testing has shown that in general,

‣ For base level data, Oracle outperforms TimesTen

‣ For aggregated data, TimesTen outperforms Oracle

•

Therefore entire lift + shift of OBIA Data Warehouse into TimesTen is possibly not going to give optimal response times

-- same query over OBIA -- 0.68 seconds

Select * from samp_revenue_f_large f, samp_customers_d cd, samp_addresses_d ad, samp_products_d pd

where cd.cust_key = f.Cust_key and cd.address_key = ad.address_key

and pd.prod_key = f.prod_key and ad.city = 'San Francisco' and pd.type = 'Cell Phones' and f.bill_mth_key = 201012 ;

-- using full sized fact table (native data types) -- 0.78 seconds query time

Select * from revenue_f_native f, customers_d cd, addresses_cd ad, products_d pd

and pd.prod_key = f.prod_key and ad.city = 'San Francisco' and pd.type = 'Cell Phones' and f.bill_mth_key=201012 ;

0.68 seconds

0.78 seconds

(30)

Comparing Oracle and TimesTen as data sources

•

One option would be to put just “Hot Data” (eg current month) into TimesTen, and then update the RPD to use fragmentation

‣ This has an overhead in terms of RPD updates (and support - added complexity), as well as an additional “ETL” process to manage

-- using full sized fact table (native data types)

-- 0.78 seconds query time

Select * from revenue_f_native f, customers_d cd, addresses_cd ad, products_d pd

and pd.prod_key = f.prod_key and ad.city = 'San Francisco' and pd.type = 'Cell Phones'

0.78 seconds

-- using Time slice table

-- 0.25 seconds

Select * from revenue_f_ts f, customers_d cd, addresses_cd ad, products_d pd

and pd.prod_key = f.prod_key and ad.city = 'San Francisco'

(31)

Conclusions

•

The scale of most BI Apps implementations means that query performance can be an issue

•

Exalytics’ TimesTen In-Memory Database could be a potential solution to this issue

•

Several approaches to putting all or part of the BI Apps DW into TimesTen / In-Memory

•

Copying all or sections of the BI Apps DW into TimesTen is a potential approach

‣ But current version of TimesTen best suited to smaller tables and datasets

•

Exalytics’ Summary Advisor now works with Oracle BI Apps 7.9.6.4

•

Automatically detects and recommends suitable aggregates, builds and maps into RPD

‣ Though custom solutions are probably more efficient for their later incremental refresh

•

A work in progress - speak to Rittman Mead for more details on how this can work

•

Offers the potential of “speed-of-thought” business analytics dashboards, with minimum additional work

(32)

Thank You for Attending!

• Thank you for attending this presentation, and more information can be found at http://www.rittmanmead.com

• Contact us at [email protected] or [email protected]

• Look out for our book, “Oracle Business Intelligence Developers Guide” out now!

(33)

In-Memory Analytics with Oracle BI Apps and Oracle Exalytics

UKOUG Analytics Event, London, July 2013

Mark Rittman, Technical Director, Rittman Mead