Transcript Oracle Warehouse Builder - An Overview
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
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