Benchmarking ETL Workflows

Benchmarking ETL Workflows

Alkis Simitsis1, Panos Vassiliadis2, Umeshwar Dayal1, Anastasios Karagiannis2, Vasiliki Tziovara2

1 Intelligent Information Management Lab Hewlett-Packard Labs Palo Alto, CA, USA

2 University of Ioannina Greece

alkis@

Joint work with Qiming Chen, Bin Zhang, Ren Wu

presented by Kevin Wilkinson1

? 2008 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice

? 2008 Hewlett-Packard

ETL workflows

DS.PS_NEW

1

DS.PS_OLD

1

DS.PS_NEW1.PKEY, DS.PS_OLD1.PKEY

DIFF1

DS.PS1

DS.PS_NEW

2

DS.PS_OLD

2

DS.PS_NEW2.PKEY, DS.PS_OLD2.PKEY

DIFF2

DS.PS2

S1_PARTSU PP

FTP1

S2_PARTSU PP

FTP2

SUPPKEY=1

DS.PS1.PKEY, LOOKUP_PS.SKEY,

SUPPKEY

COST

DATE

Add_SPK1

SK1 rejected

Log

SUPPKEY=2

DS.PS2.PKEY, LOOKUP_PS.SKEY,

SUPPKEY

$2 rejected

A2EDate rejected

Log

Log

COST

DATE=SYSDATE

U QTY>0

Add_SPK2

SK2 rejected

NotNULL rejected

AddDate

Log

Log

CheckQTY

DSA

DW.PARTSU PP

PKEY, DAY MIN(COST)

Aggregate1

V1

TIME

DW.PARTSUPP.DATE, PKEY, MONTH

DAY

AVG(COST)

Aggregate2

V2

Sources

DW

2

A.Simitsis, P. Vassiliadis, U. Dayal, A. Karagiannis, V. Tziovara @TPC-TC'09, Lyon, France ? August 24, 2009

ETL Tools

? Commercial

- Ab Initio

- SAP Business Objects

- IBM WebSphere Information Integration

- Informatica PowerCenter

- Microsoft SSIS

- Oracle Warehouse Builder

- Pervasive

- SAS Data Integration Studio

? Open Source

- Clover

- Pentaho Kettle

- Talend

3

A.Simitsis, P. Vassiliadis, U. Dayal, A. Karagiannis, V. Tziovara @TPC-TC'09, Lyon, France ? August 24, 2009

ETL Tools

206 ETL tools, current as of 2003

1. ActaWorks , Acta Technologies

26. Data EXTRactor , DogHouse Enterprises

? Commercial 2. Amadea , ISoft 3. ASG5-X1P.ADTaHta,PArolFlen, ISTyCstoenmssulGtarnocuypGroup BV

27. Data Flow Manager , Peter's Software 7268. .eDInatetagrJautniocntioSnu,itCeo,nTteanvtizExTtercahcntoorlo,gDyata Junction

4. AT S5ig2m. DaaWta-PImroppoargt a, tAordv, aIBnMced Technologies

7279. .EDnvaitraonMmaennat gMear n, aJogeerS,pW anhiciteekLight Technology

- Ab Initio 5. Auto5Im3.poDrat1t,a0W P1ro.hviMitedeeCrrgr,aeOnecredSe,yrsiMtSeomefrtswgeanreceCToemchpnaonlyogies 6. Auto5m4a.tiDcaD1taa0Pt2au.mWIpnfalfuorXerhS,oANuPseetRwB/ou3rilkd, eSTrroaf,tnwGscaiolrbepeeArtAsBsGaobciantes, Inc.

7380. .eD-SaetnaseMGapapth1eer2r6,,.AVMpigpinillieTW decDoharnktosa/lbo4ag0siee0sT,eCchonmoplougteyr Professional Systems 7391. .EDTIaEtaxtMraicgt r,a1Etivo2on7lu.TtMoiooInTlsaSr,y,FMTrieeacdnhmanagonelomg&eienAsts,IsnIonfocci.ramteastion Tools

7. Blue5D5a.taDaM1ta0inS3eta.rgI,ne1Bfo5XluL1Eien.,kDP/ALa4/stca0Loe0nat,diaeBlraS,tcoHoftmawnaloren Consulting

8302. .EDTLatEangMinigera,1tFo2irr8efo.SrpM1rS7ooA9unPta.,rTcPahebo,lepDTleraaStanowsft ,a, tPIcnPhfDoCrImnofraoptriomornaattBiiocusnilders, Inc.

- SAP Business Objects 8. Cata5ly6s.t D, aS1tya0nS4euc.ittIienc1sf,o5SPM2ao.atlhuPntloiiagoignnhetsOrD,uatItna,fomSMySsEatenGmamgsebrHOy

8313. .EDTLatMaaPnroapgae1gr a2, t9iiWo.nM1aS8yoy0zSsa.toerTfmttew,x,aMt TrAeareggeemhnaot u,SsToealusSctoi,oftnIwnsca.re

9. CDB5/7S.upDear1tlao0wa5dh.eI,nr1eCfo5D,R3MBe.fiiSPanooebwfrtw,SeIyarn*srftoeLeoTmarasdneLstrdpS.ourit,teIn,foSHQuLbP,oIwnfeorPGumropu,pCompu8te32r4.A.esDWsaootcarikaWetersaProerh1tao3lu0,s.eeWm1Tp8ooo1rkwl.seTer,erLJx,aetPgvAiaapbcceoIynr,ip,tCioeorrnyatsrttieansl iSco.cfotwmare Australia

10. Ce5re8b.eDlluam1ta0XP6o,.rDItna1aflo5tIarn4mtMe.agPitrgoioarwntaoetrDorDr,isseCcsoeigvrenbreyer lPW lulamatfroSerohmfotwu, saCeryAemrcfhointeyct , Powersof8t 335. .eDXaadtaa3s ,, ICnfroo1rs3ms1AI.ncMf1coe8rRms2Esa.,CtTiSoeooxnrpltuPSotryirooascntte2isomI0Qn0s 0 , LVRA

- IBM WebSphere Information Integration 11. Ch5ec9k.mDaat1tea0X,7PB.rietIbns1sfyo5B,r5imtE.IPanPItQtoieowrnnSeayLrotsMitogeanimsrattsilc,LsItndNf.oertmwaotrikca, D2K 12. Ch6yf0o. ,DIBs1p/i0Are8crc. eSInsy1sfso5t,er6mDm.EasPntoatws,treFuricSsettuarrvgee , Sybase

8346. .eD-zaMtaigBrlaastete,r1e23-z2,d.BauN1tas8a-.Tn3teQe.ctuhTee, rxIyntrca,.cNtoartW, Toerxktsk,eIrnncel 8357. .EDZa-PtiacBkirnix'sD,a1Eta3xc3Me.lSan1yne8satC4tgeo.menTrsivl,eioLratnk,,eTTvhiieliowWnToerckhsntaotilognyGroup, Ltd.

13. CM6S1.TeDxBt1MM0aS9p/.C,Ino1Cfpo5oyS7rcn,.aeCRnrasontponeicnrdee,pDW MtauaatiasnloaS,SgoOoefftptmwCeeaonnrmetU,SnIyinvscete.rmsasl Software

8368. .FDasattCaoCpoynv,eSr1ot3f,t4LMi.neNk1ta8Gd5Sa.-tIaTQraIn,nfNsopremowratetGiroeFnnoPeuarnarttanioiennrs,SDoifgtwitaarleFountain

- Informatica PowerCenter 14. Co6m2p.leDoB1r,i1dS0gyme. It,nr1aSS5xco8rfitb.weRae,rleaCtCiroiotnicnaavlelDrPsaaiottahnBHridogusee, Liant Software Corpora8ti37o9n..FDilea-tAaIDDi/gEgxepr1r,e3sD5so.,nN1Cn8Seo6lXml .SDpTyuarsaWtaenmsaSprsteaogrteITr ,, CNoSmXpSuoteftrwAasrseociates

15. Co6n3te.nDt CE1Ao1Pn1nIe.,cInDt1T,E5oAO9uPc.nheSR/eyP2slaat0egt0imoe0nsa, lBTluoeolIssl,ePSrionfctwetaorne,SIonfcte.ch

8480. .FDileaStapEexecdha, nC1go3em6r .pSOuR1tV8eDr7B,VeFritoaewscwsoeDSrk,ahSatTayerBksctahe,snmienoflToeSgchyhanCrokolongsyulting

- Microsoft SSIS 16. Co6n4ve.rDsieo1cni1ssi2oP.lnuIBsS1aI,E6sDe0,a.,AtRaCrteVaoTiumazdrpG,uCteetor,uArTtsohs?moocinuiayxte&s Associ?s 17. Co6n5ve. rDt /e1cIDi1sMi3o.Sn-JSDo1tBrh6en,a1CHm.oeRn,novCrdeyiorn,tg/A,nVoCcSsmAoegMliSn,oMfFtowirlalerPcertyosLstdC. orporation

8491. .FDoramtawgararetio,nC1,3aP7pat.ilvOa1ad8LySA8noP.efVtDwiagatnraeeMtteigBruastionre,ssLeIngtaecgyrattoioWn SetbudSioolu,tiVoingsnette 9402. .FDOaXtTaRImOpTo,rtE1,n3Sa8pb.aleOl1dS8mion9fngt.,iRSVIneoicpsf.utlwiacal rW toera,reLahokeuvsieew, IBTMechnology

18. Co6p6y.MDaEn1Ca1ISg4IeV.rEK,1MAI6n.df2Sovt.arumRdnotaiaoltlgi-Uo,enpK,nB,IounIwrifloodmnSebAarsrdGi,dicEIgn,ecI.nSco.ftware

9413. .FDusaiotanLeFvTeMr S,1D,3aP9tra.oOg1Lei9pnv0eaet.lrisVRoelnadnetizaV,oVuos l,aOntipaalis

- Oracle Warehouse Builder 19. Co6S7O.RDTe,1pIa1nr5ntmo. vLeai1nvtte6iavT3elr.aSRnSuoisatufegetier2nn,e0tsI0nSI0tnoetlle,ulirtAcinoonanratpilo,ynStiaaclag,leInTnoct .oTlescIhnnco. logy, Inc. 9424..GDaateta/L1oa,dSe, 1lSe4ost0fatw. Oa1r9pe1eTn.evEcThxacnghoalWonggeiebs,,CICSoTorpnnooratatitoenTechnologies 20. Cro6s8s.XpDrEe1TsA1s I6,L.C,LrSO1otr6sAisv4DaA.PcSTLcUeAecSSsh/sn,WCoBlMooargrCpeyohSroaoutfistowenaAredminstrator , SAS Institute 9435. .GD?antiaoM,aHnuamg1me4ri1n,.gJobO1ei9rpdS2epn.CaMWonmiigacmrheaakuton,ircB,aePtairocinsomsnTLItendcf.ohrmation Technology

21. Cu6b9ew. Dairse1tr1Iimb7up.toiMor1tnae6riAn5,gf.rCeaSnumctbhefeeoW Dmr aeMatrarVAeESdnv,gaSinnyecbea,dsFe,laAtipropnligSaotlourti.ocnosm

9446. .GDlaadtasMtoInGe ,C1Do4unl2vce.iarOs1ni9o,pn3eInn.PcWW a. ciakzaraegrhdeoP,ursGoeflea,sdTssaitouonrnuaesl C,SOoofmtpwpeaunrtDeear tSaeSrvyiscteesms

- Pervasive 22. Cy7kl0o.pD,oT1cou1kA8an.baMSl1yaoz6nfet6whr.ea,SimrMceroi,AbbPBeiouIwsnteMergSarhnaiatfetg,emScernibt e Software Corporation 9457. .GDoaHtauMnteinre,r G1, 4Polr3ad.ciaeO1rn9pGD4tirL.aotWuaapdar,eLheovuesreagEexdecSuotilvuetio,nAs,rdInecn.t Software

23. Da7ta1C. DycQl1e1N,9oA.wPME1,a6SDro7kQef.ttwDSNcaroirrviewpetCo, roIi-maImp,poBanucent,nktIesnrcSH.Li.ll

9468. .GDraatpahMiciarrloPre1Cr4foo4nr.mstO1eal9nrlac5rce.lHeSWueWbraiera,esrDhe,oahVuotasaueMnsegPirulBruaosurrid,lCdeSeoNorrplV,uoyOtiroaSrnatyisocstlneemCsorporation

24. Da7ta2D. DraQg1otr2na0n,.sDfMo1urDl6mcF8iAa,.nMS,,EMeInRtaDcd.giFsoAtinl.lceTorem,chSnEoRloSgoileustions

9479. .HDaarvtaeMsteirrro, rO1Tb4raj5en.cstOf1oT9recm6chha.entWsiootrlnaaotrgSeeyeh,roUvTueoKsrrer,eWDntaoStarykMsbteeirmnrocshrInC,coS.rypsotermatfiaobnrik

- SAS Data Integration Studio 25. Da7ta3E. xDcTh/1aS2nt1ug.deiMo,1eX,6rScE9Bam.toSbiragr,ncTiaaSdnIetInr,oteSTringeacnhtiianonontlaolgies

9580. .HDIRaEtaLP,ipSeW,SC1S4ryo6sf.ttawO1la9Surot7ebf.otSwWueanrverdiebc,eAFsiurteosmigantioCnom, wpuetbeMr Ceothmopdas ny

74. DTS12,2M. iMc1reo7tsa0o.fIntSteIPgINraAtioPnROWo, rDksia,gMnoesta Integration Technologie9s9. Hummingbird14E7T.L P,1aH9r8suem.-OWm-iMenbgabtDiicrad,taLPtiKdnint a, cLlOe TSOofNtwteacrhe

75. eC1a2rto3g. rMa1pe7hta1yS.,uSiAtpeMe,eBdMLDoinaaetdarvemarin,SeBorefst,nccaInhrcem. ark Consulting

100. iManageDa1t4a8,.BP1iao9rC9seo.RmWapte,SbGyMsuteyinmSinsogftw, Balroessom Software

? Open Source 124. MetaTrans , Metagenix 172. SRTransport , Schema Research Corp. 125. MinePoint , MinePoint

149. pcMainframe , cfSOFTWARE 200. Web Replicator , Media Consulting

150. PinnPoint Plus , Pinnacle Decision Systems

173. StarQuest Data Replicator , StarQuest Software

201. WebFOCUS ETL Manager , Information Builders, Inc.

174. StarTools , StarQuest

202. WebQL , Caesius Software

- Clover

175. Stat/Transfer , Circle Systems 176. Strategy , SPSS

203. WhizBang! Extraction Library , WhizBang! Labs 204. Wizport , Turning Point

177. Sunopsis , Sunopsis

- Pentaho Kettle 178. SyncSort Unix , Syncsort

205. Xentis , GrayMatter Software Corporation 206. XSB , XSB Inc.

- Talend

4

A.Simitsis, P. Vassiliadis, U. Dayal, A. Karagiannis, V. Tziovara @TPC-TC'09, Lyon, France ? August 24, 2009

Outline

? Motivation ? Goal of the benchmark

- Effectiveness - Efficiency ? Benchmark parameters - Experimental parameters - Measured effects ? ETL flows - Micro-level: activities - Macro-level: workflows ? Specific scenarios ? Open issues

5

A.Simitsis, P. Vassiliadis, U. Dayal, A. Karagiannis, V. Tziovara @TPC-TC'09, Lyon, France ? August 24, 2009

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download