APAC ETL Developement Document.docx

APAC ETL Developement Document.docx

《APAC ETL Developement Document.docx》由会员分享，可在线阅读，更多相关《APAC ETL Developement Document.docx（14页珍藏版）》请在冰豆网上搜索。

APACETLDevelopementDocument

3MAPACDWBIETLDevelopmentDocument

2008-7-10

1Instruction1

2ETLArchitecture2

3ETLProcessandRelatedTechniqueinSSIS3

3.1InitializingEnvironment4

3.2InitializingStagingArea7

3.3InitializingDataCleaning9

3.4InitializingDataWarehouse9

3.5InitializingDataMart9

3.6UpdateStagingArea9

3.7UpdateDataCleaning10

3.8UpdateDataWarehouse10

3.9UpdateDataMarts10

4ProceduresUsedinSSIS10

4.1ForETL10

4.2ForMetaData10

4.3ForAuthorization10

5ConfigurationFilesforDeploy10

6DeploytotheSSISServer11

6.1Configurationindeployment11

6.2BreakOffandRerunMechanism11

1Instruction

3MAPACdatawarehouseshouldbedesignedtosupporttheregiondifferentsubjectrequirements,suchasPOS,Sales,Logisticsandetc.TheETLisdevelopedinSSISandSQLServer2005.Inthisprojectwedesignapowerfularchitecturetointegratethemultipledatasourcesandcomplexdatawarehousingprocess.Inthefollowingphase2wewillintroducetheETLArchitecture,andinphase3theETLProcessandRelatedTechniquewillbeintroductedindetail.

2ETLArchitecture

Figure2.1:

APACDWETLArchitecture

ShownasFigure2.1,theAPACDWETLArchitectureconsistsofthebelowflows:

1.PreparationfortheDataSource.AccordingtherequestofDWdatasourcewecanpreprocesstheoriginaldatafromdifferenttables,views,suchascuttingoffthefields,combiningthedatasetsfromthetablesjoinedandetc.

2.ChoosingDataChannelsfromDataSourcetoStagingArea.Whenwepreparedthedatasource,thenweshoulddecidehowcanthedatawarehouseservercangetthesesources.Inthisprojectthereare3channelwecanchoose,

a）DirectlyduplicatethedatasourcebyOLEDBfortheSQLServerDB.

b）IfthesourceDBisnotSQLServerandthedatasizeishuge,itissuggestedtoexportthedatasourceinflatfiles.AndgettheflatfilesfromdatasourceserverbyFTPorothersimilarmethodsandbulkinsertintothestagingarea.ThetableDWdimensionrelatedshouldbeexportedafterthatthefactrelated.

c）LocalexternaldatasourcecanbeimporteddirectlybySSIS.

3.CreatingStagingArea.Thestagingareaisrequiredtoreceiveallofthedatasourceintheoriginalform,andthetablenamingshouldflowthenamingstandards.Intheextractionthefacttablerelatedtablesareearlierthanthedimensionrelated.

4.DataCleaning.Checkifthedatafollowsthebusinessrulesandotherdefinitionsandcleanthenoise.

5.ETLfromstagingareatodatawarehouseintheorder

a）DWdimensiontables

b）DWfacttables

c）DMdimensiontables

d）DMfacttables

6.ETLfromDataWarehousetoDataMartintheorder

a）DMdimensiontables

b）DMfacttable

Ofcoursethisarchitectureisdesignedaccordingcurrentrequirementsandourunderstanding,wecanexpectittobetunedstepbystepbutmaybenotbigchange.

3ETLProcessandRelatedTechniqueinSSIS

InthephysicaldevelopmenttheSQLServer2005SSISisadoptedastheETLtool,andlotsofcomponentsbeusedinthecoding,suchasPackage,ExecuteSQLTask,DataFlow,StoreProcedureandothers,andwewillintroducetheirusageandtechniqueknowledge.FromtheFigure3.1and3.2wecanhandlethetop2layersprocessionsinthesub-processionofinitializingandupdating,whichwillbesteadyandinvariableinthefutureifwedesignproperly,sowewillintroducetheETLprocessandrelatedtechniqueindetailbytheactualdataflowinthearchitecture.

Figure3.1Top2LayerProcessionsintheInitializingofETL

Figure3.2Top2LayerProcessionsintheUpdatingofETL

3.1InitializingEnvironment

1CreatingTables:

CreatingalloftheSA/DW/DMtables.（Container）

AllthetablesfollowingcanbefindtheirdefinitionandmeaninginthedatamodeldesignedbyPowerDesign.

1.1CreatingStagingAreaTables:

Creatingallofthestagingareatables.（Container,）

1.1.1CNPOS:

CreatingallofthestagingareatablescomefromCNPOS.（Container）

AllofthebelowtablescreatedbydirectlyinputSQLinExecuteSQLTaskifnotlabeled.

1.1.1.1CNPOS_ProductCategory,

1.1.1.2CNPOS_Distributor,

1.1.1.3CNPOS_SyncLog,

1.1.1.4CNPOS_SalesFreeze,

1.1.1.5CNPOS_AccessControl,

1.1.1.6CNPOS_Division,

1.1.1.7CNPOS_SalesQuota,

1.1.1.8CNPOS_SalesRecordDetail,

1.1.1.9CNPOS_CommodityCode,

1.1.1.10CNPOS_RankRule,

1.1.1.11CNPOS_SharingInventory,

1.1.1.12CNPOS_CustomerContact,

1.1.1.13CNPOS_DM_CustomerType,

1.1.1.14CNPOS_ExternalCustomer,

1.1.1.15CNPOS_StatisticsByMonth,

1.1.1.16CNPOS_SharableProduct,

1.1.1.17CNPOS_DM_Job,

1.1.1.18CNPOS_Product,

1.1.1.19CNPOS_DM_CompanySize,

1.1.1.20CNPOS_PurchaseDetail,

1.1.1.21CNPOS_DM_Regionalism,

1.1.1.22CNPOS_DM_Industry,

1.1.1.23CNPOS_SalesRecord,

1.1.1.24CNPOS_StatisticsBySeason,

1.1.1.25CNPOS_AccessLog,

1.1.1.26CNPOS_InternalCustomer

1.1.2CNCPOS:

CreatingallofthestagingareatablescomefromCNCPOS.（Container）

AllofthebelowtablescreatedbydirectlyinputSQLinExecuteSQLTaskifnotlabeled.

1.1.2.1CNCPOS_CUST,

1.1.2.2CNCPOS_PROD,

1.1.2.3CNCPOS_SALE_PROD,

1.1.2.4CNCPOS_INVENTORY,

1.1.2.5CNCPOS_ROLE,

1.1.2.6CNCPOS_USER,

1.1.2.7CNCPOS_USER_ASSO,

1.1.2.8CNCPOS_COMM_CODE,

1.1.2.9CNCPOS_ORD,

1.1.2.10CNCPOS_DOMAIN,

1.1.2.11CNCPOS_PROD_CLS,

1.1.2.12CNCPOS_STATUS,

1.1.2.13CNCPOS_SALE.

1.1.3CNLDW:

CreatingallofthestagingareatablescomefromCNLDW.（Container）

1.1.3.1None

1.1.4OtherTables:

Creatingallofthestagingareatablescomefromothersource,suchastheexternalsource.（Container）

AllofthebelowtablescreatedbydirectlyinputSQLinExecuteSQLTaskifnotlabeled

1.1.4.1CNPOSA_SalesAuthorization.

1.2CreatingDWTables:

Creatingallofthedatawarehousetables.（Container）

AllofthebelowtablescreatedbydirectlyinputSQLinExecuteSQLTaskifnotlabeled

1.2.1.1DimensionTables:

Creatingallofthedimensiontablesindatawarehouse.（Container）

1.2.1.1.1DimSalesPerson,

1.2.1.1.2DimInternalAccount,

1.2.1.1.3DimeTime,

1.2.1.1.4DimSalesPersonTemp,

1.2.1.1.5DimInternalAccountTemp,

1.2.1.1.6DimeTimeTemp,

1.2.1.1.7DimExternalAccount,

1.2.1.1.8DimProduct,

1.2.1.1.9DimExternalContact,

1.2.1.1.10DimExternalAccountTemp,

1.2.1.1.11DimProductTemp,

1.2.1.1.12DimInternalContact,

1.2.1.1.13DimCustomer,

1.2.1.1.14DimCustomerTemp,

1.2.1.2FactTables:

Creatingallofthefacttablesindatawarehouse.（Container）

1.2.1.2.1FactPOSSales,

1.2.1.2.2FactPOSSalesTemp,

1.2.1.2.3FactInventory

1.3CreatingDMTables:

CreatingtheDMtables.（Container）

1.3.1None

1.4CreatingOtherTables:

Creatingtheothertables.（Container）

AllofthebelowtablescreatedbydirectlyinputSQLinExecuteSQLTaskifnotlabeled

1.4.1ETLLog：

CreatingtableETLLogintheschemasofDWforrecordingthelogforETL.（ExecuteSQLTask）

2ImportStoreProcedures:

ImportingthestoreproceduresusedintheETL.（Container）

2.1RecordingMetaData:

ImportingthestoreproceduresforrecordingmetadataintheETL.（Container）

2.1.1EtlMDLastIfSuccess:

ChecktheETLoflasttimewhetheritissuccess.（ExecuteSQLTask）

2.1.2EtlMDAddLog:

AddthelogforETL,whichshouldaddthedescriptionofthistask.（ExecuteSQLTask）

2.2ETLProcess:

TheproceduresforETLprocession.（Container）

AllofthebelowprocedurescreatedbytheconnectionofSQLfilesinExecuteSQLtaskifnotlabeled

2.2.1StagingTables:

TheproceduresforETLprocessiononstagingareatables.（Container）

2.2.1.1DataCleaning:

Proceduresfordatacleaningstagingareatables.（Container）

2.2.1.1.1EtlIniCleaningCheckSalesAuthorization:

TheproceduresfordatacleaningoftableSA.SalesAuthorizationinstagingarea.（ExecutSQLTask）

2.2.2DWTables:

ImportingtheproceduresfortheinitializingofDW.（Container）

2.2.2.1DimensionTables:

Importingtheproceduresfortheinitializingofdimensiontablesindatawarehouse.（Container）

2.2.2.1.1EtlIniDWDimTime:

TheprocedureforinitializingdimensiontableDW.DimTime.

2.2.2.1.2EtlIniDWDimExternalAccount:

TheprocedureforinitializingdimensiontableDW.DimExternalAccount.

2.2.2.1.3EtlIniDWDimCustomer:

TheprocedureforinitializingdimensiontableDW.DimTime.

2.2.2.1.4EtlIniDWDimInternalAccount:

TheprocedureforinitializingdimensiontableDW.DimInternalAccount.

2.2.2.1.5EtlIniDWDimExternalContact:

TheprocedureforinitializingdimensiontableDW.DimExternalContact.

2.2.2.1.6EtlIniDWDimInternalContact:

TheprocedureforinitializingdimensiontableDW.DimInternalContact.

2.2.2.1.7EtlIniDWDimSalesPerson:

TheprocedureforinitializingdimensiontableDW.DimSalesPerson.

2.2.2.2FactTables:

Importingtheproceduresfortheinitializingoffacttablesindatawarehouse.（Container）

2.2.2.2.1EtlIniDWFactPOSSales:

TheprocedureforinitializingfacttableDW.FactPOSSales.

2.2.2.2.2EtlIniDWFactInventory:

TheprocedureforinitializingfacttableDW.FactInventory.

2.2.3DMTables:

ImportingtheproceduresfortheinitializingofDM.（Container）

2.2.3.1None

2.2.4OtherTables:

Importingtheproceduresfortheprocessofothertables.（Container）

2.2.4.1EtlIniAuthorizationTables:

TheprocedureforinitializingauthorizationtableDW.FactInventory

3.2InitializingStagingArea

AllofthefollowingtablesinstagingareaisinitializedbythedataflowofSSISifnotlabeled.Thetablesfactrelatedisinsertedthedatasetsearlierthanthatdimensionrelated.

1FactRelated:

Initializingthestagingareatablesfactrelated.（Container）

1.1CNPOSFactRelated:

:

InitializingthestagingareatablesfactrelatedcomefromCNPOS..（Container）

1.1.1CNPOS_SalesRecordDetail

1.1.2CNPOS_SalesRecord

1.1.3CNPOS_PurchaseDetail

1.1.4CNPOS_SharingInventory

1.1.5CNPOS_StatisticByMonth

1.1.