OLAP Roadshow 062013

1
<Insert Picture Here>
OLAP – mit multidimensionale Datenanalysen
zielgerichtet in Unternehmensdaten navigieren
Detlef E. Schröder, Leitender Systemberater DWH & BI
Oracle Deutschland B. V. & Co KG
Agenda
•
•
•
•
•
•
•
•
•
•
9:30 Begrüßung und Vorstellung
9:45 OLAP integriert ins DWH
10:25 Die Power von OLAP entdecken
10:55 Pause
11:10 Aufbau eines Würfels
11:40 Lösungsarchitektur mit Excel
12:10 Mittagspause
13:00 Lösung eines mittelständischen Unternehmens
13:45 F&A
14:00 Zusammenfassung und Abschluss
OLAP INTEGRIERT INS DWH
4
Die Datenbank Strategie von Oracle
für das DWH - Embedded Analytics
OLAP
SQL Analytics
Statistics
Data Mining
• Die Analyse zu den Daten bringen
• Die Datenbank Infrastruktur optimal nutzen
Oracle OLAP
Die Datenbank Infrastruktur optimal nutzen
Oracle Database 11g
Data Warehousing
Warehouse Builder
OLAP
Data Mining
•
•
•
•
•
•
•
Nur ein RDBMS-MDDS Prozeß
Nur ein Daten - Speicher
Nur ein Sicherheitskonzept
Nur eine Administration
Skalierbar (Grid / RAC)
Offen für alle SQL Werkzeuge
Offene Verbindung zu allen Oracle
Lösungen
Oracle OLAP - Ziele
• Die Verfügbarkeit von analytischen SQL – basierten
Abfragen und Ergebnissen von beliebigen BI-Tools
und Applikationen zu erhöhen
•
•
•
•
Hohe Abfrage Performance
Einfacher Zugang zu analytischen Berechnungen
Schneller, inkrementeller Update
Unter Verwendung des bestehenden Oracle Wissens
OLAP im Data Warehouse
Den Wert Ihres DWH mit Oracle OLAP steigern
• Vereinfachte Aggregat Verwaltung
• Abfragegeschwindigkeit maximieren
• Erweiterte Zeitreihen- und Statistische - Analysen
• Zentrale Metadatenverwaltung für Daten, Aggregate ,
Berechnungen und Sicherheit
8
OLAP im Data Warehouse
Jedes Data Warehouse kann von Oracle OLAP profitieren
• Jedes BI – Tool kann die Aggregate verwenden
• Jeder Anwender will herausragende Abfragegeschwindigkeit in
Reporting und Analyse
• Jeder Anwender will die komplexen Berechnungen und Kennzahlen
nutzen
9
OLAP im Data Warehouse
Integriertes Oracle OLAP ist vor einer externen Lösung von
den IT Abteilungen bevorzugt
• Es benutzt die selbe Datenbank wie bisher im Einsatz
• Es können die selben BI-Tools weiter verwendet werden
• Das bestehende Oracle Wissen kann weiter verwendet werden
• Integriertes Oracle OLAP ist sicher und skalierbar
10
Oracle OLAP Option
• Die Aggregat - Lösung für SQL basierte BI
Anwendungen
• Eine alternative zu Tabellen basierten
Lösungen zur Abfrage- und Update
geschwindigkeits Steigerung (MAV)
• Ein vollständiger multidimensionaler OLAP
Server
• Herausragende Abfragegeschwindigkeit bei
Ad Hoc – und unvorhergesehenen Abfragen
• Erweiterte Analytische Möglichkeiten für
jedes BI-Tool
• Schneller inkrementeller Update
11
Oracle OLAP Option
• Eine integrierte OLAP Lösungen
• Läuft in der Oracle Database
Enterprise Edition
• Die Daten werden in Oracle Data
Files gespeichert
• Die Meta Daten sind im Oracle Data
Dictionary
• Volle Kompatibilität mit RAC / Grid
12
Oracle OLAP Option
• Eine sichere Lösung
• Oracle User sind OLAP users
• SQL GRANT / REVOKE auf
OLAP Würfel und Dimensionen
• Kompatibel mit Virtual Private
Database
• Zellbezogene Sicherheit
Oracle Authentication
SQL Cube Access Control
Virtual Private Database
Fine Grained Cube Security
13
Oracle OLAP Option
• Eine offene Lösung
• Oracle Würfel und Dimensionen
sind verwendbar mit
• SQL
• PL / SQL
• Oracle OLAP API
• Transparenter Zugriff als cubeorganized materialized view
• SQL
SELECT
time,
product,
customer,
sales_ytd
FROM
sales_cube
14
Oracle OLAP Option
• Eine Lösung mit hohem
Funktionsumfang
•
•
•
•
•
•
•
Vielfältige Aggregationen
Zeitreihen
Indizes und Anteile
Ranking
Forecasting
Allokationen
Statistik
• Berechnungen sind integriert
in der Datenbank
• Zentral administriert und
qualitätsgesichert
• Verfügbar für jede Applikation
15
Oracle OLAP Option
• OLAP Würfel bieten eine überragende
Abfragegeschwindigkeit bei
unvorhergesehene Abfragen
• Verwendbar für Reporting
und Analyse
• Vorteil beim Wechsel von
Reporting in Analyse
16
Oracle OLAP Option
• OLAP Würfel stellen eine
schnelle Möglichkeit zum
inkrementellen Update zur
Verfügung
• Verwaltung aller Aggregate in
einem DB Objekt
• Schneller, inkrementeller MAV
Refresh
• Schnelle und inkrementelle
Aggregation
• Kosten basierte Aggregation
17
Oracle OLAP Option
• Ein Würfel kann verwendet werden als …
• … Aggregations - Lösung für SQL basierte BI Applikationen
in Form von cube-organized materialized views
• … analytische Datenquelle mit vielen Kennzahlen für SQL
basierte BI-Tools in Form von SQL cube-views
• … vollumfänglicher multidimensionaler Server für jegliche
multidimensionalen BI Werkzeuge
18
SQL Abfragen auf OLAP Würfel
BI Application
BI Application
SQL
SQL
Cube
Materialized
Views
Automatic
Query
Rewrite
Cube Views
Oracle Cube
19
Single Version of the Truth
Metadata
Data
Business Rules
OLAP Query
Extract, Load
& Transform (ELT)
SQL Query
Zentral verfügbare Daten, Metadaten und Geschäftslogik
20
Cube-Organized Materialized Views
Automatic Query Rewrite
BI
Application
SELECT
SUM(sales)
GROUP BY
quarter,
brand,
region,
channel
•
Automatic
Query Rewrite
•
•
•
Ein Würfel enthält alle
Aggregate für jegliche
Kombinationen
Ein Würfel kann als cubeorganized MAV verwendet
werden
Oracle schreibt
automatisch alle Abfragen
in den Würfel um
Ein Würfel kann damit für
eine Vielzahl von MAV
stehen
Fact Table: Sales by Day, Item, Customer and Channel
21
Cube-Organized Materialized Views
Fast, Incremental MAV Refresh
BI
Application
•
SELECT
SUM(sales)
GROUP BY
quarter,
brand,
region,
channel
MV Refresh
Ein Würfel wird aktualisiert
über das MAV System
• Schneller,
inkrementeller Update
über MAV logs
• Schnelle,
inkrementelle
Aggregationen
innerhalb des Würfels
• Effiziente Verwaltung
von lückenhaften
Daten
• Ersetzt hunderte von
MAV
Fact Table: Sales by Day, Item, Customer and Channel
22
Cube Organized Materialized Views
Beispiel
• Ein Vergleich von Tabellen basierten MAV gegenüber
einer cube-organized MAV mit dem Ziel:
• Abfragegeschwindigkeit der SQL bsierten BI Tools zu
erhöhen
• Die Erstellungs- und Update - Zeit zu verkürzen
• Datenbasis
• Daten einer Handelskette mit typischem Schnellen Wandel
• 7 Dimensionen
• 20 Millionen Faktzeilen
24
Cube Organized Materialized Views
Beispiel
• Vorgehen
• Indices und MAV wurden erstellt wie vom Oracle SQL Access
Advisor vorgeschlagen.
• 124 MAV
• 198 Indices
• OLAP Würfel und cube-organized MAV wurden durch den
DBA erstellt.
• 1 konprimierter Würfel
• 20% Vorverdichtung
• 1469 Test Abfragen
25
Cube Organized Materialized Views
Beispiel
• Vergleichs - Kennzahlen
• Die Zeit zum laden und vorbereiten der Daten
• MAV: Erstellung der MAV, Indices und Statistiken
• Würfel: Laden und Aggregieren der Daten
• Abfragegeschwindigkeit
• Die gleichen 1469 Abfragen gegen MAV und Würfel
26
Cube Organized Materialized Views
Beispiel - Ergebnisse
180
160
140
120
Zeit in Minuten zum
100
Load and Aggregate
Query
80
60
40
20
0
Table MVs
Cube MV
27
OLAP Cubes Views
SQL Abfragen auf einen Oracle OLAP Würfel
Der Würfel wird als Starschema
von relationalen Views
dargestellt
• Dimensionen und Fakt Views
• Detail- und Aggregatzeilen
• Analytische Berechnungen in
den Spalten
OLAP Würfel enthalen
• alle Ebenen der Aggregation
• umfangreiche analytische
Kennzahlen
28
Analytische Power für jedes SQL-Tool
Als Beispiel APEX
Application Express mit Oracle OLAP
SELECT cu.long_description customer,
f.profit_rank_cust_sh_parent,
f.profit_share_cust_sh_parent,
f.profit_rank_cust_sh_level,
f.profit,
f.gross_margin
FROM time_calendar_view t,
product_primary_view p,
customer_shipments_view cu,
channel_primary_view ch,
units_cube_view f
WHERE
AND
AND
AND
AND
AND
AND
AND
AND
t.level_name = 'CALENDAR_YEAR'
t.calendar_year = 'CY2006'
p.dim_key = 'TOTAL'
cu.parent = 'TOTAL'
ch.dim_key = 'TOTAL'
t.dim_key = f.TIME
p.dim_key = f.product
cu.dim_key = f.customer
ch.dim_key = f.channel;
Als Step by Step auf
www.oracledwh.de
29
Oracle OLAP Option
Zusammenfassung
• Ermöglicht jedem SQL basierten Werkzeug den
performanten Zugrif auf den analytischen Inhalt
• Kann verwendet werden als
• Aggregats – Lösung mit cube-organized MAV
• Vollumfänglicher multidimensional Server mit vielen
vorgerechneten Kennzahlen
• Integriert in die Oracle DB und Datendateien
• Sicher
• Skalierbar
• Administrierbar
30
31
DIE POWER VON OLAP ENTDECKEN
32
Oracle Databank Metadaten
Tabellen und Relationen
REGION
CUST
SALES FACT
INVENTORY
ITEM
TIME
Spalten Beziehungen
stehen nicht in den Tabellen Metadaten
REGION
ITEM
CUST
SALES FACT
INVENTORY
ITEM
TIME
item_key type_key
cat_key
Der Wert von Dimensionalen Metadaten
Dimensionen formalisieren die Beziehungen von Daten
Geography
REGION
CUST
SALES FACT
Product
INVENTORY
ITEM
Time
TIME
Der Wert von Dimensionalen Metadaten
Dimensionen formalisieren die Beziehungen von Daten
Geography
Product
REGION
Level-based Hierarchy
Category
CUST
SALES FACT
Type
Product
Item
INVENTORY
ITEM
Time
TIME
Definiert die
Geschäftslogik
Der Wert von Dimensionalen Metadaten
Dimensionen formalisieren die Beziehungen von Daten
Geography
Product
REGION
Complex Hierarchy
CUST
SALES FACT
INVENTORY
ITEM
Time
TIME
Auch komplexe
hierarchische Strukturen
Der Wert von Dimensionalen Metadaten
Dimensionen formalisieren die Beziehungen von Daten
Geography
Product
REGION
Hierarchical Relationships
CUST
Parent
SALES FACT
Descendants
Children
INVENTORY
ITEM
Time
TIME
Familien-Beziehungen für
Berechnungen
Der Wert von Dimensionalen Metadaten
Der Zeitbezug und ein Kalenderverständnis
Geography
REGION
CUST
SALES FACT
Product
INVENTORY
ITEM
Time
TIME
M
A
R
C
H
Sun
Mon
Tue
Wed
Thu
Fri
Sat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Fiscal Year End
Die Zeitdimension
bestimmt die
Zeitreihenanalyse
Der Wert von Dimensionalen Metadaten
Dimensionen werden über mehrere Würfel geteilt
REGION
Sales Cube
CUST
Product
Geography
SALES FACT
Time
INVENTORY
Inventory Cube
ITEM
Product
TIME
Time
Der Wert von Dimensionalen Metadaten
Aggregations - Regeln
REGION
Sales Cube
∑ Aggregation Rules
Product: Sum
Geography: Sum
Time: Sum
CUST
Product
Geography
SALES FACT
Time
INVENTORY
Inventory Cube
ITEM
∑ Aggregation Rules
Product: Sum
Time: Hierarchical Last
Product
TIME
Time
Der Wert von Dimensionalen Metadaten
Berechnungen die die dimensionalität verstehen
REGION
Sales Cube
CUST
Product
Geography
SALES FACT
Time
Calculations
• Sales
• YTD
• YTD Growth
• Market Share
• Product Alert
• Moving Average
• …
INVENTORY
Inventory Cube
ITEM
Product
TIME
Time
Calculations
• Balance
• Inventory Cost
• Stock Out
• …
Schnelle Antworten auf anspruchsvolle Fragen
Einfaches SQL für komplexe Abfragen
Abfrage mit OLAP Würfeln:
Abfrage ohne OLAP Würfel:
SELECT [breakout columns],
sales,
sales_prior_year
sales_ytd,
sales_ytd_prior_year
FROM sales_cube_view
WHERE [star join]
WITH sales_dense AS
(SELECT [breakout columns]
sales,
SUM(sales) over(PARTITION BY [breakout columns]
ORDER BY [time column] ASC range BETWEEN unbounded
preceding AND CURRENT ROW) AS sales_ytd
FROM
(SELECT [breakout columns]
a.sales
FROM
(SELECT [breakout columns]
SUM(f.sales) sales
FROM [table list]
WHERE [star join and other filters]
GROUP BY [breakout columns)
a PARTITION BY(breakout columns)
RIGHT OUTER JOIN
(-- need list of all time periods
SELECT DISTINCT [time columns]
FROM time_dim
b ON([join on relevant time level]))
) …
Continued…
Eigenschaften Analytischer Berechnungen
OLAP Berechnungen umfassen in der Regel:
•
Zeitreihen
– Period Ago, Year Ago, Year-to-Date, Moving Average
•
Vergleiche innerhalb einer Hierarchie, ebenenübergreifend
– Ratios, Share of Parent/Ancestor, Rank within Parent/Level
•
Referenz auf einen Dimensionswert
– Indexes
– Comparison to "top performer"
– Reference member at any aggregation level
•
Forecasts
– Linear, Linear Regressions, Holt-Winters, “Best-fit”, etc.
•
Modelle
– Gleichungssystem zum berechnen von Dimensionswerte (z.B.
Umlagen)
Unterstützung bei der Bildung von
Kennzahlen
• Die Definitionsmöglichkeiten unterstützen alle
gängigen Kennzahlen:
1. Kennzahlen Templates
2. Kennzahlen Gleichungen
3. OLAP DML Programme, Funktionen und Modelle
Kennzahlen Templates
Eine große Auswahl an herkömmlichen Kennzahlen
Kennzahlen Gleichungen
• OLAP Rechen - Syntax:
• Vergleichbar mit den SQL Analytischen Funktionen
• Erweiterung zur Unterstützung der OLAP Spezifika
• Auch unter Verwendung der OLAP DML
OLAP Rechen – Syntax
Ähnlich der Standard SQL Syntax
• Identisch zur SQL syntax bei:
• Zeilenweisen Berechnungen
• In etwa 90 Funktionen
• Beispiele: nvl, nullif, to_date, ||, replace, etc.
• Bedingungen
• Unterstützung aller standard Vergleichs - Operatoren
• Beispiele: case, decode, <, <=, =, >, >=, !=, etc.
Erweiterung zur Unterstützung der
OLAP Spezifika
•
•
•
•
Ebenenübergreifende Berechnungen
Hierarchie Verständnis enthalten
Zeitreihenanalysen ohne Vorberechnungen
Unter Anderem folgende Funktionen:
AVERAGE_RANK
AVG
COUNT
DENSE_RANK
HIER_ANCESTOR
HIER_CHILD_COUNT
HIER_DEPTH
HIER_LEVEL
HIER_PARENT
HIER_TOP
LAG
LAG_VARIANCE
LAG_VARIANCE_PERCENT
LEAD
LEAD_VARIANCE
LEAD_VARIANCE_PERCENT
MAX
MIN
RANK
ROW_NUMBER
SHARE
SUM
Erweiterung zur Unterstützung der
OLAP Spezifika
Am Beispiel der Window function RANK
• Ein OLAP Ausdruck ist vergleichbar mit vielen SQL Formeln
--OLAP
- Rank within parent at any level
RANK() OVER HIERARCHY (PRODUCT.STANDARD ORDER BY SALES_CUBE.QUANTITY DESC NULLS
LAST WITHIN PARENT)
--SQL
--Rank departments
RANK() OVER (PARTITION
--Rank categories
RANK() OVER (PARTITION
--Rank types
RANK() OVER (PARTITION
--Rank sub types
RANK() OVER (PARTITION
-- Rank items
RANK() OVER (PARTITION
BY total_product_id ORDER BY sales DESC NULLS LAST)
BY department_id ORDER BY sales DESC NULLS LAST)
BY category_id ORDER BY sales DESC NULLS LAST)
BY type_id ORDER BY sales DESC NULLS LAST)
BY sub_type_id ORDER BY sales DESC NULLS LAST)
Berechnungen mit OLAP DML
• OLAP DML eine multidimensionale prozedurale
Programmiersprache
• unterstützt looping, conditional logic, multidimensional
selection und mehr
• Beinhaltet hunderte von analytischen Funktionen
• OLAP DML kann eingesetzt werden für
• Kundenspezifischen Kennzahlen
• Um Zahlen zu ermitteln die auch gespeichert werden
können
• Forecasts
• Allokationen
• Gleichungssysteme (Modelle)
• Verteilung von Daten (Top – Down)
Berechnungen mit OLAP DML
Beispiel: Forecast
“Scope the forecast
limit geography to geography_levelrel eq 'REGION'
limit product to product_levelrel eq 'TYPE'
limit channel to 'ALL_CHANNELS'
limit time to time_levelrel eq 'MONTH‘
"Set forecast parameters for 'best fit'
fcset _handle APPROACH 'APPAUTO' periodicity 12 histperiods 36
"Execute the best-fit forecast
FCEXEC _handle time time INTO forecast_best_fit seasonal forecast_seasonal smseasonal forecast_smseasonal backcast sales_cube_sales
Berechnungen mit OLAP DML
Beispiel: Product Alert
“Look at product sales for the children of the current product
“Have they fallen compared to last year?
limit product to children using product_parentrel _product
limit product keep SALES_CUBE_SALES_PY_PCT_CHG lt 0
“If any products meet the criteria – return TRUE (an alert)
if statlen(product) gt 0
then return true
else return false
BNP Paribas
Advanced Time-Series Analyses in Real-Time
• Large European financial
institution
• Used by traders to help decrease
susceptibility to market volatility
• Replacing FAME Time Series
Database
• Forecasting, Analysis and
Modeling Environment
• Three billion stored facts on RAC
• Data updated every 2 seconds –
processing approximately 1m
records daily
• SQL-based custom application
used by 1500 concurrent users
The Gallup Organization
Healthcare Group
• Gallup asks over 1 billion questions annually
• Gallup Healthcare Group
• Conduct surveys measuring quality of care and patient loyalty
• Originally developed a reporting infrastructure that delivered static
reports to hospitals across the US
• Enhanced the interactivity and analytic content of solution
• Support over 1000 concurrent users
• Response time less than 2 seconds per query
• Reduced cost and complexity
• Leveraged Oracle Database investment
• Integrated OLAP into existing infrastructure (security, navigation,
XML/XSL application underpinnings)
• Lowered application development costs
• Reduced complexity for users
56
57