Oracle Business Intelligence & Data Warehousing Overview

Sankar Bala

DW/BI Specialist [email protected]

469.583.0261

What is intelligent data?

Business Analytics Information Made Powerful

• Automated management reporting • KPIs • Driver based forecasting • Near real-time view of business • Actionable projections • Performance Management / measurable accountability to results

Data Collection Enterprise Data Warehouse

Operations Data Finance Data Sales/Inv Data

Data Made Meaningful

• Standardize business logic and rules • Process controls & data integrity • Single source of data • Official source for financial, sales, operational and management reporting

Source Systems Raw Data

• Legacy systems with a long heritage • Mission critical applications (ERP, Billing, Merchandising, etc…) •

Data is made meaningful when there are standardized definitions, process controls and a focus on data integrity

•

Information is made powerful when this transformed data is used by management to improve business efficiency

Road to Success

Realizing your corporate Strategy will require the integration of people, process and technology

Legacy Environment

Success criteria:

• •

Business Strategy

•Improve Top Line Sales •Improve Margin •Increase customer service •Increase accountability

People

•Actionable Information •Accessibility of Information •Varying skill levels

Business Strategy People Processes Technology

New BI Solution • •

Processes

•Data Quality •Standard Business Language •Standard Metrics & KPI’s

Technology

•Scalability •Stability •Flexibility •Mainstream

Typical BI Environment

Oracle Warehouse Builder Informatica PowerCenter Ab Initio Co-Op IBM Ascential Oracle OLAP Hyperion EssBase Cognos PowerPlay

OLAP

MS OLAP

Engine

Oracle Balance Scorecard Hyperion Scorecard Cognos Metrics Mgr

ETL Tool Analytic Apps

Oracle EPB Hyperion Planning Cognos Planning Business Objects SRC

Lineage Transformation Engine Mining Engine ETLTool Database Query & Analysis Transformation Engine Reporting Engine Name/Address Scrubbing

Oracle Data Miner SAS Enterprise Miner SPSS Clementine Oracle 10g IBM DB2 NCR Teradata MS SQL Server

Enterprise Reporting

Oracle BI Platform / XML Publisher Business Objects XI Cognos 8 BI MicroStrategy 8 Hyperion 9 BI +

P o r t a l

Oracle Portal BEA WebLogic IBM WebSphere MS Share Point

Typical BI Environment

          RDBMS ETL Cleansing Lineage OLAP Data Mining Query & Reporting Enterprise Reporting Analytical Applications Portal

What About ?

         GIS Collaboration Unstructured Data Enterprise Security Identity Management Application Integration Metadata Management Business Activity Monitoring Business Process Flow Management

Only ORACLE Can !

          RDBMS ETL Cleansing Lineage OLAP Data Mining Query & Reporting Enterprise Reporting Analytical Applications Portal          GIS Collaboration Unstructured Data Enterprise Security Identity Management Application Integration Metadata Management Business Activity Monitoring Business Process Flow Management

Simplify

OLAP Engine

•

ETL Tool Lineage

• •

Transformation Engine Mining Engine P ETLTool Database Query & o

Reduce number of moving parts

t Transformation a Engine Reporting l Engine Name/Address Enterprise Scrubbing Reporting

Develop “best practice” architecture

Simplify

OLAP Engine Lineage ETL Tool Analytic Apps Transformation Engine ETLTool Transformation Engine Database Mining Engine Query & Analysis P o r t a l Name/Address Scrubbing Reporting Engine Enterprise Reporting

• Metadata Mgmt • Enterprise Security • GIS • Collaboration • Application Integration • Identity Mgmt • Business Process Flow Mgmt • Unstructured Data

DW/BI Products

• • • • • • •

Oracle Business Intelligence SE-1 SE1 Database ETL Analytic Server Query & Analysis Dashboards Server Administration Targeted to SME Customers

• • • • • • •

Oracle Business Intelligence SE Discoverer Discoverer OLAP Discoverer Plus Discoverer Viewer Reports BI Beans MSFT Office Add-In

• • • • • • • •

Oracle Business Intelligence EE Analytic Server Query & Analysis Dashboards Reporting & Publishing Sense & Respond Offline Analytics MSFT Office Add-In Server Administration Oracle Database ETL, OLAP, Data Mining

Oracle10g Warehouse Builder

What is Oracle Warehouse Builder?

 Enables the extraction, transformation, and loading of data to produce

quality information

in the Oracle database  Protects and leverages customer investment in the Oracle technology stack through

data and metadata integration

Gartner ETL Magic Quadrant, 1H05

Disclaimer: The Magic Quadrant is copyrighted 11 May 2005 by Gartner, Inc. and is reused with permission. The Magic Quadrant is a graphical representation of a marketplace at and for a specific time period. It depicts Gartner's analysis of how certain vendors measure against criteria for that marketplace, as defined by Gartner. Gartner does not endorse any vendor, product or service depicted in the Magic Quadrant, and does not advise technology users to select only those vendors placed in the "Leaders" quadrant. The Magic Quadrant is intended solely as a research tool, and is not meant to be a specific guide to action. Gartner disclaims all warranties, express or implied, with respect to this research, including any warranties of merchantability or fitness for a particular purpose.

Magic Quadrant for Extraction, Transformation and Loading, 1H05, T. Friedman, B.Gassman, May 2005

Sample Customers

2

Design and Deployment With Oracle Warehouse Builder

1 Design Target Objects 3 Map & Transform 4 Generate & Validate Extract Source Metadata 5 Deploy Code

• • • • Relational Flat Files Applications Mainframe • • • • Oracle9i/10g OLAP Flat file BI tools

6 Run

10gR2 themes

 Enabling Quality Information  Enabling Business Intelligence  Enabling Expertise capture

Enabling Quality Information

Quality Monitoring Capture Metadata Quality Assessment

Information

Execute Profile Data Quality Transformation Deploy Metadata Management Data Rules Process Flow Data Flow Quality Design

Data Quality in OWB

  Data Quality functionalities are

integrated

into ETL processes – – Disciplined approach to Data Quality, not an afterthought Data Quality is modeled, executed and audited just like any other transformation Consists of – Data Profiling – – Name and Address Cleansing Match-Merge

   

Data Profiling

 Allow users to analyze data – discover the structural content – – capture the semantics Identify anomalies and outliers Automatically derive business rules and maps to clean data Derive Quality Indices (e.g. 6-sigma) Auditors monitor quality on an ongoing basis Integrated as part of ETL

Columns Pattern

Hdate SSN Phone Id Contact

Format

YYYY-MM-DD Date 9(3)-9(2)-9(4) US Social Security Number 9(3)-9(3)-9(4) 9(5) US Phone Number [email protected]

Email

Format % Compliant

82% 3% 16% N/A

Unique

Yes 99% No

Data Profiling

Enabling Business Intelligence

BI Defs

Design Derivation Preview Metadata Management Generation Deploy

Enabling Business Intelligence – New in 10gR2

 Relational and Dimensional Data Object Designer  Business Intelligence Object Derivation

Data Object Design One Editor

   Dimensions, cubes, tables, views, complex objects, … Support for Star, Snowflake, Skip-Level, calculated measures, … One editor for creation, configuration, validation, code generation, impact analysis, deployment, data viewing

Data Object Editor

Business Intelligence Object Derivation

  Create and Derive Business intelligence objects  Oracle OLAP Cubes & Dimensions  OracleBI Discoverer EUL  OracleBI Beans Reports Included in Lineage and Impact analysis!

Designer Relational & OLAP Catalogs, 10g Scheduler, Workflow

z

Warehouse Builder Discoverer BI Beans

Enabling Expertise Capture

Expertise BI Defs

Enabling Business Intelligence

Information

Enabling Quality Information

Enabling Expertise capture – New in 10gR2

    Re-use OWB Components to build your OWN application!

– Define best practices & directed guidance Run Standalone or within OWB Design-time Declarative Definition & Guided execution E.g. “Data-Mover” Expert – Offers users the ability to easily copy data from:  Table-to-Table  File-to-Table

Expert Editor

Sources & Targets

     Sources

Oracle

–

Tables, Views, MViews, Queues, External Tables, Table Functions, Streams, PL/SQL API’s, Sqlloader… DB2, Sybase, SQLServer, Informix, Mainframes, … (Oracle Transparent Gateways) ODBC Flat Files Applications

–

Oracle Apps

–

Peoplesoft

– –

SAP Custom SQL App

Targets     

Oracle

–

Tables, Queues, Table Functions, Streams, PL/SQL API’s, … DB2, Sybase, SQLServer, Informix, Mainframes, … (Oracle Transparent Gateways) ODBC Flat Files Applications

– –

Oracle Apps Peoplesoft

–

Custom SQL App

Enhanced ERP Integration

  Metadata adapters for: – – Oracle EBusiness Suite Peoplesoft Enhancements to SAP adapter: – – – – Direct ABAP processing ABAP Join Ranking Variable support Sub-Process flow integration

Transportable Modules

 Move large data volumes quickly and easily between Oracle instances – Perform fast bulk loads – – Performance & usability gains over traditional technologies (DB Links, Flat Files,…) Technology varies according to source:  9

i

source - Transportable Tablespaces  10g source – Data Pump

Scheduled Process Execution

 Define complex custom calendars to control process execution – E.g.   Working Days Holidays  Standard iCal format

User-Defined Objects & Icons

   User-Definable: – – – Objects Associations Properties ALL in the OWB repository!

Custom icons for easy recognition Access full metadata services of OWB e.g. impact analysis

Impact Analysis on UDO’s

New Segmentation of OWB Functionality

Enterprise ETL

Enable Large Scale, Complex Deployments

Data Quality

Convert Data to Quality Information on an ongoing basis

ERP/CRM Connectivity

Extract from common ERP/CRM (SAP etc.)

Core ETL Features

Easily load Data into Oracle Database

What are the Core ETL Features?

OWB 10gR2 Core ETL = OWB 10gR1 Functionality – SAP connector + Enhancements + New Features

Core ETL Features

Easily load Data into Oracle Database

Core ETL Functionality

        Import Metadata. source Non-Oracle and Flat-files Target Oracle (relational & Dimensional) and Flat-Files Support for Oracle OLAP and Relational Partitioning Debugging, Mapping (Aggregation, De-duplication, Joining, etc.

Name & Address Cleansing Interface to Partners Basic Process Flows Execution on RAC/Grid HTML metadata reports, Scripting and SQL views

Core ETL New Features

          Experts Non-Oracle Target support Graphical Data Object Editor – Relational, Dimensional, EUL Remote Control Center (Runtime Service) Data Viewers (Relational & Dimensional) in maps Fiscal Time Dimension incl. Data loaders Integrated OMB*Plus Client Asynchronous Job Deployment & execution User-defined data types Runtime metadata transformations

Core ETL New Features

 Usability – – – – New Common Editor Framework Modules as source &/or target Security Management Printing – – – – – – Location Management Install Simplified/unified Repository “Right-mouse-click” Deployment Unified Browser Reporting environment BI Definition Derivation

Core ETL Enhancements UI Look & Feel Improvements

10g Release 1 10g Release 2

Core ETL Enhancements

 Dimensional   Calculated Measure Generation Advanced Cube Aggregation MV’s for ROLAP, AGGMAPS for MOLAP  Oracle OLAP 10g R2 via AWXML - Sparsity - Compressed Cube - Partitioning     Dimension Roles Dimension Import Surrogate Key Management Auto and manual binding for relational dimensions

Core ETL Enhancements

  Data Quality  Improved Match/Merge functionality  Parallelized Name & Address cleansing Other    Advanced Aggregation Options Physical Storage Data Object properties Scripting support for advanced deployment (upgrade, replace)    Materialized views Multi-File support for SQL*Loader Forced ‘Single Flavor Codegen’ (enables Analytic SQL expressions)

Other New OWB Functionality shipped as Database Options

Enterprise ETL Option

Enable Large Scale, Complex Deployments

Data Quality Option

Convert Data to Quality Information on an ongoing basis

ERP/CRM Connectors

Extract from common ERP/CRM (SAP etc.)

Core ETL Features

Easily load Data into Oracle Database

Enterprise ETL Option



Only available with Enterprise Edition of database Enterprise ETL Option

Enable Large Scale, Complex Deployments

Core ETL Features

Easily load Data into Oracle Database

Enterprise ETL Option

    Includes  Improves performance & scalability of ETL jobs   Interactive Lineage & Impact Analysis User-defined Objects, Relationships and modules Advanced Process Flow Functionality Change propagation  XML file as Target Pluggable Mappings Support large Business Intelligence deployments – Business Intelligence Beans Generation – Discoverer EUL direct deployment

Data Quality Option



Only available with Enterprise Edition of database Enterprise ETL Option

Enable Large Scale, Complex Deployments

Data Quality Option

Convert Data to Quality Information on an ongoing basis

Core ETL Features

Easily load Data into Oracle Database

Data Quality Option

    Discover Data Anomalies Identify Data Rules Auto-correction of Data Anomalies Audit ETL jobs (and take action if data quality is too low)

CRM/ERP Connector Option



Only available with Enterprise Edition of database Enterprise ETL Option

Enable Large Scale, Complex Deployments

Data Quality Option

Convert Data to Quality Information on an ongoing basis

ERP/CRM Connectors

Extract from common ERP/CRM (SAP etc.)

Core ETL Features

Easily load Data into Oracle Database

CRM/ERP Connector Option

 Extraction from ERP/CRM sources    Oracle E-Business Suite Peoplesoft SAP

Summary: OWB 10gR2

Enabling Quality Information

 Data Profiling  New Data Sources and Targets  Enhanced ERP Integration  Transportable Modules  Scheduled Process Execution  User-defined Objects & Icons

Enabling Business Intelligence

 Relational and Dimensional Data Object Designer  Business Intelligence Object Derivation

Enabling Expertise Capture

 Experts

More Information

 http://www.oracle.com/technology/products/owb – – Collateral Software – – – – – Component Exchange Discussion forum SDK Customer Testimonials Partner Information

Demo

1.

2.

3.

4.

5.

6.

7.

8.

Data Sources Data Targets Mappings Process Flows Configuration Metadata Integration Experts Data Quality (Data Profile) 9.

BI Integration 10.

Scalability/Performance