Business Intelligence mit Oracle9i

Dr. Jens Albrecht
Berater
Business Intelligence & Warehousing
Oracle Deutschland GmbH
Business
Intelligence
mit Oracle9i
Ein Überblick
Agenda
Oracle9i BI&W Architektur
ETL Funktionen
Materialized Views
Oracle Warehouse Builder
Discoverer mit Analytical Functions
Klassische BI Architektur
OLAP
Engine
ETL Tool
Analytic Apps
Transformation
Engine
ETLTool
Lineage
Mining
Engine
Query &
Analysis
Database
Transformation
Engine
Name/Address
Scrubbing
Reporting
Engine
Enterprise
Reporting
P
o
r
t
a
l
Oracle BI Architektur
Oracle BI Architektur
Oracle 9i Business Intelligence
Operational
Data
Discoverer
Oracle9i
BI Beans
ERP
Data
Warehouse
Builder
ETL
Data
Mining
Analytic
Functions
OLAP
Services
Clickstream
Personalization
External
Data
CWM and Repository
Enterprise
Enterprise Manager
Manager and
and Workflow
Workflow
Oracle 9i New Features
(relationales) Datenbanksystem
–
–
–
–
ETL
Partitioning (LIST)
Materialized Views
Analytical Functions
Tools
–
OWB, Discoverer, iAS, OLAP Services,
Agenda
Oracle9i BI&W Architektur
ETL Funktionen
Materialized Views
Oracle Warehouse Builder
Discoverer mit Analytical Functions
Klassischer ETL Prozess
DWH
OLTP
Extraktion
xtraktion
relevanter
relevanter
Daten
Daten
Transport
ransport
der
der
Daten
Daten
Cleaning
Cleaning
Transformation
ransformation
Einfügen
Einfügen //
Laden
aden
in
in das
das
DWH
DWH
„Transform-then-Load“
Einbindung externer Programme
–
aufwändig, ineffizient, langwierig, teuer
Oracle9i ETL
DWH 9i
OLTP
Extraktion
xtraktion
relevanter
relevanter
Daten
Daten
Transport
ransport
der
der
Daten
Daten
Cleaning
Cleaning
Transformation
ransformation
Einfügen
Einfügen //
Laden
aden
in
in das
das
DWH
DWH
Change
Data Capture
Transport. TS
External Tables
„Load-then-Transform“
Table Functions
Multi Table
INSERT &
MERGE
Externe Tabellen
Flat-Files als Read-Only Tabellen
Zugriff mit SELECT wie auf reguläre Tabellen
–
schneller Full-Table-Scan, aber keine Indizes
Definition über
CREATE TABLE .. ORGANIZATION EXTERNAL
Vorteile zu SQL*Loader
–
–
–
Selektion mit WHERE
Joins mit anderen (externen) Tabellen
Paralleles Lesen aus einer Datei
Externe Tabellen
CREATE TABLE Neukunden_Ext (
Vorname
CHAR(30),
Nachname
CHAR(30),
Geb_Tag
CHAR(12))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY ’/home/HRLoad’
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ’,’
BADFILE ’Neukunden_Ext.bad’
LOGFILE ’Neukunden_Ext.log’
MISSING FIELDS ARE NULL)
LOCATION (’Neukunden.txt’))
PARALLEL 4
REJECT LIMIT UNLIMITED
Externe Tabellen
SELECT * FROM Neukunden_Ext
INSERT INTO Kunden
SELECT
Ku_Seq.NEXTVAL, -- z.B. künstl. Schlüssel
Vorname,
Nachname,
TO_DATE(Geb_Tag, ’DD.MM.YY’)
FROM Neukunden_Ext
Table Functions
Funktionen, die eine Tabelle (= Menge von
Sätzen) zurückliefern
–
FROM Klausel eines SELECT Statements
Erlauben komplexe Transformationen von
Quelldaten
Ausführung durch Parallelisierung und
Pipelining beschleunigt
Implementierung in PL/SQL, JAVA, C
Table Functions
CREATE TYPE Ref_Cur_Type REF CURSOR;
CREATE TYPE Kunden_Type Kunden%ROWTYPE;
CREATE OR REPLACE FUNCTION Transform_Neukunden(c Ref_Cur_Type)
RETURN Kunden_Type
PIPELINED
PARALLEL_ENABLE (PARTITION c BY ANY) IS
v_Kunde Kunden_Type;
BEGIN
FOR rec IN c LOOP
/* Transformation Konsistenzprüfungen */
...
/* Befüllen des Datensatzes */
v_Kunde.Nr := ...;
PIPE ROW(v_kunde);
END LOOP;
RETURN;
END;
Table Functions & Ext. Table
f(x)
LOAD
FLAT FILES
STAGE
DW
Table Functions & Ext. Table
External
Table
FLAT FILES
Table
Function
NO STAGE!
INSERT /*+ APPEND, PARALLEL */
INTO Kunden
SELECT *
FROM TABLE(
Transform_Neukunden(
CURSOR(
SELECT *
FROM Neukunden_Ext)));
DW
Multi-Table INSERT
Einfügen in mehrere Tabellen mit einer
INSERT-Anweisung
–
–
Früher nur mit PL/SQL-Cursor möglich
Erspart mehrfaches Scannen der Quelldaten
Ausprägungen
–
–
Unconditional
Conditional FIRST, ALL
Intern für Refresh von MV’s verwendet
Proprietär, noch kein SQL Standard
Uncond‘l INSERT ALL Pivoting
Host_Quartal_Verkauf(
Produkt_Nr,
Jahr,
Umsatz_Q1, Umsatz_Q2,
Umsatz_Q3, Umsatz_Q4)
Quartal_Verkauf(
Produkt_Nr,
Quartal,
Umsatz)
INSERT ALL /*+ APPEND */
INTO Quartal_Verkauf VALUES
(Produkt_Nr,Jahr || ’/Q1’, Umsatz_Q1)
INTO Quartal_Verkauf VALUES
(Produkt_Nr,Jahr || ’/Q2’, Umsatz_Q2)
INTO Quartal_Verkauf VALUES
(Produkt_Nr, Jahr || ’/Q3’, Umsatz_Q3)
INTO Quartal_Verkauf VALUES
(Produkt_Nr, Jahr || ’/Q4’, Umsatz_Q4)
SELECT * FROM Host_Quartal_Verkauf
Conditional INSERT FIRST
INSERT FIRST
WHEN Umsatz > 1000
INTO Top_Produkte VALUES (Produkt_Nr)
WHEN Umsatz > 500
INTO Mittelmaessige_Produkte VALUES (Produkt_Nr)
ELSE
INTO Ladenhueter_Produkte VALUES (Produkt_Nr)
SELECT Produkt_Nr, SUM(Umsatz) AS Umsatz
FROM Quartal_Verkauf
GROUP BY Produkt_Nr, Quartal
MERGE
Insert bzw. Update
–
–
–
Abhängig davon, ob Match-Kriterium erfüllt
(Datensatz schon vorhanden) oder nicht
Früher nur mit PL/SQL-Cursor möglich
Erspart mehrfaches Scannen der Quelldaten
Standardoperation im DWH
Vorgeschlagen für SQL Standard
MERGE
Neukunden(
Name, Vorname,
Geb_Dat)
Update / Insert
Kunden(
Ku_ID,
Name, Vorname,
Geb_dat)
MERGE INTO Kunden K
USING Neukunden N
ON (N.Name = K.Name AND
N.Geb_Dat = K.Geb_Dat)
WHEN MATCHED THEN
UPDATE SET K.Name = N.Name,
K.Vorname=N.Vorname,
K.Geb_Dat=N.Geb_Dat
WHEN NOT MATCHED THEN
INSERT VALUES (Ku_Seq.NEXTVALUE,
N.Name, N.Vorname, N.Geb_Dat)
Fazit – Oracle9i ETL
Viele Aktivitäten innerhalb der Datenbank
–
Einfach und performant
Features
–
Change Data Capture, External Tables, Table
Functions, Multi-Table INSERT, Merge
Oracle 9i ermöglicht in einem Schritt
–
–
–
Paralleles Laden von Flat-Files
Filtern, Transformieren
Insert in verschiedene Zieltabellen oder Merge in
eine Zieltabelle
Agenda
Oracle9i BI&W Architektur
ETL Funktionen
Materialized Views
Oracle Warehouse Builder
Discoverer mit Analytical Functions
Materialized Views
Summen-Tabellen“
–
–
vorberechnete Aggregate
vorberechnete Joins
Ziele
–
–
–
Performance-Steigerung
Transparent für
Anwendungen („Query
Rewriting“)
möglichst inkrementelle
Wartung („Fast Refresh“)
Store
Product
Time
Sales
Data
Sum of
Sales by
Product
and
Store
Skip
Materialized View Definition
CREATE MATERIALIZED VIEW product_sales_mv
PCTFREE 0 TABLESPACE demo
BUILD DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE
AS SELECT p.prod_name, SUM(amount) AS dollar_sales
FROM sales s, products p
WHERE s.prod_id = p.prod_id
GROUP BY prod_name;
MV – Einfaches Rewriting
SELECT p.prod_name, SUM(amount) AS dollar_sales
FROM sales s, products p
WHERE s.prod_id = p.prod_id
GROUP BY prod_name;
SELECT prod_name, dollar_sales
FROM product_sales_mv;
MVs - Query Rewriting
Arten von Rewriting
Exact Match
Join Bedingungen und Grouping
Columns stimmen überein
Aggregation to All
Teilmenge der Grouping Columns
werden angefragt, z.B. MV auf
month, product; Anfrage auf
month
Rollup
Anfrage nach Aggregat auf höhrer
Ebene als MV, z.B. MV auf
month und Anfrage auf quarter
Join Back
Spalte in Anfrage kann durch Join
der MV mit Dim.Tabelle ermittelt
werden
MV – Oracle9i New Features
Query Rewrite auch bei
WHERE Klausel in Definition von MV
Weniger Restriktionen für Fast Refresh
Verbesserter Summary Advisor
Package Explain Materialized View
MV mit Restriktionen
CREATE
CREATE MATERIALIZED
MATERIALIZED VIEW
VIEW product_sales_mv2
product_sales_mv2
ENABLE
ENABLE QUERY
QUERY REWRITE
REWRITE
AS
AS SELECT
SELECT p.prod_name,
p.prod_name, SUM(amount)
SUM(amount) AS
AS dollar_sales
dollar_sales
FROM
FROM sales
sales s,
s, products
products pp WHERE
WHERE s.prod_id
s.prod_id == p.prod_id
p.prod_id
AND
AND prod_name
prod_name >> 'B'
'B' AND
AND prod_name
prod_name << 'D'
'D'
GROUP
GROUP BY
BY prod_name;
prod_name;
Anfrage
SELECT
SELECT p.prod_name,
p.prod_name,
SUM(amount)
SUM(amount)
FROM
FROM sales
sales s,
s, products
products pp
WHERE
WHERE s.prod_id
s.prod_id ==
p.prod_id
p.prod_id AND
AND
prod_name
prod_name >> 'B'
'B' AND
AND
prod_name
prod_name <'C'GROUP
<'C'GROUP BY
BY
prod_name;
prod_name;
REWRITE
SELECT
SELECT prod_name,
prod_name,
SUM(dollar_sales)
SUM(dollar_sales)
FROM
FROM
product_sales_mv2
product_sales_mv2
WHERE
WHERE prod_name
prod_name >>
'B'
'B' AND
AND prod_name
prod_name
<'C'GROUP
<'C'GROUP BY
BY
prod_name;
prod_name;
Skip
Explain_MV in OEM
Summary Advisor und OEM
Summary Advisor und OEM
Fazit – Oracle9i MV‘s
Vorteile
–
–
Beschleunigung für DW-Anfragen
Einfache Wartbarkeit
Verbessert
–
–
–
Query Rewrite
Fast Refresh
Auswahl und Management
Agenda
Oracle9i BI&W Architektur
ETL Funktionen
Materialized Views
Oracle Warehouse Builder
Discoverer mit Analytical Functions
Oracle Warehouse Builder 3i
Graphisches Entwicklungswerkzeug für
–
–
Data Warehouse Modellierung (CWM)
Load Management
Daten-Extraktion
–
–
–
–
Relational
Flat File
Oracle Apps
SAP
Oracle Warehouse Builder 3i
Common Warehouse Metamodel
–
OMG-Standard
Integration mit anderen Produkten
–
–
–
–
–
OLAP Services (CWM)
Discoverer
iAS Portal
Oracle Workflow
Oracle Enterprise Manager
OWB Architecture
User
Code
Integrators
Integrators
Interface Generator
Runtime
API
Code
Repository
Target Schema
Development Environment
Runtime Environment
OWB Features
Generiert
–
–
–
–
SQL*Loader
PL/SQL
DDL
TCL für OEM
Sehr schnell, da
–
–
–
alles Datenbank-intern (PL/SQL)
Set-Based / Row-Based / Row-Failover Set
Bulk Load
D E M O N S T R A T I O N
Oracle Warehouse
Builder
Agenda
Oracle9i BI&W Architektur
ETL Funktionen
Materialized Views
Oracle Warehouse Builder
Discoverer mit Analytical Functions
Discoverer
Interaktives Analysewerkzeug
Rein relational (nicht Express)
Admin + User Edition
Drei Varianten
–
–
–
Discoverer 4i Desktop
Discoverer 4i Java (9iAS)
Discoverer 4i Viewer (9iAS)
Analytical Functions: Wofür?
Rangfolgen
–
RANK, NTILE, ...
Kumulative und laufende Aggregate
Ermittlung von Anteilswerten
Werten in bestimmten Abstand zum aktuellen
Wert gemäß einer Sortierung
–
LAG, LEAD, FIRST, LAST
Statistische Funktionen
–
Lineare Regression, Slope, Intercept
Analytical Functions: 9i
Inverse Percentile
–
Ermittlung des Wertes in einer Datenmenge, der
einem bestimmten Percentil entspricht
Hypothetischer Rang und Distribution
–
Der Rang oder das Percentil, das eine Zeile
zugewiesen bekäme, wenn sie in eine
spezifizierte Datenmenge eingefügt würde
Analytic Function - Template
Function OVER ( Partition Clause
Order by Clause
Window Clause )
Function - the analytic function being used
Partition Clause - arrange the result set into groups
Order by Clause - how data is ordered in partition
Window Clause - define set of rows to calculate result
Discoverer - Rank
Business analysis requirement
–
A ranking report of the cities by region where the
video rental store has outlets based upon sales
figures for the year
Discoverer reporting solution
–
Create a RANK() function
Discoverer - Rank
Rank function
Rank Sales =
RANK() OVER
(PARTITION BY
Calendar Year, Region
ORDER BY
Sales SUM DESC)
D E M O N S T R A T I O N
Discoverer
Skip
Discoverer - Top N / Bottom N
Business analysis requirement
–
A report which shows the top n / bottom n
products by year, region and city
Discoverer reporting solution
–
A modification of the RANK() report
Discoverer - Top N / Bottom N
Create 2 rank calculations
(1) Top N=
RANK() OVER
(PARTITION BY Calendar Year, Region, City
ORDER BY Sales SUM DESC )
(2) Bottom N=
RANK() OVER
(PARTITION BY Calendar Year, Region, City
ORDER BY Sales SUM ASC )
Discoverer - Moving Average
Moving average calculation
Sales 3 Month Moving Avg =
AVG(Sales SUM) OVER
(PARTITION BY Calendar Year
ORDER BY Calendar Month
RANGE INTERVAL '2' MONTH PRECEDING )
Fragen
Whitepaper
[email protected]
Q U E S T I O N S
A N S W E R S