WIXLIB

Data WarehousingundData MiningSprachen für OLAP OperationenUlf LeserWissensmanagement in derBioinformatik

Übersicht Konzeptionell: Modellierung und Sprachen––Architektur & ProzesseMultidimensionale Modellierung –Extraction, Transformation, Load (ETL) Indexstrukturen für DWH: Bitmap, Join-IndexeMultidimensionale Indexstrukturen: Grid-File, kd-TreeOptimierung: Star-Join, PartitionierungImplementierung von OLAP OperationenColumn Stores, Main Memory, Map-ReduceMaterialisierte Sichten––– Differential SnapshotsTransformations and types of heterogeneityBulk loadingUmsetzung: Logische und physische Ebene––––– MDDM, MERROLAP und MOLAPOLAP Operationen und SprachenAuswahlQuery RewritingAktualisierungData Mining––––DatenaufbereitungClustering: k Means, DBScan, hierarchischKlassifikation: kNN, Naive Bayes, Decision TreesAssoziationsregelnUlf Leser: Data Warehousing und Data Mining2

Inhalt dieser Vorlesung OLAP Operationen MDX: Multidimensional Expressions SQL ErweiterungenUlf Leser: Data Warehousing und Data Mining3

OLAP Operationen OLAP Kern-Operation: Aggregation auf dem Cube– Roll-up, Drill-Down– Hierarchische Roll-Ups mit Aggregation auf allen Ebenen(Summe pro Tag, pro Monat, pro Jahr)– Aggregation über mehrere Dimensionen (Cross-Tabs): Verkäufepro Marke und Jahr und Shop, Summen in allen Kombinationen Weitere OLAP Operationen– Gleitende Durchschnitte, attributlokale Vergleiche, Arbeiten aufZeitreihen – Sequenzbasierte Operationen– Auch: „analytische Anfragen“Ulf Leser: Data Warehousing und Data Mining4

Sprachen für OLAP Operationen Ziel: Ökonomisches, aufgabengerechtes Design– Keine tief geschachtelten SQL Operationen– Verwendung von MDDM Konzepten und Begriffen Hauptsächlich zwei Ansätze– Multidimensional Expressions (MDX) Basiert direkt auf MDDM Elementen: Cube, Dimension, Fakt, .Mapping auf ROLAP und MOLAP möglich (und vorhanden)Von vielen Tools zur Kommunikation mit OLAP-Server benutztUnterstützt z.B. von Microsoft, Microstrategy, Cognos (IBM),BusinessObjects (SAP), Teradata, Pocahontas, Oracle, . Auf dem Vormarsch als de-facto Standard– Erweiterungen von SQL Einführung spezieller Operationen (ROLLUP, CUBE, ) Ausführung auf Star-/ Snowflake-SchemaUlf Leser: Data Warehousing und Data Mining5

MDX MDX: Multidimensional Expressions– Ursprung: Microsoft‘s „OLE DB for OLAP“ Eigene Anfragesprache––––Definition ohne feste Semantik („by example“)MDDM Konzepte als First-Class ElementeSQL-artige SyntaxSehr mächtig und komplex Erzeugung der Objekte (DDL) muss anderweitigerfolgen– Zugriff über MetadatenkatalogeUlf Leser: Data Warehousing und Data Mining6

MDX Elemente Measures Fakten– Modelliert als eigene Dimension mit nur einer Stufe– Name des Measures ist der einzige Klassifikationsknoten Dimensions Dimensionen– Level Klassifikationsstufe– Multiple hierarchies Verschiedene Pfade– Member KlassifikationsknotenUlf Leser: Data Warehousing und Data Mining7

Ausgabe MDX Anfragen erzeugen mehrdimensionale X rkeUlf Leser: Data Warehousing und Data Mining19998

Beispielschemayearmonthdayshop nameamountsalespg nameshopregionpriceproduct nameproductgroupUlf Leser: Data Warehousing und Data Miningproductregion name9

Member: Unique Names oder NavigationBeispielVerschiedene Klassifikationsstufen in einer Dim möglichSELECT {Wein, Bier, Limo, Saft} ON COLUMNS{[2000], [1999], [1998].[1], [1998].[2]} on ROWSFROMSalesWHERE (Measures.Menge, Region.Germany)Auswahl des Fakt „Menge“Beschränkung auf Werte Weinin BRDBierLimoSaft20001999Implizite Summierung1998.11998.2Ulf Leser: Data Warehousing und Data Mining10

Struktur einer MDX QuerySELECT axis-spec1 , axis-spec2 , .FROM cube-spec1 , cube-spec2 , .WHERE slice-specification Dimensions (SELECT)– Angabe der darzustellenden Achsen der Ausgabetabelle(n) ON COLUMNS, ROWS, PAGES, CHAPTER, .– Achsenspezifikation muss eine Menge von Members definieren Achsenbeschriftungen (geben auch Aggregationslevel vor) Explizit als Menge oder implizit durch Berechnungsfunktionen Cube (FROM): Basis-Cube für die Anfrage Slicer (WHERE)– Auswahl des Fakts– Einschränkungen auf Members (Knoten)Ulf Leser: Data Warehousing und Data Mining11

Navigation in den HierarchienSELECT {Prodgroup.MEMBERS, [Becks Bier].PARENT} ON COLUMNS{Year.MEMBERS} on ROWSFROM SalesWHERE (Measures.Menge)Navigationsfunktionen Unterstützung eines navigierenden Zugriffs (GUI) MEMBERS:Knoten einer Klassifikationsstufe CHILDREN: Kinderknoten eines Klassifikationsknoten PARENT:Vaterknoten eines Klassifikationsknoten LASTCHILD, FIRSTCHILD, NEXTMEMBER, LAG, LEAD. DESCENDENTS: Rekursives AbsteigenUlf Leser: Data Warehousing und Data Mining12

CrossjoinSELECT CROSSJOIN( {Germany, France}{Wein, Bier}) ON COLUMNS{Year.MEMBERS} on ROWSFROMSalesWHERE 998. Schachtelung zweier Dimensionen Kartesisches Produkt der Mengen vonKlassifikationsknotenUlf Leser: Data Warehousing und Data Mining13

Weitere Achsenauswahl-Features TOP-N QueriesSELECT {Year.MEMBERS} ON COLUMNS{TOPCOUNT(Country.MEMBERS, 5, Measures.Menge)} ON ROWS Auswahl von Members über BedingungenSELECT FILTER(Germany.CHILDREN,([2002], M.Menge) ([2001], M.Menge)) ON COLUMNSMonth.MEMBER ON ROWS Named Sets und Calculated MembersZeitreihenoperationen, gleitende DurchschnitteAggregationsfunktion AVG, MAX, : „AS“ Klausel.Ulf Leser: Data Warehousing und Data Mining14

Inhalt dieser Vorlesung OLAP Operationen MDX: Multidimensional Expressions SQL Erweiterungen– Rückblick: Gruppierung– OLAP: Hierarchische Aggregation– OLAP: SQL Analytical FunctionsUlf Leser: Data Warehousing und Data Mining15

SQL und OLAP Annahme: Star- oder Snowflake Schema Einfache Operationen– Auswahl (slice, dice): Joins und Selects– Verfeinerung (drill-down): Joins und Selects– Aggregation auf eine feste Granularität: Group-by und Agg-Fkt Schwieriger––––Gleichzeitige hierarchische AggregationenGleichzeitige multidimensionale AggregationenAnalytische Funktionen (gleitende Durchschnitte etc.)Alles auch in SQL-92 möglich, aber nur kompliziertauszudrücken und ineffizient in der BearbeitungUlf Leser: Data Warehousing und Data Mining16

Beispiel Star-Schemaproductproduct idproduct namepg idpg nametimeday iddaymonth idmonthyear idyearsalesproduct idday idshop idamountpricelocalizationshop idshop nameregion idregion nameUlf Leser: Data Warehousing und Data Mining17

Erinnerung: Semantik von GROUP-BYSELECTFROMWHEREGROUP BYHAVINGORDER BYT.day id, sum(amount*price) SUsales Sprice 100T.day idSU 0day id SELECT Klausel darf nur GROUP BY Ausdrücke,Konstante und Aggregatsfunktionen enthalten Semantik– Partitionierung der Ergebnistupel nach GROUP-BY Attribut– Aggregation (der Measures) pro PartitionInnere Query Partitionierung(Tuplestrom) (Group by)AggregationSelektion der Part.(pro Partition) (having)Ulf Leser: Data Warehousing und Data MiningSortierung(der Partitionen)18

BeispielAlle Verkäufe der Produktgruppe „Wein“ nach Monaten(was passiert?)SELECTFROMWHERET.month, sum(amount*price)sales S, product P, time TP.pg name „Wein“ ANDP.product id S.product id ANDT.day id S.day idGROUP BY T.month idScheitert: „. T.month is not a GROUP-BY expression .“ Funktionale Abhängigkeit T.month id- T.Month nicht bekannt (Erinnerung: „ATTRIBUTE . DETERMINES“ in Oracle)Ulf Leser: Data Warehousing und Data Mining19

Hierarchische AggregationAlle Verkäufe der Produktgruppe „Wein“ nach Tagen,Monaten und JahrenSELECTFROMWHERET.year id, T.month id, T.day id, sum(.)sales S, product P, time TP.pg name „Wein“ ANDP.product id S.product id ANDT.day id S.day idGROUP BY T.year id, T.month id, T.day id Summen nur für Tage Keine Summen pro Monat / pro Jahr SQL92: nicht mit einer SFW-Query möglichUlf Leser: Data Warehousing und Data 40.200361834520

Hierarchische Aggregation –2Alle Verkäufe der Produktgruppe „Wein“ nach Tagen,Monaten und JahrenBenötigt UNION und eine Query pro KlassifikationsstufeSELECTFROMWHERET.day id, sum(amount*price)sales S, product PP.pg name „Wein“ ANDP.product id S.product idSELECTT.month id, sum(amount*price)GROUP BY T.day idFROMsales S, product P, time TWHEREP.pg name „Wein“ ANDP.product id S.product id ANDT.day id S.day idSELECTT.year id, sum(amount*price)GROUP BY T.month idFROMsales S, product P, time TWHEREP.pg name „Wein“ ANDP.product id S.product id ANDT.day id S.day idGROUP BY T.year idUlf Leser: Data Warehousing und Data Mining21

ErgebnisTageMonateJahreUlf Leser: Data Warehousing und Data 33.31.12.19983451.1.1999455 1/1997120002/199713000.12/199815600 19971234531998143254.22

OLAP-Operator: ROLLUP Herkömmliches SQL für hierarchische ROLLUP– Dimension mit k Stufen – Union von k Queries– k Scans der Faktentabelle Typischerweise keine Optimierung wg. fehlender Multiple-QueryOptimierung in den meisten RDBMS OLAP/SQL Erweiterung: ROLLUP Operator– Hierarchische Aggregation mit Summen auf allen Stufen– Summen werden durch „ALL“ als Wert repräsentiert In Oracle ist es NULL statt ALL– Identifizierung über GROUPING-FunktionUlf Leser: Data Warehousing und Data Mining23

ROLLUP BeispielSELECTFROMWHEREGROUP BYT.year id, T.month id, T.day id, sum(.)sales S, time TT.day id S.day idROLLUP(T.year id, T.month id, T.day ALLALLUlf Leser: Data Warehousing und Data 4

Bedingtes ROLLUP Man will nicht immer alle Klassifikationsknoten sehen– Z.B.: Hierarchische Aggregation über Shop und Region, aberexplizite Ausweisung nur für die Shops „Wedding“ und „Mitte“;Gesamtsumme pro Region soll erhalten bleiben– Selektion in der WHERE Klausel geht nicht, weil sonst dieGesamtsummen pro Region verfälscht werden Zwei Möglichkeiten– HAVING, um unerwünschte Tupel zu filtern Unterdrückt Tupel in der Ausgabe– Verwendung einer CASE Anweisung im ROLLUP Operator Ermöglicht Zusammenfassen von Gruppen nach komplexenBedingungen Erzeugt neue Tupel, die andere zusammenfassen könnenUlf Leser: Data Warehousing und Data Mining25

Bedingtes ROLLUPHierarchische Aggregation über Shop und Region, aberWerte nur für die Shops „Wedding“ und „Mitte“SELECTFROMWHEREGROUP BYL.shop id, L.region id, sum(amount)sales S, localization LS.shop id L.shop idROLLUP(L.region id,CASE WHEN L.shop id IN (‚Wedding‘, ‚Mitte‘)THEN L.shop IDRegion idShop idELSE ‚Others‘END))BayernOthersKönnte man noch mitHAVING unterdrückenUlf Leser: Data Warehousing und Data Miningsum BayernAll BerlinWedding BerlinMitte BerlinOthers BerlinALL .26

Multidimensionale AggregationVerkäufe nach Produktgruppen und sser10151136Gesamt25322481sum() . GROUP BY pg id, year idUNIONsum() . GROUP BY pg idWie viele SFW-Queries sindUNIONsum() . GROUP BY year idUNIONsum()Ulf Leser: Data Warehousing und Data Miningnotwendig ?27

OLAP-Operator: Cube d Dimensionen, jeweils eine Klassifikationsstufe– Jede Dimension kann in Gruppierung enthalten sein oder nicht– 2d Gruppierungsmöglichkeiten Herkömmliches SQL– Viel Schreibarbeit– Wahrscheinlich 2d Scans der Faktentabelle OLAP/SQL Erweiterung: CUBE Operator– Berechnung der Summen aller Kombinationen derGruppierungsattribute (Klassifikationsstufen)Ulf Leser: Data Warehousing und Data Mining28

CUBE – BeispielSELECTpg id, shop id, year id, sum(amount*price)FROMsales S .GROUP BY CUBE (S.pg id, S.shop id, T.year id)BierKreuzberg1997 .WeinALL1998.ALL . arlottenburgALL.ALLALLALL.Ulf Leser: Data Warehousing und Data Mining29

Cube-Operator: VeranschaulichungAggregateGroup By(with total)SumBy ColorREDWHITEBLUECross TabChevy Ford By ColorSumREDWHITEBLUEThe Data Cube andThe Sub-Space AggregatesCH FORDEVYBy M akeSumBy Year01 99 99 11 99 21 99 31By M akeBy M ake & YearREDWHITEBLUEBy Color & YearSumBy M ake & ColorBy ColorSource: Gray et al., „Datacube“, Microsoft & IBMUlf Leser: Data Warehousing und Data Mining30