Data Vault 数据仓库

2009-09-16/p.12009-09-16/p.1SeminardatawarehousingJeroenKlepSeptember16th,2009DataVaultIntroduction2009-09-16/p.2Agenda•Introandhistoricalbackground•Positioningandconcept•DataVaultexample:‘TheDVDStore’•Demo:Quipudatawarehousemanagement2009-09-16/p.3(known)DutchDVusers•SNSBank•Belastingdienst•ANWB•Coram•HypothekersAssociatie•FrieslandBank•ING•Nutreco•…andmanymore…2009-09-16/p.4IntrobyDanLinstedtSource:YouTube2009-09-16/p.5Viewers…alsoactingassortofindicatorforinterest/applicationofDVPOSITIONINGANDCONCEPT2009-09-16/p.7ModelingforBI:EvolutionCommonDataWarehousemodelingtechniques–3NF:introducedbyCodd&Date–Starschemas&snowflakes:introducedbyKimballNewmodelingtechniqueintroducedbyDanLinstedt:DataVault!Source:DanLinstedt2009-09-16/p.8DataVaultDWarchitectureDWSemanticintegrationDependentdatamartsDWwith‘raw’dataQuality–Cleansing–Businessrules–Aggregation–KPIcalculationDATAVAULT2009-09-16/p.9WhatisDataVault?Definitions:“TheDataVaultisadetailoriented,historicaltrackinganduniquelylinkedsetofnormalizedtablesthatsupportoneormorefunctionalareasofbusiness.Itisahybridapproachencompassingthebestofbreedbetween3rdnormalform(3NF)andstarschema.”(DanLinstedt,)“TheDataVaultisadataintegrationarchitecture;aseriesofstandards,anddefinitionalelementsormethodsbywayinformationisconnectedwithinanRDBMSdatastoreinordertomakesenseofit.”(DanLinstedt,)2009-09-16/p.10DataVault–ComponentsADataVaultdatamodelconsistsofthreetypesofcomponents:1.Hubentities:Listofuniquebusinesskeyswiththeiraccompanyingsurrogate(meaningless)keys2.Linkentities:Physicalrepresentationof3NFmany-to-manyrelationships(relationshiportransactionbetweenbusinesscomponents/businesskeys)3.Satelliteentities:Containhuborlinkdescriptiveinformation(attributes)andhistoryDATAVAULTEXAMPLE:DVDSTORE2009-09-16/p.12DVDStore-Description•SimulationofanonlineeCommercesite•Sales/orderdataofDVDs(products)tocustomersincludinghistoricsalesinformationandproductinventoryfigures•Thefollowingmodelswillbeshown1.OLTP(sourcemodel)2.ERDdiagram(logicalmodel)3.DataVaultmodela.Hubsb.Linksc.Satellites2009-09-16/p.13DVDStore-ERD2009-09-16/p.14DVDStore–HubsHubsSatellitesLinks2009-09-16/p.15DVDStore–LinksHubsSatellitesLinks2009-09-16/p.16DVDStore–AdditionalsatellitesHubsSatellitesLinks2009-09-16/p.17DVDStore–AllsatellitesHubsSatellitesLinks2009-09-16/p.18DVDStore–CompleteDataVaultHubsSatellitesLinks2009-09-16/p.19Hub_CategoryCategory_IDLoad_DtsCATEGORY_SIDHub_CustomerCustomer_IDLoad_DtsCUSTOMERID_SIDHub_OrderOrder_IDLoad_DtsORDERID_SIDHub_ProductProduct_IDLoad_DtsPROD_ID_SIDLnk_Cust_HistLnk_Cust_Hist_IDCustomer_IDOrder_IDProduct_IDLoad_DtsLnk_Cust_Hist_SatLnk_Cust_Hist_IDLoad_DtsLnk_Order_CustomerLnk_Order_Customer_IDOrder_IDCustomer_IDLoad_DtsLnk_Order_Customer_SatLnk_Order_Customer_IDLoad_DtsLnk_OrderlinesLnk_Orderlines_IDOrder_IDProduct_IDLoad_DtsLnk_Orderlines_SatLnk_Orderlines_IDQUANTITYORDERDATELoad_DtsLnk_Product_CategoryLnk_Product_Category_IDProduct_IDCategory_IDLoad_DtsLnk_Product_Category_SatLnk_Product_Category_IDLoad_DtsLnk_Product_Common_ProductLnk_Product_Common_Product_IDProduct_IDProduct_ID_CommonLoad_DtsLnk_Product_Common_Product_SatLnk_Product_Common_Product_IDLoad_DtsSat_CategoryCategory_IDCATEGORYNAMELoad_DtsSat_CustomerCustomer_IDFIRSTNAMELASTNAMEADDRESS1ADDRESS2CITYSTATEZIPCOUNTRYREGIONEMAILPHONECREDITCARDTYPECREDITCARDCREDITCARDEXPI...USERNAMEPASSWORDSat_OrderOrder_IDORDERDATECUSTOMERIDNETAMOUNTTAXTOTALAMOUNTLoad_DtsSat_ProductProduct_IDTITLEACTORPRICESPECIALLoad_DtsSat_Product_InventoryProduct_IDQUAN_IN_STOCKSALESLoad_DtsSat_Product_ReorderProduct_IDDATE_LOWQUAN_LOWDATE_REORDEREDQUAN_REORDEREDDATE_EXPECTEDLoad_DtsDVDStore–Databasemodel2009-09-16/p.20DVDStore–DatabasemodelHub_CategoryCategory_IDLoad_DtsCATEGORY_SIDHub_CustomerCustomer_IDLoad_DtsCUSTOMERID_SIDHub_OrderOrder_IDLoad_DtsORDERID_SIDHub_ProductProduct_IDLoad_DtsPROD_ID_SIDLnk_Cust_HistLnk_Cust_Hist_IDCustomer_IDOrder_IDProduct_IDLoad_DtsLnk_Cust_Hist_SatLnk_Cust_Hist_IDLoad_DtsLnk_Order_CustomerLnk_Order_Customer_IDOrder_IDCustomer_IDLoad_DtsLnk_Order_Customer_SatLnk_Order_Customer_IDLoad_DtsLnk_OrderlinesLnk_Orderlines_IDOrder_IDProduct_IDLoad_DtsLnk_Orderlines_SatLnk_Orderlines_IDQUANTITYORDERDATELoad_DtsLnk_Product_CategoryLnk_Product_Category_IDProduct_IDCategory_IDLoad_DtsLnk_Product_Category_SatLnk_Product_Category_IDLoad_DtsLnk_Product_Common_ProductLnk_Product_Common_Product_IDProduct_IDProduct_ID_CommonLoad_DtsLnk_Product_Common_Product_SatLnk_Product_Common_Product_IDLoad_DtsSat_CategoryCategory_IDCATEGORYNAMELoad_DtsSat_CustomerCustomer_IDFIRSTNAMELASTNAMEADDRESS1ADDRESS2CITYSTATEZIPCOUNTRYREGIONEMAILPHONECREDITCARDTYPECREDITCARDCREDITCARDEXPI...USERNAMEPASSWORDSat_OrderOrder_IDORDERDATECUSTOMERIDNETAMOUNTTAXTOTALAMOUNTLoad_DtsSat_ProductProduct_IDTITLEACTORPRICESPECIALLoad_DtsSat_Product_InventoryProduct_IDQUAN_IN_STOCKSALESLoad_DtsSat_Product_ReorderProduct_IDDATE_LOWQUAN_LOWDATE_REORDEREDQUAN_REORDEREDDATE_EXPECTEDLoad_Dts2009-09-16/p.21DVDStore-OLTPCATEGORIESCATEGORYCATEGORYNA