_transportation_loading warehouse data数据仓库英文oracle99版 教学课件

Transportation LoadingWarehouseData Overview Objectives Aftercompletingthislesson youshouldbeabletodothefollowing ExplainkeyconceptsintransportingdataintothewarehouseOutlinehowtobuildthetransportationprocessforfirsttimeloadIdentifytransportationtechniquesIdentifythetasksthattakeplaceafterdataisloadedExplaintheissuesinvolvedindesigningthetransportation loading andschedulingprocesses TransportingDataintotheWarehouse LoadingmovesthedataintothewarehouseLoadingcanbetime consuming ConsidertheloadwindowSchedulethetask automateallprocessesInitialloadmoveslargevolumesSubsequentrefreshmovessmallervolumesBusinessdeterminesthecycle Extract Transform Transport load Warehouse ExtractProcessingEnvironment Aftereachtimeinterval buildanewdatabaseRunqueries WarehouseProcessingEnvironment BuildanewdatabaseAftereachtimeinterval addchangestodatabaseArchiveorpurgeoldestdataRunqueries T1 T2 T3 Operationaldatabases First TimeLoad SingleeventthatpopulatesthedatabasewithhistoricaldataInvolveslargevolumeofdataEmploysdistinctETTtasksInvolveslargeamountsofprocessingafterload T1 T2 T3 Operationaldatabases Refresh PerformedaccordingtoabusinesscycleSimplertaskLessdatatoloadthanfirst timeloadLess complexETTSmalleramountsofpostloadprocessing T1 T2 T3 Operationaldatabases BuildingtheTransportationProcess SpecificationTechniquesandtoolsFiletransfermethodsTheloadwindowTimewindowforothertasksFirst timeandrefreshvolumesFrequencyoftherefreshcycleConnectivitybandwidth BuildingtheTransportationProcess TesttheproposedtechniqueDocumentproposedloadGainagreementontheprocessMonitorReviewRevise Granularity ImportantdesignandoperationalissueSpacerequirementsStorageBackupRecoveryPartitioningLoad Low levelgrainExpensive highlevelofprocessing moredisk detailHigh levelgrainCheaper lessprocessing lessdisk littledetail TransportationTechniques ToolsUtilitiesand3GLGatewaysCustomizedcopyprogramsReplicationFTPManual TransportationTechniqueConsiderations Toolsarecomprehensivebutcostly Data movementutilitiesarefastandpowerful Gatewaysarenotalwaysthefastestmethod AccessotherdatabasesSupplydependentdatamartsSupportadistributedenvironmentProvidereal timeaccessifneeded UsingSQL LoadertoLoadData SQL Loader Controlfile Badfiles Logfiles Discardfiles FastestloadmechanismDirectpathParallelandunrecoverableDirect loadINSERT Oracle8 Direct pathloadAPI Oracle8i Direct PathLoadAPIinOracle8i Loadutility AllowsETTandothertoolstoloadOracledatabasesefficientlyPermitsloadbehaviortobecustomizedGivesdirect pathloadperformanceProvidescompleteaccesstoalldirect loadfunctionalityusingOCI MoreTransportationTechniqueConsiderations Usecustomizedprogramsasalastresort Replicationislimitedbydata transferrates PostprocessingofLoadedData Extract Transform Transport Loadeddata Postprocessingofloadeddata Createindexes Generatekeys Summarize Filter IndexingData Beforeload fastindexreenablementDuringload addstimetoloadwindowAfterload addstimetoloadwindow Index UniqueIndexes DisableconstraintstoloadEnableconstraintstocreateindex CreatingArtificialKeys UsegeneralizedorderivedkeysMaintaintheuniquenessofarowUseanadministrativeprocesstoassignthekeyConcatenateoperationalkeywithnumber EasytomaintainCumbersomekeysNocleanvalueforretrieval 109908 10990801 CreatingUniqueKeysforRecords Assignanumberfromalist NosemanticmeaningExtractoperationsmustreferencetabletoassignnumbersUpdatemetadataVerdict 109908 1 CreatingSummaryTables CTASpCTAS Summarydata FilteringData FromwarehousetodatamartsCTASpCTAS Summarydata Warehouse Datamarts VerifyingDataIntegrity LoaddataintointermediatefileComparetargetflashtotalswithtotalsbeforeload Load File1 File2 Flashtotals Countsandamounts Intermediatefile File2 File1 Load Warehouse Preserve inspect fix thenload StepsforVerifyingDataIntegrity Control Extract SQL Loader bad 1 2 3 4 5 6 7 log StandardQualityAssuranceChecks LoadstatusCompletionoftheprocessCompletenessofthedataDatareconciliationViolationsReprocessingComparisonofcountsandamounts 1 Summary Thislessondiscussedthefollowingtopics First timeloadcons