Название пакета: DWH_ETL_SellOut¶

Описание¶

Загрузка данных SellOut в хранилище KRAFT_DWH_CSL

Схема¶

Список шагов¶

1. Create new batch identifier¶

Тип: Execute SQL Task
Описание: Генерация ID выполнения ETL-процесса
Процедура: audit.usp_GetBatch
Параметры мэппинга данных:
Batch → User::Batch (Output)

2. Create, Transform Dimensions and Other Tables¶

Тип: Sequence Container
Описание: Создание и обработка измерений

2.1 Dimension Relationship¶

Тип: Execute SQL Task
Описание: Генерация связей между измерениями (отключено - Disabled="True")
Процедура: etl.usp_GenerateDimensionRelationship
Параметры мэппинга данных:
Batch → User::Batch

2.2 Other Dimension¶

Тип: Foreach Loop Container
Описание: Загружает данные в таблицы измерений SellOut
Цикличность: Foreach Loop Container
Процедура: etl.usp_LoadOtherDIM
Параметры мэппинга данных:
@Batch → User::Batch
@DIM_SourceViewName → User::DIM_SourceViewName
@DIM_DimensionTableName → User::DIM_DimensionTableName
@DIM_MergeOnColumns → User::DIM_MergeOnColumns
@DIM_MergeExcludedColumns → User::DIM_MergeExcludedColumns
@DIM_Mode → User::DIM_Mode

Используемые объекты:

DIM_SourceViewName	DIM_DimensionTableName	DIM_MergeOnColumns	DIM_Mode
dbo.VIEW_D_POSSpecial	dbo.D_POSSpecial	Code	2
dbo.VIEW_D_POSChain	dbo.D_POSChain	UID	2
dbo.VIEW_D_POSInvestment	dbo.D_POSInvestment	UID	2
dbo.VIEW_D_GeographicalRegionRF	dbo.D_GeographicalRegionRF	UID	2
dbo.VIEW_D_GeographicalCityRF	dbo.D_GeographicalCityRF	UID	2
dbo.VIEW_D_POSCategory	dbo.D_POSCategory	Code	2
dbo.VIEW_D_POSTradeFormat	dbo.D_POSTradeFormat	Code	2
dbo.VIEW_D_POSRetailEnvironment	dbo.D_POSRetailEnvironment	Code	2
dbo.VIEW_D_POSBanner	dbo.D_POSBanner	Code	2
dbo.VIEW_D_POS	dbo.D_POS	UID_POS	2
dbo.VIEW_D_CustomerIn_SellOut	dbo.D_CustomerIn_SellOut	DataSource, UID_CustomerIn, DateBegin	2
dbo.VIEW_D_WarehouseType	dbo.D_WarehouseType	UID_WarehouseType	2
dbo.VIEW_D_Warehouse	dbo.D_Warehouse	UID_Warehouse	2
dbo.VIEW_D_SellOutBonus	dbo.D_SellOutBonus	ID_YearMonth	1
dbo.VIEW_D_ProductEAN	dbo.D_ProductEAN	EAN	2
dbo.VIEW_D_SalesForce	dbo.D_SalesForce	UID	2
dbo.VIEW_D_SalesForce_HashSalesForceHistory	dbo.D_SalesForce	UID	2
dbo.VIEW_D_POS_SalesForce	dbo.D_POS_SalesForce	ID_POS, ID_SalesForce	1
dbo.VIEW_D_SalesForceNow	dbo.D_SalesForceNow	ID_POS, ID_ProductAssortment, ID_SalesForce	1
dbo.VIEW_D_Route	dbo.D_Route	UID	2
dbo.VIEW_D_SalesForce_POSChain	dbo.D_SalesForce_POSChain	ID_POSChain, ID_SalesForce, ID_POS	1
dbo.VIEW_D_WarehouseHistory	dbo.D_WarehouseHistory	ID_Warehouse, ID_YearMonthBegin	1
dbo.VIEW_D_MSLScenario	dbo.D_MSLScenario	UID	1
dbo.VIEW_D_SellOutPromo	dbo.D_SellOutPromo	ID_SellOutSales, ID_ProductEAN	1
dbo.VIEW_D_POS_RDC	dbo.D_POS_RDC	ID_POS, ID_POS_RDC	1
dbo.VIEW_D_TDPCalculationParameters	dbo.D_TDPCalculationParameters	nPartYearMonth, ID_ProductCategory	2
dbo.VIEW_D_PosInRouteVersion	dbo.D_PosInRouteVersion	ID	1
dbo.VIEW_D_SalesForce_LowLevel_PosChain	dbo.D_SalesForce_LowLevel_PosChain	ID_POSChain, ID_SalesForce, ID_POS	1
dbo.VIEW_D_TDP_Products	dbo.D_TDP_Products	ID_TDP_Scenario, ID_ProductFamily	1
dbo.VIEW_D_POS_SalesForce_Reassignment	dbo.D_POS_SalesForce_Reassignment		1
dbo.VIEW_D_POS_CustomerInHierarchy	dbo.D_POS_CustomerInHierarchy		1
dbo.VIEW_D_SalesForce_Responsible	dbo.D_SalesForce_Responsible		1
dbo.VIEW_D_UserRights	dbo.D_UserRights	UserLogin	1
dbo.VIEW_D_OrdersExtended	dbo.D_OrdersExtended	OrderNumberMerged	2
dbo.VIEW_D_Orders_OrdersExtended	dbo.D_Orders_OrdersExtended	ID_OrdersExtended, ID_Orders	-1
dbo.VIEW_D_OrdersExtended_CHGO_Docs	dbo.D_OrdersExtended_CHGO_Docs	nPartYearMonth, ID_Doc_Chicago	2

3. Load data to StageArea¶

Тип: Sequence Container
Описание: Загрузка данных из различных источников в Stage Area

3.1 Source ASSA to SA DWH¶

Тип: Foreach Loop Container
Описание: Загрузка данных из источника ASSA
Цикличность: Foreach Loop Container

Подшаг: Load Source 13 data for table

Тип: Execute SQL Task
Процедура: etl.usp_ImportDataFromSQLServer
Параметры мэппинга данных:
@Batch → User::Batch
@Link_Name → User::Source13_Link_Name
@DB_Name → User::Source13_DB_Name
@TableNameSource → User::Source13_TableNameSource
@TableNameDestination → User::Source13_TableNameDestination
@TableIncludedColumns → User::Source13_TableIncludedColumns
@TableExcludedColumns → User::Source13_TableExcludedColumns
@TableDistinct → User::Source13_TableDistinct
@TableWhere → User::Source13_TableWhere

Используемые объекты:

Source13_TableNameSource	Source13_TableNameDestination
dbo.vw_POS_Route_6tier	sa.SA_ASSA_vw_POS_Route_6tier
dbo.tbl_SRC_F_PriceDefinitions	sa.SA_ASSA_tbl_SRC_F_PriceDefinitions
dbo.tbl_SRC_Customers	sa.SA_ASSA_tbl_SRC_Customers
dbo.vw_WiSE_Photo_Descr	sa.SA_ASSA_vw_WiSE_Photo_Descr
dbo.VIEW_Wise_IL_Display	sa.SA_WISE_Display
dbo.tblRef	sa.SA_ASSA_tblRef
dbo.tbl_SIP_NKA_IMS_NET	sa.SA_ASSA_tbl_SIP_NKA_IMS_NET
dbo.tblRef2	sa.SA_ASSA_tblRef2
dbo.vw_DisplayTypeOfApproval_for_Photoreport	sa.SA_ASSA_vw_DisplayTypeOfApproval_for_Photoreport
dbo.tbl_SIP_NKA_IMS_NET_POS	sa.SA_ASSA_tbl_SIP_NKA_IMS_NET_POS
dbo.ref_Category_TDP	sa.SA_ASSA_ref_Category_TDP
dbo.vw_NKA_MergeDocs_forOLAP	sa.SA_ASSA_vw_NKA_MergeDocs_forOLAP

3.2 Source CHGO to SA DWH¶

Тип: Foreach Loop Container
Описание: Загрузка данных из источника Chicago
Цикличность: Foreach Loop Container

Подшаг: Load Source 14 data for table

Тип: Execute SQL Task
Процедура: etl.usp_ImportDataFromSQLServer
Параметры мэппинга данных: (аналогично Source 13)

Используемые объекты:

Source14_TableNameSource	Source14_TableNameDestination	Source14_TableWhere
dbo.DistributorDim	sa.SA_CHGO_DistributorDim
dbo.OutletsDim	sa.SA_CHGO_OutletsDim
dbo.SkuDim	sa.SA_CHGO_SkuDim
dbo.StoresDim	sa.SA_CHGO_StoresDim
dbo.OwnersDim	sa.SA_CHGO_OwnersDim
dbo.OutletsActivity	sa.SA_CHGO_OutletsActivity	SK_Date_ID >= cast(convert(varchar(6),(dateadd(m,-3,getdate())),112) as int) * 100 + 1 and SK_Date_ID < cast(convert(varchar(6),(dateadd(m,1,getdate())),112) as int) * 100 + 1
dbo.RetailChainDim	sa.SA_CHGO_RetailChainDim
dbo.RdcOutletsFact	sa.SA_CHGO_RdcOutletsFact
dbo.PositionsDim	sa.SA_CHGO_PositionsDim
dbo.OutletsPositions	sa.SA_CHGO_OutletsPositions
dbo.EmployeeDim	sa.SA_CHGO_EmployeeDim
dbo.TopicDim	sa.SA_CHGO_TopicDim
dbo.TemplateDim	sa.SA_CHGO_TemplateDim
dbo.AnswerDim	sa.SA_CHGO_AnswerDim
dbo.RouteDim	sa.SA_CHGO_RouteDim
dbo.PosmDim	sa.SA_CHGO_PosmDim
dbo.CampaignDim	sa.SA_CHGO_CampaignDim
dbo.CampaignMatrixDim	sa.SA_CHGO_CampaignMatrixDim
dbo.ReturnFact	sa.SA_CHGO_ReturnFact	SK_Date_ID >= cast(convert(varchar(6),(dateadd(m,-1,getdate())),112) as int) * 100 + 1 and SK_Date_ID < cast(convert(varchar(6),(dateadd(m,1,getdate())),112) as int) * 100 + 1
dbo.CallsFact	sa.SA_CHGO_CallsFact	SK_Date_ID >= cast(convert(varchar(6),(dateadd(m,-12,getdate())),112) as int) * 100 + 1 and SK_Date_ID < cast(convert(varchar(6),(dateadd(m,1,getdate())),112) as int) * 100 + 1
dbo.OrdersFact	sa.SA_CHGO_OrdersFact	SK_Date_ID >= cast(convert(varchar(6),(dateadd(m,-3,getdate())),112) as int) * 100 + 1 and SK_Date_ID < cast(convert(varchar(6),(dateadd(m,1,getdate())),112) as int) * 100 + 1
dbo.TimeDim	sa.SA_CHGO_TimeDim
dbo.WorkdaysCalendarFact	sa.SA_CHGO_WorkdaysCalendarFact	SK_Date_ID >= cast(convert(varchar(6),(dateadd(m,-3,getdate())),112) as int) * 100 + 1 and SK_Date_ID < cast(convert(varchar(6),(dateadd(m,1,getdate())),112) as int) * 100 + 1
dbo.refNetworkResponsible	sa.SA_CHGO_refNetworkResponsible
dbo.nsf_PromoGoodsSales	sa.SA_CHGO_nsf_PromoGoodsSales
dbo.SurveyFact	sa.SA_CHGO_SurveyFact	SK_Date_ID >= cast(convert(varchar(6),(dateadd(m,-3,getdate())),112) as int) * 100 + 1 and SK_Date_ID < cast(convert(varchar(6),(dateadd(m,1,getdate())),112) as int) * 100 + 1
dbo.SurveyDim	sa.SA_CHGO_SurveyDim
admin.Settings	sa.SA_CHGO_admin_Settings

3.3 Source CHGO to SA DWH Route¶

Тип: Foreach Loop Container
Описание: Загрузка данных маршрутов из источника Chicago
Цикличность: Foreach Loop Container

Подшаг: Load Source 17 data for table

Тип: Execute SQL Task
Процедура: etl.usp_ImportDataFromSQLServer
Параметры мэппинга данных: (аналогично Source 13)

Используемые объекты:

Source17_TableNameSource	Source17_TableNameDestination	Source17_TableWhere
dbo.RouteTerritoryFact	sa.SA_CHGO_RouteTerritoryFact	SK_Date_ID >= cast(convert(varchar(6),(dateadd(m,-3,getdate())),112) as int) * 100 + 1 and SK_Date_ID < cast(convert(varchar(6),(dateadd(m,1,getdate())),112) as int) * 100 + 1

3.4 Source CHGO to SA DWH Sales Fact¶

Тип: Foreach Loop Container
Описание: Загрузка фактов продаж из источника Chicago
Цикличность: Foreach Loop Container

Подшаг: Load Source 18 data for table

Тип: Execute SQL Task
Процедура: etl.usp_ImportDataFromSQLServer
Параметры мэппинга данных: (аналогично Source 13)

Используемые объекты:

Source18_TableNameSource	Source18_TableNameDestination	Source18_TableWhere
dbo.SalesFact	sa.SA_CHGO_SalesFact	SK_Date_ID >= cast(convert(varchar(6),(dateadd(m,-1,getdate())),112) as int) * 100 + 1 and SK_Date_ID < cast(convert(varchar(6),(dateadd(m,1,getdate())),112) as int) * 100 + 1

3.5 Source CHGOV to SA DWH¶

Тип: Foreach Loop Container
Описание: Загрузка данных из источника Chicago Vendor
Цикличность: Foreach Loop Container

Подшаг: Load Source 15 data for table

Тип: Execute SQL Task
Процедура: etl.usp_ImportDataFromSQLServer
Параметры мэппинга данных: (аналогично Source 13)

Используемые объекты:

Source15_TableNameSource	Source15_TableNameDestination	Source15_TableExcludedColumns
dbo.NKAOutlets	sa.SA_CHGOV_NKAOutlets	Banner_LST_CODE, Banner_LST_CODE, Banner_LST_NAME, TownPOS, RegPOS, SpecType, CurrStatus, NetworkType, HoldOutletType, HoldSpecialOutletType, HoldResponsibleCode, HoldResponsibleName, HoldStatus, LoadDate, LoadPosition
dbo.EmployeeEmails	sa.SA_CHGOV_EmployeeEmails
dbo.Orders_InvoicesforVitrina	sa.SA_CHGOV_Orders_InvoicesforVitrina

3.6 Source SOPTool to SA DWH¶

Тип: Foreach Loop Container
Описание: Загрузка данных из источника SOPTool
Цикличность: Foreach Loop Container

Подшаг: Load Source 6 data for table

Тип: Execute SQL Task
Процедура: etl.usp_ImportDataFromSQLServer
Параметры мэппинга данных: (аналогично Source 13)

Используемые объекты:

Source6_TableNameSource	Source6_TableNameDestination
bi.vw_F_ACB_TDP_plans	sa.SA_SOPTool_vw_F_ACB_TDP_plans
pnl.vw_SalesForce_Reassignment	sa.SA_SOPTool_vw_SalesForce_Reassignment
DSR.SO_Bonus_Fact	sa.SA_SOPTool_DSR_SO_Bonus_Fact
DSR.SO_Bonus_Scale	sa.SA_SOPTool_DSR_SO_Bonus_Scale
dsr.SO_Bonus_Exception	sa.SA_SOPTool_DSR_SO_Bonus_Exception

3.7 Source STAR to SA DWH¶

Тип: Foreach Loop Container
Описание: Загрузка данных из источника STAR
Цикличность: Foreach Loop Container

Подшаг: Load Source 4 data for table

Тип: Execute SQL Task
Процедура: etl.usp_ImportDataFromSQLServer
Параметры мэппинга данных: (аналогично Source 13)

Используемые объекты:

Source4_TableNameSource	Source4_TableNameDestination	Source4_TableWhere
dbo.BUDGETS	sa.SA_STAR_BUDGETS
dbo.CSL_REPORTS	sa.SA_STAR_CSL_REPORTS
dbo.CSL_REPORTS_POS	sa.SA_STAR_CSL_REPORTS_POS	Type not in ('P') and ReadyForPayChecked = 1
dbo.KF_MONTHS	sa.SA_STAR_KF_MONTHS

4. Execute Package DWH_ETL_SA¶

Тип: Execute Package Task
Описание: Выполнение пакета загрузки Stage Area (отключено - Disabled="True")
Выполняемый пакет: DWH_ETL_SA.dtsx

5. Execute Package DWH_MDT¶

Тип: Execute Package Task
Описание: Выполнение пакета загрузки MDT
Выполняемый пакет: DWH_MDT.dtsx

6. Execute Package DWH_MDT_SellOut¶

Тип: Execute Package Task
Описание: Выполнение пакета загрузки MDT для SellOut
Выполняемый пакет: DWH_MDT_SellOut.dtsx

7. Process Fact SellOut Real¶

Тип: Sequence Container
Описание: Обработка реальных фактов SellOut

7.1 Execute Package DWH_ETL_SellOutReal¶

Тип: Execute Package Task
Описание: Выполнение пакета обработки реальных данных SellOut
Выполняемый пакет: DWH_ETL_SellOutReal.dtsx

7.2 Execute SQL Task¶

Тип: Execute SQL Task
Описание: Дополнительная обработка данных SellOut

8. Process Fact tables¶

Тип: Sequence Container
Описание: Обработка таблиц фактов SellOut
Цикличность: Foreach Loop Container (Enumerate Fact tables 1)

8.1 Load data to Fact table¶

Тип: Execute SQL Task
Описание: Загрузка данных в таблицы фактов
Процедура: etl.usp_LoadFactPart
Параметры мэппинга данных:
@Batch → User::Batch
@sSourceViewName → User::FACT_SourceViewName
@sFactTableName → User::FACT_FactTableName
@sPartColumnDate → User::FACT_PartColumnDate
@sExcludedColumns → User::FACT_ExcludedColumns
@nUseTempTable → User::FACT_UseTempTable
@nMode → User::FACT_Mode
@sTablePartition → User::FACT_FactPartTableName

Используемые объекты:

FACT_SourceViewName	FACT_FactTableName	FACT_PartColumnDate	FACT_Mode	FACT_FactPartTableName
dbo.VIEW_F_SellOutMarkUpHistory	dbo.F_SellOutMarkUpHistory	nPartYearMonth	1
dbo.VIEW_F_SellOutMarkUpException	dbo.F_SellOutMarkUpException	nPartYearMonth	0
dbo.VIEW_F_ProductVATHistory	dbo.F_ProductVATHistory	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_ProductBasePriceListExtendedOrdersEAN	dbo.F_ProductBasePriceListExtendedOrdersEAN	nPartYearMonth	1	dbo.VIEW_F_ProductBasePrice_Period
dbo.VIEW_F_SellOutFull	dbo.F_SellOutFull	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOut	dbo.F_SellOut	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutFull_SFIS	dbo.F_SellOutFull_SFIS	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_IMS_WHS	dbo.F_IMS_WHS	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_IMS_SellOutBonus	dbo.F_IMS_SellOutBonus	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_Plan_IMS	dbo.F_Plan_IMS	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth3
dbo.VIEW_F_Plan_ACB	dbo.F_Plan_ACB	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth3
dbo.VIEW_F_POS_ACB	dbo.F_POS_ACB	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutFull_TDP	dbo.F_SellOutFull_TDP	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_Plan_TDP	dbo.F_Plan_TDP	nPartYearMonth	1
dbo.VIEW_F_SellOutOrders	dbo.F_SellOutOrders	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutCalls	dbo.F_SellOutCalls	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutWorkdays	dbo.F_SellOutWorkdays	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutRoute	dbo.F_SellOutRoute	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutCallsProductive	dbo.F_SellOutCallsProductive	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_POSMBudget	dbo.F_POSMBudget	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth2
dbo.VIEW_F_Plan_IMS_SellOut	dbo.F_Plan_IMS_SellOut	nPartYearMonth	1
dbo.VIEW_F_Plan_IMS_SellOut_SalesForce	dbo.F_Plan_IMS_SellOut_SalesForce	nPartYearMonth	1
dbo.VIEW_F_Plan_TargetTOPLKA	dbo.F_Plan_TargetTOPLKA	nPartYearMonth	1
dbo.VIEW_F_SellOutPosInRoute	dbo.F_SellOutPosInRoute	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth3
dbo.VIEW_F_ProductBasePriceHistoryFreezeEAN	dbo.F_ProductBasePriceHistoryFreezeEAN	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutListing	dbo.F_SellOutListing	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_ProductBasePriceHistoryBrandGroup	dbo.F_ProductBasePriceHistoryBrandGroup	nPartYearMonth	1
dbo.VIEW_F_Plan_SalesForce_POSChain	dbo.F_Plan_SalesForce_POSChain	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_Forecast_IMS_Subchannel	dbo.F_Forecast_IMS_Subchannel	nPartYearMonth	1	dbo.VIEW_F_Forecast_IMS_Subchannel
dbo.VIEW_F_SellOutBonusPercent	dbo.F_SellOutBonusPercent	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth2
dbo.VIEW_F_Plan_TDP_ChainResponsible	dbo.F_Plan_TDP_ChainResponsible	nPartYearMonth	1
dbo.VIEW_F_SellOutMarkUpTCActual	dbo.F_SellOutMarkUpTCActual	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth2
dbo.VIEW_F_POSChain_History	dbo.F_POSChain_History	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutReal	dbo.F_SellOutReal	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutFull_CHIPITA	dbo.F_SellOutFull_CHIPITA	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOut_CHIPITA	dbo.F_SellOut_CHIPITA	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutOutletsActivity	dbo.F_SellOutOutletsActivity	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOut_CustomerIn	dbo.F_SellOut_CustomerIn	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_IMS_KPIResult	dbo.F_IMS_KPIResult	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth2
dbo.VIEW_F_IMS_KPIScale_Prepare	dbo.F_IMS_KPIScale_Prepare	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth2
dbo.VIEW_F_IMS_KPIScale	dbo.F_IMS_KPIScale	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth2
dbo.VIEW_F_IMS_KPIException	dbo.F_IMS_KPIException	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth2
dbo.VIEW_F_IMS_KPIBonus	dbo.F_IMS_KPIBonus	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth2

9. Process Fact tables 1¶

Тип: Sequence Container
Описание: Дополнительная обработка таблиц фактов (отключено - Disabled="True")
Цикличность: Foreach Loop Container (Enumerate Fact tables 1)

9.1 Load data to Fact table¶

Тип: Execute SQL Task
Процедура: etl.usp_LoadFactPart
Параметры мэппинга данных: (аналогично шагу 8.1)

Используемые объекты:

FACT_SourceViewName	FACT_FactTableName	FACT_PartColumnDate	FACT_Mode	FACT_FactPartTableName
dbo.VIEW_F_SellOutWorkdays	dbo.F_SellOutWorkdays	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutRoute	dbo.F_SellOutRoute	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutCallsProductive	dbo.F_SellOutCallsProductive	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_POSMBudget	dbo.F_POSMBudget	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth2
dbo.VIEW_F_Plan_IMS_SellOut	dbo.F_Plan_IMS_SellOut	nPartYearMonth	1
dbo.VIEW_F_Plan_IMS_SellOut_SalesForce	dbo.F_Plan_IMS_SellOut_SalesForce	nPartYearMonth	1
dbo.VIEW_F_Plan_TargetTOPLKA	dbo.F_Plan_TargetTOPLKA	nPartYearMonth	1
dbo.VIEW_F_SellOutPosInRoute	dbo.F_SellOutPosInRoute	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth3
dbo.VIEW_F_ProductBasePriceHistoryFreezeEAN	dbo.F_ProductBasePriceHistoryFreezeEAN	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutListing	dbo.F_SellOutListing	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_ProductBasePriceHistoryBrandGroup	dbo.F_ProductBasePriceHistoryBrandGroup	nPartYearMonth	1
dbo.VIEW_F_Plan_SalesForce_POSChain	dbo.F_Plan_SalesForce_POSChain	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_Forecast_IMS_Subchannel	dbo.F_Forecast_IMS_Subchannel	nPartYearMonth	1	dbo.VIEW_F_Forecast_IMS_Subchannel
dbo.VIEW_F_SellOutBonusPercent	dbo.F_SellOutBonusPercent	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth2
dbo.VIEW_F_Plan_TDP_ChainResponsible	dbo.F_Plan_TDP_ChainResponsible	nPartYearMonth	1
dbo.VIEW_F_SellOutMarkUpTCActual	dbo.F_SellOutMarkUpTCActual	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth2
dbo.VIEW_F_POSChain_History	dbo.F_POSChain_History	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutReal	dbo.F_SellOutReal	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOutFull_CHIPITA	dbo.F_SellOutFull_CHIPITA	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SellOut_CHIPITA	dbo.F_SellOut_CHIPITA	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1

10. Process Fact tables 2¶

Тип: Sequence Container
Описание: Дополнительная обработка таблиц фактов (отключено - Disabled="True")
Цикличность: Foreach Loop Container (Enumerate Fact tables 1)

10.1 Load data to Fact table¶

Тип: Execute SQL Task
Процедура: etl.usp_LoadFactPart
Параметры мэппинга данных: (аналогично шагу 8.1)

Используемые объекты:

FACT_SourceViewName	FACT_FactTableName	FACT_PartColumnDate	FACT_Mode	FACT_FactPartTableName
dbo.VIEW_F_SellOutOutletsActivity	dbo.F_SellOutOutletsActivity	nPartYearMonth	1	dbo.VIEW_F_SellOut_PeriodMonth1

11. Process Historical tables¶

Тип: Sequence Container
Описание: Обработка исторических таблиц
Цикличность: Foreach Loop Container (Enumerate Historical tables)

11.1 Load data to Historical table¶

Тип: Execute SQL Task
Процедура: etl.usp_LoadHistoryPart
Параметры мэппинга данных:
@Batch → User::Batch
@ViewNameHistory → User::ViewNameHistory
@TableNameHistory → User::TableNameHistory
@WhereColumns → User::WhereColumns
@HorizonLoadData → User::HorizonLoadData
@PartTableNameHistory → User::PartTableNameHistory

Используемые объекты:

ViewNameHistory	TableNameHistory	PartTableNameHistory
dbo.VIEW_H_SellOut_CHGO	dbo.H_SellOut_CHGO	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutReturn_CHGO	dbo.H_SellOutReturn_CHGO	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutFull_CHGO	dbo.H_SellOutFull_CHGO	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutReturnFull_CHGO	dbo.H_SellOutReturnFull_CHGO	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutFull_SFIS	dbo.H_SellOutFull_SFIS	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutOrders_CHGO	dbo.H_SellOutOrders_CHGO	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutCalls_CHGO	dbo.H_SellOutCalls_CHGO	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutRoute_CHGO	dbo.H_SellOutRoute_CHGO	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutOutletsActivity_CHGO	dbo.H_SellOutOutletsActivity_CHGO	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutWorkdays_CHGO	dbo.H_SellOutWorkdays_CHGO	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutReal_CHGO	dbo.H_SellOutReal_CHGO	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutReturnReal_CHGO	dbo.H_SellOutReturnReal_CHGO	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_ASSA_vw_NKA_MergeDocs_forOLAP	dbo.H_ASSA_vw_NKA_MergeDocs_forOLAP	dbo.VIEW_H_SellOut_PeriodMonth1
dbo.VIEW_H_SellOutDocuments	dbo.H_SellOutDocuments	dbo.VIEW_H_SellOut_PeriodMonth1

12. Process History Tables¶

Тип: Sequence Container
Описание: Обработка исторических таблиц (дополнительно)

12.1 DailySCD¶

Тип: Foreach Loop Container
Описание: Загрузка данных Daily SCD
Цикличность: Foreach Loop Container

Подшаг: Execute usp_Load_Other_DIM

Тип: Execute SQL Task
Процедура: etl.usp_LoadOtherDIM
Параметры мэппинга данных: (аналогично шагу 2.2)

Используемые объекты:

DIM_SourceViewName	DIM_DimensionTableName	DIM_MergeOnColumns	DIM_Mode
dbo.VIEW_D_Employee_HistoryDaily	dbo.D_Employee_HistoryDaily	UID, HashDailySCD, DateFrom	2
dbo.VIEW_D_SalesForce_HistoryDaily	dbo.D_SalesForce_HistoryDaily	UID, HashDailySCD, DateFrom	2
dbo.VIEW_D_Route_HistoryDaily	dbo.D_Route_HistoryDaily	UID, HashDailySCD, DateFrom	2
dbo.VIEW_R_SalesForce_Employee_HistoryDaily	dbo.R_SalesForce_Employee_HistoryDaily	ID_SalesForce_HistoryDaily, ID_Employee_HistoryDaily, DateFrom	1
dbo.VIEW_D_ChicagoSettingsUpdateTimestamp_HistoryDaily	dbo.D_ChicagoSettingsUpdateTimestamp_HistoryDaily	ID_Date, LastDataUpdateTimestamp	2

12.2 Enum History Other Dims¶

Тип: Foreach Loop Container
Описание: Загрузка других исторических измерений
Цикличность: Foreach Loop Container

Подшаг: Execute usp_Load_Other_DIM

Тип: Execute SQL Task
Процедура: etl.usp_LoadOtherDIM
Параметры мэппинга данных: (аналогично шагу 2.2)

Используемые объекты:

DIM_SourceViewName	DIM_DimensionTableName	DIM_MergeOnColumns	DIM_Mode
dbo.VIEW_D_SalesForce_History	dbo.D_SalesForce_History	HashSalesForceHistory	2

12.3 Enumerate history Fact tables¶

Тип: Foreach Loop Container
Описание: Загрузка исторических фактов
Цикличность: Foreach Loop Container

Подшаг: Load data to Fact table

Тип: Execute SQL Task
Процедура: etl.usp_LoadFactPart
Параметры мэппинга данных: (аналогично шагу 8.1)

Используемые объекты:

FACT_SourceViewName	FACT_FactTableName	FACT_PartColumnDate	FACT_ExcludedColumns	FACT_Mode	FACT_FactPartTableName
dbo.VIEW_F_POS_History	dbo.F_POS_History	nPartYearMonth		1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_POS_RDC_History	dbo.F_POS_RDC_History	nPartYearMonth		1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SalesForce_History	dbo.F_SalesForce_History	nPartYearMonth	-ID	1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_POS_SalesForce_History	dbo.F_POS_SalesForce_History	nPartYearMonth		1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SalesForce_POSChain_History	dbo.F_SalesForce_POSChain_History	nPartYearMonth		1	dbo.VIEW_F_SellOut_PeriodMonth1
dbo.VIEW_F_SalesForce_Responsible_History	dbo.F_SalesForce_Responsible_History	nPartYearMonth		1	dbo.udtf_getMonthPeriod(1, null, null)
dbo.VIEW_F_POS_CustomerInHierarchy_History	dbo.F_POS_CustomerInHierarchy_History	nPartYearMonth		1	dbo.udtf_getMonthPeriod(1, null, null)

13. RemoveDuplicates CHGO OutletsDim¶

Тип: Sequence Container
Описание: Удаление дубликатов в OutletsDim

13.1 RemoveDuplicates_CHGO_OutletsDim¶

Тип: Execute SQL Task
Процедура: etl.usp_RemoveDuplicates_CHGO_OutletsDim
Параметры мэппинга данных:
@Batch → User::Batch

14. Update Dimension tables by rule statement¶

Тип: Sequence Container
Описание: Обновление таблиц измерений по правилам

14.1 Update dbo_D_SalesForce¶

Тип: Execute SQL Task
Процедура: etl.usp_Update_D_SalesForce
Параметры мэппинга данных:
@Batch → User::Batch

15. Lock Monitor¶

Тип: Execute SQL Task
Описание: Мониторинг блокировок

16. Finish batch identifier¶

Тип: Execute SQL Task
Описание: Завершение выполнения ETL-процесса

Список задействованных объектов¶

1. Базы данных¶

DWH: KRAFT_DWH_CSL

2. Соединение с другими пакетами¶

DWH_ETL_SA.dtsx (отключено)
DWH_MDT.dtsx
DWH_MDT_SellOut.dtsx
DWH_ETL_SellOutReal.dtsx
DWH_ETL_MSL_Calculation.dtsx (отключено)

3. Источники данных¶

Source4: STAR_Production (KRAFT_STAR_CSL_LINK)
Source6: SOPtool (KRAFT_SOPtool_CSL_LINK)
Source13: ASSA_SUPP (KRAFT_ASSA_SUPP_CSL_LINK)
Source14: chicago_kraft_wh (KRAFT_CHGO_CSL_LINK)
Source15: Chicago_Vitrina (KRAFT_CHGO_CSL_LINK)
Source17: chicago_kraft_wh (KRAFT_CHGO_CSL_LINK)
Source18: chicago_kraft_wh (KRAFT_CHGO_CSL_LINK)

4. Внешние параметры¶

Запуск осуществляется с параметрами конфигурации внутри пакета, через переменные:

DWH_DB_Name = KRAFT_DWH_CSL
DWH_Link_Name = KRAFT_DWH_CSL_LINK
DWH_LoginName = DWHSQLUser
DWH_LoginPwd = ***
DWH_ServerName = arrrumosapp17