owb_v2.ppt
DESCRIPTION
TRANSCRIPT
Oracle Business Intelligence & Data Warehousing Overview
Sankar BalaDW/BI [email protected]
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 CollectionEnterprise Data Warehouse
Source Systems
Sales/InvData
Pro
cess
Co
ntr
ols
an
d D
ata
Inte
gri
ty
Info
rmat
ion
Val
ue
• 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
• 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
OperationsData
FinanceData
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
Realizing your corporate Strategy will require the integration of people, process and technology
Lineage
Query & Analysis
Name/AddressScrubbing
ETLTool
TransformationEngine
MiningEngine
Database
OLAPEngine
Analytic Apps
ReportingEngine
EnterpriseReporting
Portal
ETL Tool
TransformationEngine
Typical BI Environment
Oracle PortalBEA WebLogic
IBM WebSphereMS Share Point
Oracle Balance ScorecardHyperion ScorecardCognos Metrics Mgr
Oracle Warehouse BuilderInformatica PowerCenter
Ab Initio Co-OpIBM Ascential
Oracle BI Platform / XML PublisherBusiness Objects XI
Cognos 8 BIMicroStrategy 8Hyperion 9 BI +
Oracle 10gIBM DB2
NCR TeradataMS SQL Server
Oracle EPBHyperion PlanningCognos Planning
Business Objects SRC
Oracle OLAPHyperion EssBaseCognos PowerPlay
MS OLAP
Oracle Data MinerSAS 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/AddressScrubbing
ETLTool
Analytic Apps
ReportingEngine
EnterpriseReporting
Portal
ETL Tool
TransformationEngine
Database
TransformationEngine
MiningEngine
OLAPEngine
• Eliminate redundancy
• Reduce number of moving parts
• Develop “best practice” architecture
Simplify
Lineage
Query & Analysis
Name/AddressScrubbing
ETLTool
Analytic Apps
ReportingEngine
EnterpriseReporting
Portal
ETL Tool
TransformationEngine
Database
TransformationEngine
MiningEngine
OLAPEngine
• Metadata Mgmt • Enterprise Security • Application Integration• Collaboration
• Identity Mgmt • Business Process Flow Mgmt • Unstructured Data• GIS
DW/BI ProductsOracle 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 DatabaseETL, 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
Design and Deployment With Oracle Warehouse Builder Design Target Objects Design Target Objects 1
Map & Transform Map & Transform 3
• Oracle9i/10g• OLAP• Flat file• BI tools
Generate & Validate Generate & Validate 4
Deploy CodeDeploy Code5
• Relational• Flat Files• Applications• Mainframe
RunRunRunRun66
2 Extract Source Extract Source MetadataMetadata
10gR2 themes
Enabling Quality Information
Enabling Business Intelligence
Enabling Expertise capture
MetadataManagement
Enabling Quality Information
CaptureMetadata
ProcessFlow
ProfileData
Data Rules
DataFlow
Deploy
Execute
QualityAssessment
QualityDesign
QualityTransformation
QualityMonitoring
Information
Data
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 ETLColumns Pattern Format Format %
CompliantUnique
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 YesContact [email protected] Email 99% No
Data Profiling
MetadataManagement
Enabling Business Intelligence
Derivation
Preview
BI Defs
Generation
Information
Deploy
Design
Enabling Business Intelligence – New in 10gR2
Relational and Dimensional Data Object Designer
Business Intelligence Object Derivation
Data Object DesignOne 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 ObjectDerivation
z
Relational & OLAP Catalogs,
10g Scheduler, Workflow
WarehouseBuilder
DiscovererBI 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
EnablingBusiness
IntelligenceBI Defs
EnablingQuality
Information
Data
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 & TargetsSources 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:
9i 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 FeaturesCore ETL Features Easily load Data into Oracle Database
Data QualityData Quality
Convert Data to Quality Information on an ongoing basis
Enterprise ETLEnterprise ETL
Enable Large Scale,Complex Deployments
ERP/CRM ERP/CRM ConnectivityConnectivity
Extract from commonERP/CRM (SAP etc.)
What are the Core ETL Features?OWB 10gR2 Core ETL =
OWB 10gR1 Functionality– SAP connector+ Enhancements+ New Features
Core ETL FeaturesCore 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 EnhancementsUI Look & Feel Improvements
10g Release 210g 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 ETLEnterprise ETLOption Option
Enable Large Scale,Complex Deployments
Other New OWB Functionality shipped as Database Options
Core ETL FeaturesCore ETL Features Easily load Data into Oracle Database
Data QualityData QualityOptionOption
Convert Data to Quality Information on an ongoing basis
ERP/CRM ERP/CRM ConnectorsConnectors
Extract from commonERP/CRM (SAP etc.)
Core ETL FeaturesCore ETL Features Easily load Data into Oracle Database
Only available with Enterprise Edition of database
Enterprise ETL Option
Enterprise ETLEnterprise ETLOption 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 FeaturesCore ETL Features Easily load Data into Oracle Database
Only available with Enterprise Edition of database
Data Quality Option
Enterprise ETLEnterprise ETLOption Option
Enable Large Scale,Complex Deployments
Data QualityData QualityOptionOption
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 ETLEnterprise ETLOption Option
Enable Large Scale,Complex Deployments
Only available with Enterprise Edition of database
CRM/ERP Connector Option
Core ETL FeaturesCore ETL Features Easily load Data into Oracle Database
Data QualityData QualityOptionOption
Convert Data to Quality Information on an ongoing basis
ERP/CRM ERP/CRM ConnectorsConnectors
Extract from commonERP/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 Targets3. Mappings4. Process Flows5. Configuration6. Metadata Integration7. Experts8. Data Quality (Data Profile)9. BI Integration10.Scalability/Performance