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
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.
- 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 22.214.171.124
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
|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)
- Schema information
- Table relationships
- Indices (ETL / Query)
- Source / Target tables
- Full and incremental commands
- 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
- 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
- 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
- Abstracted Rich Data Model
- Conformed Dimensions
- Heterogeneous Database support
- Database specific indexing
- 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