Data Warehousing

1. BentMøllerMadsen Data Warehousing Del 2 af 3: Opbygning af et Data Warehouse Aalborg Universitet, d. 1. februar 2007

2. Opsamling, rensning & bearbejdning af data OLTP-systemer Analyse-værktøjer Produktions- system Excel DWH OLAP ERP Rapporter Tekstfiler Data Mining … Andre data-kilder Det store billede

3. Data Warehouse dele • Target / mål • Databasen (tabellerne) der udgør der faktiske data warehouse • Source / kildesystemer • Kildesystemerne der hentes data fra • Extraction, Transformation and Loading (ETL) – Data Staging • Processen hvormed der overføres data fra kilderne til data warehouse target

4. Target / mål • Design af Data Warehouse udfra ens problemstilling. • Valg af relevant databasemodel til design af Data Warehouse

5. Databasemodeller • Normaliseret model • Den traditionelle måde at designe databaser på • Anvendes normalt i de fleste kildesystemer, såsom et ERP-system • Dog skal man ikke regne med, at de alle er ”pænt” designet • Væsentligste formål er at undgå redundant data i databasen • Nemt at arbejde med de enkelte posteringer • Indtaste, opdatere, slette og hente data om enkelte kunder, ordrer, produkter osv. • Kompleks at overskue samlede modeller

6. Databasemodeller • Dimensionaliserede modeller • Forskellige arter af dimensionaliserede modeller • Star schema (stjerneskema) • Snowflake schema • Constellation schema • Datamodellen er relativ nem at overskue • Velegnet som grundlag til analyser og rapporter: • Idet det er nemmere at gennemskue strukturen • Endvidere vil hastigheden på forespørgsler være hurtigere pga. af færre tabeller og dermed færre joins mellem tabeller • Ikke velegnet til OLTP-systemer pga. at der bevidst er redundante data i modellen.

7. Dimensions- tabel A Dimensions- tabel B Fakta-tabel Dimensions- tabel C Dimensions- tabel D Star schema

8. Simpelt eksempel på star schema Kunde Kunde_id (pk) Navn Adresse Land Produkt Produkt_id (pk) Navn Serie Gruppe Salg Kunde_id (pk) Produkt_id (pk) Medarb_id (pk) Tid_id (pk) Maengde Beloeb Medarbejder Medarb_id (pk) Navn Stilling Tid Tid_id (pk) Dato Maaned Kvartal Aar

9. Star schema • Fakta-tabel • Attributterne i en fakta-tabel er typisk numeriske og kan normalt summeres (dog ikke f.eks. %) • Disse attributter er dem, som der foretages analyser på • Har enten en primærnøgle, der dannes af fremmednøglerne fra hver dimension eller en syntetisk (sekvens) dannet primærnøgle • Stor mængde af værdier

10. Star schema • Dimensionstabeller • Dimensionerne indeholder informationen, som beskriver fakta-attributerne • Normalt er dimensionstabellens attributter tekstfelter/beskrivelser • Relativt få værdier i forhold til Fakta-tabel • Der er næsten altid et ”et-til-mange” forhold mellem data i en dimension og data i fakta-tabellen

11. Simpelt eksempel på star schema Kunde Kunde_id (pk) Navn Adresse Land Produkt Produkt_id (pk) Navn Serie Gruppe Salg Kunde_id (pk) Produkt_id (pk) Medarb_id (pk) Tid_id (pk) Maengde Beloeb Medarbejder Medarb_id (pk) Navn Stilling Tid Tid_id (pk) Dato Maaned Kvartal Aar

12. Produkt Produkt_id Navn Serie Gruppe Produkt Produkt_id Navn Salg Produkt_id Maengde Beloeb Salg Produkt_id Maengde Beloeb Produktgruppe Serie Gruppe Snowflake schema • Et snowflake schema er lig star schemaet, på nær at dimensionstabellerne her er (delvis) normaliserede.

13. Dim A Dim E Dim C Fakta I Fakta II Dim D Dim B Constellation schema • To eller flere star schemas (og/eller snowflake schemas) der deler en eller flere dimensioner.

14. Nøgler i dimensionaliserede modeller • Naturlige nøgler • Informationsbærende • Eks: produktkode, cpr-nr • Syntetiske nøgler (surrogate keys) – f.eks. en sekvens, der tæller op for hver enkelt post • Det anbefales at anvende syntetiske nøgler mellem faktatabel og dimensionstabeller for at sikre integriteten i data warehouset. • Uafhængighed af ændringer i kildesystemer • Relevant hvis samme data findes i flere systemer • Nødvendigt hvis historik gemmes i dimensioner

15. Granularitet i target • På hvilket niveau vil man gemme data i sit data warehouse? • Atomare data/detaildata/transaktionsdata • Summerede/aggregerede data • Afhænger af analyse- og rapporteringsbehovet • Stor betydning for hastighed og størrelse • Det første valg der skal foretages i designet af datamodellen, da det bestemmer indhold af dimensioner og fakta

16. verden verdensdel land by dag år måned uge Hierarkier i dimensioner • Hierarkier opbygges i dimensioner for at gøre det nemt at analysere og rapportere data på forskellige niveauer. • Bore op og ned i dimensioner • Flere hierarkier i en dimension • Naturlige og problemafledte hierarkier

17. Alle produkter Stol Kontorstol Køkkenstol Læderstol Bord Køkkenbord Sofabord Hierarki – parent-child

18. Alle produkter Stol Kontorstol Køkkenstol Læderstol Bord Køkkenbord Sofabord Hierarki – niveaubaseret – kun registrering på nederste niveau

19. Alle produkter Stol Kontorstol Køkkenstol Læderstol Bord Køkkenbord Sofabord Hierarki – niveaubaseret - med registrering på overliggende niveauer

20. Opsamling, rensning & bearbejdning af data OLTP-systemer Analyse-værktøjer Produktions- system Excel DWH OLAP ERP Rapporter Tekstfiler Data Mining … Andre data-kilder Det store billede

21. Source / kildesystemer • Identificer alle datakilder, der kan give data til ens data warehouse. • Kortlæg kilders datastrukturer (E/R-diagrammering) • Vurder datakvaliteten i kilderne (Data profiling).

22. OLTP-systemer • Kilden til et Data Warehouse er forskellige OLTP-systemer (Online Transaction Processing systemer) • Eksempler på disse er: • ERP-systemer (Axapta, SAP, m.fl.) • Regneark (Excel) • Databaser • Tekstfiler

23. Dataintegritet/-kvalitet • Dataintegritet/-kvalitet • Komplette • Valide • Konsistente • Rettidig • Præcise • Sikring af datakvalitet vil ofte være en af de største opgaver i et data warehouse projekt fordi datakvaliteten i OLTP-systemer kan være meget svingende!

24. 1002 Bent Møller Madsen 9000 bmm@business.aau.dk 1003 Bent Madsen 90 1144 Bent M. Madsen 9000 Aalborg asdf@asdf.de 1003 Bent Møller Madsen bmm@adm.aau.dk Id Navn Postnr E-mail Dataintegritet/-kvalitet Diverse fejltyper • Manglende integritet • Manglende unik nøgle • Støj • Stavefejl • m.fl.

25. Eksempler fra AAU-data warehouse • Omkring 75 studerende er indtastet 2 gange • Eks. på resultaters bedømmelsesdatoer: • 17/11 2029, 15/8 0200, 20/9 0099 • Omkring 100, der ikke var annulleret • I tabel over studerendes uddannelsesdele gælder det at for over 50.000 rækker (ca. 5%) kommer fra_dato efter til_dato • 2 tilfælde af adgangsgivende eksaminer, hvor karakteren var henholdsvis 70,3 og 91,0 • 4 betalinger fra studerende på Åben Uddannelse, hvor personen ikke kan findes.

26. Dataintegritet/-kvalitet • Håndtering af fejlbehæftede data • Dataene kan smides ud af systemet ved overførslen til Data Warehouset • Fejlene rettes ikke og føres direkte over i Data Warehouset • Fejl identificeres via Data Warehouset og • Rettes efterfølgende i kilderne • Der oprettes logiske regler der automatisk retter fejl ved overførslen til Data Warehouset

27. Opsamling, rensning & bearbejdning af data OLTP-systemer Analyse-værktøjer Produktions- system Excel DWH OLAP ERP Rapporter Tekstfiler Data Mining … Andre data-kilder Det store billede

28. ETL - Data Staging • Hvordan får vi flyttet data fra vores datakilder til data warehouset og herunder • Renset vores data • Beriget data ved f.eks. beregninger ud fra økonomistyringsmodeller. • Transformeret data til en dimensional datamodel • Extraction • Transformation (og cleaning) • Load

29. Extraction • Udtræk af data fra de tidligere identificerede kilder. • Der anvendes forskellige sprog/programmer afhængig af kildens type • SQL • ODBC/JDBC • Fil-loadere til tekst- og excel-filer

30. Transformation • Anvendelse af forskellige operatorer til at transformere data, således at de kan indsættes i data warehouset’s datamodel. • Joins • Key Lookups • Filtre • Sorteringsoperatorer • Set-operator (union, intersection, minus) • Beregninger • Summeringer • Programmering vha. SQL, PL/SQL, Javascript, etc. • M.fl.

31. Load • Indsættelse og opdatering af (de transformerede) data i ens target/data warehouse.

32. ETL – Data staging • 2 typer af dataoverførsler • Den oprindelige oprettelse af data i DW • Alle senere opdateringer af data i DW

33. ETL – Opdatering af data • Tilføjelse af nye data i dimensioner og facts. • Overskrivning af alle data hver gang • Anvendelse af datoer i kilderne • Anvendelse af delta-/revisionsfiler • Sammenligning af tabeldata mellem forrige og nuværende overførsel

34. ETL – Opdatering af data • Rettelse af tidligere overførte data til dimensioner • Slowly changing dimensions • Type 1: Ingen historik • Overskriv den gamle dimensionsværdi • Type 2: Fuld historik • Opret en ny dimensionsrække/-post og behold den gamle (Fra- og til-dato kolonner fortæller, hvornår en række har været gældende) • Type 3: Delvis historik • Flyt den gamle/forrige værdi over i et nyt attribut/ kolonne i den samme dimension, og opret den nye værdi i den oprindelige attribut/kolonne.