Three months ago started the Oracle program: Are You The Smartest.
For me it is an opportunity to test my current knowledge level and to extend my knowledge.
After every session I follow, I will write a brief summary as part of the preparation for the test.
I will continue with the summary of the following session.

ARCHITECTS TRAINING – BUSINESS INTELLIGENCE – Oracle Business Intelligence Applications – Overview

This 1,5 hour during session was divided into the following parts:

• BI Challenges
• What are Oracle BI Applications?
• Oracle BI Applications Components & Architecture

BI Challenges

Today’s Typical BI Landscape

Hard to Maintain, Duplication and Inconsistency of Tooling

Business questions touch multiple processes.

Building BI Solutions is Challenging

Start from scratch?

Oracle BI Applications Provide a Single Integrated View of Enterprise Information

• Integrated enterprise-wide intelligence
• Summary level to lowest level of detail
• Data warehousing best practices – conformed dimensions, lowest level of granularity, full change histories for time comparisons, built for speed, extensible

On top we have the datamodel (warehouse) to answer business questions.
ETL: Extraction Transformation Loading

What are Oracle BI Applications?

Prebuilt BI Solutions for EBS, PeopleSoft, Siebel, JD Edwards, Fusion Applications & more.
Customers always have the same kind of questions.

• Benefits
  • Add insight to CRM and ERP applications
  • Easy to adapt and extend
• Unique features
  • Tight integration with OLTP systems (OnLine Transaction Processing)
  • Works with existing IT environment
  • Fast time to value; Low TCO
  • Over 4,000 customers

BI Applications – Rapid Performance Insight (samples)

• Employee Productivity
• Project Revenue
• Resolution Rates

Has over 6,500 pre-defined assets (dashboards, dashboard pages, reports and metrics).

Provide Rich Analysis based on best practices  

Analytic Workflows – e.g. Financial Analytics

Overview of what’s in Financial Analytics 7.9.6.3

Alignment Across the Enterprise. eg: Assess impact of product mix and discounts on revenue and margins

More than just dashboards and reports: Value of BI Applications lies under the surface

Conformed dimensions: eq same for sales and finance departments.

Robust Application Infrastructure

• Prebuilt Integrations: Fusion Applications, E- Business Suite, Peoplesoft, JD Edwards, Siebel, SAP. Action links to click through to a specific ‘order’ in the operational system.
• Rich Data Model: 10 years of best practices from BI modeling accommodating source system complexities
• Extensive BI related transformations: Slowly Changing Dimension support, Hierarchy flattening support, Currency Conversion, UOM conversion, Dynamic Data translations, Code standardization (Domains), Historical Snapshots, Cycle and process lifecycle computations, Balance Facts
• Flexible ETL Architecture: Multi Source, Multi Technology, Simplified but Optimized Orchestration Plan, High availability with ‘Follow the Sun’ support, Near Real Time Support with Micro-ETL
• Broad Deployment Choices: Heterogeneous Database & Operating System Neutral Deployment Support
• Support for Custom Configuration and Extensions: Support for Key Flex fields and Descriptive Flex fields, Extensible attribute s(JDE), and Conformed Domains
• Highly tuned Performance: Optimized for BI and analytic queries, Prebuilt aggregates for scalable end user performance, Incremental extracts and loads, Incremental Aggregate, Automatic table index and statistics management, Parallel ETL loads, Low latency Micro ETL support, Bitmap Indexes, Partitioning support

Speeds Time To Value and Lowers TCO 

Key Benefits

Improve visibility and  insight into  performance, processes, and  customers	•   Compare operational results to plans in real-time•   Quickly identify and respond to problems & opportunities•   Drive revenue and profit growth with better targeting•   Increase customer profitability and share of wallet
Align strategy and tactics across  functions	•   Manage and execute at all levels based on common view of information and common performance metrics•   Improve efficiency and reduce costs while maintaining good product quality and customer satisfaction•   Identify and replicate operational best practices
Leverage existing data,  applications, and IT  staff	•   Add value and insight to CRM and ERP applications•   Get faster time-to-value with lower cost and risk•   Lower the total cost of ownership compared to custom built solutions

Oracle BI Applications Components & Architecture

Oracle BI Applications Components

• Source system (eBS, Peoplesoft, JDE, Siebel, Fusion, SAP)
• ETL (Data Warehouse Administration Console (DAC) + metadata, Informatica + metadata) – Probably informatica will be replaced somewere in the future by ODI.
• Data Warehouse Database (Prebuilt schema)
• Oracle BI Enterprise Edition (Repository metadata, Web Catalog metadata) – repository contains the mapping of the physical db schema into the logical business attributes,  Web catalog contains reports and dashboards
• Client Tools to maintain all components

 Oracle BI Applications Architecture 

 ETL – DAC & Informatica

• Highly Parallel execution
• Multistage and Customizable
• Supports Deployment Modularity

What is the DAC?

Tool to manage the prebuilt OBIA data warehouse

• Create prebuilt data warehouse schema
• Contains high level semantic ETL metadata
• Run & Monitor ETL processes (Executes Informatica workflows) Knows the correct execution order.

About DAC Repository Objects

• Containers / Adaptors
  • Information on source system metadata (per version)
• Tables
  • Schema information
  • Table relationships
  • Indices (ETL / Query)
• Tasks
  • Source / Target tables
  • Full and incremental commands
  • Phases
•  Subject Areas
  • Defined by one or more star schemas
  • Assembling a Subject Area automatically collects the tasks that need to be run to populate the star schemas
• Execution Plans
  • Defined by one or more Subject Areas from one or more containers/adaptors
  • Building an Execution Plan results in ordered execution graph of the collection of tasks from the Subject Areas

What is Informatica?

Informatica is a Data Integration Platform

• A platform to define logic to Extract-Transform-Load data into data warehouse

ETL Objects

• Mappings – define transformation logic to load a certain warehouse table
• Sessions – Compiled versions of Mappings
• Workflows – A collection of Sessions

How does DAC interact with Informatica?

DAC Orchestrates ETL routines written in Informatica
Uses the command line interface tools of Informatica to run workflows

• PMCMD / PMREP

Consolidates runtime / database specific / SQL / application level parameters into a parameter file while invoking the workflows
Collect statistics

• Workflow status
• Number of rows processed
• Read/Write throughputs

Informatica Mappings – 2 Stage-Loading

Source Dependent Extract (SDE)

• Extracts (changed) data from source(s) to staging table

Source Independent Loads (SIL)

• Loads data from staging table to warehouse table

Informatica Mappings 

Data Warehouse 

• Abstracted Rich Data Model
• Conformed Dimensions
• Heterogeneous Database support
• Database specific indexing

Syntax

• W_     Warehouse
• _DS   Dimension staging
• _D     Dimension
• _FS    Fact staging
• _F      Fact

OBIEE – Repository

• Logical to Physical Abstraction Layer
• Calculations and Metrics Definition
• Visibility & Personalization
• Dynamic SQL Generation

Common Enterprise Information Model

Single Consistent View and User Self-Sufficiency

One more thing…
Even more 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

OBIEE – Reports & Dashboards 

• Role Based Dashboards
• Analytic Workflow
• Guided Navigation
• Alerts & Proactive Delivery
• Security / Visibility
• Integrated BI

All BI Applications are BI Mobile Certified

Typical Effort & Customization balance