BI Apps Architecture

INSTRUCTIONS FOR USE • THIS PRESENTATION IS MEANT FOR TECHNICAL AUDIENCES TO COVER DETAILED ARCHITECTURE FOR THE ORACLE BI APPS

© 2008 Oracle Corporation – Proprietary and Confidential

1

NOTES TO SPEAKER • This presentation is meant for an audience that doesn’t understand the value of the prebuilt BI Apps / BI & Data Warehousing • Looking to buy a point solution like DBI, Noetix, etc. • Thinks they can build their own solution for f or similar cost with Cognos or BOBJ

• This is not a product pitch – rather an educational session • The flow is to argue for a BI App, then argue for a pre-built BI App, then reinforce the need to look at what it takes to build a world class BI solution (not just reports) • There are additional slides in the Backup section. Please use them as appropriate • Use the PeopleSoft specific slides and Oracle EBS specific slides as appropriate for the customer’s source system


2

NOTES TO SPEAKER • This presentation is meant for an audience that doesn’t understand the value of the prebuilt BI Apps / BI & Data Warehousing • Looking to buy a point solution like DBI, Noetix, etc. • Thinks they can build their own solution for f or similar cost with Cognos or BOBJ

• This is not a product pitch – rather an educational session • The flow is to argue for a BI App, then argue for a pre-built BI App, then reinforce the need to look at what it takes to build a world class BI solution (not just reports) • There are additional slides in the Backup section. Please use them as appropriate • Use the PeopleSoft specific slides and Oracle EBS specific slides as appropriate for the customer’s source system


2

OTHER RELATED MATERIALS • Each application has its own presentation • Oracle BI Applications Overview • Oracle Financial Analytics • Oracle Supply Chain and Order Management Analytics • Oracle Procurement and Spend Analytics • Oracle Human Resource Analytics • Oracle Sales Analytics • Oracle Service and Contact Center Telephony Analytics • Oracle Marketing Analytics

• There are presentations for specific topics • Oracle BI Applications Architecture Overview • Oracle BI Applications Consolidated Data Model • Oracle BI Applications Integration with Oracle Applications © 2008 Oracle Corporation – Proprietary and Confidential

3


4

Safe Harbor Statement

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.


5

Oracle BI Applications Architecture

Program Agenda Overview of Oracle BI Applications Oracle BI Applications Architecture Q&A


7

Product Overview


8

Oracle BI Applications Multi-source Analytic Apps Built on BI Suite EE Plus Interactive Dashboards

Sales

Reporting & Publishing

Service & Contact Center

Ad-hoc Analysis

Marketing

Proactive Detection and Alerts

Procurement & Spend

Disconnected Analytics

Supply Chain & Order Management

MS Office Plug-in

Financials

Web Services

Human Resources

Oracle BI Applications Oracle BI Apps built on Oracle BI EE Plus

• Common Enterprise Information Model • Prebuilt Hierarchies, Drill Paths, Security, dashboards, reports • Based on industry and analytic best practices

Packaged ETL Maps © 2008 Oracle Corporation – Proprietary and Confidential

Universal Adapters

IVR, ACD, CTI Hyperion MS Excel Other Data Sources

9

Oracle BI Applications Multi-Source Analytics with Single Architecture Auto

Comms & & Media

Complex Complex Mfg Mfg

Consumer Consumer Sector Sector

Energy

Financial Financial Services Services

High High Tech Tech

Insurance Insurance & & Health Health

Life Life Sciences Sciences

Public Public Sector Sector

Travel Travel & & Trans Trans

Sales


Marketing

Procurement & Spend


Financials

Human Resources

Pipeline Analysis

Service Effectiveness

Campaign Effectiveness

Direct / Indirect Spend

Revenue and Backlog

General Ledger

Employee Productivity

Forecast Accuracy

Customer Satisfaction

Customer Insight

Buyer Productivity

Inventory

Accounts Receivable

Compensation

Sales Team Effectiveness

Resolution Rates

Product Propensity

Off Contract Purchases

Fulfillment Status

Accounts Payable

Compliance Reporting

Up-sell/ Cross-sell

Service Rep Efficiency

Loyalty & Attrition

Supplier Performance

Customer Status

Cash Flow

Workforce Profile

Cycle Times

Service Cost

Market Basket Analysis

Purchase Cycle Time

Order Cycle Time

Profitability

Retention Analysis

Lead Conversion

Churn & Service Trends

Campaign ROI

Employee Expenses

BOM Analysis

Expense Management

Return on Human Capital

Other Operational & Analytic Sources

Source adapters:

Oracle BI Suite Enterprise Edition Plus © 2008 Oracle Corporation – Proprietary and Confidential

10

Financial Analytics Components 1

2

Pre-built warehouse with 16 star-schemas designed for analysis and reporting on financial analytics

Pre-built ETL to extract data from over 3,000 operational tables and load it into the DW, sourced from SAP, PSFT, Oracle EBS and other sources


3

4

Pre-mapped metadata, including embedded best practice calculations and metrics for financial, executives and other business users •

Presentation layer

•

Logical business model

•

Physical sources

A “best practice” library of over 360 pre-built metrics, 30 intelligent dashboards, 200+ reports and several alerts for CFO, Finance Controller, Financial Analyst, AR/AP Managers and Executives

11

Technical Overview


12

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

Load Process Staging Area

ETL

Extraction Process

Oracle

SAP R/3

Siebel

PSFT

C A D

a t a d a t e M



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

EDW

Federated Data Sources


Other

13

ETL Overview Dashboards by Role





Oracle BI Server



• Multiple Approaches to loading source data • ETL Layered architecture for extract, universal staging and load • Provides isolation, modularity and extensibility


a t a d a t e M

ETL

Extraction Process

C A D

• Architected for performance • All mappings architected with incremental extractions

• Data Warehouse Administration Console (DAC) • Application Administration, Execution and Monitoring

Oracle

SAP R/3

Siebel

PSFT

EDW



Other

14

ETL Overview Dashboards by Role


Business Analytics Warehouse




Oracle BI Server

Physical Map

a t a d a t e M

Source Independent Load

Staging Tables

Data Warehouse / Data Model



ETL

Extraction Process

C A D

Source Dependent Extract L Q S

Siebel OLTP

Oracle

d a o L

SAP R/3

Siebel

PSFT

EDW



Other

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

L Q S

t c a r t x E

Other

PeopleSoft SAP

15

Data Extraction and Load Process Business Analytics Warehouse

Extract 









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

d a o L

Staging Tables

Extract L Q S

Siebel OLTP

L Q S

Oracle



L Q S

t c a r t x E

Other

PeopleSoft SAP © 2008 Oracle Corporation – Proprietary and Confidential

16

Data Extraction and Load Process Extraction Modes 





Direct Database Access  Oracle EBS  Siebel CRM PowerConnect technology  SAP R/3  PeopleSoft Enterprise Universal Business Adapters  Used for sources with no prepackaged business adapter  Transforms and loads data from universal sources through a flat file interface  Supply flat file(s) or tables meeting a defined standard format  Reduces implementation and maintenance significantly compared to building full ETL solution from scratch

Business Analytics Analytics Warehouse


d a o L

Staging Tables


Siebel OLTP

L Q S

Oracle



L Q S

t c a r t x E

Other


17

Data Extraction and Load Process Business Analytics Warehouse

Source Independent Layer 

Encapsulates warehouse load logic



Handles:





Slowly changing dimensions



Key lookup resolution / surrogate key generation



Insert/update strategies



Currency conversion



Data consolidation

Uses Bulk Loaders on all db platforms


d a o L

Staging Tables


Siebel OLTP

L Q S

Oracle



L Q S

t c a r t x E

Other


18

ETL Component Architecture Source Dependent Extract

(SDE)

Oracle

s e c r uSAP R/3 o S a t a Siebel D d e t a r PSFT e d e F

t n e n o p t me o lp C p s a s M e n i s u B

EDW

r e t p a d A e c r u o S

t e l p p a M

mplt

Universal Universal Staging Staging Area Area


(SIL)

t n e d n e p e d n I e c r u o S

t e l p p a M

Business Business Analytics Analytics Warehouse Warehouse

PLP

mplt

Other

mplt


Each record is stamped with Source ID

Post Load Process – Designed to build Aggregates on Base Fact Tables

19

ETL Mapping Example – Product Dimension Business Component Mapplet for Oracle Applications Source

Oracle EBS Source Table

Source Qualifier


Expression Transformation

Mapplet output to Extract Mapping 20

ETL Mapping Example – Product Dimension Extract Mapping (to Temporary Staging) for Oracle Applications Source

Business Component Mapplet for Oracle EBS Product Master © 2008 Oracle Corporation – Proprietary and Confidential

Source Adapter Mapplet

Temporary Staging Table

21

ETL Mapping Example – Product Dimension Extract Mapping (Temporary Staging to Final Staging) for Oracle Applications Source

Temporary Staging Tables (Product Master)

Source Qualifier



Universal Staging Table 22

ETL Mapping Example – Product Dimension Load Mapping for Oracle Applications Source

Universal Staging Table

Source Qualifier


Source Independent Load (SIL) Mapplet

W_PRODUCT_D Data Warehouse Table 23

ETL Mapping Example – Product Dimension Configuration Example – ROW_WID (Primary Key) •

Flexibility of Configuration • Configuration of grain of data for facts/dimensions easily possible


24

ETL Mapping Example – Product Dimension Configuration Example – Slowly Changing Dimension


25

Incremental Extraction and Load The Logic Behind incremental ETL • A variety of strategies used to optimize incremental extracts and loads • Overall Philosophy – Extract incrementally if possible, else load incrementally • Siebel

• 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 • PeopleSoft

• Use a date window and last update dates for extraction wherever possible • 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


26

Incremental Extraction and Load How Last Minute Changes Are Captured • Mostly one set of Mappings for both Initial and Incremental Loads • Incremental extract will pick up the data that has been created/updated since the last extract date • No need not worry about last minute changes • Changes before the extract mapping runs, will be picked up in the current run • Changes after that will be picked up in the next run

• Controlled through DAC, Parameter “Prune Days” • Allows to go back "n" number of days since the last extract which will ensure that last minute changes have been picked up.


27

Incremental Extraction and Load DAC Parameter Setting for Prune Days


28

Data Warehouse Administration Console (DAC) Strong Competitive Differentiator • 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


29

Physical Data Model Overview Dashboards by Role





Oracle BI Server




ETL

Extraction Process

Oracle

SAP R/3

Siebel

PSFT

C A D

EDW



Other

a t a d a t e M

• 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 30

Common Enterprise Information Data Model

Customers

Customers

e c i v r e S

s e l a S

Customers

g n it e k r a M

n o it u b ir t s i D

e c n a n i F

e c r o f k r o W / R H

s n o it a r e p O

t n e m e r u c o r P

Suppliers

Suppliers

Suppliers

Features:

Benefits:

• Conformed dimensions

•

• Transaction data stored in most granular fashion

Enterprise-wide business analysis (across entire value chain)

•

Access summary metrics or drill to lowest level of detail

•

Accurate historical representations

• Tracks full history of changes • Prebuilt and extensible • Built for speed


31

Support for Cross-Functional Analysis Pre-requisite of common conformed dimensions

Dim Table Dimension Dim Table Tables

Dim Table Dimension Dim Table Tables

Products Dimension

Purchase Orders Fact Table

Sales Orders Fact Table

Supply Chain & Order Management Analytics

Time Dimension

Procurement and Spend Analytics

Q. How many of my top customers bought products from my worst suppliers? © 2008 Oracle Corporation – Proprietary and Confidential

32

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 



Marketing Marketing 

Campaigns  Campaigns  Responses  Responses  Marketing Costs  Marketing Costs

Supply SupplyChain Chain 

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 



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 



Workforce Workforce 

Compensation Compensation Employee Profile  Employee Profile  Employee Events  Employee Events 



Pharma Pharma  

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  Incidents  Leads  Leads 




Conformed ConformedDimensions Dimensions                                

Customer Customer Products Products Suppliers Suppliers Cost CostCenters Centers Profit Centers Profit Centers Internal InternalOrganizations Organizations Customer CustomerLocations Locations Customer CustomerContacts Contacts GL Accounts GL Accounts Employee Employee Sales SalesReps Reps Service ServiceReps Reps Partners Partners Campaign Campaign Offers Offers Ledger Ledger

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) ~15,000 Data Elements ~15,000 Data Elements 33

Types of Fact Tables • All base facts are based on single business function, e.g. Sales Order Lines, Sales Cycle Lines, etc. • Types of Fact Tables • Transactional Facts

• Mirror of transactional data from the source • Lowest atomic grain of each transactional record • E.g. Employee Events Fact, Order Lines Fact • Snapshot Facts

• Snapshot of balances across time • E.g. Employee Daily Snapshot, Inventory & Account Balances, AR and AP aging snapshots & Opportunity Pipeline • Cycle Lines Facts

• Derived from multiple fact tables, typically store process cycle times • E.g. Sales Order Cycle Lines, Purchase Cycle Lines • State Transition Facts

• Required for specialized transition count metrics • Customer State transitions (# New, # Inactive customers, etc) • Aggregate Facts

• Performance enhancement on key metrics • E.g. Payroll Aggregate (out of box Monthly, but configurable to other grains) © 2008 Oracle Corporation – Proprietary and Confidential

34

Business Process Conformance • Native source specific transaction types are transformed and appended to standardized warehouse codes • Standardized warehouse codes are superset of all supported sources • Native source specific transaction types are retained for source specific analysis • E.g. Ethnic Codes, HR Event Types, Credit Memo, Debit Memo types


35

Business Process Conformance Example: Accounts Receivable Process Standard Warehouse Code Invoice

Invoice and Payment Schedules

Invoice items

AR documents with DR document type

CR Memo/DR Memo

CR Memo and DR Memo

CR Memo and DR Memo items

AR documents with DA document type

Payments

Cash Receipts etc.

Payments/ Deposits

AR documents with DA document type and ‘A’ as special indicator

Payment Applications

Cash/CR Memo applications/Adjustments

Payment Item Activities

AR documents with DZ document type

Other

All other types of transactions

BI/AR: All other types of items

All other types of AR documents


36

Business Process Conformance Example: Accounts Receivable Process • All source transaction type code values are stored and appended with standardized warehouse codes • All higher layers in the architecture and metrics use same standardized warehouse codes • Stored source transaction type code values enables integration back into each transactional system

TYPE CODE

TYPE_DESC

WAREHOUSE CODE

O

Credit memo

Credit Memo

4

Credit memo list

Credit Memo

S

Credit memo cancellation

Credit Memo Cancellation

P

Debit memo

Debit Memo

6

Intercompany credit memo

5

TYPE CODE

TYPE_DESC

WAREHOUSE CODE

Credit Memo

Credit Memo

Credit Memo

On-Account Credit

On-Account Credit

Credit Memo

Debit Memo

Debit Memo

Debit Memo

Intercompany Credit Memo

Debit note

Debit note

Debit Memo

Intercompany invoice

Intercompany Invoice

Invoice

Invoice

Standard Invoice

N

Invoice cancellation

Invoice Cancellation

Contra

Contra

Standard Invoice

U

Pro forma invoice

Pro forma Invoice

3

Invoice list

Standard Invoice

M

Invoice

Standard Invoice


37

Business Process Conformance Example: Employee FLSA Status Derivation Process • All source FLSA Status code values are stored and appended with standardized warehouse codes • All higher layers in the architecture and metrics use same standardized warehouse warehouse codes • Stored source FLSA Status code values enables integration back into each transactional system

TYPE CODE

TYPE_DESC

WAR WA REHOUSE CODE

TYPE CODE

TYPE_DESC

WAREHOUSE CODE

N

Nonexempt

Non-Exempt

NEX

Non Exempt

Non-Exempt

E

Executive

Exempt

EX

Exempt

Exempt

M

Management

Exempt

A

Administrative

Exempt

P

Professional

Exempt

O

Outside Salesperson

Exempt


38

Enhanced Multi Currency Support •

BI Apps Physical Data Model stores and supports 5 currencies • • •

• • • •

Document Currency – the currency of the transaction Local Currency - the base currency of your ledger, the currency in which your accounting entries are recorded in Global Currencies – common currencies used • OOTB support for three global currencies (Global1, Global2, Global3) • Default currency for all reporting is Global1

Every amount from source system is loaded into Document Currency amount and Local Currency amount Exchange rates sourced from source system to load each of the 3 global currencies All currency amounts are stored as persisted calculations Currency conversion is done at ETL time


39

Multiple Calendar Support • Supports Gregorian and Fiscal hierarchies out of the box • Allows configuration of fiscal calendar, e.g. 4-4-5

• Configuration to support multiple fiscal hierarchies depending on user profile • Requires Initialization block to read user profile • Dynamically use the appropriate calendar table

• CRM OnDemand implementation supports twelve fiscal calendars


40

Localization / Globalization • Dynamic Language Conversion (translation to 28 languages) • Arabic, Chinese (Simplified), Chinese (Traditional), Croatian, Czech, Danish, Dutch, English, Finnish, French, German, Greek, Hebrew, Hungarian, Italian, Japanese, Korean, Norwegian, Polish, Portuguese (Brazilian), Portuguese (European), Romanian, Russian, Slovak, Spanish, Swedish, Thai, Turkish

• Codepage / Unicode support • Dynamic Time Zone translations • All timestamps are stored in warehouse stored as UTC

• Custom labeling and naming support / Flex Field Naming • Column Names can be dynamically named


41

Data Integration – Multi Source Loads • Segregation and Lineage captured in data model • Source data keys, comprised of:

INTEGRATION_ID

+

DATASOURCE_NUM_ID

Part no. Plant id ‘MT27907b’

+

‘NW3’

+

‘SAPJapan’

Example: Key_id for Products dimension SAP

‘MATNR ~ MANDT’

Oracle EBS

‘item_id ~ organization_id


42

Business Process Outsourcing Support Oracle BI Apps supports multi-tenant concept • Unique Enterprise Multi-Source data model • Segregates and maintains data lineage via DATASOURCE_NUM_ID when loading data from several sources • Additional TENANT_ID key allows even more flexibility for data model to support multiple-tenanted source systems

• Enables a variety of deployment, data segregation / separation strategies within the same data warehouse End Customer

Data Sources

BPO Customer 1

PSFT HR 8.8 North America instance

BPO Customer 1

PSFT HR 8.8 European instance

BPO Customer 2

Oracle EBS 11i10 HR instance


43

Server Repository Overview

Dashboards by Role





Oracle BI Server



• Multi-layered Abstraction • Prebuilt Metrics/Dimensions • Prebuilt hierarchy drills and cross dimensional drills • Prebuilt Aggregate navigation


a t a d a t e M

ETL

Extraction Process

C A D

• Multi-pass complex calculated Metrics / KPIs • Federation of queries • Visibility & Personalization

Oracle

SAP R/3

Siebel

PSFT

• Prebuilt Security inherited from Oracle EBS, PeopleSoft, Siebel CRM

EDW



Other

44

Metrics and Calculations • The applications provide several complex metrics defined logically with an expression builder • Share base (e.g. % share of one product sales over all products sales) • Indexes (e.g. this division’s performance as a ratio of all divisions) • Cross subject area metrics (across Finance and HR, e.g. Revenue per employee) • Variances (e.g. Budget vs Actual) •

Time series metrics •

MTD/YTD/Running balances/MAVG (e.g. Revenue Chg % YTD)

•

Period Ago Metrics (e.g. Revenue YAGO)

•

Snapshot at any point of time support (e.g. # Open Service Requests)

All of these are done logically without adding data model/ ETL complexity © 2008 Oracle Corporation – Proprietary and Confidential

45

OBI EE Plus vs BI Applications Oracle Business Intelligence Enterprise Edition Plus

Oracle Business Intelligence Applications – Prebuilt Metadata


46

BI Applications – Prebuilt Metadata Example Physical to Business Model Mapping


47

BI Applications – Prebuilt Metadata Example Business Model to Presentation Mapping


48

BI Applications – Prebuilt Metadata Example Inventory Compound Metrics- Inventory Turns Example • Flexibility of configuring derived calculation expressions


49

BI Applications – Prebuilt Metadata Example Hierarchies – Plant Location Example


50

BI Applications – Prebuilt Metadata Example Rich Dimensional Attributes


51

Web Catalog Overview

Dashboards by Role





Oracle BI Server




ETL

Extraction Process

Oracle

SAP R/3

Siebel

PSFT

C A D

EDW



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

Other

52

Dashboard Pages

Example: Role Based Dashboard Roles

Performance Measures

Highlighting Conditional Navigation

Flexible View Selectors

Reports based on Multiple Sources

Prebuilt Reports with over 5200 metrics

Guided Navigation


53

Even more metric value under the surface Metrics used in Reports & Dashboards

Not all measures in presentation layer used in reports & dashboards

Metrics in Subject Areas

Subset of logical measures are exposed in presentation layer

Metrics in Logical Layer

Aggregations, time series calculations and derived calculated measures extend physical measures

Metrics in Physical Warehouse

Measures from physical columns in data warehouse


54

Analytic Workflows Directed analysis helps inexpert users 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


55

Analytic Workflows – Financial Analytics Business Objectives / Issues

Maximize Cash Flow

Is DSO on target?

Is Overdue Balances trending up?

Is DPO on target?

Are Payment Terms in compliance?

• Business Function: Gain Insights

What is the aging of Due Balances?

Who are the Customers and Collectors?

Drill to Due Balances by Region

Drill to Overdue Invoice Detail

Take Action

Receivables

How long is the underlying Overdue Balance pending?

• Role: Director, Credits & Collections

• Objectives: • 1) Maximize Cash Flow • 2) Control Risk of Receivables Portfolio

Target collection efforts to reduce overdue balances


56

Analytic Workflows – Financial Analytics Business Objectives/ Issues

Maximize cash flow

Is DSO on target?

Is overdue balances trending up?

Gain Insights

Drill to Detail

How long is the underlying overdue balance pending?

Who are the customers?

Drill to overdue invoice detail

Take Action

Target collection efforts to reduce overdue balances


57

Navigate from Analytical to Operational Insight to Action via Action Links • Seamless navigation from analytical information to transactional detail while maintaining context • Works with Oracle EBS, Siebel CRM, PeopleSoft

Action Links


58

Flexfield and Value Set Integration • Key Flexfields (KFFs) • • • • • •

Accounting Flexfield (GL#) Territory Flexfield (CT#) Item Categories (MCAT) HR Position (POS) HR Job (JOB) HR Pay Grade (GRD)

• Descriptive Flexfields (DFFs) • Stored in data warehouse extension columns • E.g. GL Account attribute fields

• Value Set Hierarchy • Extract hierarchical relationships of Value Sets


59

Deployment Options Standalone Interactive Dashboards


BI in a Portal via JSR-168/WSRP

60

Deployment Options Embedded Directly in Applications E-Business Suite

PeopleSoft

Siebel © 2008 Oracle Corporation – Proprietary and Confidential

61

Oracle BI Apps Security Integration Tight security integration with Oracle Applications

Sales


Marketing

Procurement & Spend


Financials

Human Resources

Oracle BI Applications Tightly integrated security model

Oracle BI Foundation

User Security

Object Security

Data Security

Supports iPlanet, Novell, MSFT AD, Oracle Identity Directory (OID), and others Right content shown to the right user


62

User Security

User Security

How do I get in? • Oracle BI EE Security Based Authentication • Defines and stores user information in the RPD

• LDAP Authentication • Stores information from the LDAP server in session variables

• External Table Authentication • Maintains list of users in an external database

• Database Authentication • Authenticates users through database logins

• Single Sign-On (SSO) • Provides an open interface to enable web integration with SSO products


63

User Security

Example: Single Sign-On with Oracle E-Business Suite Applications 3 navigate to

2 store ICX session cookie in browser

5 establish connection with Oracle EBS

user

Web Browser

Oracle EBS

Oracle BI

1 log in once

6 drill back to Oracle EBS via Action Links

4 validate session via the ICX cookie


64

Object Security

Object Security What parts of the application can you see? • Business Logic Object Security Presentation Layer Semantic Object Layer Physical Layer

Controls access to Subject Areas, Tables and Columns

• Web Object Security Limits access to Dashboards, Reports and Web Folders

• Query Blocking • Specified combinations of columns in queries can be blocked from running and the user is notified of the illegal query © 2008 Oracle Corporation – Proprietary and Confidential

65

Object Security for Siebel, EBS and PeopleSoft Associated with Web Catalog Group: Order Management Manager

Object Security

Dashboards: Order Management Order Fulfillment

is assigned

User:

Responsibility/Role : Order Management Manager

is associated with

OM Manager

Subject Areas: Associated with Security Group: Order Management Manager


Sales - Order Lines Sales - Orders, Backlog and Invoices

66

Data Security for EBS

Data Security

What data can you see? • Multi-Org data access support with Oracle E-Business Suite applications: • Operating Unit • Inventory Organization • Business Group • Company Org • Ledger/ Set of Books • Position • HR Security Profile


67

Data Security

Data Security for EBS Example: Responsibility and Inventory Org relationship in Oracle E-Business Suite

assigned to

Responsibility

user

associated with

Inventory Organization


68

Data Security

Data Security for EBS Example: Inventory Org Based Security in Oracle BI

Oracle E-Business Suite 2

retrieve Inventory Orgs based on FND_USER tables 1

log in

Oracle BI user

show data based on security group filters 3


69

Application Upgrade Methodology Dashboards by Role


Web Catalog Upgrade UI based tool to analyze and merge reports/dashboards  Forward compatible with new BI Presentation Services releases 




Oracle BI Server




ETL

Extraction Process

C A D

a t a d a t e M

Analytics Repository Upgrade  Automated 3-way merge functionality functionality  Original OOTB, Customer Modified, Current OOTB  Preserves customer changes and incorporates Oracle enhancements ETL & Warehouse Upgrade  Customization Methodology isolates customer changes  Diff tools to determine and reapply changes  DW model and data upgrade shipped with each release DAC metadata  Diff tools to determine and reapply changes

Oracle

SAP R/3

Siebel

PSFT

EDW



Other

70

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

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 Quarters or Years

Easy to use, easy to adapt

Prebuilt Business Adapters for Oracle, PeopleSoft, Siebel, SAP, others

Weeks or Months

Source: Patricia Seybold Research, Merrill Lynch, Oracle Analysis


71

Typical Effort & Customization balance

Dashboards & Reports

OBIEE Metadata

Easy

Moderate

DW Schema

Intermediate

ETL

Involved

Degree of Customization © 2008 Oracle Corporation – Proprietary and Confidential

Additional dashboards and reports, guided and conditional navigations, iBots, etc. Additional derived metrics, custom drill paths, exposing extensions in physical, logical and presentation layer, etc. Extension of DW Schema for extension columns, additional tables, external sources, aggregates, indices, etc. Extension of ETL for extension columns, descriptive flexfields, additional tables, external sources, etc.

Level of Effort 72


73

BACKUP SLIDES


74

ETL


75

Example: Employee Dimension populated from PeopleSoft SDE_PSFT_EmployeeDimension_Phones SDE_PSFT_EmployeeDimension_Phones SDE_PSFT_EmployeeDimension_Addresses SDE_PSFT_EmployeeDimension_Addresses SDE_PSFT_EmployeeDimension_Biography1 SDE_PSFT_EmployeeDimension_Biography1 SDE_PSFT_EmployeeDimension_Biography2 SDE_PSFT_EmployeeDimension_Biography2

W_EMPLOYEE_D W_EMPLOYEE_D

SDE_PSFT_EmployeeDimension_JobInformation SDE_PSFT_EmployeeDimension_JobInformation

SDE_PSFT_EmployeeDimension SDE_PSFT_EmployeeDimension SIL_EmployeeDimension SIL_EmployeeDimension


76

Employee Dimension populated from PeopleSoft SDE_PSFT_EmployeeDimension_Phones

PeopleSoft Source BC maplet (see below)

PeopleSoft Source Table


Source Qualifier




Maplet output to Extract Mapping 77

Employee Dimension populated from PeopleSoft SDE_PSFT_EmployeeDimension_Addresses




Source Qualifier





Employee Dimension populated from PeopleSoft SDE_PSFT_EmployeeDimension_Biography1 Temporary Staging Table


Expression Transformation Lookup Procedures

PeopleSoft Source Tables

Source Qualifier




Employee Dimension populated from PeopleSoft SDE_PSFT_EmployeeDimension_Biography2





Source Qualifier




Employee Dimension populated from PeopleSoft SDE_PSFT_EmployeeDimension_JobInformation




Lookup Procedures


Source Qualifier




Employee Dimension populated from PeopleSoft SDE_PSFT_EmployeeDimension

maplet


Source Qualifier




82

Employee Dimension populated from PeopleSoft SDE_PSFT_EmployeeDimension



Lookup Procedures

Input to Maplet (from above) © 2008 Oracle Corporation – Proprietary and Confidential


Employee Dimension populated from PeopleSoft SIL_EmployeeDimension


84

Employee Dimension populated from PeopleSoft SIL_EmployeeDimension_SCDUpdate

Expression Transformation Target Definition

Source Definition

Filter (SCD logic)

Source Qualifier


85

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



Mapplet output to Extract Mapping 86

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)


Source Adapter Mapplet


Universal Source

Flat File Source (for PO

Source

© 2008 Oracle fact) Corporation – Proprietary and Confidential Qualifier


Universal Staging 87 Table

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




Source Independent (SIL) Mapplet

W_PURCH_ORDER_F Data Warehouse Table 88

PeopleSoft Trees


89

PeopleSoft Tree Support via ETL • Challenge • Tree structures require recursive joins • Relational BI technologies do not support this

• Approach • Import tree structures into metadata • Flatten structures via ETL • Use a star schema for data storage • Define hierarchies instead of nodes This is accomplished using the provided ETL technology as part of the pre-built BI Applications – using concept of an application source rather than a relational source


90

Importing Records


91

Importing PeopleSoft Pages


92

Importing PeopleSoft Trees


93

Action Links


94

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 • Works with Oracle EBS, Siebel CRM, PeopleSoft Enterprise


95


96


97


98


99

Action Link configuration for Oracle EBS • Identify the Oracle E-Business Suite Application page/function that you want to link to. Obtain the function_id of that page and identify the query string parameters required by that page. This will have to be done by going through Oracle E-Business Suite documentation • Identify the Oracle E-Business Suite table that will support the parameters needed for the Oracle E-Business Suite function (page) that you want to build an Action link to and create this physical table in the Oracle E-Business Suite OLTP schema as an opaque view • Action Link is dynamically generated by calling the FND_RUN_FUNCTION.GET_RUN_FUNCTION_URL() function in the Oracle E-Business Suite Database Schema


100

Action links between Analytics reports and Siebel CRM • Direct navigation from record to transactional while maintaining context • Java script in Siebel Analytics issues SWE command to navigate to given view and query for ROW_ID of record in given applet


101

Action Links Configuration for Siebel CRM


102

Action Link configuration for PeopleSoft Enterprise • Create a URL Id for the path to the PIA component in the PSURLDEFN table in EPM. Add a forward slash to the beginning of the string. Make sure that all the search key fields are on the string and their parameter values are denoted as :1, :2 etc depending on the number of keys of that component • In the OBIEE Administration tool, create an Initialization Block and a Session Variable that we will use to create the URL for the source system. We need to create an Init Block. for each drill-down PIA page • Create a new logical column and use as action link


103

Security


104

Example: External Table Authentication with Siebel CRM Applications

User Security

2 execute “Authentication” Init Block

user

Web Browser

Siebel CRM Application DB Table S_USER

Oracle BI

1 log in

3 authenticate username and password


105

Example: Single Sign-On with Siebel CRM Applications

User Security

2 integrate via symbolic URL infrastructure

3 establish connection using NQHOST parameter

user

Web Browser

Siebel CRM

Oracle BI

1 log in once

4 drill back to Siebel CRM via Action Links


106

Example: Role Identification with PeopleSoft

Object Security

PeopleSoft

2

fetches roles from PSROLEUSER via session init block 1

log in

Oracle BI user


3

show objects based on matching object security group

107

Data Security for Siebel CRM

Data Security

What data can you see?

• Data Security support with Siebel CRM Applications: • Position • Owner • Organization


108


Data Security

Example: Position Based Security

is assigned

Position: Sales Manager Analytics

User:

Sales Manager

(is stamped on Data Object) can view

Data Object: Account Partner Order


109


Data Security

Example: Position Based Security ETL

Oracle Business Analytics Warehouse

Siebel CRM

2

retrieve position hierarchy from the warehouse 1

log in

Oracle BI user



110

Data Security for PeopleSoft

Data Security

What data can you see? • Data Security support with PeopleSoft Applications: • Set ID • Company Org • Operating Unit • HR Business Unit • Payables Business Unit • Receivables Business Unit • Position • Ledger/ Set of Books


111


Data Security

Example: Operating Unit Based Security

PeopleSoft

2

fetches Operating Unit Organization (s) via session init block 1

log in

Oracle BI user



112


Data Security

Example: HR Business Unit Based Security

Managed by

User:

HR Organization

HR Analyst

Owned by

can view

Data Object: HR Data


113

Organization Based Data Security

Data Security

Regular Employee Data Visibility • We extract employee-manager relationships from PS_JOB

• A regular employee can only see his data and his subordinates’ data


114


Data Security

HR Analyst Data Visibility • An HR analyst can see all HR data for an Organization he or she is managing


115


Data Security

HR Analyst Data Visibility • Within his own Organization, an HR analyst can only see his subordinates’ HR data


116

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.


117


118

BI Apps Architecture

Recommend Documents