The Data WarehouseETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data

526 Pages·2004·5.314 MB·English

by  Ralph Kimball, Joe Caserta

P1:FCH/SPH P2:FCH/SPH QC:FCH/SPH T1:FCH
WY046-FM WY046-Kimball-v4.cls August18,2004 13:42
The Data Warehouse
ETL Toolkit
i
P1:FCH/SPH P2:FCH/SPH QC:FCH/SPH T1:FCH
WY046-FM WY046-Kimball-v4.cls August18,2004 13:42
ii
P1:FCH/SPH P2:FCH/SPH QC:FCH/SPH T1:FCH
WY046-FM WY046-Kimball-v4.cls August18,2004 13:42
The Data Warehouse
ETL Toolkit
Practical Techniques for
Extracting, Cleaning,
Conforming, and
Delivering Data
Ralph Kimball
Joe Caserta
WileyPublishing,Inc.
iii
P1:FCH/SPH P2:FCH/SPH QC:FCH/SPH T1:FCH
WY046-FM WY046-Kimball-v4.cls August18,2004 13:42
Publishedby
WileyPublishing,Inc.
10475CrosspointBoulevard
Indianapolis,IN46256
www.wiley.com
Copyright(cid:2)C 2004byWileyPublishing,Inc.Allrightsreserved.
PublishedsimultaneouslyinCanada
eISBN:0-764-57923-1
PrintedintheUnitedStatesofAmerica
10 9 8 7 6 5 4 3 2 1
Nopartofthispublicationmaybereproduced,storedinaretrievalsystem,ortransmittedinany
formorbyanymeans,electronic,mechanical,photocopying,recording,scanningorotherwise,ex-
ceptaspermittedunderSections107or108ofthe1976UnitedStatesCopyrightAct,withouteither
thepriorwrittenpermissionofthePublisher,orauthorizationthroughpaymentoftheappropriate
per-copyfeetotheCopyrightClearanceCenter,222RosewoodDrive,Danvers,MA01923,(978)
750-8400,fax[phone].RequeststothePublisherforpermissionshouldbeaddressedtothe
LegalDepartment,WileyPublishing,Inc.,10475CrosspointBlvd.,Indianapolis,IN46256,(317)
572-3447,fax[phone],e-mail:[email protected].
LimitofLiability/DisclaimerofWarranty:Thepublisherandtheauthormakenorepresentations
orwarrantieswithrespecttotheaccuracyorcompletenessofthecontentsofthisworkandspecif-
icallydisclaimallwarranties,includingwithoutlimitationwarrantiesoffitnessforaparticular
purpose.Nowarrantymaybecreatedorextendedbysalesorpromotionalmaterials.Theadvice
andstrategiescontainedhereinmaynotbesuitableforeverysituation.Thisworkissoldwith
theunderstandingthatthepublisherisnotengagedinrenderinglegal,accounting,orotherpro-
fessionalservices.Ifprofessionalassistanceisrequired,theservicesofacompetentprofessional
personshouldbesought.Neitherthepublishernottheauthorshallbeliablefordamagesarising
herefrom.ThefactthatanorganizationorWebsiteisreferredtointhisworkasacitationand/or
apotentialsourceoffurtherinformationdoesnotmeanthattheauthororthepublisherendorses
theinformationtheorganizationorWebsitemayprovideorrecommendationsitmaymake.Fur-
ther, readers should be aware that Internet Websites listed in this work may have changed or
disappearedbetweenwhenthisworkwaswrittenandwhenitisread.
For general information on our other products and services please contact our Customer Care
DepartmentwithintheUnitedStatesat(800)762-2974,outsidetheUnitedStatesat(317)572-3993
orfax(317)572-4002.
Wileyalsopublishesitsbooksinavarietyofelectronicformats.Somecontentthatappearsinprint
maynotbeavailableinelectronicbooks.
LibraryofCongressCataloging-in-PublicationData
Kimball,Ralph.
ThedatawarehouseETLtoolkit:practicaltechniquesforextracting,cleaning,conforming,and
deliveringdata/RalphKimball,JoeCaserta.
p. cm.
Includesindex.
eISBN0-7645-7923-1
1.Datawarehousing. 2.Databasedesign. I.Caserta,Joe,1965- II.Title.
QA76.9.D37K53 2004
005.74—dc22 2004016909
Trademarks:Wiley,theWileyPublishinglogo,andrelatedtradedressaretrademarksorregistered
trademarksofJohnWiley&Sons,Inc.and/oritsaffiliates.Allothertrademarksaretheproperty
oftheirrespectiveowners.WileyPublishing,Inc.,isnotassociatedwithanyproductorvendor
mentionedinthisbook.
iv
P1:FCH/SPH P2:FCH/SPH QC:FCH/SPH T1:FCH
WY046-FM WY046-Kimball-v4.cls August18,2004 13:42
Credits
VicePresidentandExecutive DevelopmentEditor:
GroupPublisher: AdaobiObiTulton
RichardSwadley
ProductionEditor:
VicePresidentandPublisher: PamelaHanley
JosephB.Wikert
MediaDevelopmentSpecialist:
ExecutiveEditorialDirector: TravisSilvers
MaryBednarek
TextDesign&Composition:
ExecutiveEditor: TechBooksCompositionServices
RobertElliot
EditorialManager:
KathrynA.Malm
v
P1:FCH/SPH P2:FCH/SPH QC:FCH/SPH T1:FCH
WY046-FM WY046-Kimball-v4.cls August18,2004 13:42
vi
P1:FCH/SPH P2:FCH/SPH QC:FCH/SPH T1:FCH
WY046-FM WY046-Kimball-v4.cls August18,2004 13:42
Contents
Acknowledgments xvii
AbouttheAuthors xix
Introduction xxi
Part I Requirements, Realities, and Architecture 1
Chapter1 SurroundingtheRequirements 3
Requirements 4
BusinessNeeds 4
ComplianceRequirements 4
DataProfiling 5
SecurityRequirements 6
DataIntegration 7
DataLatency 7
ArchivingandLineage 8
EndUserDeliveryInterfaces 8
AvailableSkills 9
LegacyLicenses 9
Architecture 9
ETLToolversusHandCoding(BuyaToolSuiteorRoll
YourOwn?) 10
TheBackRoom–PreparingtheData 16
TheFrontRoom–DataAccess 20
TheMissionoftheDataWarehouse 22
WhattheDataWarehouseIs 22
WhattheDataWarehouseIsNot 23
IndustryTermsNotUsedConsistently 25
vii
P1:FCH/SPH P2:FCH/SPH QC:FCH/SPH T1:FCH
WY046-FM WY046-Kimball-v4.cls August18,2004 13:42
viii Contents
ResolvingArchitecturalConflict:AHybridApproach 27
HowtheDataWarehouseIsChanging 27
TheMissionoftheETLTeam 28
Chapter2 ETLDataStructures 29
ToStageorNottoStage 29
DesigningtheStagingArea 31
DataStructuresintheETLSystem 35
FlatFiles 35
XMLDataSets 38
RelationalTables 40
IndependentDBMSWorkingTables 41
ThirdNormalFormEntity/RelationModels 42
NonrelationalDataSources 42
DimensionalDataModels:TheHandofffromtheBack
RoomtotheFrontRoom 45
FactTables 45
DimensionTables 46
AtomicandAggregateFactTables 47
SurrogateKeyMappingTables 48
PlanningandDesignStandards 48
ImpactAnalysis 49
MetadataCapture 49
NamingConventions 51
AuditingDataTransformationSteps 51
Summary 52
Part II Data Flow 53
Chapter3 Extracting 55
Part1:TheLogicalDataMap 56
DesigningLogicalBeforePhysical 56
InsidetheLogicalDataMap 58
ComponentsoftheLogicalDataMap 58
UsingToolsfortheLogicalDataMap 62
BuildingtheLogicalDataMap 62
DataDiscoveryPhase 63
DataContentAnalysis 71
CollectingBusinessRulesintheETLProcess 73
IntegratingHeterogeneousDataSources 73
Part2:TheChallengeofExtractingfromDisparate
Platforms 76
ConnectingtoDiverseSourcesthroughODBC 76
MainframeSources 78
WorkingwithCOBOLCopybooks 78
EBCDICCharacterSet 79
ConvertingEBCDICtoASCII 80
P1:FCH/SPH P2:FCH/SPH QC:FCH/SPH T1:FCH
WY046-FM WY046-Kimball-v4.cls August18,2004 13:42
Contents ix
TransferringDatabetweenPlatforms 80
HandlingMainframeNumericData 81
UsingPICtures 81
UnpackingPackedDecimals 83
WorkingwithRedefinedFields 84
MultipleOCCURS 85
ManagingMultipleMainframeRecordTypeFiles 87
HandlingMainframeVariableRecordLengths 89
FlatFiles 90
ProcessingFixedLengthFlatFiles 91
ProcessingDelimitedFlatFiles 93
XMLSources 93
CharacterSets 94
XMLMetaData 94
WebLogSources 97
W3CCommonandExtendedFormats 98
NameValuePairsinWebLogs 100
ERPSystemSources 102
Part3:ExtractingChangedData 105
DetectingChanges 106
ExtractionTips 109
DetectingDeletedorOverwrittenFactRecordsattheSource 111
Summary 111
Chapter4 CleaningandConforming 113
DefiningDataQuality 115
Assumptions 116
Part1:DesignObjectives 117
UnderstandYourKeyConstituencies 117
CompetingFactors 119
BalancingConflictingPriorities 120
FormulateaPolicy 122
Part2:CleaningDeliverables 124
DataProfilingDeliverable 125
CleaningDeliverable#1:ErrorEventTable 125
CleaningDeliverable#2:AuditDimension 128
AuditDimensionFinePoints 130
Part3:ScreensandTheirMeasurements 131
AnomalyDetectionPhase 131
TypesofEnforcement 134
ColumnPropertyEnforcement 134
StructureEnforcement 135
DataandValueRuleEnforcement 135
MeasurementsDrivingScreenDesign 136
OverallProcessFlow 136
TheShowMustGoOn—Usually 138
Screens 139