Management-Informationssysteme (MIS) Data Warehouses

Management-Informationssysteme (MIS)

Data Warehouses

© Prof. T. Kudraß, HTWK Leipzig

Überblick

HistorieFunktionenArchitekturData WarehouseOLAPData Mining


Historie Wurzeln

– 60er Jahre: Executive Information Systems (EIS) qualitative Informationsversorgung von Entscheidern kleine, verdichtete Extrakte der operativen Datenbestände Aufbereitung in Form statischer Berichte Mainframe

– 80er Jahre: Management Information Systems (MIS)

meist statische Berichtsgeneratoren Einführung von Hierarchieebenen für Auswertung von

Kennzahlen (Roll-Up, Drill-Down) Client-Server-Architekturen, GUI (Windows, Apple)


Historie (Forts.) 1992: Einführung des Data-Warehouse-Konzeptes durch

W.H. Inmon– redundante Haltung von Daten, losgelöst von

Quellsystemen– Beschränkung der Daten auf Analysezweck

1993: Definition des Begriffs OLAP durch E.F. Codd– Dynamische, multidimensionale Analyse

Weitere Einflussgebiete– Verbreitung geschäftsprozessorientierter

Transaktionssysteme (SAP R/3) Bereitstellung von entscheidungsrelevanten Informationen

– Data Mining– WWW (Web-enabled Data Warehouse etc.)


Funktionen periodische und standardisierte Berichte Verfügbarkeit auf allen Managementebenen verdichtete, zentralisierte Informationen über

alle Geschäftsaktivitäten interaktive Beschaffung von entscheidungs-

relevanten Daten, die den Ist-Zustand des Unternehmens beschreiben

größtmögliche Interaktivität Darstellung von Kennzahlen / Visualisierung /

Erkennen von Trends regelmäßige und ad-hoc Berichte


Funktionen (Forts.) Unterstützung des Managers im Sinne einer

Assistenz Management von Modellen und Methoden Datenbankmanagement konzentriert auf fachliche Teilprobleme eingebettet in komplexe Informationssysteme

(z.B. ERP-Systeme, SAP BW) als Decision Support System

– in den frühen Phasen von Entscheidungsprozessen – strategische Funktionen

Data Warehouse


Data Warehouse Überblick Begriff Anwendungen Definition und Abgrenzung Architekturmodell

– Komponenten

Phasen des Data Warehousing– ETL– Datenkonflikte


Was ist Data Warehousing?

• Data Warehouse: Sammlung von Technologien zur

Unterstützung von Entscheidungsprozessen

• Herausforderung an Datenbanktechnologien

- Datenvolumen (effiziente Speicherung und

Verwaltung, Anfragebearbeitung) - Datenmodellierung (Zeitbezug,

mehrere Dimensionen) - Integration heterogener

Datenbestände


Anwendungen Betriebswirtschaftliche Anwendungen

- Informationsbereitstellung

- Analyse

- Planung

- Kampagnenmanagement Wissenschaftliche Anwendungen

- Statistical und Scientific Databases Technische Anwendungen

- Öffentlicher Bereich: DW mit Umwelt- oder

geographischen Daten (z.B. Wasseranalysen)


Definition Data Warehouse Begriff „A Data Warehouse is a subject-oriented, integrated, non-volatile, and time

variant collection of data in support of managements decisions.” (W.H. Inmon 1996) Charakteristika1. Themenorientierung (subject-oriented): - Zweck des Systems ist nicht Erfüllung einer Aufgabe (z.B. Verwaltung), sondern Modellierung eines spezifischen Anwendungsziels2. Integrierte Datenbasis (integrated): - Verarbeitung von Daten aus mehreren verschiedenen Datenquellen (intern und extern) in einheitlicher konsistenter Sicht3. Nicht-flüchtige Datenbasis (non-volatile): - stabile, persistente Datenbasis - Daten im DW werden nicht mehr entfernt oder geändert (Beständigkeit)4. Historische Daten (time-variant): - Speicherung der Daten zeitraumbezogen - Vergleich der Daten über Zeit möglich (Zeitreihenanalyse)

© Prof. T. Kudraß, HTWK LeipzigTrennung operativer und analytischer Systeme Klassische operative Informationssysteme (OLTP) - Erfassung und Verwaltung von Daten - Verarbeitung unter Verantwortung der jeweiligen Abteilung - Transaktionale Verarbeitung: kurze Lese-/ Schreibzugriffe auf wenige Datensätze Data Warehouse - Analyse im Mittelpunkt - lange Lesetransaktionen auf vielen Datensätzen - Integration, Konsolidierung und Aggregation der Daten Gründe - Antwortzeitverhalten - Verfügbarkeit, Integrationsproblematik - Vereinheitlichung des Datenformats - Gewährleistung der Datenqualität


Beispiel einer Anfrage

„Welche Umsätze sind in den Jahren 1998 und 1999 in

den Abteilungen Kosmetik, Elektro und Haushaltswaren in den

Bundesländern Sachsen-Anhalt und Thüringen angefallen?“


Multidimensionales Datenmodell

• Datenmodell zur Unterstützung der Analyse - Fakten und Dimensionen - Klassifikationsschema - Würfel - Operationen

• Notationen zur konzeptuellen Modellierung• Relationale Umsetzung

- Star-, Snowflake-Schema

• Multidimensionale Speicherung


Fallbeispiel Wal-Mart Marktführer im amerikanischen Einzelhandel Weltgrößtes Data Warehouse mit ca. 0.5 PB (2006): 100 Mio

Kunden, Milliarden Einkäufe pro Woche

Wal-Mart Data Center in MacDonald County


Fallbeispiel Wal-Mart: Orange Juice

How much orange juice did we sell last year, last month, last week in store X?

Comparing sales data of orange juice in various stores? What internal factors (position in store, advertising campaigns...)

influence orange juice sales? What external factors (weather...) influence orange juice sales? Who bought orange juice last year, last month, last week? And most important: How much orange juice are we going to sell

next week, next month, next year?

Other business questions include: What is the suppliers price of orange juice last year, this year, next

year? How can we help suppliers to reduce their cost? What are the shipping/stocking costs of orange juice to/in store X? How can suppliers help us reduce those cost?


Data Warehouse Anforderungen Unabhängigkeit zwischen Datenquellen und Analysesystemen

(bzgl. Verfügbarkeit, Belastung, laufender Änderungen) Dauerhafte Bereitstellung integrierter und abgeleiteter Daten

(Persistenz) Mehrfachverwendbarkeit der bereitgestellten Daten Möglichkeit der Durchführung prinzipiell beliebiger

Auswertungen

Unterstützung individueller Sichten (z.B. bzgl. Zeithorizont, Struktur)

Erweiterbarkeit (z.B. Integration neuer Quellen) Automatisierung der Abläufe Eindeutigkeit über Datenstrukturen, Zugriffsberechtigungen

und Prozesse Ausrichtung am Zweck: Analyse der Daten


Data Warehouse Architekturmodell


Manager & Datenquellen Data-Warehouse-Manager - Zentrale Komponente eines DW-Systems - Initiierung, Steuerung der einzelnen Prozesse (Ablaufsteuerung) - Überwachung + Koordination - Fehlerhandling - Zugriff auf Metadaten aus dem Repository Datenquellen - Gehören nicht zum DWH - Klassifikation nach Herkunft, Zeit, Nutzungsebene - Auswahlkriterien: Zweck, Qualität, Verfügbarkeit, Preis - Qualitätsforderungen: Konsistenz , Korrektheit, Vollständigkeit, Genauigkeit und Granularität, Zuverlässigkeit und Glaub- würdigkeit, Verständlichkeit, Verwendbarkeit und Relevanz


Monitore & Arbeitsbereich Monitore

- Entdeckung von Datenmanipulationen in einer Datenquelle

- Strategien:

Trigger-basiert, replikationsbasiert, Log-basiert,

zeitstempelbasiert, Snapshot-basiert Arbeitsbereich

- Zentrale Datenhaltungskomponente des Daten-

beschaffungsbereichs (staging area)

- Temporärer Zwischenspeicher zur Integration

- Ausführungsort der Transformationen

Keine Beeinflussung der Quellen oder des DW

Keine Übernahme fehlerbehafteter Daten

© Prof. T. Kudraß, HTWK LeipzigExtraktions-, Transformations- und Ladekomponente Extraktionskomponente - Übertragung von Daten aus Quellen in den Arbeitsbereich - abhängig von Monitoring-Strategie - Nutzung von Standardschnittstellen - Ausnahmebehandlung zur Fortsetzung im Fehlerfall Transformationskomponente - Vorbereitung und Anpassung der Daten für das Laden - Überführung aller Daten in ein einheitliches Format - Data Cleaning, Data Scrubbing, Data Auditing Ladekomponente - Übertragung der bereinigten und aufbereiteten (z.B. aggregierten) Daten in das DWH - Nutzung spezieller Ladewerkzeuge (z.B. SQL*Loader von Oracle) - Historisierung: Änderung in Quellen dürfen DWH-Daten nicht überschreiben, stattdessen zusätzliches Abspeichern - Online/Offline Ladevorgang


Data Warehouse & Data Marts

Data Warehouse - Datenbank für Analysezwecke; orientiert sich in Struktur an Analysebedürfnissen - Basis: DBMS - Unterstützung des Ladeprozesses - Unterstützung des Analyseprozesses Data Marts - Bereitstellung einer inhaltlich beschränkten Sicht auf das DW (z.B. für Abteilung) - Gründe: Eigenständigkeit, Datenschutz, Lastverteilung, Datenvolumen, etc. - Abhängige Data Marts / Unabhängige Data Marts


Repository & Metadaten-Manager Repository - Speicherung der Metadaten des DWH-Systems Metadaten - Informationen, die Aufbau, Wartung und Administration des DW-Systemsvereinfachen und Informationsgewinnung ermöglichen - Beispiele: Datenbankschemata, Zugriffsrechte, Prozessinformationen (Verarbeitungsschritte und Parameter), etc. Metadaten-Manager - Steuerung der Metadatenverwaltung - Zugriff, Anfrage, Navigation - Versions- und Konfigurationsverwaltung


Phasen des Data Warehousing Phasen

1. Überwachung der Quellen auf Änderungen durch Monitore

2. Kopieren der relevanten Daten mittels Extraktion in temporären

Arbeitsbereich

3. Transformation der Daten im Arbeitsbereich (Bereinigung,

Integration)

4. Laden der Daten in das Data Warehouse

5. Analyse: Operationen auf Daten des DWH ETL-Prozeß

1. Extraktion: Selektion eines Ausschnitts der Daten aus den

Quellen und Bereitstellung für Transformation

2. Transformation: Anpassung der Daten an vorgegebene Schema- und Qualitätsanforderungen

3. Laden: physisches Einbringen der Daten aus dem Arbeitsbereich

(staging area) in das Data Warehouse


Datenkonflikte Probleme 1. heterogene Bezeichungen, Formate

etc. Beispiel2. inkorrekte Einträge: - Tippfehler bei Eingabe von Werten - falsche Einträge aufgrund von

Programmierfehlern in einzelnen Anwendungsprogrammen i.d.R. nicht

automatisch behebbar !!!3. veraltete Einträge: - durch unterschiedliche

Aktualisierungszeitpunkte - „vergessene“ Aktualisierungen in

einzelnen Quellen

Behebung - explizite Werteabbildung - Einführung von Ähnlichkeitsmaßen - Bevorzugung der Werte aus einer

lokalen Quelle - Verwendung von Hintergrundwissen Einsatz wissensbasierter Verfahren

© Prof. T. Kudraß, HTWK LeipzigData Cleaning, Data Scrubbing, Data Auditing Data Cleaning - Korrektur inkorrekter, inkonsistenter oder unvollständiger Daten - Techniken: - Domänenspezifische Bereinigung - Domänenunabhängige Bereinigung - Regelbasierte Bereinigung - Konvertierungs- und Normalisierungsfunktionen Data Scrubbing - Ausnutzung von domänenspezifischen Wissen (z.B.

Geschäftsregeln) zum Erkennen von Verunreinigungen - Beispiel: Erkennen von Redundanzen Data Auditing - Anwendung von Data-Mining-Verfahren zum Aufdecken von Regeln - Aufspüren von Abweichungen

OLAP


OLAP Überblick Einführendes Beispiel Begriffsdefinition Charakteristika Architektur Funktionalität OLAP & SQL

(insb. ROLLUP & CUBE)


Warum? Daten einer Firma verfügbar machen für

Entscheidungsprozesse

– Umsetzung schwierig

neue Konzepte notwendig zur analytischen Informationsverarbeitung

– OLAP– Data Warehousing– Data Mining


OLAP Einleitung

DSS: Decision Support System


Einführungsbeispiel

Umsatz pro Zeit und Produkt

UmsatzJan Feb Mrz Q1 Apr ... 2000

Haarzeugs 33 55 56 144 18 ... 760Lippenstift 72 136 117 325 74 ... 1338Deo 85 128 99 312 92 ... 1662Kosmetik 190 319 272 781 184 ... 3760DVD 55 69 99 223 84 ... 1051CD 22 17 47 86 39 ... 493Elektro 77 86 146 309 123 ... 1544Alle Produkte 267 405 418 1090 307 ... 5304



Alle RegionenJan Feb Mrz Q1 Apr ... 2000


Umsatz ThüringenJan Feb Mrz Q1 Apr ... 2000


Umsatz pro Zeit, Produkt und Region

Umsatz Sachsen AnhaltJan Feb Mrz Q1 Apr ... 2000


Umsatz, SachsenJan Feb Mrz Q1 Apr ... 2000




Umsatz, Sachsen Anhalt, TelefonJan Feb Mrz Q1 Apr ... 2000

Haar... 11 26 22 59 4 ... 299Lippenstift 16 54 49 119 18 ... 480Deo 29 34 35 98 18 ... 402Kosmetik 56 114 106 276 40 ... 1181DVD 19 18 53 90 27 ... 482CD 6 5 12 23 15 ... 202Elektronik 25 23 65 113 42 ... 684Alle Produkte 81 137 171 389 82 ... 1865

Umsatz, S-A, HomepageJan Feb Mrz Q1 Apr ... 2000


Umsatz,Sachsen Anahlt , FaxJan Feb Mrz Q1 Apr ... 2000


Umsatz,S-A, Alle DistributionskanäleJan Feb Mrz Q1 Apr ... 2000


Umsatz, Sachsen, TelefonJan Feb Mrz Q1 Apr ... 2000


Umsatz, Sachsen, HomepageJan Feb Mrz Q1 Apr ... 2000


Umsatz Sachsen, FAXJan Feb Mrz Q1 Apr ... 2000


Umsatz, Sachsen, Alle DistributionskanäleJan Feb Mrz Q1 Apr ... 2000


Umsatz, Alle Regionen, TelefonJan Feb Mrz Q1 Apr ... 2000


Umsatz, Alle Regionen, Telefon,HomepageJan Feb Mrz Q1 Apr ... 2000


Umsatz, Alle Regionen, FaxJan Feb Mrz Q1 Apr ... 2000


Umsatz, Alle Regionen, Telefon, Alle DistributionskanäleJan Feb Mrz Q1 Apr ... 2000


Umsatz, Thüringen, TelefonJan Feb Mrz Q1 Apr ... 2000


Umsatz, Th, HomepageJan Feb Mrz Q1 Apr ... 2000


Umsatz,Thüringen , FaxJan Feb Mrz Q1 Apr ... 2000


Umsatz, Th, Alle DistributionskanäleJan Feb Mrz Q1 Apr ... 2000



OLAP

OLAP erleichtert die Analyse von Kennzahlen unter verschiedenen Gesichtspunkten (Dimensionen)

– z.B. Produktmanager, Bereichsleiterin – Kennzahlen– graphische Darstellung (Diagramme)

Dynamische, multidimensionale Geschäftsanalyse mit Simulationskomponente


Was ist OLAP?

OLAP ist ...

... ein Überbegriff für Technologien, Methoden und Tools zur Ad-hoc-Analyse multidimensionaler Informationen

... bietet verschiedene Sichtweisen

... eine Komponente der entscheidungsorientierten Informationsverarbeitung


Analyse-Datenmodelle kategorisches (beschreibendes) Modell

– statisches Analysemodell zur Beschreibung des gegenwärtigen Zustands

– Vergleich von historischen mit aktuellen Daten exegetisches (erklärendes) Modell

– zur Erklärung der Ursachen für Zustand durch Nach-vollziehen der Schritte, die ihn hervorgebracht haben(durch einfache Anfragen)

kontemplatives (bedenkendes) Modell – Simulation von „What If“Szenarios für vorgegebene

Werte oder Abweichungen innerhalb einer Dimensionoder über mehrere Dimensionen hinweg

formelbasiertes Modell– gibt Lösungswege vor: ermittelt für vorgegebene

Anfangs- und Endzustände, welche Veränderung fürwelche Kenngröße bzgl. welcher Kenngröße für ange-strebtes Ergebnis notwendig

Kom

plex

ität


OLAP Charakteristika*

Multidimensionale konzeptionelle Sichten funktionale Transparenz unbeschränkter Zugriff auf operative und/oder externe

Datenquellen gleichbleibende Berichtsleistung Client-/Server Architektur gleichgestellte Dimensionen dynamische Behandlung dünn besetzter Datenwürfel mehrere Anwender unbeschränkte, dimensionsübergreifende Operationen intuitive Datenmanipulation flexibles Berichtswesen unbegrenzte Dimensions- und Aggregationsstufen

12 Regeln nach E. F. Codd


OLAP Charakteristika - FASMI

FASMI = Fast Analysis of Shared Multidimensional Information

Fast: 1-2 Sekunden als Antwortzeit bei einfachen Anfragen bis maximal 20 Sekunden für komplexe Datenanalysen

Analysis: Verfahren und Techniken zu einfachen mathematischen Berechnungen und Strukturuntersuchungen

Shared: Schutzmechanismen für den Zugriff im Mehrbenutzerbetrieb

Multidimensional: Multidimensionale konzeptionelle Sicht auf Informationsobjekte, d.h. freier Zugriff auf einen Datenwürfel und multiple Berichtshierarchien über die Dimensionen


OLAP Charakteristika

Daten werden über Dimensionen beschrieben.

Begriffe: Multidimensionalität, Hypercubes, Ausprägungen (Members), Zellen

ZeitReg

ionen

Pro

dukt

e

Ja

n

Fe

b

Mrz

Ap

r

Ma

i

Ju

n

Ju

l

Au

g

Se

p

Ok

t

No

v

De

z

CD

DVD

Fernseher

Video

KühlschrankSachsen-Anhalt

ThüringenSachsen


Dimensionen können Hierarchien haben.

Pro

dukt

e

Zeit

Sachsen-A.Sachsen

Thüringen

Lippenstift

Deo

Haarzeugs

DVD

CD

Ja

n

Fe

b

Mrz

Ap

r

Ma

i

Ju

n

Ju

l

Au

g

Se

p

Ok

t

No

v

De

z

Elektro

Kosmetik

Alle Produkte

Alle Regionen

Q1

Q2

Q3

Q4

20

00 Region

OLAP Charakteristika


Zu Hierarchien

Hierarchie– Hierarchische Aufteilung der Dimension

DVD

KosmetikLipp.

Deo

CD

Elektro

Produkte Haar...DimensionProdukte


OLAP Grobarchitektur


OLAP Architekturkonzepte ROLAP = Relational OLAP

– bei Abbildung in Relationen: möglichst wenig Verlust von Semantik, die im multidimensionalen Modell enthalten

– Effiziente Übersetzung und Abarbeitung von multidimensionalen Anfragen

– Einfache Wartung (z.B. Laden neuer Daten) MOLAP = Multidimensional OLAP

– direkte Speicherung multidimensionaler Daten in multidimensionalen DBMS

HOLAP = Hybrid OLAP– Kombiniert Vorteile von relationaler und

multidimensionaler Realisierung


Architekturkonzept ROLAP SQL zur Datentransformation Multidimensionale Datenmodelle werden in 2-

dimensionalen Tabellen gespeichert Star-, Snowflake, Starflake-Schema

Facts Dim1ID Dim2ID Dim3ID Dim4ID

Dim1

Star-Schema

Verkaufszahl

FilialeTagProduktAnzahl

1

N

N

1

N

1

ProduktProduktMarkeHerstellerProduktgruppe

OrtFilialeStadtRegionLand

Zeit

TagWocheMonatQuartalJahr

Dim2

Dim3

Dim4

Beispiel


ROLAP - Star-Schema erstellen von Fakten- und Dimensionstabellen Faktentabelle mit Schlüsseln für

Dimensionstabellen in Dimensionstabellen stehen relevante Daten Redundanz

– Alternative wäre Snowflake-Schema– Dimensionsdaten relativ stabil


Architekturkonzept MOLAP Speicherung erfolgt in multidimensionalen Speicher-

Arrays Ordnung der Dimensionen zur Adressierung der

Würfelzellen notwendig Klassifikationshierarchien und Aggregation (Echtzeit

oder Vorberechnung?) optional: Attribute Behandlung mehrerer Kenngrößen? Single-Cube-Ansatz (Datenbestand in einem Würfel) vs.

Multicube-Systeme (mehrere kleinere Würfel) Bewertung des Ansatzes:

– Begrenzte Skalierbarkeit bei Dünnbesetztheit– Verbesserung durch Nutzung von Indexierungstechniken


Architekturkonzepte

Query and Calculation

Engine

OLAP-Fronten

d

MOLAP Multidimen-sionales OLAP

Daten werdenmultidimensionalgespeichert

ROLAPRelationales OLAP

Daten werden relational gespeichert

HO

LA

P

Hyb

rid

es O

LA

P

Komplexe Anfragen können hohe Antwortzeiten verursachen

Grosse Datenmengen verarbeitbar

Schnelle Antwortzeiten auch auf komplexe Anfragen

Verarbeitbare Datenmenge beschränkt


Unterschiede OLTP/OLAP

Transaktionsorientierte SystemeOperative Systeme

Auswertungsorientierte Systeme

Weniger häufige, komplexe Anfragen

Grosse Datenmengen je Anfrage

Häufige, einfache Anfragen

Kleine Datenmengen je Anfrage

Schnelle Kalkulation wichtigSchneller Update wichtig

Paralleles Ausführung von OLAP-Anfragen auf operationalen Datenbe-ständen könnte Leistungsfähigkeit der OLTP-Anwendungen beeinträchtigen

Operieren auf aktuellen und historischen Daten

Operieren hauptsächlich auf aktuellen Daten

Datenbanksystem kann nicht gleichzeitig für OLTP- und für OLAP-Anwendungen optimiert werden

OLTP(Online Transaction Processing)

OLAP(Online Analytical Processing)


OLAP Funktionalität Drill Down

– erhöhen des Detaillierungsgrades, d.h. Navigation von den verdichteten Daten zu den detaillierten

Roll Up– invers zu Drill Down– Aggregration entlang des Konsolidierungspfades

Pivotieren / Rotieren– Betrachten aus unterschiedlichen Perspektiven

(vertauschen der Dimensionen um seine Achsen) Slice & Dice

– Einschränken des Analyseblickwinkels (Erzeugung von Scheiben oder Teilwürfeln)


Umsatz, JunCH D A Alle Regionen

Kosmetik 200 100 50 350DVD 100 75 20 180CD 20 35 10 80Elektro 120 110 30 260Alle Produkte 320 210 80 610

OLAP Funktionen

Die multidimensionalen Daten können am Bildschirm flexibel präsentiert werden.

Umsatz, JunSA Th S Alle Regionen

Kosmetik 200 100 50 350Elektro 120 110 30 260Alle Produkte 320 210 80 610

Drill-down

Roll-up


OLAP Funktionalität


SliceKennzahlen

UmsatzGewinn

ProdukteRegionenZeit

KennzahlenUmsatzGewinn

ProdukteRegionenZeit

UmsatzAlle Regionen

Qtr1 900Qtr2 1300Qtr3 1200Qtr4 17002000 5100

UmsatzAlle Regionen

Kosmetik 3200Elektro 1900Alle Produkte 5100

Eine beliebige Kombination von Dimensionen und Aus-prägungen kann angezeigt werden.


OLAP Funktionalität


UmsatzSA Th S Alle R.

KosmetikQtr1 200 100 50 350Elektro 120 110 30 260Alle P. 320 210 80 610KosmetikQtr2 180 90 50 320

... ... ... ... ...Alle P. 270 130 80 480Kos... 2000 910 390 180 1480E... 370 310 190 870Alle P. 1280 700 370 2350

Die Achsen können beliebig ausgetauscht werden.

UmsatzKos... Elektro Alle P.

Qtr1 SA 200 120 320Th 100 110 210SA 50 30 80Alle R. 350 260 610

Qtr2 SA 180 100 280Th 90 50 140

... ... ... ...2000 SA 910 370 1280

Th 390 310 700SA 180 190 370Alle R. 1480 870 2350

Pivot


SQL & OLAP Materialized Views

Merge von Tabellen

SQL für Drill down und Roll up (ROLAP)

CUBE-Operator


Tabellen für Beispiel (Star Schema)OrtFiliale Stadt Region LandHamburg Hamburg Nord DLeipzig Leipzig Ost DStuttgart Stuttgart Süd DBremen-Nord Bremen Nord DBremen-Süd Bremen Nord DMünchen München Süd D

ZeitTag Woche Monat Quartal Jahr05.01.2006 2006-1 2006-1 2006-Q1 200612.01.2006 2006-2 2006-1 2006-Q1 200613.02.2006 2006-7 2006-2 2006-Q1 200623.02.2006 2006-8 2006-2 2006-Q1 200604.03.2006 2006-9 2006-3 2006-Q1 200607.04.2006 2006-14 2006-4 2006-Q2 200625.04.2006 2006-17 2006-4 2006-Q2 2006

ProduktProdukt Marke Hersteller ProduktgruppePizza Funghi Gourmet-Pizza Frost GmbH TiefkühlkostPizza Hawaii Gourmet-Pizza Frost GmbH TiefkühlkostPizza Napoli Pizza TK-Pizza AG TiefkühlkostPizza Vegetale Good&Cheap Frost GmbH TiefkühlkostPizza Calzione Pizza TK-Pizza AG Tiefkühlkost

VerkaufszahlFiliale Produkt Tag AnzHamburg Pizza Funghi 05.01.2006 78Hamburg Pizza Funghi 12.01.2006 67Leipzig Pizza Hawaii 12.01.2006 42München Pizza Calzione13.02.2006 53Stuttgart Pizza Napoli 23.02.2006 23Bremen-Nord Pizza Funghi 04.03.2006 69Bremen-Süd Pizza Vegetale07.04.2006 45Stuttgart Pizza Hawaii 25.04.2006 92


OLAP Anfragebeispiel

CREATE MATERIALIZED VIEW Region_Marke_Quartal ASSELECT O.Region, P.Marke, Z.Quartal, SUM(V.Anz) AS AnzahlFROM (((Verkaufszahl V JOIN Ort O ON (V.Filiale=O.Filiale)) JOIN Zeit Z ON (V.Tag=Z.Tag)) JOIN Produkt P ON (V.Produkt=P.Produkt))GROUP BY O.Region, P.Marke, Z.Quartal;

Erzeugen einer materialisierten Sicht: CREATE MATERIALIZE VIEWVerdichtung mittels GROUP BY

Marialized View: Region_Marke_QuartalRegion Marke Quartal AnzahlNord Gourmet-Pizza 2006-Q1 214Nord Good&Cheap 2006-Q2 45Ost Gourmet-Pizza 2006-Q1 42Süd Pizza 2006-Q1 76Süd Gourmet-Pizza 2006-Q1 92

Beispiel: „Ermittle die quartalsweises Verkaufszahlen pro Pizza-Marke und Region“

Materialisierte Sichten vielbenutzte Aggregate (Analysen)

materialisieren schnellerer Zugriff auf Daten Ablegen der Daten in eigene

Relationen


OLAP-Anfragebeispiel

SELECT O.Region, SUM(V.Anz) AS AnzahlFROM (((Verkaufszahl V JOIN Ort O ON (V.Filiale=O.Filiale)) JOIN Zeit Z ON (V.Tag=Z.Tag)) JOIN Produkt P ON (V.Produkt=P.Produkt))GROUP BY O.Region

Verdichtung erhöhen durch Entfernung von Attributen ausder GROUP BY-Klausel.

Region AnzahlNord 249Ost 42Süd 168

Beispiel: „Ermittle die Gesamt-Verkaufszahlen für alle Regionen“

•weitere Verdichtungen möglich

weitere Verdichtungen möglich automatisches Umschreiben der Anfrage

durch Datenbank-Optimierer (query rewrite): Verwenden der materialisierten Sicht zur weiteren Verdichtung


SQL-Erweiterungen zum Einfügen Kombination von Aktualisieren und Einfügen Beispiel:

Liste neuer Produkte mit bestehender Tabelle Produkt mischen

ProduktID Name4711 Pizza Funghi4712 Pizza Quattro Stagione4713 Pizza Vegetale

Produkt_NeuID Name4711 Pilz-Pizza4712 Pizza Quattro Stagione4713 Pizza Vegetale4714 Pizza Hawaii

MERGE INTO Produkt P1USING (SELECT ID, Name FROM Produkt_Neu) P2ON (P1.ID = P2.ID)WHEN MATCHED THEN UPDATE SET P1.Name = P2.NameWHEN NOT MATCHED THEN INSERT (P1.ID, P1.Name) VALUES (P2.ID, P2.Name) GROUP BY O.Region

ProduktID Name4711 Pilz-Pizza4712 Pizza Quattro Stagione4713 Pizza Vegetale4714 Pizza Hawaii


Komplexes Gruppieren

Beispieldaten für Gruppierungsanfragen

Ort_Produkt_Monat_VerkaufOrt Produkt Monat AnzStuttgart Pizza Funghi 2006-1 155Stuttgart Pizza Vegatale 2006-1 133Stuttgart Pizza Hawaii 2006-1 89Stuttgart Pizza Funghi 2006-2 141Stuttgart Pizza Vegetale 2006-2 112Stuttgart Pizza Hawaii 2006-2 95Frankfurt Pizza Funghi 2006-1 77Frankfurt Pizza Vegatale 2006-1 93Frankfurt Pizza Hawaii 2006-1 102Frankfurt Pizza Funghi 2006-2 144Frankfurt Pizza Vegetale 2006-2 178Frankfurt Pizza Hawaii 2006-2 177

Monat Produkt Anz2006-1 Pizza Funghi 2322006-1 Pizza Vegatale 2262006-1 Pizza Hawaii 1912006-2 Pizza Funghi 2852006-2 Pizza Vegetale 2902006-2 Pizza Hawaii 272

SELECT Monat, Produkt,SUM(Anz) AS Anz

FROM Ort_Produkt_Monat_VerkaufGROUP BY Monat, Produkt;


Komplexes Gruppieren

Monat Produkt Anz2006-1 Pizza Funghi 2322006-1 Pizza Vegatale 2262006-1 Pizza Hawaii 1912006-1 Alle Produkte 6492006-2 Pizza Funghi 2852006-2 Pizza Vegetale 2902006-2 Pizza Hawaii 2722006-2 Alle Produkte 847Alle Monate Alle Produkte 1496

SELECT DECODE (GROUPING (Monat),1,‘Alle Monate‘, Monat) AS Monat, DECODE (GROUPING(Produkt),1,‘Alle Produkte‘, Produkt) AS Produkt, SUM(Anz) AS AnzFROM Ort_Produkt_Monat_VerkaufGROUP BY ROLLUP (Monat, Produkt);

Monat Produkt Anz2006-1 Pizza Funghi 2322006-1 Pizza Vegatale 2262006-1 Pizza Hawaii 1912006-1 Alle Produkte 6492006-2 Pizza Funghi 2852006-2 Pizza Vegetale 2902006-2 Pizza Hawaii 2722006-2 Alle Produkte 847Alle Monate Pizza Funghi 517Alle Monate Pizza Vegetale 516Alle Monate Pizza Hawaii 463Alle Monate Alle Produkte 1496

SELECT DECODE (GROUPING (Monat),1,‘Alle Monate‘, Monat) AS Monat, DECODE (GROUPING(Produkt),1,‘Alle Produkte‘, Produkt) AS Produkt, SUM(Anz) AS AnzFROM Ort_Produkt_Monat_VerkaufGROUP BY CUBE (Monat, Produkt);


Hinweise zum Beispiel ROLLUP

– berücksichtigt auch Zwischen- und Endsummen– Funktion GROUPING liefert 1 bei Summe– DECODE kann Standard-Rückgabewert mit Text

füllen DECODE vergleichbar mit bedingter Anweisung DECODE (ausdruck, if1, then1, if2, then2, …, else)

– ohne GROUPING/DECODE erscheinen Nullwerte

CUBE-Operator– GROUP BY CUBE (Spalte1,Spalte2,Spalte3,…)

– k Dimensionen: 2k mögliche GROUP BY-Klauseln– bietet Ansatz zur Optimierung


Alternative MD Anfragesprachen MDX [Microsoft] Multidimensional SQL (Cube- und Rollup-

Erweiterungen) [SQL-Standard 2006] Multidimensional Query Language (MDSQL)

[Platinum Technologies] Red Brick Intelligent SQL (RISQL)

[IBM/Informix], mit Erweiterungen der Aggregatfuntionen (Top-N, lfd. Durchschnitt)

MQL, XML-basierte Sprache


MDX Standard für viele Softwarehersteller für OLAP-

Datenbanken besteht aus DML und DDL (aber nicht DCL) Funktionalität:

– Entwurf von Datenwürfeln– Abfrage von Daten eine MD Datenbank unter

Verwendung von Datenwürfeln – Formatieren der Anfrageergebnisse– Definieren von berechneten Elementen und

benannten Mengen– Nutzung von Key Performance Indicators (KPI)– Ausführen von Verwaltungsaufgaben


MDX Beispiel

SELECT axis_specification ON COLUMNS, axis_specification ON ROWS FROM cube_nameWHERE slicer_specification

SELECT {[Verkaufsregion].[Kontinent].[USA], [Verkaufsregion].[Kontinent].[Kanada]} ON COLUMNS, {[Zeitraum].[Quartal].[Q1], [Zeitraum].[Quartal].[Q2], [Zeitraum].[Quartal].[Q3], [Zeitraum].[Quartal].[Q4]} ON ROWS FROM [VerkaufsCube]WHERE ([Measures].[Umsatz], [Zeitraum].[Jahr].[2007])

„Ermittle den Verkaufsumsatz für die Regionen USA und Kanada für jedes Quartal im Jahre 2007“

Bestandteile einer MDX-Anfrage


Zukünftige Entwicklung Web OLAP mit Front End über Web

Verknüpfung von OLAP mit anderen Werkzeugen (z.B. Reporting)

Konzepte weiterentwickeln

Konvergenz mit OLTP

Anwendungen auf horizontalen oder vertikalen Markt zuschneiden (bestimmte Branche oder bestimmte Unternehmensfunktion)

Data Mining

66


Drastischer Anstieg des Datenvolumens Dauerhafte Speicherung von Daten wird immer günstiger riesige Datenfriedhöfe in Wissenschaft und Wirtschaft

– manuelle Sichtung unmöglich– Unzufriedenheit mit existierenden Analysemethoden

aus Daten Informationen gewinnen (meist Wettbewerbsvorteile) Aussagen über Grundgesamtheit treffen, wenn nur eine zufällige

Stichprobe zur Verfügung steht SQL-,OLAP-Queries nicht ausreichend wegen mangelnder

Datenqualität Aufdeckung latenter Zusammenhänge zwischen Daten steigende Anzahl an Data Warehouses

Analyseziel: „Finde Gold in Deinen Daten!“

Motivation

67


Einzelhandel oft gemeinsam gekaufte Produkte treue Kunden, Premium-Kunden und Schnäppchen- Jäger Spezifische Interessensgruppen Erfolg einer Marketing-Aktion Absatzchancen neuer Produktsegmente Cross-Selling (Partnerschaft mit anderen Anbietern) Bestandsplanung: Wann kaufen Kunden wieviel wovon?

Banken Finden von Kriterien für die Kreditwürdigkeit von Kunden Prognose von Aktienkursen

Wissenschaft Wirksamkeit von Medikamenten Zusammenhang von Umwelteinflüssen und Krankheiten Finden von Genen in DNA-Strängen

Anwendungen für Data Mining


Data Mining Verfahren

ClusteringZusammenfassen ähnlicher Objekte

AssozationsanalyseAuffinden von Regeln (→ Anwendungsbeispiel)

KlassifikationZuordnen von Datenobjekten zu vorgegebenen Klassen

AnomalieentdeckungAuffinden von „Ausreißern“

Documents

Management-Informationssysteme (MIS) Data Warehouses