Oracle Business Intelligence & Data Warehousing Overview Sankar Bala DW/BI Specialist Sankar. bala@oracle. com 469. 583. 0261
What is intelligent data? Inf or mat ion Val ue Process Controls and Data Integrity 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 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 Business Strategy People New BI Solution Processes Technology Success criteria: • Business Strategy • Improve Top Line Sales • Improve Margin • Increase customer service • Increase accountability • People • Actionable Information • Accessibility of Information • Varying skill levels • Processes • Data Quality • Standard Business Language • Standard Metrics & KPI’s • Technology • Scalability • Stability • Flexibility • Mainstream
Typical BI Environment Oracle Warehouse Builder Informatica Power. Center Ab Initio Co Op IBM Ascential Oracle OLAP Hyperion Ess. Base Cognos Power. Play MS OLAP Engine ETL Tool Transformation Engine Mining Engine Query & Analysis Database Reporting Engine Oracle 10 g IBM DB 2 Enterprise NCR Teradata Reporting MS SQL Server Oracle BI Platform / XML Publisher Oracle Data Miner Business Objects XI SAS Enterprise Miner Cognos 8 BI SPSS Clementine Micro. Strategy 8 Hyperion 9 BI + Name/Address Scrubbing Lineage Analytic Apps Transformation Engine ETLTool Oracle Balance Scorecard Hyperion Scorecard Cognos Metrics Mgr Oracle EPB Hyperion Planning Cognos Planning Business Objects SRC P o r t a l Oracle Portal BEA Web. Logic IBM Web. Sphere 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 Lineage • Eliminate redundancy ETL Tool Analytic Apps Transformation Engine ETLTool Mining Engine Query & Analysis P o r t a l • Reduce number of moving parts Transformation Engine Name/Address Scrubbing Database Reporting Engine Enterprise Reporting • Develop “best practice” architecture
Simplify OLAP Engine ETL Tool Analytic Apps Transformation Engine ETLTool Transformation Engine Name/Address Scrubbing Lineage Mining Engine Query & Analysis Database Reporting Engine P o r t a l Enterprise Reporting • Metadata Mgmt • Enterprise Security • Collaboration • Application Integration • GIS • Identity Mgmt • Business Process Flow Mgmt • Unstructured Data
DW/BI Products Oracle Business Intelligence SE-1 Oracle Business Intelligence SE Oracle Business Intelligence EE • • • • • • SE 1 Database ETL Analytic Server Query & Analysis Dashboards Server Administration Targeted to SME Customers Discoverer OLAP Discoverer Plus Discoverer Viewer Reports BI Beans MSFT Office Add-In Oracle Database ETL, OLAP, Data Mining Analytic Server Query & Analysis Dashboards Reporting & Publishing Sense & Respond Offline Analytics MSFT Office Add-In Server Administration
Oracle 10 g 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, 1 H 05 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 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, 1 H 05, T. Friedman, B. Gassman, May 2005
Sample Customers
Design and Deployment With Oracle Warehouse Builder 1 Design Target Objects 3 Map & Transform 4 2 • • 5 Extract Source Metadata Relational Flat Files Applications Mainframe Generate & Validate Deploy Code • • 6 Run Oracle 9 i/10 g OLAP Flat file BI tools
10 g. R 2 themes Ÿ Enabling Quality Information Ÿ Enabling Business Intelligence Ÿ Enabling Expertise capture
Enabling Quality Information Quality Monitoring Da Information ta Profile Data Execute Deploy Quality Transformation Quality Assessment Capture Metadata Management Process Flow Data Rules 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 YYYY MM DD 9(3) 9(2) 9(4) 9(3) 9(4) 9(5) A@A. A Format Date US Social Security Number US Phone Number <Not Found> Email Format % Unique Compliant 82% 3% 16% N/A Yes 99% No
Data Profiling
Enabling Business Intelligence Inf orm ati on Design Derivation BI Defs Preview Metadata Management Deploy Generation
Enabling Business Intelligence – New in 10 g. R 2 Ÿ 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 Ÿ Oracle. BI Discoverer EUL Ÿ Oracle. BI Beans Reports Ÿ Included in Lineage and Impact analysis! Designer Relational & OLAP Catalogs, 10 g Scheduler, Workflow z Warehouse Builder Discoverer BI Beans
Enabling Expertise Capture Da ta Expertise BI Defs Enabling Business Intelligence Information Enabling Quality Information
Enabling Expertise capture – New in 10 g. R 2 Ÿ 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… Ÿ DB 2, Sybase, SQLServer, Informix, Mainframes, … (Oracle Transparent Gateways) Ÿ ODBC Ÿ Flat Files Ÿ Applications – Oracle Apps – Peoplesoft – SAP – Custom SQL App Ÿ Oracle Tables, Queues, Table Functions, Streams, PL/SQL API’s, … DB 2, 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 Ÿ 10 g source – Data Pump
Scheduled Process Execution Ÿ Define complex custom calendars to control process execution – E. g. Ÿ Working Days Ÿ Holidays Ÿ Standard i. Cal 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 Data Quality Enable Large Scale, Complex Deployments Convert Data to Quality Information on an ongoing basis ERP/CRM Connectivity Extract from common ERP/CRM (SAP etc. ) C o r e E T L F e a tu r e s Easily load Data into Oracle Database
What are the Core ETL Features? OWB 10 g. R 2 Core ETL = OWB 10 g. R 1 Functionality – SAP connector + Enhancements + New Features C o r e E T L F e a tu r e s 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 10 g Release 1 10 g Release 2
Core ETL Enhancements Ÿ Dimensional Ÿ Calculated Measure Generation Ÿ Advanced Cube Aggregation MV’s for ROLAP, AGGMAPS for MOLAP Ÿ Oracle OLAP 10 g R 2 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. ) C o r e E T L F e a tu r e s Easily load Data into Oracle Database
Enterprise ETL Option Ÿ Only available with Enterprise Edition of database Enterprise ETL Option Enable Large Scale, Complex Deployments C o r e E T L F e a tu r e s 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 C o r e E T L F e a tu r e s 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. ) C o r e E T L F e a tu r e s Easily load Data into Oracle Database
CRM/ERP Connector Option Ÿ Extraction from ERP/CRM sources Ÿ Oracle E Business Suite Ÿ Peoplesoft Ÿ SAP
Summary: OWB 10 g. R 2 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
Скачать презентацию Oracle Business Intelligence Data Warehousing Overview Sankar
0ef6ff2213ca2353455603fad9bdcb2b.ppt