Oracle's multidimensionale Lösungen: Oracle OLAP und ... · PDF fileOracle‘s...

Datenbankstammtisch

Oracle's multidimensionale Lösungen: Oracle OLAP und Oracle Essbase

M. Fischer – Dresden, 15.06.2011

Agenda

� Kurzvorstellung Robotron� Oracle‘s multidimensionale Lösungen im Überblick� Oracle OLAP

– Geschichte– Technologie– OLAP Cube Views– Cube-Organized Materialized Views– Praxisbeispiel: Münchner Preisindex

� Oracle Essbase– Geschichte– Technologie– Praxisbeispiel: Informationsdatenbank Kommunale Finanzen

� Produktpositionierung

Lösungs- und Entwicklungs-partnerschaft

Oracle-Partner

ISO 9001 zertifiziert

Robotron - Zahlen und Fakten

Gründungsjahr 1990

Mitarbeiterzahl 260 (Stand 04/2011)

Stammkapital 1,8 Mio. EUR

Umsatz 2010 20,2 Mio. EUR

Geschäftssitz

Stuttgarter Str. 2901189 Dresden

Tschechien

Tochter-unternehmen

Schweiz

Österreich

Russland

Agenda





Oracle‘s multidimensionale Lösungen im Überblick

Oracle Essbase Oracle OLAP

Zweck

Kundenspezifisce analytische und BI-Anwendungen mit einem Fokusauf EPM; gebaut und gewartet von Fachanwendern

Verbessert die Performance und analytischen Fähigkeiten SQL-basierter BI-Anwendungen

Typ. EndanwenderController, Analysten in Fach-abteilung, Fachabteilungsmgmt.

DBA, Fachanwender, Anwender bestehender Berichtswerkzeuge

Käufer Fachabteilung IT

Systembetreiber Fachabteilung (und IT) IT

DatenmanagementSpeichert Daten in einermultidimensionalen Datenbank auf Platte und im Hauptspeicher

Speichert Daten in multidimensioalenObjekten in der Oracle DB

Speicherform Multidimensional Multidimensional

Abfragesprache MDX und XML/A SQL oder Query Rewrite

Metadaten-Management Benutzerverwaltet IT-administriert

Security Essbase und andere (z.B. LDAP) Oracle DB

ArchitekturEigenständiger OLAP Server, Teilder Oracle Fusion Middleware

Integrierte OLAP Engine in der Oracle DB

Agenda





Oracle OLAP – Geschichte

� 1960 - 1985 Mainframe Express– Entwickelt von Management Decision Systems (MDS)

� 1985 Übernahme von MDS durch Information Resources (IRI)� 1985 - 1990 C-basierte Express Engine

– pcExpress (auf PC), Express MDB (auf anderen Plattformen)

� 1990 - 1995 Express bekommt GUIs– Financial Management System, SalesAnalyzer, u.a.

� 1995 Übernahme von IRI durch Oracle– Express Server, Oracle Financial Analyzer, Oracle Sales Analyzer,

Express Web Agent, Express SPL, Entwicklerwerkzeuge

� Ab 1998 Oracle integriert Express Engine direkt in die Datenbank� 2002 Erstes OLAP Release: Oracle9i OLAP

– Analytic Workspace Manager, OLAP DML, OLAP API

Agenda





Oracle OLAP

� Option zur Oracle Database Enterprise Edition, aktuell 11gR2

� Integrierte multidimensionale Engine mit fortgeschrittenen analytischen Funktionen und Aggregationen

– Gewichtete und hierarchische Aggregationsmethoden

� OLAP Catalog (OLAPSYS) erweitert das Data Dictionary, um multidimensionale Modelle zu beschreiben

� OLAP DML und java-basierendes OLAP API

� SQL-Erweiterungen für den Zugriff auf multidimensionale Datentypen (Analytic Workspace): OLAP_TABLE, MODEL

� Administration über Oracle Enterprise Manager und AnalyticWorkspace Manager

Analytic Workspace

� Container multidimensionaler Daten und Objekte (Dimensionen, Variablen, Programme, Formeln)

� Analytic Workspace (AW) wird als BLOB in relationalen Tabellen verwaltet

� Programmiersprache OLAP DML

� AW kann angelegt und verwaltet werden mit:– Analytic Workspace Manager (AWM)

– Oracle Warehouse Builder (OWB)

– AW XML API

– OLAP DML

� AW entspricht im weiteren Sprachgebrauch konzeptionell einem OLAP Cube

Analytic Workspace Manager

Oracle OLAP Frontends

� Oracle BI Enterprise Edition

� Oracle Discoverer Plus OLAP

� Oracle OLAP Excel Add-in (OLAP 10g)

� Oracle BI Beans (OLAP 10g)

� SQL-basierte BI-Werkzeuge und kundenspez. SQL-basierte oder API-basierte Anwendungen

� Drittanbieter: Simba Technologies MDX Provider for Oracle OLAP, Bissantz DeltaMaster, Miner3D

Agenda





OLAP Cubes Views

� SQL Query von OLAP Cubes (Analytic Workspaces)

� OLAP Cube wird als Star-Schema in relationalen Views repräsentiert

– Dimensionen- und Fakten-Views

– Detail- und Summen-Faktenzeilen

– Analytische Faktenspalten fürKennzahlen

� OLAP Cube enthält

– Alle Kombinationen und Summenebenen

– Analytische Berechnungen

Abfrage an ein Star-Schema

Abfrage mit OLAP Cube Views

SELECT [breakout columns],sales,sales_prior_yearsales_ytd,sales_ytd_prior_year

FROM sales_cube_viewWHERE [star join]

� Vereinfachte, performante Abfrage

Abfrage ohne OLAP Cubes

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.salesFROM

(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 periodsSELECT DISTINCT [time columns] FROM time_dim

b ON([join on relevant time level]))) …

Continued…

Analytische Funktionen in OLAP

� Zeitreihenanalye

� Finanz-Modelle

� Prognose

– einfach

– Expertensystem

� Verteilungen

� Regressionen

� Berechnete Elemente

� Eigene Funktionen

� …und vieles mehr

� mittels OLAP Cube Views alle per SQL erreichbar!

Auswahl an Funktionen

Agenda





Materialized Views fürbessere Performance

Sales by Region

Sales by Date

Sales by Product

Sales by Channel

QueryRewrite

Klassische Lösung für die Verwaltungvon Aggregaten

Region Date

Product Channel

BI Tool

RelationalesStar-Schema

(Tabellen)

Sales

SQL

Star Schema

More Time

Less Time

Ad-Hoc Nature of Application and Query Patterns

Less Ad-Hoc

Predictable Queries

Simple Calculations

More Ad-Hoc

Unpredictable Query Patterns

Sophisticated Calculations

Preparation Time

Viele unterschiedliche Abfragen = viele MVs = Umfangreich!

Cube-Organized Materialized Views

�Ein einzelner OLAP Cube liefert das Äquivalent tausender Summen-Kombinationen

�Der Oracle11g SQL Query Optimizer behandelt OLAP Cubes als MV’s und schreibt die Abfragen entsprechend um

�Cube Refresh mit den Standard MV Prozeduren

Innovative, einmalige Funktion in Oracle Datenbank

Region Date

Product Channel

BI Tool

QueryRewrite

AutomaticRefresh

SQL

dbms_mview.refresh('CB$UNIT_CUBE','F')dbms_mview.refresh('CB$UNIT_CUBE','F')

RelationalesStar-Schema

(Tabellen)

Cube-Organized Materialized Views

Multidimensional Data Types

Star Schema

More Time

Less Time

Ad-Hoc Nature of Application and Query Patterns

Less Ad-Hoc

Predictable Queries

Simple Calculations

More Ad-Hoc

Unpredictable Query Patterns

Sophisticated Calculations

Preparation Time

Viele unterschiedliche Abfragen = Ein Cube = Einfach!

Query Rewrite zum OLAP Cube


Explain Plan Before Que ry Time Before


Explain Plan After Query Time After

� Database Query Optimizer schreibt automatisch Abfrage auf OLAP Cube um

Agenda





Praxisbeispiel Oracle OLAP:Münchner Preisindex

� Wunsch des Sozialreferates einen Münchner Preisindex zu berechnen � Aussagen zu Preisentwicklungen, auch im Vergleich zu Land und Bund

� Preisindex wird monatlich ermittelt und basiert auf einem Warenkorb mit 733 gewichteten Produkten

– Produkte werden mittels COICOP (Classification of Individual Consumption by Purpose, UN-Klassifizierung) verschlüsselt

– COICOPs lassen sich nach Verwendungsart über mehrere Ebenen gruppieren

Preisindex-Berechnung

� Aus Einzelpreisen für jedes der 733 Produkte (COICOP) im Warenkorb werden

– über Durchschnittspreise pro Geschäftskategorie

– zunächst Elementarindizes pro Geschäftskategorie

– und daraus Teilindizes

gebildet

� Teilindizes werden mittels Wägungsanteil gewichtet entlang der Ebenen bis hin zum Verbraucherpreisindex aggregiert

usw. � Oracle OLAP

� Ausgangsmaterial: monatliche Einzelpreise, sowie zentral ermittelte Teilindizes (sogenannte Z-Positionen)

Definition des OLAP-Cubes im AWM

Auswertung im Discoverer für OLAP

Warum Oracle OLAP?

� Ausgangssituation: zwei Dimensionen (COICOP, Zeit) mit komplexer Aggregation der Kennzahl (Verbraucherpreisindex)

� Multidimensionale Berechnungsengine in der Datenbank

� hohe Abfrageperformance

� Vielzahl an fortgeschrittenen Aggregationsfunktionen

�gewichtete und hierarchische Operatoren

�Konzentration auf Algorithmus, statt auf Implementierung

� Vorhandene Oracle-Infrastruktur im Projekt

– Datenbank Enterprise Edition

– Discoverer für OLAP

Agenda





Oracle Essbase – Geschichte

� 1992 Essbase wird durch Arbor Software veröffentlicht

– Extended Spread Sheet dataBASE

� 1994 - 1998 APIs und Essbase Web Gateway

� 1998 Fusion von Arbor mit Hyperion Solutions

� 1998 - 2003 neue Hyperion Anwendungen

– Begriff Business Performance Management (BPM) wird geprägt

� 2003 - 2007 neue Speicherformen, Hybrides OLAP

� 2007 Übernahme von Hyperion durch Oracle

– Essbase wird Kern von Oracle‘s Enterprise Performance Management Strategie

Agenda





Oracle Essbase

� Komponenten– Essbase Datenbank (MDBMS)

– Essbase Administration Services

– Essbase Studio

� Features– Großer Funktionsumfang (Zeitreihen, Forecasts, Statistische und

multidimensionale Funktionen)

– Vielzahl von Berechnungsmethoden (Aggregation entlang definierter Hierarchien, gespeicherte und dynamische Berechnung auf Dimensionswerten, Kalkulationsskripte)

– Zurückschreiben in den Cube (z.B. für Plandaten)

– Unterstützung von MDX- und XML/A-Abfragen

– Zahlreiche Schnittstellen (Java-, C-, VB-API, MaxL) und Anbindung von Quellen (RDBMS, Flat Files, Excel, XML, …)

Essbase Administration Services

Oracle Essbase Frontends

� Oracle BI Enterprise Edition

� Oracle Hyperion Smart View for Office

� Oracle Hyperion Anwendungen

– Hyperion Planning, Hyperion Financial Reporting, Hyperion Web Analyzer, Hyperion Interactive Reporting, Hyperion Visual Explorer, Hyperion Smart Space

� MDX/XMLA-basierte BI-Werkzeuge und kundenspez. MDX/XMLA-basierte oder API-basierte Anwendungen

� Drittanbieter: Applix, Arcplan, Cognos, Bissantz, Business Objects …

Agenda





Praxisbeispiel Oracle Essbase:Informationsdatenbank Komm. Finanzen

� Ursprünglich eine OFA-Anwendung des Statistisches Landesamt Sachsen

– Produktiv seit 1998

– Basiert auf Oracle Express

– Oracle Financial Analyzer (OFA) seit 2009 desupported

�� Migrationsprojekt: Ablösung der OFA-Anwendung durch Kombination von Oracle Essbase und Oracle BI Suite

� Über 100 Finanzkennzahlen sächsischer Gemeinden

� Daten liegen seit 1992 sehr detailliert vor

� Wesentliche Auswertedimensionen:

– Parameter, Gemeinden, Zeit

Datenbasis

� Aktuell Verarbeitung von zwei Datensätzen

– Quartalsweise Kassendaten (ca. 50.000 neue DS pro Quartal)

– Jährl. Jahresendrechnungsdaten (ca. 200.000 neue DS pro Jahr)

� Datensätze, sowie sämtliche Informationen zu Dimensionen und Hierarchien werden in Textdateien geliefert

� Aktuelles Datenvolumen: ca. 50 GByte in 32 Datenwürfeln

Wert

Jahre

GliederungParameter

Gebietsstand

(Datum)GemeindenWert

Parameter

Gebietsstand

(Datum)Gemeinden

Kassendaten

Zeit

Jahresendrechnung

Besonderheiten der Auswertedimensionen

� Parameterdimension:

– Umfasst tatsächlich erfasste und berechnete Kennzahlen

– Gruppiert in unbalancierter Hierarchie

� Gemeindedimension:

– Umfasst alle sächsischen Gemeinden und kommunalen Verbände

– Neben Regionalhierarchie, zahlreiche hierarchisch gruppierbare Zuordnungen für Gemeinden

– Zeitveränderliche Gemeindestrukturen und -zuordnungen:

• Neuer Gebietsstand, in der Regel zum 01.01.

• Für Vergleichbarkeit der Finanzdaten findet mit jedem neuen Jahr Umrechnung der vergangenen Jahre auf neuen Gebietsstand statt

� erhöhtes Datenvolumen

Besonderheiten der Aggregation

� Aggregation in der Regel Summen

� Ausnahmen:

– Bestandsgrößen: letzter Wert über Zeit, ansonsten Summe

– Quoten: nicht aggregierbar, Berechnung aus aggregierten Basiswerten

– Besonderheiten bei Einwohnerzahlen

� Für jede Kennzahl wird zusätzlich Kennzahlwert/1000 und Kennzahlwert/Einwohner bestimmt

Aufbau Essbase-Datenbank

Auswertung mittels Oracle BI EE 11g

Agenda





Oracle‘s multidimensionale Lösungen im Überblick

Oracle Essbase Oracle OLAP

Zweck

Kundenspezifisce analytische und BI-Anwendungen mit einem Fokusauf EPM; gebaut und gewartet von Fachanwendern

Verbessert die Performance und analytischen Fähigkeiten SQL-basierter BI-Anwendungen

Typ. EndanwenderController, Analysten in Fach-abteilung, Fachabteilungsmgmt.

DBA, Fachanwender, Anwender bestehender Berichtswerkzeuge

Käufer Fachabteilung IT

Systembetreiber Fachabteilung (und IT) IT

DatenmanagementSpeichert Daten in einermultidimensionalen Datenbank auf Platte und im Hauptspeicher

Speichert Daten in multidimensioalenObjekten in der Oracle DB

Speicherform Multidimensional Multidimensional

Abfragesprache MDX und XML/A SQL oder Query Rewrite

Metadaten-Management Benutzerverwaltet IT-administriert

Security Essbase und andere (z.B. LDAP) Oracle DB

ArchitekturEigenständiger OLAP Server, Teilder Oracle Fusion Middleware

Integrierte OLAP Engine in der Oracle DB

Strategie treibt Produktevolution

� Oracle Essbase und Oracle OLAP haben ein Jahrzehnte altes Erbe und Erfahrung

� Früher waren Oracle Essbase und Oracle OLAP (Express) in der gleichen Position

– Viele gemeinsame/ähnliche Funktionalitäten und Fähigkeiten

� ABER die Oracle und Hyperion Strategien gingen vor Jahren auseinander

� Darum sind die OLAP-Produkte heute sehr unterschiedlich

– Oracle OLAP – Zielmarkt: Data Warehousing, Abfrageperformance, analytischer Inhalt für SQL-basierte BI Tools

– Oracle Essbase – Zielmarkt: Performance Management, Controlling

Produktpositionierung

� Oracle OLAP ist optimal für – Beschleunigung von Abfragegeschwindigkeiten

– analytischen Inhalt für SQL-basierte BI Tools auf Oracle Datenbank

� Oracle Essbase ist ideal für – Performance Management Anwendungen

� Beide Produkte sind strategisch für Oracle

� Beide können Bestandteil einer Kundenlösung sein; Architektur und Mitwirkung der IT bestimmen, was am besten passt

� Produktsynergien und Integrationsplanung haben gerade erst begonnen

Spektrum der OLAP Anforderungen

Enhanced Data Warehousing

Enterprise Performance Management

OracleEssbase

Oracle OLAP

Buchempfehlung

� Oracle Essbase & Oracle OLAP: The Guide to Oracle'sMultidimensional Solution (Osborne Oracle Press Series)

– Mcgraw-Hill Professional (2009)

– ISBN-13: 978-0071621823

Fragen und Antworten

Marco FischerDipl.-WirtschaftsinformatikerLeitender SystemberaterCertified Business Intelligence Professional

Robotron Datenbank-Software GmbHStuttgarter Straße 2901189 Dresden

Telefon: (0351) 258 59 2755Telefax: (0351) 258 59 3699Mobil: (0151) [email protected]

Vielen Dank für Ihre

Aufmerksamkeit!

Oracle's multidimensionale Lösungen: Oracle OLAP und ... · PDF fileOracle‘s...

Documents

Transcript of Oracle's multidimensionale Lösungen: Oracle OLAP und ... · PDF fileOracle‘s...