Oracle Business Intelligence Applications Technical Hands-on Workshop – Day 1
Workshop Goals The PTS Oracle Business Intelligence Applications Workshop W orkshop provides a business and technical overview of the BI Applications platform as well as an understanding for the different offerings. off erings. 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 com ponents including Informatica, Data Warehouse Administration Console (DAC), Oracle BI Applications Warehouse (OBAW), OBI EE Dashboards and OBI EE Answers Ans wers in a controlled lab environment
Workshop Goals The PTS Oracle Business Intelligence Applications Workshop W orkshop provides a business and technical overview of the BI Applications platform as well as an understanding for the different offerings. off erings. 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 com ponents including Informatica, Data Warehouse Administration Console (DAC), Oracle BI Applications Warehouse (OBAW), OBI EE Dashboards and OBI EE Answers Ans wers in a controlled lab environment
Agenda – Day 1 Start
End
Agenda Item
8:30
9:00
Breakfast
9:00
9:30
Welcome, Overview, and Introductions and Environment Setup Lab
9:30
10:00
Lab Environment Overview
10:00
11:00
Oracle BI Applications Overview
11:00
11:45
Oracle BI Applications Installation and Configuration Lab (Viewlet)
11:45
12:15
DAC Overview
12:15
1:00
Lunch
1:30
3:00
DAC (Order Management) Configuration Lab
3:00
5:30
DAC Execution (Order Management) – Warehouse Load Lab
3:30
4:30
Oracle Business Analytics Warehouse (OBAW) Overview
4:30
5:00
Oracle BI Applications – Order Management Analytics Overview
Agenda – Day 2 Start
End
Agenda Item
8:30
9:00
Breakfast / Questions & Answers (Review Day 1)
9:00
9:30
DAC Execution Review
9:30
10:30
Warehouse Load Confirmation Lab – BI EE
10:30
11:30
Informatica Overview Lab (Viewlet)
11:30
12:00
OBAW Customization Overview
12:00
12:30
Lunch
12:30
1:30
Informatica Lab – Custom ETL (Viewlet)
1:30
3:00
DAC Configuration Lab for Custom ETL
3:00
4:30
DAC Execution and Warehouse Load Confirmation Lab (Custom ETL) – SQL*Plus
4:30
5:00
Workshop Wrap up / Evaluations
Workshop Site Information
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?
Oracle Platform Technology Solutions BI and EPM Team Mission
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.
Programmatic Investment for Partnership Success Consulting Services
OBI EE+ Enablement Successful
• Webinars
• Architecture
• Hands-on Workshops
• Design
• Center’s of Excellence
• Build
to self sufficiency
OBI EE+ Engagements Repeatable
Solutions
Incremental
BI Factory
• Baseline Platform Reference – e.g. Integration, LOB, etc.
• POC Sandbox
• Industry specific exploitations of platform
• Early Adoption Engagements to Insure Success
• OBI EE+ Demonstration
PTS OBI Workshops Available / Planned SI Partner Technical Training WORKSHOP NAME
DURATION
OBI EE+ & Essbase
1.5 Days
Essbase Advanced
OBI EE+ Advanced
OBI Applications
DESCRIPTION
STATUS
Introductory Hands-On Technical Workshop
AVAILABLE
2 Days
Advanced Essbase Technical Workshop
AVAILABLE
2.5 Days
Architecture, Hands-on Labs, Solution Puzzlers
AVAILABLE
BI Applications, Architecture, ETL, Metadata Mappings, Deployment Best Practices
AVAILABLE
2 Days
Presentation & Lab Exercise: Lab Environment
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
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
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
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 W eb 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
Oracle BI EE 10.1.3.3.3 and Oracle BI Applications Applications 7.9.5 7.9.5 • BI EE Lives at: • /biapps/OracleBI and /biapps/OracleBIData • BI Applications Live at: • /biapps/OracleBI/DAC
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 the execution of workflows and related programmatic components • The Administration Console runs under Tomcat and lives at: /biapps/Informatica/PowerCenter8.1.1/server/tomcat/bin
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.
Presentation: Oracle BI Applications Overview
What Gartner is Saying
“Through 2009 there will be a swing toward buying pre-packaged analytic applications… (0.7 probability)”
Source: “Business Intelligence Scenario: Pervasive BI,” Gartner Symposium ITxpo 2006
Oracle is the Worldwide Leader
Oracle BI Applications Oracle BI EE Suite
#1 in BI/Analytic Applications - IDC “One of the most comprehensive and innovative BI platforms…” - Gartner
Oracle Data Warehousing
#1 in DW Tools - IDC
BI Approaches: Tools Tools & Build Approach
These steps require different types of BI and DW technology
These steps require significant resources with specialized skills / expertise
These steps typically take a long time to perfect as knowledge of best practices is learned
Develop detailed understanding of operational data sources Design a data warehouse by subject area
No prebuilt content
License an *ETL tool to move data from operational systems to this DW Build ETL programs for every data source
Oracle BI Platform (custom metadata)
License interactive user access tools Research analytic needs of each user community
Oracle
Build analytics for each audience License / create information delivery tools
Siebel Set up user security & visibility rules
SAP Other Sources
Custom Built DW Custom ETL
Perform QA & performance testing Manage onon-going changes/upgrades ETL= Execute, Transfer and Load Data
BI Approaches: Tools vs Applications Tools & Build Approach
Prebuilt BI Applications Approach
Prebuilt BI Content
No prebuilt content
Oracle BI Platform w/ Prebuilt Metadata
Oracle BI Platform (custom metadata) Oracle
Oracle
Siebel
Siebel
SAP Other Sources
Custom Built DW Custom ETL
SAP Other Sources
Prebuilt ETL
Prebuilt DW
Oracle BI Applications Multi-source Analytic Apps Built on BI Suite EE Interactive Dashboards
Sales
Reporting & Publishing
Service & Contact Center
Ad-hoc Analysis
Marketing
Proactive Detection and Alerts
Order Management & Fulfillment
Disconnected Analytics
Supply Chain
MS Office Plug-in
Financials
Web Services
Human Resources
Oracle BI Applications Oracle BI Apps built on Oracle BI EE Suite
• Common Enterprise Information Model • Prebuilt Hierarchies, Drill Paths, Security, dashboards, reports • Based on industry and analytic best practices
Packaged ETL Maps
Universal Adapters
IVR, ACD, CTI Hyperion MS Excel Other Data Sources
Oracle BI Applications Multi-Source Analytics with Single Architecture Auto
Comms & Media
Complex Consumer Sector Mfg
Sales
Service & Contact Center
Pipeline Analysis
Energy
Financial Services
High Tech
Insurance Life & Health Sciences
Public Sector
Travel & Trans
Marketing
Order Management & Fulfillment
Supply Chain
Financials
Human Resources
Churn Propensity
Campaign Scorecard
Order Linearity
Supplier Performance
A/R & A/P Analysis
Employee Productivity
Triangulated Forecasting
Customer Satisfaction
Response Rates
Orders vs. Available Inventory
Spend Analysis
Sales Team Effectiveness
Resolution Rates
Product Propensity
Cycle Time Analysis
Procurement Cycle Times
Customer & Product Profitability
HR Compliance Reporting
Up-sell / Cross-sell
Service Rep Effectiveness
Loyalty and Attrition
Backlog Analysis
Inventory Availability
P&L Analysis
Workforce Profile
Cycle Time Analysis
Service Cost Analysis
Market Basket Analysis
Fulfillment Status
Employee Expenses
Expense Management
Turnover Trends
Lead Conversion
Service Trends
Campaign ROI
Customer Receivables
BOM Analysis
Cash Flow Analysis
Return on Human Capital
GL / Balance Compensation Sheet Analysis Analysis
Other Operational & Analytic Sources
Prebuilt adapters:
Oracle BI Suite Enterprise Edition Plus
Pre-Built, Pre-Mapped, Pre-Packaged Insights Example: Financial Analytics 1
2
Pre-built warehouse with more than 16 star-schemas designed for analysis and reporting on Financial Analytics
Pre-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.
3
Pre-mapped metadata, including embedded best practice calculations and metrics for Financial, Executives & other Business Users. •
Presentation Layer
•
Logical Business Model
•
Physical Sources
4
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
Speeds Time To Value and Lowers TCO Oracle BI Applications Build from Scratch with Traditional BI Tools
Oracle BI Applications
Training / Roll-out Define Metrics & Dashboards
Faster
deployment Lower TCO Assured business value
DW Design Training / Rollout Back-end ETL and Mapping
Quarters or Years
Easy to use, easy to adapt
Define Metrics & Dashboards
Role-based dashboards and thousands of pre-defined metrics
DW Design
Prebuilt DW design, adapts to your EDW
Back-end ETL and Mapping
Prebuilt Business Adapters for Oracle, PeopleSoft, Siebel, SAP, others
Weeks or Months
Source: Patricia Seybold Research, Merrill Lynch, Oracle Analysis
Rapid Deployments Oracle BI Applications 6 weeks 6 weeks 9 weeks 10 weeks 12 weeks 3 months 3½ months 100 days
Oracle BI Apps 7.9.x 7.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
Oracle BI Apps Roadmap
Oracle BI Applications 7.9 Unified enterprise data model Enhanced DAC Leverages OBI EE 10gR3 Translation to 15 languages Oracle BI Applications 7.9.1 Certify SEBL 8.0 adapter
Oracle BI Applications 7.9.2 Profitability Analytics for OFSA
Oracle BI Applications 7.9.3 Certify PSFT Financials 8.4 / 8.8 and PSFT HR 8.8 FSPA Enhancements
Oracle BI Applications 7.9.4 Certify Oracle EBS R12
Oracle BI Applications 7.9.5 Certify PSFT 8.9&9.0 / SEBL 8.1 Informatica 8.1 support Oracle BI Applications 7.9.x New Application Content Oracle BI Applications 11gR2 Next Generation BI Apps Certify Additional Adapters New Application Content Oracle BI Apps for Fusion Applications
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
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
Oracle BI Applications ETL Adapter Support Summary Operational Application (e.g. PeopleSoft HR) CRM
Financials
Human Resources
Supply Chain
Supported
Order Management
Procurement & Spend
Not Supported
EBS 12 EBS 11.5.10 EBS 11.5.9
Currently shipping OBIA 7.9.5
EBS 11.5.8 PSFT 9.0 PSFT 8.9
** SAP 4.6c supported in OBIA 7.8.4
PSFT 8.8 PSFT 8.4 SAP 4.6c
**
**
Universal Siebel 8.1 Siebel 8.0 Siebel 7.8 Siebel 7.7 Siebel 7.5 Siebel 6.3
Not Applicable
**
**
Oracle BI Applications for Oracle eBusiness Suite Certified for 11i8, 11i9, 11i10, R12 Oracle BI Application
Oracle EBS module
Order Management Analytics
Oracle Order Management Oracle Financials (for Revenue)
Order Fulfillment Analytics Option
Oracle Order Management Oracle Discrete Manufacturing (for Inventory) Oracle Financials (for Receivables and Revenue)
Inventory Analytics
Oracle Discrete Manufacturing
Procurement and Spend Analytics Supplier Performance Analytics
Oracle Purchasing/Procurement Oracle iProcurement Oracle Financials (Payables)
Oracle BI Applications for Oracle eBusiness Suite Certified for 11i8, 11i9, 11i10, R12 Oracle BI Application
Oracle EBS module
General Ledger & Profitability Analytics Oracle Financials Payables Analytics (GL, Payables, Receivables) Receivables Analytics Human Resources Operations & Compliance Analytics
Oracle Human Resources
Human Resources Compensation Analytics
Oracle Payroll
Oracle BI Applications for Oracle Financial Services Applications (OFSA) Certified for 4.5 Oracle BI Application
Oracle product
Financial Services Profitability Analytics
Oracle Financial Services Applications
Oracle BI Applications for PeopleSoft Enterprise Certified for 8.4 (Financials only), 8.8
Oracle BI Application
PeopleSoft Enterprise module
General Ledger & Profitability Analytics Payables Analytics Receivables Analytics
Financials (GL, Payables, Receivables)
Human Resources Operations & Compliance Analytics
Human Resources
Human Resources Compensation Analytics
Payroll eCompensation
Oracle BI Applications for SAP R/3 Applications Certified for 4.6c* Oracle BI Application
SAP R/3 module
Order Management Analytics
Sales & Distribution (SD) Financial Accounting (FI) (for Revenue)
Order Fulfillment Analytics Option
Materials Management (MM) (for Inventory) Financial Accounting (FI) (for Revenue)
* Support for version 4.6c is with the Oracle BI Applications version 7.8.4
Oracle BI Applications for SAP R/3 Applications Certified for 4.6c* Oracle BI Application
SAP R/3 module
Inventory Analytics
Materials Management (MM)
Procurement and Spend Analytics Supplier Performance Analytics
Materials Management (MM) Financial Accounting (FI)
General Ledger & Profitability Analytics Financial Accounting (FI) Payables Analytics Receivables Analytics
* Support for version 4.6c is with the Oracle BI Applications version 7.8.4
Oracle BI Applications for Siebel CRM Applications Certified for 6.3, 7.5.x, 7.7.x, 7.8.x, 8.0.x Oracle BI Application Sales Analytics
Siebel CRM Horizontal App Siebel Sales
Usage Accelerator Analytics Option Service Analytics
Siebel Call Center, Siebel Service, Siebel Field Service
Marketing Analytics
Siebel Enterprise Marketing
Marketing Planning Analytics Option
Siebel Marketing Resource Management
Order Management Analytics
Siebel Customer Order Management (C/OM)
Partner Analytics
Siebel Partner Relationship Management
Oracle BI Applications for Siebel CRM Applications Certified for 6.3, 7.5.x, 7.7.x, 7.8.x, 8.0.x Oracle BI Application Pharma Sales Analytics
Siebel CRM Vertical App Siebel Life Sciences Pharmaceuticals
Pharma Marketing Analytics Financial Institution Analytics
Siebel FINS
Financial Retail Analytics Consumer Packaged Goods Sales Analytics
Siebel Consumer Goods
Case Management Analytics
Siebel Public Sector
Case Investigations Analytics Option Benefits Management Analytics Option
Oracle BI Applications with Universal Adapters
Oracle BI Application All BI Applications now have Universal Adapter support, including CRM, which was previously unsupported
Architecture
Oracle BI Applications Architecture
Dashboards by Role
n o it a rt s i n i m d A
Oracle BI Presentation Services
Reports, Analysis / Analytic Workflows
Metrics / KPIs Logical Model / Subject Areas
Oracle BI Server
Physical Map Data Warehouse / Data Model
Direct Access to Source Data
a t a d a t e M
Load Process Staging Area
ETL
Extraction Process
C A D
Oracle
SAP R/3
Siebel
PSFT
EDW
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 Abstracted Data Model Conformed Dimensions Heterogeneous Database support Database specific indexing Highly Parallel Multistage and Customizable Deployment Modularity
General Packaging & Integration Points • Analytic applications support multiple Dashboards by Role
n o it a rt s i n i m d A
source systems and data types
Oracle BI Presentation Services
Reports, Analysis / Analytic Workflows Metrics / KPIs Logical Model / Subject Areas
Oracle BI Server
Physical Map Data Warehouse / Data Model
Direct Access to Source Data
Load Process Staging Area
ETL
Extraction Process
Oracle
SAP R/3
Siebel
PSFT
EDW
C A D
a t a d • a t e M
• • • •
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
Example of ETL Adapter Business 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 Tables
Source Qualifier
Expression Expression Transformation
Mapplet output to Extract Mapping
ETL Overview • Three approaches to accessing / loading Dashboards by Role
n o it a rt s i n i m d A
source data
Oracle BI Presentation Services
• Batch ETL (Full or Incremental) • Micro ETL or Trickle Feed ETL • Direct access to source data from Oracle BI Server
Reports, Analysis / Analytic Workflows Metrics / KPIs Logical Model / Subject Areas
Oracle BI Server
Physical Map Data Warehouse / Data Model
Direct Access to Source Data
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
Load Process Staging Area
a t a d a t e M
• ETL Layered architecture for extract,
ETL
Extraction Process
C A D
• 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 Oracle
SAP R/3
Siebel
PSFT
EDW
Data Extraction and Load Process Oracle Business Analytics Warehouse (OBAW)
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
Source Independent Layer
d a o L
Staging Tables
Source Dependent Extract L Q S
Siebel OLTP
L Q S
Oracle
Power Connect L Q S r e y a L p p A
Power Connect P A B A r e y a L p p A
PeopleSoft SAP
L Q S
Other
t c a r t x E
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
Oracle EBS
Business Component Mapplet (for PO Fact)
Universal Source
Expression Transformation
Source Adapter Mapplet
Universal Staging Table
Data Extraction and Load Process Business Analytics Warehouse
Source Independent Layer (SIL)
Encapsulates warehouse load logic
Source Independent Layer
Handles:
Slowly changing dimensions
Key lookup resolution / surrogate key generation
Insert/update strategic
Currency conversion
Data consolidation
Uses Bulk Loaders on all db platforms
d a o L
Staging Tables
Source Dependent Extract L Q S
Siebel OLTP
L Q S
Oracle
Power Connect L Q S r e y a L p p A
Power Connect P A B A r e y a L p p A
PeopleSoft SAP
L Q S
Other
t c a r t x E
Example of ETL Adapter…Contd. Source Independent Load (SIL) mapping for Purchase Order Lines Fact • 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
Universal Staging Table
Expression Expression Transformation
Source Independent (SIL) Mapplet
W_PURCH_ORDER_F Data Warehouse Table
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
Physical Data Model Overview
Dashboards by Role
n o it a rt s i n i m d A
Oracle BI Presentation Services
Reports, Analysis / Analytic Workflows Metrics / KPIs Logical Model / Subject Areas
Oracle BI Server
Physical Map Data Warehouse / Data Model
Direct Access to Source Data
Load Process Staging Area
ETL
Extraction Process
C A D
a t a d a t e M
• Integrated enterprise-wide data
• • •
• • Oracle
SAP R/3
Siebel
PSFT
EDW
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
Oracle BI Apps: Selected Key Entities Unified multi-source data model Sales Sales
Opportunities Opportunities Quotes Quotes Pipeline Pipeline
Order OrderManagement Management Sales Order Lines Sales Order Lines Sales Schedule Lines Sales Schedule Lines Bookings Bookings Pick Lines Pick Lines Billings Billings Backlogs Backlogs
Call CallCenter Center
ACD Events ACD Events Rep Activities Rep Activities Contact-Rep Snapshot Contact-Rep Snapshot Targets and Benchmark Targets and Benchmark IVR Navigation History IVR Navigation History
Service Service
Service Requests Service Requests Activities Activities Agreements Agreements
Marketing Marketing
Workforce Workforce
Supply SupplyChain Chain
Pharma Pharma
Campaigns Campaigns Responses Responses Marketing Costs Marketing Costs Purchase Order Lines Purchase Order Lines Purchase Requisition Lines Purchase Requisition Lines Purchase Order Receipts Purchase Order Receipts Inventory Balance Inventory Balance Inventory Transactions Inventory Transactions
Finance Finance
Receivables Receivables Payables Payables General Ledger General Ledger COGS COGS
Compensation Compensation Employee Profile Employee Profile Employee Events Employee Events Prescriptions Prescriptions Syndicated Market Data Syndicated Market Data
Financials Financials
Financial Assets Financial Assets Insurance Claims Insurance Claims
Public PublicSector Sector Benefits Benefits Cases Cases Incidents
Conformed ConformedDimensions Dimensions Customer Customer Products Products Suppliers Suppliers Cost Centers Cost Centers Profit Centers Profit Centers Internal Organizations Internal Organizations Customer Locations Customer Locations Customer Contacts Customer Contacts GL Accounts GL Accounts Employee Employee Sales Reps Sales Reps Service Reps Service Reps Partners Partners Campaign Campaign Offers Offers Employee Position Employee Position Hierarchy Hierarchy Users Users Modular ModularDW DWData DataModel Modelincludes: includes: ~350 Fact Tables ~350 Fact Tables ~550 ~550Dimension DimensionTables Tables ~5,200 prebuilt Metrics ~5,200 prebuilt Metrics (2,500+ (2,500+are arederived derivedmetrics) metrics)
Server Repository Overview
Dashboards by Role
n o it a rt s i n i m d A
Oracle BI Presentation Services
Reports, Analysis / Analytic Workflows Metrics / KPIs Logical Model / Subject Areas
Oracle BI Server
Physical Map Data Warehouse / Data Model
Direct Access to Source Data
• Multi-layered Abstraction • Prebuilt Metrics/Dimensions • Prebuilt hierarchy drills and cross •
Load Process Staging Area
a t a d a t e M
ETL
Extraction Process
C A D
• • • •
Oracle
SAP R/3
Siebel
PSFT
EDW
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
OBI EE Plus Vs OBI Applications Oracle Business Intelligence Enterprise Edition Plus
Oracle Business Intelligence Applications –Prebuilt Metadata
OBI Applications – Prebuilt Metadata Inventory Compound Metrics: Inventory Turns Example
OBI Applications – Prebuilt Metadata Dimensions
OBI Applications – Prebuilt Metadata Hierarchies – Plant Location Example
Web Catalog Overview
Dashboards by Role
n o it a rt s i n i m d A
Oracle BI Presentation Services
Reports, Analysis / Analytic Workflows Metrics / KPIs Logical Model / Subject Areas
Oracle BI Server
Physical Map Data Warehouse / Data Model
Direct Access to Source Data
Load Process Staging Area
ETL
Extraction Process
Oracle
SAP R/3
Siebel
PSFT
EDW
C A D
a t a d a t e M
• • • • • • • •
Role based dashboards Prebuilt Reports/Dashboards Guided Navigation Conditional navigational links Analytic Workflows Alerts Highlighting Action Links to Oracle EBS, PeopleSoft, Siebel CRM
Example of Role Based Dashboard Order Management Overview Dashboard Dashboard Pages Roles
Conditional Navigation
Performance Measures
Highlighting
Guided Navigation
Reports based on Multiple Sources
Flexible View Selectors
Prebuilt Reports
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
Example of Inventory Analytics Workflow Business Objectives / Issues
Reduce Inventory
Is Inventory Turns on target?
Gain Insights
Is Inventory Balances trending up?
Is Sales declining?
What are the Top 10 Products by Inventory Value?
Is Cost of Goods sold increasing
What is the Sales Trend for these products
Drill to Sales Backlogs/ Bookings
What are the Plants holding these inventories
Take Action
Identify top 5 plants with highest inventory and
Is Days of Supply on target?
• Business Function: Inventory • Role: Inventory Manger • Objectives: • 1) Reduce Inventory • 2) Increase working capital
Analytic Workflows – Inventory Analytics Business Objectives / Issues
Reduce Inventory
Is Inventory Turns on target??
Is Inventory Balances trending up?
Gain Insights
What are Top 10 Products By Inventory Value?
What is the Sales Trend for these products
Drill to Inventory Location Details
Take Action
Target Efforts to reduce the inventory
D r i l l t o D e ta i l
Tight Integration with Oracle Applications Action 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
Deployment Options • Standalone Dashboards • Portal integration via JSR-168/WSRP • Embedded Directly in Oracle EBS
Oracle BI Applications Process Flow
Lab Exercise: Installation and Configuration (Viewlet)
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
Presentation & Lab Exercise: Data Warehouse Administration Console (DAC) Overview
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 warehouse processes.
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. 4. 5. 6.
Informatica server accesses workflows in Informatica repository Informatica server processes the workflows Data is extracted from the transactional database(s) Data is transformed and then loaded in OBAW
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 you assemble a subject area, the DAC automatically assigns tasks to it.
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 Applications Data Warehouse Administration Console Guide located on the DVD in the BI_Apps_795_Docs folder
Lab Exercise: DAC Configuration for Order Management
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 Applications Data Warehouse Administration Console Guide located on the DVD in the BI_Apps_795_Docs folder
Lab Exercise: DAC Execution for Order Management – Extraction & Load
DAC Execution Lab Lab 1.4 DAC Execution (OM) – Main Steps
• • •
Error procedure
• • • •
•
Run Execution Plan from previous lab Monitor progress 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
Presentation: Oracle Business Analytics Warehouse (OBAW) Overview
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
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
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
Selected Star Schemas in OBAW
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
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
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
Base Fact Table
DayTime_ID Store_ID
5 Million Rows
Customer_ID Sales_$
Aggregated Table
Month_ID Region_ID Customer_Category_ID Sales_$
100,000 Rows
Aggregate Tables • OBAW contains pre-built
aggregate tables
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
Slowly Changing Dimension Problem 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
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
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 tables For 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
Conforming Dimensions • Fact tables share the same dimension tables • Conforming across CRM & ERP • Conforming across Multiple Sources • Ensure Cross Fact Analysis
Dimension
Fact
Dimension
Dimension
Fact
Dimension
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
Time Dimensions DIMENSION • Date
PHYSICAL TABLES
DESCRIPTION
• W_DAY_D, W_MONTH_D,
• Store the date and Gregorian
W_QTR_D, W_YEAR_D
calendar hierarchy information • Store the fiscal calendar
• W_FSCL_WEEK_D, • Fiscal
W_FSCL_MONTH_D, W_FSCL_QTR_D, W_FSCL_YEAR_D
• Hour of the Day
• W_HOUR_OF_DAY_D
• Time of the Day
• W_TIME_OF_DAY_D
• Period
• W_PERIOD_D,
W_PERIOD_DH
hierarchy information • These dimension tables are mainly used in Financial subject area • Aggregate facts against these tables • Store the time interval at the
granularity of seconds • Store the hours of a day • One record per hour • Marketing Period • Not a conformed dimension
today
Account Dimensions
DIMENSION
PHYSICAL TABLES
DESCRIPTION • Stores information of all
• GL Account
• Profit Center
• Cost Center
• W_GL_ACCOUNT_D
• W_PROFIT_CENTER_D • W_IERARCHY_DH • W_COST_CENTER_D • W_HIERARCHY_DH
General Ledger accounts and account hierarchy such as GL Account Number, Name, Account Group etc. • Stores profit center
information such as Number, Name and Hierarchy etc. • Stores cost center information
such as Number, Name and Hierarchy etc
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
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
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.
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
OBAW Data Model Reference https://metalink3.oracle.com/od/faces/secure/km/DocumentDisplay.jspx?id=578880.1
Metalink3 > Knowledge Tab > Business Intelligence category > Data Model Reference > Oracle Business Analytics Warehouse Data Model Reference, Version 7.9.5 (Doc ID 578880.1)
Presentation: Oracle BI Applications – Order Management Analytics Overview
Sales Process – Scenario for Selling Computers Lead There is a person in the world who wants to buy a computer
Oppty There is a person in the world who wants to buy a computer – THAT my company builds
Order #
abc123
Computer
$
600
Speakers
$
100
Monitor
$
300
Total
$
1,000
Discount Total Order Amount
Order
Booking
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.
Booking
Booked List Amount
Computer
$
600
Booked Discounted Amount
$
900
Speakers
Monitor
Order #abc123
$
$
$
100
300
1,000
$
$
$
The factory needs to build/assemble/ find this computer.
Unscheduled Backlog Unscheduled Backlog Amount
540
10% $
Unsched Backlog
90
Computer
$
540
Speakers
$
90
Monitor
$
270
Order #abc123
$
900
270
900
Sales Process – Scenario Selling Computers continued… Sched. Item
Picked Item
The factory schedules the build /assembly of the computer to be completed on a specific date. This date is the Promised 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.
Computer
$
540
Speakers
$
90
Monitor
$
270 900
Account Receiv.
Cash
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.
Invoice Total Picked Amount
Scheduled Backlog Amount
$
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.*
Picking
Scheduled Backlog
Order #abc123
Invoice Item
Account Receivable Invoice List Amount
Net Invoiced Amount
Closing Group Amount
Computer
$
540
Computer
$
600
$
540
Speakers
$
90
Speakers
$
100
$
90
Monitor
$
270
Monitor
$
300
$
270
Order #abc123
$
900
Order #abc123
$
1,000
$
900
Customer A
$
900
Order to Cash: Business Problems • 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
Order received
Order built
Order verified for accuracy
Order picked
Finance accepts it Or not
Order shipped
Order scheduled
Customer accepts the order Or not
Order invoiced
Invoice paid Or not
• 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
The more quickly and accurately an organization executes this process, the more efficiently it runs.
Oracle BI Applications Multi-Source Analytics with Single Architecture Auto
Comms & Media
Complex Consumer Sector Mfg
Sales
Service & Contact Center
Pipeline Analysis
Energy
Financial Services
High Tech
Insurance Life & Health Sciences
Public Sector
Travel & Trans
Marketing
Order Management & Fulfillment
Supply Chain
Financials
Human Resources
Churn Propensity
Campaign Scorecard
Order Linearity
Supplier Performance
A/R & A/P Analysis
Employee Productivity
Triangulated Forecasting
Customer Satisfaction
Response Rates
Orders vs. Available Inventory
Spend Analysis
Sales Team Effectiveness
Resolution Rates
Product Propensity
Cycle Time Analysis
Procurement Cycle Times
Customer & Product Profitability
HR Compliance Reporting
Up-sell / Cross-sell
Service Rep Effectiveness
Loyalty and Attrition
Backlog Analysis
Inventory Availability
P&L Analysis
Workforce Profile
Cycle Time Analysis
Service Cost Analysis
Market Basket Analysis
Fulfillment Status
Employee Expenses
Expense Management
Turnover Trends
Lead Conversion
Service Trends
Campaign ROI
Customer Receivables
BOM Analysis
Cash Flow Analysis
Return on Human Capital
GL / Balance Compensation Sheet Analysis Analysis
Other Operational & Analytic Sources
Prebuilt adapters:
Oracle BI Suite Enterprise Edition
Oracle Order Management and Fulfillment Analytics 1
Pre-built warehouse with 20 star-schemas designed for analysis and reporting on sales, fulfillment and receivables data.
3
Pre-mapped metadata, including embedded best practice calculations and metrics for the sales organization. organization. Presentation Layer
16 Subject Areas
Logical Business Model
20 Fact and 50 Dimension Tables
2
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. Business Analytics Warehouse
CRM Finan Financi cial al HR
OM
ETL & Business Adapters
Legacy Legacy POS Inventory OM
4
A “best practice” library of pre-built pre-built intelligence dashboards, reports and alerts for sales representatives, representatives, analysts, managers and executives
Oracle Order Management & Fulfillment Analytics Sales Order Lines Sales SalesOrder Order Details Details
Cust. Cust.Location Location Sold to / Ship to / Sold to / Ship to / Bill to Bill to
Employee Employee
Customers Customers
Sales SalesChannel Channel
Products Products Mfg / Sales /
Sales Order Lines
Mfg / Sales / Supplier Supplier
Locations Locations Plant / Mfg
Sales SalesOrgs Orgs
Plant / Mfg Ship / Storage Ship / Storage
EAI
Payment ETL Payment Terms Terms
Date Date
Features
Includes 27 logical dimensions and 33 3 3 out of the box metrics
Provides ability to do detailed analysis of sales order lines
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 Booking Amt / Qty Total Ordered Amt / Qty Total Return Amt / Qty
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 c ycle 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, com plete, contextual information that is tailored specifically to their role ro le
Oracle Order Management and Fulfillment Analytics Complete solution for gaining insight into the Order to Cash process Order Management Analytics Foundation application module that provides insight into critical Order Management business processes and key information, including Orders, Invoices, Sales Effectiveness and Customer Reports.
Order Fulfillment Analytics Provides 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 Oracle Sales Analytics Analyze 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.
Analytic Workflows – Order Management Analytics Manage Order Management performance
Business Objectives / Issues
Is Order Fulfillment on target?
Is Cumulative Invoice Revenue Trending up?
Gain Insights
Take Action
Is Cumulative Order Revenue Trending up?
How much order revenue is in highest fulfillment lag?
What is the trend of Average Order Size?
Which products have the highest fulfillment lag?
What is the trend of Order Revenue by Channel and Customer Category
Drill to Current Backlog and Inventory by Product
Create more Inventory of Products in demand
Is Sales Cycle Time on target?
How much are my Top Customers ordering?
• Business Function: Order Management • Role: Director, Sales Operations • Objectives: • Optimize Order Fulfillment • Reduce Sales Cycle Time
Analytic Workflows – Order Management Analytics Business Objectives / Issues
Manage Order Management performance Is Order Fulfillment on target? Is Cumulative Invoice Revenue Trending up?
Gain Insights
How much order revenue is in highest fulfillment lag?
Which products have the highest fulfillment lag? Drill to Current Backlog and Inventory by Product
Take Action
Create more Inventory of Products in demand
D r i l l to D e t a i l
Oracle Order Management Analytics Sample Order Management Analytics Metrics Order Fulfillment Metrics
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
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 Pre-Built Dashboards VP Sales
OM Manager
Receivables Manager
Sales Rep
• • • •
• • • •
• • • •
• • • •
Revenue Forecast Backlog Sales Cycle
Effectiveness Fulfillment Backlog Exception
A/R Revenue Forecast Overview
Customer Sales Cycle Fulfillment Effectiveness