Albert Heijn Enterprise Data Warehouse Het PALLAS project
AH-datawarehouse project Pallas in 2003 1000 900 800
40.000
Aantal Gebruikers per week Aantal Rapporten per week
35.000 30.000
700 600 500 400
25.000 20.000 15.000
300 10.000 200 100
0
wk 1 wk 3 wk 5 wk 7 wk 9 wk 11 wk 13 wk 15 wk 17 wk 19 wk 21 wk 23 wk 25 wk 27 wk 29 wk 31 wk 33 wk 35 wk 37 wk 39 wk 41 wk 43
0
5.000
2
AH-DWH presentatie OGH
§ § § § § § §
§
de businesscase van 1999 algemene architectuur tooling, hardware, sizing koffie backup & recovery datamodellen. TR, staging, Datamart databaseontwerp. opslag, partitioning, indexing, data proximity, analyze werken met veel data
3
businesscase : outset 1999 / 2000 n
Defensief : - Management info uit diverse bronnen. - Te duur
n
Offensief : - Differentiatiestrategie ondersteunen - POS data en winkelniveau centraal ontsluiten - Herbruikbaar model / techniek
4
Informational versus Operational The Enterprise Data Warehouse Tijd
Klant
Tijd Filiaal
Prod uct
Aantal
Caissière
Klant
Tijd Filiaal
Prod uct
Aantal
Caissière
Klant
Tijd Filiaal
Prod uct
Aantal
Caissière
Filiaal
Prod uct
Aantal
Klant
Verkoopprijs
Verkoopprijs
Verkoopprijs
Verkoopprijs
Inkoopprijs
Inkoopprijs
Inkoopprijs
Inkoopprijs
Promotie
Weer
Promotie
Plaats in filiaal
Weer
Promotie
Plaats in filiaal
Weer Plaats in filiaal
Promotie
Caissière
Weer Plaats in filiaal
Standard tools
Informational environment
Transaction
Single source
Repository
Integrated data Time dimension Specially tuned
REFIL C&P NASA READ Kikvors PeopleSoft Operational System X Vivaldi Omega environment 5 System Y
Pallas Basic Architecture Back office Source Intern/Extern
Data Staging Area
Transaction Repository
Front office Data Marts
End-user
ETL Data Extraction, Transformation & Load (ETL)
ETL ETL
ETL ETL
ODS near real time
ETL
ETL
Flat FlatFile File
6
Pallas Basic Architecture Front office
Back office Source
Transaction Repository
Intern/Extern
Data Staging Area
Data Marts
End-user
ETL Data Extraction, Transformation & Load (ETL)
ETL ETL
ETL
ETL
7
Fasering Pallas project n n n n n n n n n n
2000 2001 2001 2002 2002 2002 2002 2003 2003 2003
EDOG non-tr datamart 150 GB TR opbouw start Gall datamart Sales Day datamart 1e fase Essbase meerdere cubes Bonuskaart datamart Sales Day datamart 2e fase Warehouse Execution datamart Rosie filiaal datamart 1000-plus named users.. 8
Hardware en Tools
n
Hardware IBM S80 AIX, Ess disks
n
RDBMS Oracle 8.1.7 Partitioning
n
ETL-Tool PowerCenter 6.2
n
End-user tool Reporting MicroStrategy 7.2
n
End-user tool Analyse Hyperion Essbase 6.1
9
Functionele as – end-user tools Aantal gebruikers
Templates-push Limited drill-down Exception Reporting
OLAP
Statistical analysis
Data Mining
Analysis
Statistics
Soort gebruik
Analysis
Reporting
80% of
Standard Reports
usag e
staties
Mining
?
dynamies
10
Hardware n
2000
1 IBM S80 : 12 maal 450 Mhz + 16 GB 1 ESS 384 maal 18 GB disks (5 TB) 2 NT servers 400 Mhz 500 Mb
n
2003
1 IBM S80 : 24 maal 450 Mhz + 32 GB 1 IBM S80 : 12 maal 450 Mhz + 16 GB 1 IBM S85 : 8 maal 700 Mhz + 16 GB 1 IBM P670 : 8 maal 850 Mhz + 16 GB 2 EMC met 36 GB disks (12 TB) 8 NT servers 750 Mhz 2Gb backup : 2 IBM 3494 tape robots 2 IBM H80’s TSM servers.
11
Data Warehouse - disk volume 14 TB
Spool,, temp, overhead space Spool
2,3
10,5
Data Mart - Customer Loyalty Replacement MarketExpert MarketExpert;; Basket Analyses, eCommerce Customer Receipt data mart (customer-store-item-time), 6 months history Customer Week data mart (customer-store-miac-week), 9 months history
7,5 TB budget 2001
3,8
Data Mart - Essbase Solution for SAS, Delta AA
0,4
Spool,, temp, overhead space Spool
1,5
7
Data Mart - SalesDay Powerplay, SAS Replacement POS+dimensions POS+ dimensions,, item-store- day day-in -in promo y/n history:: two year+ history year+current year
2,5
OTA, Backup issues
Transaction Repository POS
0,8 0,8
POS+dimensions POS+ dimensions,, 6 months history, history, archiving 5 years receipt line level, level, 64 million lines per week
1,3
Spool,, temp, overhead space Spool
3,5 5 TB in place
12
13
Pallas databases per mid 2003 Essbase 0,5 TB Sales Day 2,1 TB TR 2,3 TB GALL 30 GB STATS 70 GB REPOS 60 GB
Bonuskaart 1,9 TB
Rosie 200 GB WAEX 100 GB ( Ontwikkel / Acceptatie : 1,9 TB )
14
Koffie ?!
15
Backup and Recovery n n n n n
n
n
n
Draaien in archivelog ! Ad Hoc Maintenance operaties draaien vaak nologging Oracle Rman 80 tot 85% van het datavolume is Read Only RW : 5 maal per week INCR1/2 + 1 maal per week INCR0 (alles dus skip RO) RO : todo list bepalen middels handgeschreven sync van de controlfile met de rman catalog. 4 maal p wk INCR0. Separaat Gbit-eth theoretisch max is 450 GB / uur maar nu ligt dat op 85 GB /uur Recovery : werkt. Partiele restores.. 16
Datamodellen : TR 1 n n n n n n n
corporate datamodel maakt direct aansluiten mogelijk dreiging UDM 3 NF time variant vrijwel geen indexes vrijwel geen PK’s bewaar dimension history. uitgebreide DSA, maar aan de “achterkant” !
17
Datamodellen : TR 2 Kassabon header
promotion
kassabonregel
betalingswijze
18
Datamodellen : Data Staging “prejoined” partitioned hulptabellen op partition key mod(day,28) plus 7 hulptabellen
19
Datamodellen : Data Staging “prejoined” partitioned hulptabellen op partition key mod(day,20) plus 7 hulptabellen Toevoegen “delta” Plus 43 hulptabellen.....
20
Datamodellen : Datamarts 1
n
Fact-tables n n n
n
vrij veel aggregaten onze aggregaten zijn niet altijd technisch de moeder van alle fact-tables is opgestaan
conformed dimensions n n
straightforward snowflaked + platgeslagen 21
Datamodellen : Datamarts 2 Sales Day Dag
category Fact Fili-art-dag
miac artikel
Miac week
Filiaal
22
Datamodellen : Datamarts 3 Sales Day
Fili miac week
Fili art week
Fili art dag
Art dag
23
Datamodellen : Datamarts 3 Sales Day
Fili miac week
Select art, omzet from fili art dag, filiaal,artikel where 2 weken, miac = 297 Fili art week en filiaal niet een franchiser (lees 8 GB) Fili art dag
Art dag
24
Datamodellen : Datamarts 3 Sales Day
Franchise indicator in de fact-table Fili miac week
Fili art week
Fili art dag
Frai art dag
Art dag
25
Datamodellen : Datamarts 3 Sales Day Fili miac periode Fili art periode
Frai art periode
periode
Fili miac week
Fili art week
Frai art week
week
Fili miac dag
Fili art dag
Frai art dag
Art dag
dag 26
Datamodellen : Datamarts 4 Bonuskaart Klant 4,4 miljoen gedepersonaliseerd
Kassabonregel
Artikel
5 miljard
90.000
Dag 7300 27
Database ontwerp : opslag n n
n n n n n
Basis : “Stop defragmenting and ..” Autoextendable locally managed uniform size tablespaces. Slechts twee uniform sizes : 128k en 4m. Geen enkel object heeft dus eigen storage clause. Partitioned data in periode (28 dagen) tablespaces. maximale datafile size 9 GB (ivm backup-pool) Naamgeving tablespaces : n partitions : TRAS_2003PD10_01 n klein TREP0101 / TREP0102 (resp. tables / indexes) n groot TREP0201 / TREP0202 28
Database ontwerp : partitioning
n n n n n
n n
Partition table alleen als > 5GB (maintenance..) Altijd range op dag of week of periode Sterke focus op partition elimination (between !) Let op de verschillen tussen plan en uitvoering. Nog steeds : Eens per jaar maak-nieuwe-partitionsfeest !! Doorzetten naar acceptatie via dblink Archiveren : exchange, RO, transport, backup, drop.
29
Database ontwerp : indexing
n
Bij mij doen ze het niet altijd..
n
Lijn lijkt : hoe groter de tabel hoe moeilijker
n
Wij hebben op enkele cruciale plaatsen sorted indexen op fact tables.
n
9i
n
Doe geen updates op data met usable bitmap-indexes 30
Database ontwerp : data proximity n
Pre-sorting kan factoren (4-200) versnelling bereiken als verbeteren van SQL en indexes onmogelijk lijkt.
n
select count(distinct(block)) from (select dbms_rowid.rowid_block_number(ROWID) block from trak_0911_sorted where exists (select 1 from CULO_ARTIKEL a14 where a14.MIAF_ID = 1507 and arti_id = a14.id))
n n n
31
Database ontwerp : analyze
n
Nu : dbms_stats direct na laden, en tevens periodiek
n
op basis gather stale
n
pluggen van hulptabel statistics
n
plannen voor pluggen van alle statistics
n
Scalzo : number of rows = 20.000 voldoet prima. 32
Werken met zeer veel data.
n
Rules of thumb voor werk met heel veel data : n
n n n
n
eerst nederlands, dan sql, dan denken en dan andere sql. maak operatie voorspelbaar. 10 kleine sorts zijn samen sneller dan 1 grote. De sum over (de union over de output van 10 kleine sorts) is sneller dan 1 grote sort. Of niet ? Een 24 CPU machine is single user sneller, dan als JOUW queries elkaar in de weg zitten. Want Oracle heeft maar 1 temp-tablespace. Oh nee, per user. 33
Aanrader :
n
Bert Scalzo : Oracle DBA Guide to data warehousing and star schemas.
34
