Download - PTS BI Apps Workshop - Day1
<Insert Picture Here>
Oracle Business Intelligence ApplicationsTechnical Hands-on Workshop – Day 1
2
Workshop Goals
The PTS Oracle Business Intelligence Applications Workshop provides a business and technical overview of the BI Applications platform as well as an understanding for the different offerings.
Participants will be familiarized with the procedures needed to properly install and configure the BI applications and gain substantial hands-on experience using BI Application components including Informatica, Data Warehouse Administration Console (DAC), Oracle BI Applications Warehouse (OBAW), OBI EE Dashboards and OBI EE Answers in a controlled lab environment
3
Oracle BI Applications – Order Management Analytics Overview5:004:30Oracle Business Analytics Warehouse (OBAW) Overview4:303:30DAC Execution (Order Management) – Warehouse Load Lab5:303:00DAC (Order Management) Configuration Lab3:001:30Lunch1:0012:15DAC Overview12:1511:45Oracle BI Applications Installation and Configuration Lab (Viewlet)�11:4511:00Oracle BI Applications Overview11:0010:00Lab Environment Overview10:009:30Welcome, Overview, and Introductions and Environment Setup Lab9:309:00Breakfast 9:008:30Agenda ItemAgenda ItemAgenda ItemAgenda ItemEndEndEndEndStartStartStartStart
Agenda – Day 1
4
Agenda – Day 2
Workshop Wrap up / Evaluations5:004:30
DAC Execution and Warehouse Load Confirmation Lab (Custom ETL) – SQL*Plus4:303:00
DAC Configuration Lab for Custom ETL3:001:30Informatica Lab – Custom ETL (Viewlet)�1:3012:30Lunch12:3012:00OBAW Customization Overview12:0011:30Informatica Overview Lab (Viewlet)�11:3010:30Warehouse Load Confirmation Lab – BI EE10:309:30DAC Execution Review9:309:00Breakfast / Questions & Answers (Review Day 1)�9:008:30Agenda ItemAgenda ItemAgenda ItemAgenda ItemEndEndEndEndStartStartStartStart
5
Workshop Site Information
6
Instructor and Workshop Participants
• Who are you?• Name
• Company
• Role
• What is your prior experience?• Business Intelligence
• Data Warehouse Design
• Database Design and Administration
• How do you expect to benefit from this course?
7
We drive customer success by ensuring our partners:
1) Are aware of our product positioning and roadmaps,
2) Have adopted best practices and technical skills for
successful delivery of our products,
3) Can effectively scale the delivery of our products through
repeatable integrated solutions, centers of excellence, and
cost effective POCs.
Oracle Platform Technology SolutionsBI and EPM Team Mission
8
Programmatic Investment for Partnership Success
• Webinars
• Hands-on Workshops
• Center’s of Excellence
OBI EE+ Enablement
• Baseline Platform Reference – e.g. Integration, LOB, etc.
• Industry specific exploitations of platform
Solutions• POC Sandbox
• OBI EE+ Demonstration
• Early Adoption Engagements to Insure Success
BI Factory
• Architecture
• Design
• Build ���� to self sufficiency
Consulting Services
IncrementalIncremental
SuccessfulSuccessful
RepeatableRepeatable
OBI EE+OBI EE+
EngagementsEngagements
9
PTS OBI Workshops Available / PlannedSI Partner Technical Training
2 Days
2.5 Days
2 Days
1.5 Days
DURATION
AVAILABLEArchitecture, Hands-on Labs,
Solution PuzzlersOBI EE+ Advanced
AVAILABLEBI Applications, Architecture, ETL,
Metadata Mappings, Deployment Best Practices
OBI Applications
AVAILABLEAdvanced Essbase Technical
WorkshopEssbase Advanced
OBI EE+ & Essbase
WORKSHOP NAME
AVAILABLEIntroductory Hands-On Technical
Workshop
STATUSDESCRIPTION
10
<Insert Picture Here>
Presentation & Lab Exercise:Lab Environment
11
Setting up Lab Environment
• Pre-requisites• 2 GB Memory
• 22 GB Free HDD space
• VMware installed (Player, Server or Workstation) �
• Lab 0.1 Lab Environment and Installations – Main Steps• Copy Virtual Machine (VM) files to hard drive:
– BIApps_Student_VM.part01.exe
– BIApps_Student_VM.part02.rar
– BIApps_Student_VM.part03.rar
– BIApps_Student_VM.part04.rar
• Uncompress VM: run BIApps_Student_VM.part01.exe
• Open VM in VMware Player, Server or Workstation
• Login to VM: oracle/oracle
• Duration: 1.5 Hours
12
Lab Environment Features
• The Base OS is Oracle Enterprise Linux 4 running inside a VMware Virtual Machine
• Contains:• Oracle 10g Database Release 2 Enterprise Edition
• Oracle BI EE 10.1.3.3.3
• Oracle BI Applications 7.9.5
• Informatica PowerCenter 8.1.1 SP4
13
Lab Environment Features
• All services except for the 10g Database require manual start and stopping.
• This is to minimize strain on the virtual memory allocation vice the actual physical memory of the Host OS
14
Oracle 10g Release 2 Enterprise Edition
• Lives at:
/home/oracle/oracle/product/10.2.0/db_1/bin
• Useful tools• DB Console (Oracle Enterprise Manager in a Web Browser) �
• The ever reliable SQL*Plus
• The Database contains• An Oracle E-Business Suite 11.5.10 subset Schema
• The OBAW – Oracle BI Applications Data Warehouse
• The DAC Repository Metadata Schema
• The Informatica Repository Metadata Schema
15
Oracle BI EE 10.1.3.3.3 and Oracle BI Applications 7.9.5
• BI EE Lives at:• /biapps/OracleBI and /biapps/OracleBIData
• BI Applications Live at:• /biapps/OracleBI/DAC
16
Informatica PowerCenter 8.1.1 SP4
• Lives at:• /biapps/Informatica
• In this version of Informatica PowerCenter the Repository Server and Informatica Server have been integrated into a single Informatica Service that runs two subordinate services:• Informatica Repository Service: This component manages the
access and update to the Repository content
• Informatica Integration Service: This component coordinates theexecution of workflows and related programmatic components
• The Administration Console runs under Tomcat and lives at: /biapps/Informatica/PowerCenter8.1.1/server/tomcat/bin
17
What is not on the VM
• Oracle BI Applications DAC Client (in an official supported capacity – it is there but it works “unofficially”)�
• Informatica Client Tools
• Oracle BI EE Administration Tool
• All of the products listed above are Windows only at the moment and unfortunately we are prevented from distributing a Windows VMware Image.
18
<Insert Picture Here>
Presentation:Oracle BI Applications Overview
19
“Through 2009 there will be a swing toward buying pre-packaged analytic applications… (0.7 probability)”
What Gartner is Saying
Source: “Business Intelligence Scenario: Pervasive BI,” Gartner Symposium ITxpo 2006
20
Oracle is the Worldwide Leader
#1 in BI/Analytic Applications
- IDC
“One of the most comprehensive and innovative BI platforms…”
- Gartner
#1 in DW Tools
- IDC
Oracle BI EE Suite
Oracle BI Applications
Oracle Data Warehousing
21
BI Approaches: Tools
Oracle BI Platform(custom metadata) �
Tools & Build Approach
Siebel
SAP
Other Sources
Custom
ETL
Custom Built DW
Oracle
No prebuilt content
These steps These steps
require different require different
types of BI and types of BI and
DW technologyDW technology
License an *ETL tool to move data from operational systemsLicense an *ETL tool to move data from operational systemsto this DWto this DW
License interactive user access toolsLicense interactive user access tools
License / create information delivery toolsLicense / create information delivery tools
These steps These steps
typically take a long typically take a long
time to perfect as time to perfect as knowledge of best knowledge of best
practices is learnedpractices is learned
Research analytic needs of each user communityResearch analytic needs of each user community
Set up user security & visibility rulesSet up user security & visibility rules
Perform QA & performance testingPerform QA & performance testing
Manage onManage on--going changes/upgradesgoing changes/upgrades
These steps These steps require significant require significant
resources with resources with specialized skills / specialized skills /
expertiseexpertise
Develop detailed understanding of operational data sourcesDevelop detailed understanding of operational data sources
Build ETL programs for every data sourceBuild ETL programs for every data source
Build analytics for each audienceBuild analytics for each audience
Design a data warehouse by subject areaDesign a data warehouse by subject area
ETL= Execute, Transfer and Load Data
22
BI Approaches: Tools vs Applications
Prebuilt BI Content
Oracle BI Platformw/ Prebuilt Metadata
Prebuilt BI Applications Approach
Siebel
SAP
Other Sources
Prebuilt
ETL
Prebuilt DW
Oracle
Oracle BI Platform(custom metadata) �
Tools & Build Approach
Siebel
SAP
Other Sources
Custom
ETL
Custom Built DW
Oracle
No prebuilt content
23
PackagedPackagedETL MapsETL Maps
UniversalUniversalAdaptersAdapters
IVR, ACD, CTIIVR, ACD, CTIHyperionHyperionMS ExcelMS Excel
Other Data SourcesOther Data Sources
Ad-hoc Analysis
ProactiveDetectionand Alerts
MS OfficePlug-in
Reporting & Publishing
Interactive Dashboards
DisconnectedAnalytics
WebServices
Oracle BI Applications
SalesService &Contact Center
MarketingOrder
Management& Fulfillment
Supply Chain
FinancialsHuman
Resources
Oracle BI ApplicationsMulti-source Analytic Apps Built on BI Suite EE
Oracle BI Apps built on
Oracle BI EE Suite• Prebuilt Hierarchies, Drill Paths, Security, dashboards, reports
• Common Enterprise Information Model
• Based on industry and analytic best practices
24
Oracle BI ApplicationsMulti-Source Analytics with Single Architecture
Travel& Trans
AutoComms& Media
ComplexMfg
ConsumerSector
EnergyFinancialServices
HighTech
Insurance& Health
LifeSciences
Public Sector
Other Operational & Analytic Sources
Oracle BI Suite Enterprise Edition Plus
Prebuilt adapters:
SalesService &Contact Center
MarketingOrder
Management& Fulfillment
Supply Chain
FinancialsHuman
Resources
PipelineAnalysis
TriangulatedForecasting
Sales Team Effectiveness
Up-sell / Cross-sell
Cycle TimeAnalysis
Lead Conversion
Employee Productivity
Compensation Analysis
HR Compliance Reporting
WorkforceProfile
TurnoverTrends
Return on Human Capital
A/R & A/PAnalysis
GL / BalanceSheet Analysis
Customer& Product
Profitability
P&L Analysis
ExpenseManagement
Cash FlowAnalysis
Supplier Performance
Spend Analysis
Procurement Cycle Times
Inventory Availability
EmployeeExpenses
BOM Analysis
OrderLinearity
Ordersvs. Available
Inventory
Cycle TimeAnalysis
BacklogAnalysis
FulfillmentStatus
CustomerReceivables
Campaign Scorecard
Response Rates
Product Propensity
Loyalty andAttrition
Market Basket Analysis
Campaign ROI
Churn Propensity
Customer Satisfaction
ResolutionRates
Service RepEffectiveness
Service CostAnalysis
ServiceTrends
25
Pre-Built, Pre-Mapped, Pre-Packaged InsightsExample: Financial Analytics
Pre-built warehouse with more than 16 star-schemas designed for analysis and reporting on Financial Analytics
Pre-mapped metadata, including embedded best practice calculations and metrics for Financial, Executives & other Business Users.
1
2
3
4Pre-built ETL to extract data from hundreds of operational tables and load it into the DW, sourced from Oracle EBS, PeopleSoft Enterprise, SAP R/3, and other sources.
A “best practice” library of over 360 pre-built metrics, Intelligent Dashboards, 200+ Reports and alerts for CFO, Finance Controller, Financial Analyst, AR/AP Managers and Executives
• Presentation Layer
• Logical Business Model
• Physical Sources
26
Build from Scratchwith Traditional BI Tools
Oracle BI Applications
Prebuilt Business Adapters for Oracle, PeopleSoft, Siebel, SAP, others
Prebuilt DW design, adapts to your EDW
Role-based dashboards and thousands of pre-defined metrics
Easy to use, easy to adapt
Weeks or Months
Back-end ETL andMapping
DW Design
Define Metrics& Dashboards
Back-end ETL andMapping
DW Design
Define Metrics& Dashboards
Training / Roll-out
Training / Rollout
Quarters or Years
Source: Patricia Seybold Research, Merrill Lynch, Oracle Analysis
Speeds Time To Value and Lowers TCOOracle BI Applications
�Faster deployment�Lower TCO�Assured business value
27
Rapid DeploymentsOracle BI Applications
3½ months
6 weeks
12 weeks
100 days
9 weeks
10 weeks
3 months
6 weeks
28
Oracle BI Apps 7.9.x7.9.5 Now Available*
• Unified multi-source data model
• Enhanced user experience
• Data Warehouse Administration Console
• Expanded deployment options• Support for Siebel CRM 6.x, 7.5.x, 7.7.x, 7.8.x, 8.0.x and 8.1*
• Support for Oracle EBS 11i8, 11i9, 11i10 and R12
• Support for PSFT 8.4 (Financials only), 8.8, 8.9* and 9.0*
• Support DB2 V8 on z/OS as both source and target database
• Support Teradata V2R6 as target database
• Support for Informatica 8.1*
• Enhanced security options• PeopleSoft data security (enhanced)�
• Oracle EBS data security (enhanced)�
• Full localization• Translation to 28 languages
29
Oracle BI Apps Roadmap
Oracle BI Apps forFusion Applications
�Unified enterprise data model
�Enhanced DAC
�Leverages OBI EE 10gR3
�Translation to 15 languages
Oracle BI Applications
7.9 ����
�Certify SEBL 8.0 adapter
Oracle BI Applications
7.9.1 ����
�Profitability Analytics for OFSA
Oracle BI Applications
7.9.2 ����
Oracle BI Applications
7.9.5
�Certify PSFT 8.9&9.0 / SEBL 8.1
� Informatica 8.1 support
�Certify Oracle EBS R12
Oracle BI Applications
7.9.4
Oracle BI Applications
7.9.3
�Certify PSFT Financials 8.4 / 8.8 and PSFT HR 8.8
�FSPA Enhancements
����
Oracle BI Applications
11gR2
�Next Generation BI Apps
�Certify Additional Adapters
�New Application Content
����
"This information is not a commitment to deliver any material, code, or functionality. The development, release, and timing of any features or functionality described remains at the sole discretion of Oracle"
�New Application Content
Oracle BI Applications
7.9.x
����
30
Statement of Direction (1 of 2)�
• Enhance Adapter Matrix
• Oracle EBS CRM v11i10 and R12– Sales and Service
• Enhance SAP Adapter
• Enhance Content matrix• HR Enhancements – Benefits, Recruitment, Absenteeism,
Training
• Federal Financial Analytics
• Project Analytics – Accounting Only
• Fixed Asset Analytics
• Spend Analytics Enhancements
"This information is not a commitment to deliver any material, code, or functionality. The development, release, and timing of any features or functionality described remains at the sole discretion of Oracle"
31
Statement of Direction (2 of 2)�
• Build New/Extended Content
• Pricing Analytics
• Scheduling and Dispatch Analytics
• Marketing Loyalty Analytics
• Project Analytics – Resourcing
• Upgrade ETL Infrastructure
• Support for Oracle Data Integrator
"This information is not a commitment to deliver any material, code, or functionality. The development, release, and timing of any features or functionality described remains at the sole discretion of Oracle"
32
Oracle BI ApplicationsETL Adapter Support Summary
PSFT 9.0
PSFT 8.9
Siebel 8.1
Operational Application (e.g. PeopleSoft HR) �
HumanResources
Universal
Not Applicable
Siebel 8.0
Siebel 7.8
Siebel 7.7
Siebel 7.5
Siebel 6.3
**
OrderManagement
**
Procurement& Spend
EBS 12
**
Supply Chain
**SAP 4.6c
PSFT 8.4
PSFT 8.8
EBS 11.5.8
EBS 11.5.9
EBS 11.5.10
FinancialsCRM
Not Supported
Supported
"This information is not a commitment to deliver any material, code, or functionality. The development, release, and timing of any features or functionality described remains at the sole discretion of Oracle"
Currently shipping OBIA 7.9.5
** SAP 4.6c supported in OBIA 7.8.4
33
Oracle BI Applications for Oracle eBusiness Suite
Oracle Discrete Manufacturing
Oracle Purchasing/ProcurementOracle iProcurementOracle Financials (Payables) �
Inventory Analytics
Procurement and Spend AnalyticsSupplier Performance Analytics
Oracle Order ManagementOracle Financials (for Revenue) �
Oracle Order ManagementOracle Discrete Manufacturing (for Inventory) �Oracle Financials (for Receivables and Revenue)�
Order Management Analytics
Order Fulfillment Analytics Option
Oracle EBS moduleOracle BI Application
Certified for 11i8, 11i9, 11i10, R12
34
Oracle BI Applications for Oracle eBusiness Suite
Oracle Human Resources
Oracle Payroll
Human Resources Operations & Compliance Analytics
Human Resources Compensation Analytics
Oracle Financials(GL, Payables, Receivables)�
General Ledger & Profitability AnalyticsPayables AnalyticsReceivables Analytics
Oracle EBS moduleOracle BI Application
Certified for 11i8, 11i9, 11i10, R12
35
Oracle BI Applications for Oracle Financial Services Applications (OFSA)
Oracle Financial Services Applications
Financial Services Profitability Analytics
Oracle productOracle BI Application
Certified for 4.5
36
Oracle BI Applications for PeopleSoft Enterprise
Human Resources
PayrolleCompensation
Human Resources Operations & Compliance Analytics
Human Resources Compensation Analytics
Financials (GL, Payables, Receivables)�
General Ledger & Profitability AnalyticsPayables AnalyticsReceivables Analytics
PeopleSoft Enterprise module
Oracle BI Application
Certified for 8.4 (Financials only), 8.8
37
Oracle BI Applications for SAP R/3 Applications
Sales & Distribution (SD) Financial Accounting (FI) (for Revenue) �
Materials Management (MM) (for Inventory) �Financial Accounting (FI) (for Revenue) �
Order Management Analytics
Order Fulfillment Analytics Option
SAP R/3 moduleOracle BI Application
Certified for 4.6c*
* Support for version 4.6c is with the Oracle BI Applications version 7.8.4
38
Oracle BI Applications for SAP R/3 Applications
Financial Accounting (FI)�General Ledger & Profitability AnalyticsPayables AnalyticsReceivables Analytics
Materials Management (MM)�
Materials Management (MM)�Financial Accounting (FI)�
Inventory Analytics
Procurement and Spend AnalyticsSupplier Performance Analytics
SAP R/3 moduleOracle BI Application
Certified for 4.6c*
* Support for version 4.6c is with the Oracle BI Applications version 7.8.4
39
Oracle BI Applications for Siebel CRM Applications
Siebel Partner Relationship ManagementPartner Analytics
Siebel Customer Order Management (C/OM)�
Order Management Analytics
Siebel Call Center, Siebel Service, Siebel Field Service
Service Analytics
Siebel SalesSales Analytics
Usage Accelerator Analytics Option
Siebel Enterprise Marketing
Siebel Marketing Resource Management
Marketing Analytics
Marketing Planning Analytics Option
Siebel CRM Horizontal AppOracle BI Application
Certified for 6.3, 7.5.x, 7.7.x, 7.8.x, 8.0.x
40
Oracle BI Applications for Siebel CRM Applications
Siebel Consumer GoodsConsumer Packaged Goods Sales Analytics
Siebel FINSFinancial Institution Analytics
Financial Retail Analytics
Siebel Life Sciences -Pharmaceuticals
Pharma Sales Analytics
Pharma Marketing Analytics
Siebel Public SectorCase Management Analytics
Case Investigations Analytics Option
Benefits Management Analytics Option
Siebel CRM Vertical AppOracle BI Application
Certified for 6.3, 7.5.x, 7.7.x, 7.8.x, 8.0.x
41
Oracle BI Applications with Universal Adapters
All BI Applications now have Universal Adapter support, including CRM, which was previously unsupported
Oracle BI Application
42
Architecture
43
� Role Based Dashboards
� Analytic Workflow
� Guided Navigation
� Security / Visibility
� Alerts & Proactive Delivery
� Logical to Physical Abstraction Layer
� Calculations and Metrics Definition
� Visibility & Personalization
� Dynamic SQL Generation
� Highly Parallel
� Multistage and Customizable
� Deployment Modularity
� Abstracted Data Model
� Conformed Dimensions
� Heterogeneous Database support
� Database specific indexing
Oracle BI Applications ArchitectureA
dm
inis
trati
on
Meta
data
Oracle BI Presentation
Services
Dashboards by Role
Reports, Analysis / Analytic Workflows
Metrics / KPIs
Logical Model / Subject Areas
Physical Map
Oracle BI Server
Direct Access to
Source Data
Data Warehouse /Data Model
ETL
Load Process
Staging Area
Extraction Process
DA
C
Federated Data Sources
SiebelOracle SAP R/3 PSFT EDW
Other
44
General Packaging & Integration Points
• Analytic applications support multiple source systems and data types
• Oracle
• PeopleSoft
• Siebel
• SAP
• Out of the box Business Adapters that support for Oracle, PeopleSoft, Siebel, and SAP applications
• Universal Adapters to support other source systems
• JD Edwards
• Legacy
• IVR, CTI, ACD
Ad
min
istr
ati
on
Meta
data
Oracle BI Presentation
Services
Dashboards by Role
Reports, Analysis / Analytic Workflows
Metrics / KPIs
Logical Model / Subject Areas
Physical Map
Oracle BI Server
Direct Access to
Source Data
Data Warehouse /Data Model
ETL
Load Process
Staging Area
Extraction Process
DA
C
Federated Data Sources
SiebelOracle SAP R/3 PSFT EDW
Other
45
Example of ETL AdapterBusiness Component for Oracle EBS Purchase Order Lines Fact
• Reusable and part of extract mapping
• Isolates customers from dealing with source system complexity
Oracle EBS PO Source Oracle EBS PO Source
Tables Tables Source Source
QualifierQualifierExpression Expression
TransformationTransformation
MappletMapplet output to output to
Extract MappingExtract Mapping
46
ETL OverviewA
dm
inis
trati
on
Meta
data
Oracle BI Presentation
Services
Dashboards by Role
Reports, Analysis / Analytic Workflows
Metrics / KPIs
Logical Model / Subject Areas
Physical Map
Oracle BI Server
Direct Access to
Source Data
Data Warehouse /Data Model
DA
C
Federated Data Sources
SiebelOracle SAP R/3 PSFT EDW
Other
• Three approaches to accessing / loading source data
• Batch ETL (Full or Incremental)�
• Micro ETL or Trickle Feed ETL
• Direct access to source data from Oracle BI Server
• ETL Layered architecture for extract, universal staging and load
• Provides isolation, modularity and extensibility
• Ability to support source systems version changes quickly
• Ability to extend with additional adapters
• Slowly changing dimensions support
• Architected for performance• All mappings architected with incremental
extractions
• Highly optimized and concurrent loads
• Bulk Loader enabled for all databases
• Data Warehouse Administration Console (DAC)�
• Application Administration, Execution and Monitoring
ETL
Load Process
Staging Area
Extraction Process
DA
C
47
Data Extraction and Load Process
Source Dependent Extract (SDE) �
� Source-specific and Universal
Business Adapters
� Expose simplified business
entities from complex source
systems
� Converts source-specific data to
universal staging table format
� Lightweight and designed for
performance, parallelism
� Extensible
Load
Load
Extract
Extract
SAPSAPPeopleSoftPeopleSoft
Source Independent Layer
Staging TablesStaging Tables
Source Dependent Extract
OtherOtherSiebel Siebel
OLTPOLTPOracleOracle
PowerConnect
PowerConnect
SQL
SQL
SQL
SQL
App Layer
ABAP
App Layer
OracleOracleBusiness Analytics Business Analytics Warehouse (OBAW)Warehouse (OBAW) ��
48
Example of ETL Adapter…Contd.Source Dependent Extract (SDE) mappings for Purchase Order Lines Fact
• Allows to keep all source specific logic in the extract layer
• Allows to keep data extracts separate from data loads
Flat File Source (for PO Flat File Source (for PO
fact)fact) ��Source Source
QualifierQualifierExpression Expression
TransformationTransformationUniversal Staging Universal Staging
TableTable
Universal Source
Business Component Business Component
MappletMapplet (for PO Fact)(for PO Fact) ��Source AdapterSource Adapter
MappletMappletExpression Expression
TransformationTransformationUniversal Staging Universal Staging
TableTable
Oracle EBS
49
Data Extraction and Load Process
Source Independent Layer (SIL) �
� Encapsulates warehouse load
logic
�Handles:
� Slowly changing dimensions
� Key lookup resolution /
surrogate key generation
� Insert/update strategic
� Currency conversion
� Data consolidation
�Uses Bulk Loaders on all db
platforms
Load
Load
Extract
Extract
SAPSAPPeopleSoftPeopleSoft
Source Independent Layer
Staging TablesStaging Tables
Source Dependent Extract
OtherOtherSiebel Siebel
OLTPOLTPOracleOracle
PowerConnect
PowerConnect
SQL
SQL
SQL
SQL
App Layer
ABAP
App Layer
Business AnalyticsBusiness AnalyticsWarehouseWarehouse
50
Example of ETL Adapter…Contd.Source Independent Load (SIL) mapping for Purchase Order Lines Fact
Universal Staging Universal Staging
TableTableExpression Expression
TransformationTransformationW_PURCH_ORDER_FW_PURCH_ORDER_F
Data Warehouse TableData Warehouse TableSource Independent Source Independent
(SIL) (SIL) MappletMapplet
• Common for all sources (Oracle EBS, PeopleSoft, SAP and Universal)
• Provides the ability to deliver new adapters quickly
• Helps customers to add new legacy sources easily with minimum efforts
51
Data Warehouse Administration Console (DAC)Strong Competitive Differentiator
• DAC is a metadata driven administration and deployment tool for ETL and data warehouse objects
• For warehouse developers and ETL Administrator
• Metadata driven “ETL orchestration tool”• Application Configuration
• Execution & Recovery
• Monitoring
• Allows:• Pin-point deployment
• Load balancing / parallel loading
• Reduced load windows
• Fine-grained failure recovery
• Index management
• Database statistics collection
52
Physical Data Model OverviewA
dm
inis
trati
on
Meta
data
Oracle BI Presentation
Services
Dashboards by Role
Reports, Analysis / Analytic Workflows
Metrics / KPIs
Logical Model / Subject Areas
Physical Map
Oracle BI Server
Direct Access to
Source Data ETL
Load Process
Staging Area
Extraction Process
DA
C
Federated Data Sources
SiebelOracle SAP R/3 PSFT EDW
Other
Data Warehouse /Data Model
• Integrated enterprise-wide data warehouse built with conformed dimensions
• Allows modular deployment
• Lowest grain of Information
• Prebuilt Aggregates to support navigation from Summary to details
• Tracks historical changes
• Implemented and optimized for Oracle, SQL Server, IBM UDB/390, Teradata
53
Oracle BI Apps: Selected Key EntitiesUnified multi-source data model
Conformed Dimensions� Customer� Products� Suppliers� Cost Centers� Profit Centers� Internal Organizations� Customer Locations� Customer Contacts� GL Accounts� Employee� Sales Reps� Service Reps� Partners� Campaign� Offers� Employee Position
Hierarchy� Users
Conformed Dimensions� Customer� Products� Suppliers� Cost Centers� Profit Centers� Internal Organizations� Customer Locations� Customer Contacts� GL Accounts� Employee� Sales Reps� Service Reps� Partners� Campaign� Offers� Employee Position
Hierarchy� Users
Sales� Opportunities� Quotes� Pipeline
Order Management� Sales Order Lines� Sales Schedule Lines� Bookings� Pick Lines� Billings� Backlogs
Marketing� Campaigns� Responses� Marketing Costs
Supply Chain� Purchase Order Lines� Purchase Requisition Lines� Purchase Order Receipts� Inventory Balance� Inventory Transactions
Finance� Receivables � Payables � General Ledger� COGS
Sales� Opportunities� Quotes� Pipeline
Order Management� Sales Order Lines� Sales Schedule Lines� Bookings� Pick Lines� Billings� Backlogs
Marketing� Campaigns� Responses� Marketing Costs
Supply Chain� Purchase Order Lines� Purchase Requisition Lines� Purchase Order Receipts� Inventory Balance� Inventory Transactions
Finance� Receivables � Payables � General Ledger� COGS
Call Center� ACD Events� Rep Activities� Contact-Rep Snapshot� Targets and Benchmark� IVR Navigation History
Service� Service Requests� Activities� Agreements
Workforce� Compensation� Employee Profile� Employee Events
Pharma� Prescriptions� Syndicated Market Data
Financials� Financial Assets� Insurance Claims
Public Sector� Benefits� Cases� Incidents� Leads
Call Center� ACD Events� Rep Activities� Contact-Rep Snapshot� Targets and Benchmark� IVR Navigation History
Service� Service Requests� Activities� Agreements
Workforce� Compensation� Employee Profile� Employee Events
Pharma� Prescriptions� Syndicated Market Data
Financials� Financial Assets� Insurance Claims
Public Sector� Benefits� Cases� Incidents� Leads
Modular DW Data Model includes:
~350 Fact Tables
~550 Dimension Tables
~5,200 prebuilt Metrics
(2,500+ are derived metrics)�
~15,000 Data Elements
Modular DW Data Model includes:
~350 Fact Tables
~550 Dimension Tables
~5,200 prebuilt Metrics
(2,500+ are derived metrics)�
~15,000 Data Elements
54
Server Repository OverviewA
dm
inis
trati
on
Meta
data
Oracle BI Presentation
Services
Dashboards by Role
Reports, Analysis / Analytic Workflows
Direct Access to
Source Data
Data Warehouse /Data Model
ETL
Load Process
Staging Area
Extraction Process
DA
C
Federated Data Sources
SiebelOracle SAP R/3 PSFT EDW
Other
• Multi-layered Abstraction
• Prebuilt Metrics/Dimensions
• Prebuilt hierarchy drills and cross dimensional drills
• Prebuilt Aggregate navigation
• Multi-pass complex calculated Metrics / KPIs
• Federation of queries
• Visibility & Personalization
• Prebuilt Security inherited from Oracle EBS, PeopleSoft, Siebel CRM
Metrics / KPIs
Logical Model / Subject Areas
Physical Map
Oracle BI Server
55
OBI EE Plus Vs OBI Applications
Oracle Business Intelligence Enterprise Edition Plus
Oracle Business Intelligence Applications –Prebuilt Metadata
56
OBI Applications – Prebuilt MetadataInventory Compound Metrics: Inventory Turns Example
57
OBI Applications – Prebuilt MetadataDimensions
58
OBI Applications – Prebuilt MetadataHierarchies – Plant Location Example
59
Web Catalog Overview
• Role based dashboards
• Prebuilt Reports/Dashboards
• Guided Navigation
• Conditional navigational links
• Analytic Workflows
• Alerts
• Highlighting
• Action Links to Oracle EBS, PeopleSoft, Siebel CRM
Ad
min
istr
ati
on
Meta
data
Metrics / KPIs
Logical Model / Subject Areas
Physical Map
Oracle BI Server
Direct Access to
Source Data
Data Warehouse /Data Model
ETL
Load Process
Staging Area
Extraction Process
DA
C
Federated Data Sources
SiebelOracle SAP R/3 PSFT EDW
Other
Oracle BI Presentation
Services
Dashboards by Role
Reports, Analysis / Analytic Workflows
60
Example of Role Based DashboardOrder Management Overview Dashboard
DashboardPages
Roles
Performance Measures
Reports based on Multiple Sources
FlexibleView Selectors
Prebuilt Reports
Conditional NavigationHighlighting
Guided Navigation
61
More than just Dashboards & Reports
• Guided Navigation• Enables users to quickly navigate a standard path of
analytical discovery specific to their function and role
• Enhances usability and lowers learning curve for new users
• Conditional Navigation• Appears only when conditions are met and alerts users to
potential out of ordinary conditions that require attention
• Guides users to next logical step of analytical discovery
62
Example of Inventory Analytics Workflow
Business Objectives /
Issues
GainInsights
Take Action
Identify top 5 plants withhighest inventory and cut back production
What are the Plants holding these inventories
What is the Sales Trend for these products
What are the Top 10 Products by
Inventory Value?
Is Inventory Balancestrending up?
Is Inventory Turns on target?
Reduce Inventory
Drill toSales Backlogs/
Bookings
Is Cost of Goods sold increasing
Is Sales declining?
Is Days of Supply on target?
• Business Function:Inventory
• Role:Inventory Manger
• Objectives:• 1) Reduce Inventory
• 2) Increase working capital
63
Analytic Workflows – Inventory Analytics
Business Objectives /
Issues
GainInsights
Take ActionTarget Efforts to reduce
the inventory
Is Inventory Balancestrending up?
Is Inventory Turns on target??
Reduce Inventory
Drill to
Deta
il
What are Top 10 Products By Inventory Value?
What is the Sales Trend for these products
Drill to Inventory Location Details
64
Tight Integration with Oracle ApplicationsAction Links navigate from analytical to operational
• Action Links• Seamless navigation from analytical information to transactional
detail while maintaining context within Oracle EBS, Siebel CRM, and PeopleSoft Enterprise
65
Deployment Options
• Standalone Dashboards
• Portal integration via JSR-168/WSRP
• Embedded Directly in Oracle EBS
66
67
68
69
70
Oracle BI Applications Process Flow
71
72
<Insert Picture Here>
Lab Exercise:Installation and Configuration (Viewlet)�
73
Installation and Configuration (Viewlet)�
• Lab 1.1 Installation and Configuration – Main Steps
• Run installation and configuration viewlet:– Open Labs folder– Run install_config_viewlet_viewlet_swf.html file
• Simulates installation of Oracle BI Applications
• Includes Informatica
• Simulates typical configuration steps
• Duration: 45 minutes
For more information on installing and configuring Oracle BI Applications,refer to the Oracle BI Applications Installation and Configuration Guide
located on the DVD in the BI_Apps_795_Docs folder
74
75
<Insert Picture Here>
Presentation & Lab Exercise:Data Warehouse Administration Console (DAC) Overview
76
Data Warehouse Administration Console (DAC)Overview
• DAC complements the Informatica ETL platform• Subject areas and execution plans
• Load balancing / parallel loading
• Ability to restart at any point of failure
• Phase-based analysis tools for isolating ETL bottlenecks
• Three parts to DAC• DAC client - interface for management
and configuration, administration and monitoring of data warehouse processes.
• DAC server – executes commands from the DAC client, manages data warehouse processes including loading of the ETL and scheduling execution plans.
• DAC Repository - Stores the metadata (semantics of the Oracle Business Analytics Warehouse) that represents the data warehouseprocesses.
77
ETL Process Flow
1. Administrator initiates ETL in DAC client
2. DAC server issues ETL tasks• Informatica-related ETL tasks are issued against Informatica server
3. Informatica server accesses workflows in Informatica repository
4. Informatica server processes the workflows
5. Data is extracted from the transactional database(s) �
6. Data is transformed and then loaded in OBAW
78
DAC Objects
• Source system containers
• Hold repository objects that correspond to a specific source system.
• EBS, Peoplesoft, Siebel, SAP
• Execution plan
• A data transformation plan defined on subject areas and is comprised of the following: ordered tasks, indexes, tags, parameters, source system folders, and phases.
• Subject area
• A logical grouping of tables related to a particular subject or application context
• Task
• A unit of work for loading one or more tables. A task comprises the following: source and target tables, phase, execution type, truncate properties, and commands for full or incremental loads. When youassemble a subject area, the DAC automatically assigns tasks to it.
79
DAC Overview Lab
• Lab 1.2 DAC Overview – Main Steps
• Start services on VM
– Informatica repository and server
– DAC server and client
• Familiarize with DAC layout and features
• DO NOT close and shutdown at the end of the lab
• Duration: 30 minutes
For more information on DAC, refer to the Oracle BI ApplicationsData Warehouse Administration Console Guide
located on the DVD in the BI_Apps_795_Docs folder
80
81
<Insert Picture Here>
Lab Exercise:DAC Configuration for Order Management
82
DAC Configuration Lab
• Lab 1.3 DAC Configuration – Main Steps
• Configure DAC for the Order Management application
– Create new Source System Container
– Create Execution Plan
– Modify parameters
• Gotchas
– Ensure dates are correct
– Case sensitive, watch for misspellings
• Duration: 1.5 hours
For more information on DAC, refer to the Oracle BI ApplicationsData Warehouse Administration Console Guide
located on the DVD in the BI_Apps_795_Docs folder
83
84
<Insert Picture Here>
Lab Exercise:DAC Execution for Order Management – Extraction & Load
85
DAC Execution Lab
• Lab 1.4 DAC Execution (OM) – Main Steps• Run Execution Plan from previous lab
• Monitor progress
• Error procedure• Allow run to complete even if you see a failed task
• Check parameters in previous lab for incorrect dates and misspellings
• If dates were wrong or errors persist, truncate data warehouse tables and redo previous lab before re-running Execution Plan
– See step 2 in Post Workshop Lab: VM Image Reset and Environment Migration
• Duration: 2.5 hours
86
87
<Insert Picture Here>
Presentation:Oracle Business Analytics Warehouse (OBAW) Overview
88
BI Application Data Model Features
• Dimensional Modeling – Model designed for Analytics and Reporting
• Maintain Aggregation – Store summary data for better performance
• Universal Data Warehouse and Staging
• Multiple Source – Data Source Num and Warehouse Code Standardization
• Transaction data stored in most granular fashion
• History Tracking – Slowly Changing Dimension support
89
BI Application Data Model Features
• Dimension Hierarchy tables – Flatten Tree Hierarchies from OLTP
• Enforce Conforming Dimension Tables
• Multiple Currency Support
• Multiple Calendar Support
• Implemented and optimized for Oracle, SQL Server, DB2, Teradata
90
Star Schema
• Is a denormalized format that is more effective for query processing
• Is populated by ETL processes
• Is composed of• One fact table
• A set of dimension tables
• The joins that relate the dimension tables to the fact table
91
Selected Star Schemas in OBAW
92
Fact Tables
• Are central tables in star schema
• Typically contain numeric measurements
• It has multiple joins to the dimension tables surrounding it
• Transaction data stored in most granular fashion
• Are identified with suffix _F
93
Dimension Tables
• A surrogate key (ROW_WID) for each dimension table is generated during the ETL process.
• ROW_WID is a numeric column, which is used to join to fact tables
• In some cases, the ROW_WID is shared between the dimension and dimension hierarchy tables
• In every dimension table, the ROW_WID value of zero is reserved for unspecified
• Are identified with the suffix _D
94
Aggregate Tables
• Detail level facts are summarized
• Can dramatically improve query performance
• Identified with the suffix _A
• Use Rolled-up Dimensions
• Dimensions created from base dimension tables
• Examples:
• W_GEO_D
Customer_ID
Sales_$
Store_ID
DayTime_ID
Base Fact Table
Customer_Category_ID
Sales_$
Region_ID
Month_ID
Aggregated Table
5 Million Rows
100,000 Rows
95
Aggregate Tables
• OBAW contains pre-built aggregate tables
96
Staging Tables
• Normally populate with the incremental data from transactional database
• Always truncate after each load
• Are loaded by the extract (SDE) process
• A single table may be populated by one or more SDE processes during a ETL run
• Are the source tables for load (SIL) process
• The staging table in BI Apps is independent of source data and closely resembles the structure of the data warehouse tables
• Universal adapters are available to load the staging tables
• Are identified with the suffix _DS, _FS, _DHS
97
Slowly Changing DimensionProblem Definition
• The attributes of a dimension may change overtime, but changes are not frequent. For example, the marital status of your employees or customers.
• Here are the industry standard ways to handle this problem: • Type 1: Overwrite the dimension record with the latest values,
therefore losing history
• Type 2: Create a new dimension record for the new values
• Type 3: Create a new field in the dimension table to hold both the current and the previous values
98
Slowly Changing Dimension Support
• Slowly changing dimension Type II can be enabled/disabled at a per dimension basis using the parameter $$TYPE2_FLG
• Every new record in the dimension will have the surrogate key generated in the data warehouse
• The fact table join to the specific dimension record that is effective at the time of the transaction
• Out of the box, certain dimensions are enabled for slowly changing
• Only a small set of columns are considered historically significant. Customization is required to add or remove columns
99
Conforming Dimensions
• A dimension is used to describe the measures in the fact table. A conformed dimension is a dimension that can be used to describe multiple facts table and the dimension has exactly the same meaning and context when being referred from different fact tablesFor example, a customer dimension may be used with the sales fact as well as with the service fact.
• Conforming dimensions is a way to enable drill across information
100
Conforming Dimensions
• Fact tables share the same dimension tables
• Conforming across CRM & ERP
• Conforming across Multiple Sources
• Ensure Cross Fact Analysis
DimensionDimension
Dimension
Fact
Fact Dimension
101
Conformed Dimensions
• Time Dimensions
• Customer Dimensions
• Product Dimensions
• Supplier Dimensions
• Internal Organization Dimensions
• Employee Dimensions
• Business Location Dimensions
• Accounts Dimensions• GL Account
• Cost Center
• Profit Center
102
Time Dimensions
• W_PERIOD_D, W_PERIOD_DH
• W_TIME_OF_DAY_D
• W_HOUR_OF_DAY_D
• W_FSCL_WEEK_D, W_FSCL_MONTH_D, W_FSCL_QTR_D, W_FSCL_YEAR_D
• W_DAY_D, W_MONTH_D, W_QTR_D, W_YEAR_D
PHYSICAL TABLES
• Marketing Period
• Not a conformed dimension today
• Period
• Store the hours of a day
• One record per hour• Time of the Day
• Store the time interval at the granularity of seconds
• Hour of the Day
• Store the fiscal calendar hierarchy information
• These dimension tables are mainly used in Financial subject area
• Aggregate facts against these tables
• Fiscal
• Store the date and Gregorian calendar hierarchy information
• Date
DESCRIPTIONDIMENSION
103
Account Dimensions
• W_COST_CENTER_D
• W_HIERARCHY_DH
• W_PROFIT_CENTER_D
• W_IERARCHY_DH
• W_GL_ACCOUNT_D
PHYSICAL TABLES
• Stores cost center information such as Number, Name and Hierarchy etc
• Cost Center
• Stores profit center information such as Number, Name and Hierarchy etc.
• Profit Center
• Stores information of all General Ledger accounts and account hierarchy such as GL Account Number, Name, Account Group etc.
• GL Account
DESCRIPTIONDIMENSION
104
Multiple Currency Support
• Data model stores multiple currencies in each fact• Document Currency - Actual Transaction Currency
• Local Currency - Local Country / Region Currency
• Global Currency - Corporate Reporting Currency
• Support for different exchange rate types through ETL configuration• Corporate
• User
• Global currency code and rate types are configured in DAC under the source system parameters tab
• Currency conversions are done as part of ETL
105
Multiple Calendar Support
• Supports Gregorian and Fiscal hierarchies out of the box
• Configuration to support multiple fiscal hierarchies depending on user profile• Requires Initialization block to read user profile
• Dynamically use the appropriate calendar table
• For Example, Siebel OnDemand implementation supports twelve fiscal calendars
106
Multi-Source Load
• INTEGRATION_ID: Stores the primary key or the unique identifier of the record from the OLTP• The transaction sources may use the same ID for identifying
different objects which may share the same target table
• DATASOURCE_NUM_ID: Source the data source from which the data is extracted. • All warehouse tables have the DATASOURCE_NUM_ID as part
of the unique user key
• OOTB, it is used in resolving the FK from fact to dimension
• The value is predefined in DAC for each physical data source
• However, it is possible to have multiple instances of the same OLTP source system. A different data source number can be assigned for each OLTP instance.
107
Incremental Extraction and Load
• A variety of strategies used to optimize incremental extracts and loads
• Overall Philosophy – Extract incrementally if possible, else load incrementally• Siebel Source
• Use a combination of a date window and rowid comparisons
• Oracle
• Use a date window and last update date for extraction
• Also use dates/record images to control updates on target
• SAP
• Use a date window and last update date for extraction
• Also use dates/record images to control updates on target
• Certain dimensions are fully extracted and but updates on target are controlled
• PeopleSoft
• Use a date window and last update dates for extraction wherever possible
108
OBAW Data Model Reference
Metalink3 > Knowledge Tab > Business Intelligence category > Data Model Reference > Oracle Business Analytics Warehouse Data Model Reference, Version 7.9.5 (Doc ID 578880.1)
https://metalink3.oracle.com/od/faces/secure/km/DocumentDisplay.jspx?id=578880.1
109
110
<Insert Picture Here>
Presentation:Oracle BI Applications – Order Management Analytics Overview
111
Sales Process – Scenario for Selling Computers
Lead Oppty Order BookingUnsched
Backlog
There is a person
in the world who
wants to buy a
computer
There is a person
in the world who
wants to buy a
computer – THAT
my company
builds
That person calls
the company and
requests a
computer. An
order is placed as
shown below.
This is the Order
Date.
Company reviews
the order for
correctness and asks
the factory to
‘fulfill’ the order.
That moment is the
Book Date and the
order is considered
unscheduled
backlog.
The factory needs
to build/assemble/
find this computer.
$ 900Total Order Amount
10%Discount
$ 1,000 Total
$ 300 Monitor
$ 100 Speakers
$ 600 Computer
abc123Order #
$ 900 Order #abc123
$ 270 Monitor
$ 90 Speakers
$ 540 Computer
Unscheduled Backlog Amount
Unscheduled Backlog
$ 900 $ 1,000 Order #abc123
$ 270 $ 300 Monitor
$ 90 $ 100 Speakers
$ 540 $ 600 Computer
Booked Discounted Amount
Booked List Amount
Booking
112
Sales Process – Scenario Selling Computerscontinued…
Sched.
Item
Picked
Item
Invoice
Item
Account
Receiv.Cash
The factory
schedules the build
/assembly of the
computer to be
completed on a
specific date. This
date is the
Promised Date.
The factory
completes the
computer and it is
picked from the
warehouse and
shipped. This date
is the Pick Date.
An On-time pick is
one where Pick
Date = Promised
(Plan Pick) Date.*
The customer
receives the
computer and the
‘bill’ – or invoice
for the amount of
the computer. The
date the invoice is
sent to customer is
Invoice Date.
The company is now
awaiting payment
from the customer
for the computer
(receivable.) The #
days b/w the Invoice
Date and Payment
Date = Days Sales
Outstanding (DSO)�
The company
receives payment
for the computer.
The date the
company receives
payment is the
Payment Date.
$ 900 Order #abc123
$ 270 Monitor
$ 90 Speakers
$ 540 Computer
Scheduled Backlog Amount
Scheduled Backlog
$ 900 Order #abc123
$ 270 Monitor
$ 90 Speakers
$ 540 Computer
Total Picked Amount
Picking
$ 900 $ 1,000 Order #abc123
$ 270 $ 300 Monitor
$ 90 $ 100 Speakers
$ 540 $ 600 Computer
Net Invoiced Amount
Invoice List Amount
Invoice
$ 900 Customer A
Closing Group Amount
Account Receivable
113
Order to Cash: Business Problems
The more quickly and accurately an organizationexecutes this process, the more efficiently it runs.
Order received
Order verifiedfor accuracy
Finance accepts itOr not
Order scheduled
Order built
Order picked
Order shipped
Customeraccepts the order
Or not
Order invoiced
Invoice paidOr not
• Data for entire Sales Process (Lead to Cash) is kept in multiple systems, which makes it either too difficult or impossible to analyze all the data together
• Lack of integrated view of Sales Orders demand, Inventory availability and account receivables status
• Disparate information systems make aggregated view difficult
• Lack of single version of truth – Which version is the right one?
• Rapidly developing product lines with high opportunity to up-sell / cross-sell but inability to get the sales force to effectively cross or up-sell
• Inability to accurately forecast
• No early warning systems to detect business deviations
114
Oracle BI ApplicationsMulti-Source Analytics with Single Architecture
Travel& Trans
AutoComms& Media
ComplexMfg
ConsumerSector
EnergyFinancialServices
HighTech
Insurance& Health
LifeSciences
Public Sector
Other Operational & Analytic Sources
Oracle BI Suite Enterprise Edition
Prebuilt adapters:
Sales MarketingHuman
Resources
Employee Productivity
Compensation Analysis
HR Compliance Reporting
WorkforceProfile
TurnoverTrends
Return on Human Capital
Campaign Scorecard
Response Rates
Product Propensity
Loyalty andAttrition
Market Basket Analysis
Campaign ROI
Service &Contact Center
Churn Propensity
Customer Satisfaction
ResolutionRates
Service RepEffectiveness
Service CostAnalysis
ServiceTrends
Financials
A/R & A/PAnalysis
GL / BalanceSheet Analysis
Customer& Product
Profitability
P&L Analysis
ExpenseManagement
Cash FlowAnalysis
Supply Chain
Supplier Performance
Spend Analysis
Procurement Cycle Times
Inventory Availability
EmployeeExpenses
BOM Analysis
PipelineAnalysis
TriangulatedForecasting
Sales Team Effectiveness
Up-sell / Cross-sell
Cycle TimeAnalysis
Lead Conversion
OrderLinearity
Ordersvs. Available
Inventory
Cycle TimeAnalysis
BacklogAnalysis
FulfillmentStatus
CustomerReceivables
Order Management& Fulfillment
115
Pre-mapped metadata, including embedded
best practice calculations and metrics for the
sales organization.
� Presentation Layer
16 Subject Areas
� Logical Business Model
20 Fact and 50 Dimension
Tables
3
2A “best practice” library of pre-built
intelligence dashboards, reports and alerts
for sales representatives, analysts, managers
and executives
4
Oracle Order Management and Fulfillment Analytics
Pre-built warehouse with 20 star-schemas
designed for analysis and reporting on sales,
fulfillment and receivables data.
1
Pre-built ETL to extract data from operational
tables and load it into the DW, sourced from
EBS, Siebel and SAP and other legacy
sources.
ETL & Business AdaptersCRM Legacy
OM
OMFinancial HRPOS
Legacy
Inventory
Other Supply Chain SystemsOther Supply Chain Systems
Operational SystemsOperational Systems
Business AnalyticsWarehouse
116
Sales Order Lines
Sales Order Details
Sales Order Details
EAIETL
Cust. LocationSold to / Ship to /
Bill to
Cust. LocationSold to / Ship to /
Bill to
DateDatePayment Terms
Payment Terms
Sales ChannelSales Channel
ProductsMfg / Sales / Supplier
ProductsMfg / Sales / Supplier
EmployeeEmployee
CustomersCustomers
LocationsPlant / Mfg
Ship / Storage
LocationsPlant / Mfg
Ship / StorageSales OrgsSales Orgs
Example Metrics
• # of Cancelled Order Lines• # of Customers• # of First Customers• # of Order Lines• # of Orders• # of Products• # of Returned Order Lines• % Order Discount• Average # of Products per Order
• Average Order Size• Cancelled Amt / Qty• Orders to Booking Close Rate• Outstanding Booking Amt / Qty
• Total Ordered Amt / Qty• Total Return Amt / Qty
Features
� Includes 27 logical dimensions and 33 out of the box metrics
� Provides ability to do detailed analysis of sales order lines
� Data stored at transaction grain and at line level
Oracle Order Management & Fulfillment AnalyticsSales Order Lines
117
Value of Oracle Order Management and Fulfillment Analytics
• Provide single, reliable version of the truth that enables monitoring of entire business process from Order to Cash
• Offer the ability to accelerate the Order Management cycle and
Revenue Recognition through more effective order management,
fulfillment and receivables management
• Eliminate Order Management bottlenecks and increase on-time
delivery, and customer satisfaction by getting insight on problem
areas in inventory and credit collection
• Expedite sales cycles by providing the ability to do detailed
operational and financial backlog analysis
• Improve order capture, fulfillment and receivables closure process by
providing every individual with relevant, complete, contextual
information that is tailored specifically to their role
118
Oracle Order Management and Fulfillment AnalyticsComplete solution for gaining insight into the Order to Cash process
Order Management AnalyticsFoundation application module that provides insight into critical Order Management business processes and key information, including Orders, Invoices, Sales Effectiveness and Customer Reports.
Oracle Sales AnalyticsAnalyze pipeline opportunities and forecasts to determine actions required to meet sales targets. Determine which products and customer segments generate the most revenue and how to effectively cross-sell and up-sell. Understand which competitors are faced most often and how to win against them.
Order Fulfillment AnalyticsProvides complete analysis of every step in the back-office Sales Cycle from Order to Cash, enabling companies to respond more quickly to unfulfilled orders, outstanding receivables and resolve them before they become critical.
…add Oracle Sales Analytics for complete Contact to Cash
119
Business Objectives /
Issues
GainInsights
Take ActionCreate more Inventoryof Products in demand
Drill to Current Backlogand Inventory by Product
Which products have thehighest fulfillment lag?
How much order revenue isin highest fulfillment lag?
Is Cumulative InvoiceRevenue Trending up?
Is Order Fulfillmenton target?
Manage OrderManagement performance
What is the trend of OrderRevenue by Channel and
Customer Category
What is the trend ofAverage Order Size?
Is Cumulative OrderRevenue Trending up?
Is Sales Cycle Timeon target?
• Business Function:Order Management
• Role:Director, Sales Operations
• Objectives:• Optimize Order
Fulfillment
• Reduce Sales Cycle Time
Analytic Workflows – Order Management Analytics
How much are myTop Customers ordering?
120
Business Objectives /
Issues
GainInsights
Take ActionCreate more Inventoryof Products in demand
Drill to Current Backlogand Inventory by Product
Which products have thehighest fulfillment lag?
How much order revenue isin highest fulfillment lag?
Is Cumulative InvoiceRevenue Trending up?
Is Order Fulfillmenton target?
Manage OrderManagement performance
Drill to
Deta
ilAnalytic Workflows – Order Management Analytics
121
Order Management Metrics
• # of Orders• Total Ordered Amount• Total Invoiced Amount• Cancelled Amount• Total Return Amount• Order To Ship Days Lag• Ship To Invoice Days Lag• Order To Invoice Days Lag• # of New Customers• # of Lost Customers• # of Active Customers• Quarter Ago Total Ordered Amount• Company Average Order Size• Company % Order Discount
Order Fulfillment Metrics
• Outstanding Booking Quantity• Outstanding Booking Amount• Number of Outstanding Bookings• Financial Backlog Amount• Operational Backlog Amount• Hold Volume Rate• Available Inventory• Blocked Inventory• Total Open RMA Value• Total AR Due and Overdue Amount• Credit Limit Used %• AR Overdue Items To Total %• Average Order Size• Orders To Booking Close Rate
Sample Order Management Analytics Metrics
Sample Pre-Built Dashboards
VP Sales• Revenue• Forecast• Backlog• Sales Cycle
Sales Rep• Customer• Sales Cycle• Fulfillment• Effectiveness
Receivables Manager• A/R• Revenue• Forecast• Overview
OM Manager• Effectiveness• Fulfillment• Backlog• Exception
Oracle Order Management Analytics
122