Oracle Business Intelligence & Data Warehousing Overview

egyptiannorweiganInternet and Web Development

Oct 31, 2013 (3 years and 7 months ago)

59 views

Oracle Business Intelligence &
Data Warehousing Overview



Sankar Bala

DW/BI Specialist

Sankar.bala@oracle.com

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 Made
Meaningful


Standardize business logic and rules


Process controls & data integrity


Single source of data


Official source for financial, sales,
operational and management
reporting

Raw Data


Legacy systems with a long heritage


Mission critical applications (ERP,
Billing, Merchandising, etc…)

Data Collection

Enterprise Data Warehouse

Source Systems

Sales/Inv

Data

Process Controls and Data Integrity

Information Value


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

Operations

Data

Finance

Data

Road to Success

Success criteria:


Business Strategy


Improve Top Line Sales


Improve Margin


Increase customer service


Increase accountability


People


Actionable Information


Accessibility of Information


Varying skill levels


Legacy

Environment

New BI
Solution


Processes


Data Quality


Standard Business Language


Standard Metrics & KPI’s


Technology


Scalability


Stability


Flexibility


Mainstream

People

Business Strategy

Processes

Technology

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

Lineage

Query &

Analysis

Name/Address

Scrubbing

ETLTool

Transformation

Engine

Mining

Engine

Database

OLAP

Engine

Analytic Apps

Reporting

Engine

Enterprise

Reporting

P
o
r
t
a
l

ETL Tool

Transformation

Engine

Typical BI Environment

Oracle Portal

BEA WebLogic

IBM WebSphere

MS Share Point

Oracle Balance Scorecard

Hyperion Scorecard

Cognos Metrics Mgr

Oracle Warehouse Builder

Informatica PowerCenter

Ab Initio Co
-
Op

IBM Ascential

Oracle BI Platform / XML Publisher

Business Objects XI

Cognos 8 BI

MicroStrategy 8

Hyperion 9 BI +

Oracle 10g

IBM DB2

NCR Teradata

MS SQL Server

Oracle EPB

Hyperion Planning

Cognos Planning

Business Objects SRC

Oracle OLAP

Hyperion EssBase

Cognos PowerPlay

MS OLAP

Oracle Data Miner

SAS Enterprise Miner

SPSS Clementine

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

Lineage

Query &

Analysis

Name/Address

Scrubbing

ETLTool

Analytic Apps

Reporting

Engine

Enterprise

Reporting

P
o
r
t
a
l

ETL Tool

Transformation

Engine

Database

Transformation

Engine

Mining

Engine

OLAP

Engine



Eliminate redundancy



Reduce number of moving parts



Develop “best practice” architecture

Simplify

Lineage

Query &

Analysis

Name/Address

Scrubbing

ETLTool

Analytic Apps

Reporting

Engine

Enterprise

Reporting

P
o
r
t
a
l

ETL Tool

Transformation

Engine

Database

Transformation

Engine

Mining

Engine

OLAP

Engine



Metadata Mgmt



Enterprise Security



Application Integration



Collaboration



Identity Mgmt



Business Process Flow Mgmt



Unstructured Data



GIS

DW/BI Products

Oracle Business

Intelligence EE



Analytic Server


Query & Analysis


Dashboards


Reporting & Publishing


Sense & Respond


Offline Analytics


MSFT Office Add
-
In


Server Administration

Oracle Business

Intelligence SE



Discoverer


Discoverer OLAP


Discoverer Plus


Discoverer Viewer


Reports


BI Beans


MSFT Office Add
-
In

Oracle Business

Intelligence SE
-
1



SE1 Database


ETL


Analytic Server


Query & Analysis


Dashboards


Server Administration


Targeted to SME
Customers

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 measu
re
against criteria
for that marketplace, as defined by Gartner. Gartner does not endorse any vendor, product or service depicted in the Magic Qu
adr
ant, 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

re
search, 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

Design and Deployment With
Oracle Warehouse Builder

Design Target Objects

1

Map & Transform

3


Oracle9i/10g


OLAP


Flat file


BI tools

Generate & Validate

4

Deploy Code

5


Relational


Flat Files


Applications


Mainframe

Run

6

2

Extract Source
Metadata

10
g
R2 themes


Enabling Quality Information



Enabling Business Intelligence



Enabling Expertise capture




Metadata

Management

Enabling Quality Information

Capture

Metadata

Process

Flow

Profile

Data

Data


Rules

Data

Flow

Deploy

Execute

Quality

Assessment

Quality

Design

Quality

Transformation

Quality

Monitoring

Information

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

Format

Format %

Compliant

Unique

Hdate

YYYY
-
MM
-
DD

Date

82%

SSN

9(3)
-
9(2)
-
9(4)

US Social Security Number

3%

Phone

9(3)
-
9(3)
-
9(4)

US Phone Number

16%

Id

9(5)

<Not Found>

N/A

Yes

Contact

A@A.A

Email

99%

No

Data Profiling

Metadata

Management

Enabling Business Intelligence

Derivation

Preview

BI Defs

Generation

Deploy

Design

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

z

Relational & OLAP

Catalogs,

10g Scheduler, Workflow

Warehouse

Builder


Discoverer

BI Beans

Designer


Create and Derive Business
intelligence objects


Oracle OLAP Cubes &
Dimensions


OracleBI Discoverer
EUL


OracleBI Beans Reports


Included in Lineage and
Impact analysis!

Enabling Expertise Capture

Enabling

Business

Intelligence

BI Defs

Enabling

Quality

Information

Information

Expertise

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



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

Sources & Targets

Sources

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

Core ETL Features


Easily load Data into Oracle Database

Data Quality


Convert Data to

Quality Information

on an ongoing basis

Enterprise ETL



Enable Large Scale,

Complex Deployments

ERP/CRM

Connectivity


Extract from common

ERP/CRM (SAP etc.)

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



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 Functionality


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
New Features


Core ETL
Enhancements

UI Look & Feel Improvements

10g Release 2

10g Release 1


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)

Core ETL
Enhancements

Enterprise ETL

Option


Enable Large Scale,

Complex Deployments

Other New OWB Functionality
shipped as Database Options

Core ETL Features


Easily load Data into Oracle Database

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


Only available with Enterprise Edition of database

Enterprise ETL Option

Enterprise ETL

Option


Enable Large Scale,

Complex Deployments

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

Enterprise ETL Option

Core ETL Features


Easily load Data into Oracle Database


Only available with Enterprise Edition of database

Data Quality Option

Enterprise ETL

Option


Enable Large Scale,

Complex Deployments

Data Quality

Option

Convert Data to

Quality Information

on an ongoing basis


Discover Data Anomalies


Identify Data Rules


Auto
-
correction of Data Anomalies


Audit ETL jobs (and take action if data quality is too low)

Data Quality Option

Enterprise ETL

Option


Enable Large Scale,

Complex Deployments


Only available with Enterprise Edition of database

CRM/ERP Connector Option

Core ETL Features


Easily load Data into Oracle Database

Data Quality

Option

Convert Data to

Quality Information

on an ongoing basis

ERP/CRM

Connectors


Extract from common

ERP/CRM (SAP etc.)


Extraction from ERP/CRM sources



Oracle E
-
Business Suite


Peoplesoft


SAP


CRM/ERP Connector Option

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

2.
Data Targets

3.
Mappings

4.
Process Flows

5.
Configuration

6.
Metadata Integration

7.
Experts

8.
Data Quality (Data Profile)

9.
BI Integration

10.
Scalability/Performance