Название пакета: DWH_ETL_SA¶
Описание¶
Загрузка данных из источников в слой Stage Area (SA) в хранилище KRAFT_DWH_CSL
Схема¶
Список шагов¶
1. Load data to StageArea¶
- Тип: Sequence Container
- Описание: Основной контейнер для загрузки данных из источников в Stage Area
1.1 Source ASSA to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника ASSA_SUPP
- Цикличность: 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 | Source13_TableIncludedColumns | Source13_TableExcludedColumns | Source13_TableDistinct | Source13_TableWhere |
|---|---|---|---|---|---|
| dbo.tmp_Standard_TPR | sa.SA_ASSA_tmp_Standard_TPR | 0 |
1.2 Source BCP to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника BCP
- Цикличность: Foreach Loop Container
Подшаг: Load Source 15 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source15_Link_Name
- DB_Name → User::Source15_DB_Name
- TableNameSource → User::Source15_TableNameSource
- TableNameDestination → User::Source15_TableNameDestination
- TableIncludedColumns → User::Source15_TableIncludedColumns
- TableExcludedColumns → User::Source15_TableExcludedColumns
- TableDistinct → User::Source15_TableDistinct
- TableWhere → User::Source15_TableWhere
Используемые объекты:
| Source15_TableNameSource | Source15_TableNameDestination | Source15_TableIncludedColumns | Source15_TableExcludedColumns | Source15_TableDistinct | Source15_TableWhere |
|---|---|---|---|---|---|
| dbo.Movement_reports_tbl | sa.SA_BCP_dbo_Movement_reports_tbl | loaddate | 0 |
1.3 Source Complaints_SAP to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника Complaints_SAP
- Цикличность: Foreach Loop Container
Подшаг: Load Source 12 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source12_Link_Name
- DB_Name → User::Source12_DB_Name
- TableNameSource → User::Source12_TableNameSource
- TableNameDestination → User::Source12_TableNameDestination
- TableIncludedColumns → User::Source12_TableIncludedColumns
- TableExcludedColumns → User::Source12_TableExcludedColumns
- TableDistinct → User::Source12_TableDistinct
- TableWhere → User::Source12_TableWhere
Используемые объекты:
| Source12_TableNameSource | Source12_TableNameDestination | Source12_TableIncludedColumns | Source12_TableExcludedColumns | Source12_TableDistinct | Source12_TableWhere |
|---|---|---|---|---|---|
| dbo.vw_Cust_days_payment_both_companies | sa.SA_CMP_Cust_days_payment | 0 | |||
| dbo.ars_note_hdr | sa.SA_COMPLAINTS_ars_note_hdr | 0 |
1.4 Source Controlling to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника Controlling
- Цикличность: Foreach Loop Container
Подшаг: Load Source 10 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source10_Link_Name
- DB_Name → User::Source10_DB_Name
- TableNameSource → User::Source10_TableNameSource
- TableNameDestination → User::Source10_TableNameDestination
- TableIncludedColumns → User::Source10_TableIncludedColumns
- TableExcludedColumns → User::Source10_TableExcludedColumns
- TableDistinct → User::Source10_TableDistinct
- TableWhere → User::Source10_TableWhere
Используемые объекты:
| Source10_TableNameSource | Source10_TableNameDestination | Source10_TableIncludedColumns | Source10_TableExcludedColumns | Source10_TableDistinct | Source10_TableWhere |
|---|---|---|---|---|---|
| dbo.vw_Transfer_sap_for_OLAP_load | sa.SA_Controlling_vw_Transfer_sap_for_OLAP_load | Mroute_num, Ord_num, Type_tr, Count_plls, Cost_category, [Actual Pallet Places], Transfer_pallet | 0 | Ord_num like '7%' or Ord_num like '45%' |
1.5 Source Deployment Tool to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника Deployment Tool
- Цикличность: Foreach Loop Container
Подшаг: Load Source 11 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source11_Link_Name
- DB_Name → User::Source11_DB_Name
- TableNameSource → User::Source11_TableNameSource
- TableNameDestination → User::Source11_TableNameDestination
- TableIncludedColumns → User::Source11_TableIncludedColumns
- TableExcludedColumns → User::Source11_TableExcludedColumns
- TableDistinct → User::Source11_TableDistinct
- TableWhere → User::Source11_TableWhere
Используемые объекты:
| Source11_TableNameSource | Source11_TableNameDestination | Source11_TableIncludedColumns | Source11_TableExcludedColumns | Source11_TableDistinct | Source11_TableWhere |
|---|---|---|---|---|---|
| dbo.[vw_DT.CS&L.Header_both_companies] | sa.SA_DT_DTCSLHeader | 0 | |||
| dbo.[vw_DT.CS&L.Detail_both_companies] | sa.SA_DT_DTCSLDetail | 0 |
1.6 Source MDDB to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника MDDB
- Цикличность: Foreach Loop Container
Подшаг: Load Source 3 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source3_Link_Name
- DB_Name → User::Source3_DB_Name
- TableNameSource → User::Source3_TableNameSource
- TableNameDestination → User::Source3_TableNameDestination
- TableIncludedColumns → User::Source3_TableIncludedColumns
- TableExcludedColumns → User::Source3_TableExcludedColumns
- TableDistinct → User::Source3_TableDistinct
- isActive → User::Source3_isActive
Используемые объекты:
| Source3_TableNameSource | Source3_TableNameDestination | Source3_TableIncludedColumns | Source3_TableExcludedColumns | Source3_TableDistinct | Source3_isActive |
|---|---|---|---|---|---|
| dbo.mfg_ds_item_masterdata | sa.SA_MDDB_mfg_ds_item_masterdata | 0 | -1 | ||
| dbo.mfg_ds_customers_masterdata_new | sa.SA_MDDB_mfg_ds_customers_masterdata_new | 0 | 0 | ||
| dbo.mfg_ds_warehouses_masterdata | sa.SA_MDDB_mfg_ds_warehouses_masterdata | 0 | -1 | ||
| dbo.DatesKFI | sa.SA_MDDB_DatesKFI | 0 | -1 | ||
| dbo.MBP | sa.SA_MDDB_MBP | 0 | -1 | ||
| dbo.MBP_ext | sa.SA_MDDB_MBP_ext | 0 | -1 | ||
| dbo.EAN2KIC7 | sa.SA_MDDB_EAN2KIC7 | 0 | -1 | ||
| dbo.vw_chicago_MSL_and_Listing | sa.SA_MDDB_vw_chicago_MSL_and_Listing | 0 | -1 |
1.7 Source MRouter to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника MRouter
- Цикличность: Foreach Loop Container
Подшаг: Load Source 7 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source7_Link_Name
- DB_Name → User::Source7_DB_Name
- TableNameSource → User::Source7_TableNameSource
- TableNameDestination → User::Source7_TableNameDestination
- TableIncludedColumns → User::Source7_TableIncludedColumns
- TableExcludedColumns → User::Source7_TableExcludedColumns
- TableDistinct → User::Source7_TableDistinct
Используемые объекты:
| Source7_TableNameSource | Source7_TableNameDestination | Source7_TableIncludedColumns | Source7_TableExcludedColumns | Source7_TableDistinct |
|---|---|---|---|---|
| dbo.MT_links | sa.SA_MRouter_MT_links | ord_num, OTM, full_invoice, deliv_date | 0 | |
| dbo.vw_MT_Links_SAP_both_companies | sa.SA_MRouter_MT_Links_SAP | ord_num, OTM, full_invoice, deliv_date, act_ship_time, mroute_num | 0 |
1.8 Source OPDB to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника OPDB
- Цикличность: Foreach Loop Container
Подшаг: Load Source 2 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source2_Link_Name
- DB_Name → User::Source2_DB_Name
- TableNameSource → User::Source2_TableNameSource
- TableNameDestination → User::Source2_TableNameDestination
- TableIncludedColumns → User::Source2_TableIncludedColumns
- TableExcludedColumns → User::Source2_TableExcludedColumns
- TableDistinct → User::Source2_TableDistinct
- TableWhere → User::Source2_TableWhere
Используемые объекты:
| Source2_TableNameSource | Source2_TableNameDestination | Source2_TableIncludedColumns | Source2_TableExcludedColumns | Source2_TableDistinct | Source2_TableWhere |
|---|---|---|---|---|---|
| dbo.vw_History_sales_frct | sa.SA_OPDB_History_sales_frct | 0 | year >= 2017 | ||
| dbo.vw_History_score_frct | sa.SA_OPDB_History_score_frct | 0 | date_load >= convert(int, convert(varchar(6), dateadd(yyyy, -1, getdate()), 112)) | ||
| dbo.History_IMS_frct | sa.SA_OPDB_History_IMS_frct | 0 | Cycle = 'SICalc' | ||
| dbo.vw_CLOSING_STOCK_CHICAGO_forDWH | sa.SA_OPDB_CLOSING_STOCK_CHICAGO | 0 | [Date] >= dateadd(m, -3, getdate()) | ||
| dbo.DistrRef_CHICAGO | sa.SA_OPDB_DistrRef_CHICAGO | 0 | |||
| dbo.vw_IMS_CHICAGO_forDWH | sa.SA_OPDB_IMS_CHICAGO | 0 | [Date] >= dateadd(m, -3, getdate()) | ||
| dbo.vw_SALES_IN_CHICAGO_forDWH | sa.SA_OPDB_SALES_IN_CHICAGO | 0 | [Date] >= dateadd(m, -3, getdate()) | ||
| dbo.Reports_Warehouses | sa.SA_OPDB_Reports_Warehouses | Delivery_NoDoc | 0 | cast(convert(char(6),Report_Date,112) as int) >= cast(convert(varchar(6),(dateadd(m,-1,getdate())),112) as int) | |
| dbo.SAFIR_MasterData_history | sa.SA_OPDB_SAFIR_MasterData_history | ID_ProductMD, PriceUnit | 0 | ||
| dbo.MDR_Safir_data | sa.SA_OPDB_MDR_Safir_data | 0 | |||
| dbo.DatesKFI_by_Year | sa.SA_OPDB_DatesKFI_by_Year | 0 | |||
| dbo.WarehouseInbound | sa.SA_OPDB_WarehouseInbound | ID | 0 | ||
| dbo.WarehouseOutbound | sa.SA_OPDB_WarehouseOutbound | ID | 0 | ||
| dbo.History_IMS_Frct_lag2 | sa.SA_OPDB_History_IMS_Frct_lag2 | 0 |
1.9 Source OTM to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника OTM
- Цикличность: Foreach Loop Container
Подшаг: Load Source 16 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source16_Link_Name
- DB_Name → User::Source16_DB_Name
- TableNameSource → User::Source16_TableNameSource
- TableNameDestination → User::Source16_TableNameDestination
- TableIncludedColumns → User::Source16_TableIncludedColumns
- TableExcludedColumns → User::Source16_TableExcludedColumns
- TableDistinct → User::Source16_TableDistinct
- TableWhere → User::Source16_TableWhere
Используемые объекты:
| Source16_TableNameSource | Source16_TableNameDestination | Source16_TableIncludedColumns | Source16_TableExcludedColumns | Source16_TableDistinct | Source16_TableWhere |
|---|---|---|---|---|---|
| dbo.ORDER_RELEASE | sa.SA_OTM_ORDER_RELEASE | 0 | |||
| dbo.ORDER_RELEASE_REFNUM | sa.SA_OTM_ORDER_RELEASE_REFNUM | 0 | |||
| dbo.SHIPMENT | sa.SA_OTM_SHIPMENT | 0 | |||
| dbo.SHIPMENT_REFNUM | sa.SA_OTM_SHIPMENT_REFNUM | 0 | |||
| dbo.SHIPMENT_STATUS | sa.SA_OTM_SHIPMENT_STATUS | 0 | insert_date >= dateadd(m, -24, getdate()) | ||
| dbo.SHIPMENT_S_EQUIPMENT_JOIN | sa.SA_OTM_SHIPMENT_S_EQUIPMENT_JOIN | s_equipment_gid, shipment_gid | 0 | ||
| dbo.S_EQUIPMENT_S_SHIP_UNIT_JOIN | sa.SA_OTM_S_EQUIPMENT_S_SHIP_UNIT_JOIN | s_ship_unit_gid, s_equipment_gid | 0 | ||
| dbo.S_SHIP_UNIT_LINE | sa.SA_OTM_S_SHIP_UNIT_LINE | order_release_gid, s_ship_unit_gid | 0 | insert_date >= dateadd(m, -24, getdate()) | |
| dbo.ALLOCATION | sa.SA_OTM_ALLOCATION | shipment_gid, private_cost, private_cost_currency_gid, private_cost_base, base_cost, base_cost_currency_gid, base_cost_base, total_alloc_cost, total_cost_currency_gid, order_release_gid | 0 |
1.10 Source SAFIR factInvoiceConditions to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных инвойсов из источника SAFIR
- Цикличность: Foreach Loop Container
Подшаг: Load Source 17 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source17_Link_Name
- DB_Name → User::Source17_DB_Name
- TableNameSource → User::Source17_TableNameSource
- TableNameDestination → User::Source17_TableNameDestination
- TableIncludedColumns → User::Source17_TableIncludedColumns
- TableExcludedColumns → User::Source17_TableExcludedColumns
- TableDistinct → User::Source17_TableDistinct
- TableWhere → User::Source17_TableWhere
Используемые объекты:
| Source17_TableNameSource | Source17_TableNameDestination | Source17_TableIncludedColumns | Source17_TableExcludedColumns | Source17_TableDistinct | Source17_TableWhere |
|---|---|---|---|---|---|
| dbo.factInvoiceConditions | sa.SA_SAFIR_factInvoiceConditions | hash, rowguid, nPartYearMonth | 0 | npartyearmonth>= cast(convert(char(6), dateadd(m, -25, getdate()), 112) as int) |
1.11 Source SAFIR factOrdersIn to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных заказов из источника SAFIR
- Цикличность: Foreach Loop Container
Подшаг: Load Source 17 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source17_Link_Name
- DB_Name → User::Source17_DB_Name
- TableNameSource → User::Source17_TableNameSource
- TableNameDestination → User::Source17_TableNameDestination
- TableIncludedColumns → User::Source17_TableIncludedColumns
- TableExcludedColumns → User::Source17_TableExcludedColumns
- TableDistinct → User::Source17_TableDistinct
- TableWhere → User::Source17_TableWhere
Используемые объекты:
| Source17_TableNameSource | Source17_TableNameDestination | Source17_TableIncludedColumns | Source17_TableExcludedColumns | Source17_TableDistinct | Source17_TableWhere |
|---|---|---|---|---|---|
| dbo.factOrdersIn | sa.SA_SAFIR_factOrdersIn | 0 | npartyearmonth>= cast(convert(char(6), dateadd(m, -25, getdate()), 112) as int) |
1.12 Source SAFIR to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника SAFIR (измерения и факты)
- Цикличность: Foreach Loop Container
Подшаг: Load Source 1 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source1_Link_Name
- DB_Name → User::Source1_DB_Name
- TableNameSource → User::Source1_TableNameSource
- TableNameDestination → User::Source1_TableNameDestination
- TableIncludedColumns → User::Source1_TableIncludedColumns
- TableExcludedColumns → User::Source1_TableExcludedColumns
- TableDistinct → User::Source1_TableDistinct
- TableWhere → User::Source1_TableWhere
Используемые объекты:
| Source1_TableNameSource | Source1_TableNameDestination | Source1_TableIncludedColumns | Source1_TableExcludedColumns | Source1_TableDistinct | Source1_TableWhere |
|---|---|---|---|---|---|
| dbo.dimCustomers | sa.SA_SAFIR_dimCustomers | 0 | |||
| dbo.dimCustomerSalesData | sa.SA_SAFIR_dimCustomerSalesData | 0 | not exists (select * from sa.VIEW_SA_SAFIR_dimCustomerSalesData_Exception as fe where f.CustomerID = fe.CustomerID and f.SalesOrganizationID = fe.SalesOrganizationID and f.DistributionChannelID = fe.DistributionChannelID and f.DivisionID = fe.DivisionID) | ||
| dbo.dimCustomerPartners | sa.SA_SAFIR_dimCustomerPartners | 0 | |||
| DataST.dimBusinessPartnerFunctions | sa.SA_SAFIR_dimBusinessPartnerFunctions | 0 | |||
| DataST.dimDistributionChannels | sa.SA_SAFIR_dimDistributionChannels | 0 | |||
| DataST.dimDivisions | sa.SA_SAFIR_dimDivisions | 0 | |||
| DataST.dimSalesOrganizations | sa.SA_SAFIR_dimSalesOrganizations | 0 | |||
| dbo.dimProducts | sa.SA_SAFIR_dimProducts | 0 | |||
| DataST.dimProductTypes | sa.SA_SAFIR_dimProductTypes | 0 | |||
| dbo.factUomRates | sa.SA_SAFIR_factUomRates | 0 | |||
| dbo.dimUnitsOfMeasure | sa.SA_SAFIR_dimUnitsOfMeasure | 0 | |||
| dbo.factDeliveries | sa.SA_SAFIR_factDeliveries | 0 | npartyearmonth>= cast(convert(char(6), dateadd(m, -25, getdate()), 112) as int) | ||
| dbo.factSellIn | sa.SA_SAFIR_factSellIn | 0 | npartyearmonth>= cast(convert(char(6), dateadd(m, -25, getdate()), 112) as int) | ||
| DataST.dimPlants | sa.SA_SAFIR_dimPlants | 0 | |||
| dbo.dimKraftLocations | sa.SA_SAFIR_dimKraftLocations | 0 | |||
| dbo.dimProductSalesData | sa.SA_SAFIR_dimProductSalesData | 0 | |||
| DataST.dimStorageConditions | sa.SA_SAFIR_dimStorageConditions | 0 | |||
| DataST.dimSalesDocTypes | sa.SA_SAFIR_dimSalesDocTypes | 0 | |||
| DataST.dimSalesOrderItemReasons | sa.SA_SAFIR_dimSalesOrderItemReasons | 0 | |||
| DataST.dimSalesOrderReasons | sa.SA_SAFIR_dimSalesOrderReasons | 0 | |||
| dbo.dimCurrencies | sa.SA_SAFIR_dimCurrencies | 0 | |||
| DataST.dimTemperatureConditions | sa.SA_SAFIR_dimTemperatureConditions | 0 | |||
| DataST.dimCountries | sa.SA_SAFIR_dimCountries | 0 | |||
| DataST.dimTransportZones | sa.SA_SAFIR_dimTransportZones | 0 | |||
| dbo.factExchangeRates | sa.SA_SAFIR_factExchangeRates | 0 | |||
| dbo.factInvoiceWorkflow | sa.SA_SAFIR_factInvoiceWorkflow | 0 | CreateDateID >cast(convert(char(8), dateadd(m, -26, eomonth(getdate())), 112) as int) | ||
| dbo.dimPriceDefinitions | sa.SA_SAFIR_dimPriceDefinitions | 0 | ConditionType = 'MWST' | ||
| DataST.dimProductGroup3 | sa.SA_SAFIR_dimProductGroup3 | 0 | |||
| dbo.dimProductPlantData | sa.SA_SAFIR_dimProductPlantData | 0 | |||
| DataST.dimProductPlantStatuses | sa.SA_SAFIR_dimProductPlantStatuses | 0 | |||
| dbo.dimCustomerAddresses | sa.SA_SAFIR_dimCustomerAddresses | 0 | |||
| DataST.dimSalesDocBlockingReasons | sa.SA_SAFIR_dimSalesDocBlockingReasons | 0 | |||
| dbo.factKraftStocks | sa.SA_SAFIR_factKraftStocks | 0 | round(StockDateID/100,0,1) >= cast(convert(varchar(6),(dateadd(m,-1,getdate())),112) as int) | ||
| dbo.factPurchaseOrders | sa.SA_SAFIR_factPurchaseOrders | 0 | PurchaseOrderType in ('ZUB', 'ZBN', 'ZPOC') and PurchasingDocDateID > cast(convert(char(8), dateadd(m, -26, eomonth(getdate())), 112) as int) | ||
| dbo.factPurchaseOrderHistory | sa.SA_SAFIR_factPurchaseOrderHistory | 0 | PurchaseOrderType in ('ZUB', 'ZBN', 'ZPOC') and OrderPostingDateID > cast(convert(char(8), dateadd(m, -26, eomonth(getdate())), 112) as int) | ||
| dbo.dimVendors | sa.SA_SAFIR_dimVendors | 0 | |||
| dbo.dimVendorAddressVersions | sa.SA_SAFIR_dimVendorAddressVersions | 0 | |||
| dbo.dimTime | sa.SA_SAFIR_dimTime | 0 | |||
| DataST.dimProductGroup4 | sa.SA_SAFIR_dimProductGroup4 | 0 |
1.13 Source SOPTool to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника SOPTool
- Цикличность: Foreach Loop Container
Подшаг: Load Source 6 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source6_Link_Name
- DB_Name → User::Source6_DB_Name
- TableNameSource → User::Source6_TableNameSource
- TableNameDestination → User::Source6_TableNameDestination
- TableIncludedColumns → User::Source6_TableIncludedColumns
- TableExcludedColumns → User::Source6_TableExcludedColumns
- TableDistinct → User::Source6_TableDistinct
Используемые объекты:
| Source6_TableNameSource | Source6_TableNameDestination | Source6_TableIncludedColumns | Source6_TableExcludedColumns | Source6_TableDistinct |
|---|---|---|---|---|
| dbo.vw_tlt_SAP | sa.SA_SOPTool_vw_tlt_SAP | 0 | ||
| dbo.SA_Target$ | sa.SA_SOPTool_SA_Target | 0 | ||
| dbo.SA_Consolidation$ | sa.SA_SOPTool_SA_Consolidation | 0 | ||
| dbo.SA_Exclusion$ | sa.SA_SOPTool_SA_Exclusion | 0 | ||
| dbo.vw_Outlook_for_OLAP | sa.SA_SOPTool_vw_Outlook_for_OLAP | 0 | ||
| si.tmp_data_for_si | sa.SA_SOPTool_tmp_data_for_si | 0 | ||
| pnl.vw_F_Channel_Markup_for_SellOut_margins | sa.SA_SOPTool_vw_F_Channel_Markup | 0 | ||
| pnl.vw_F_SellOut_Bonus_Percentage | sa.SA_SOPTool_vw_F_SellOut_Bonus_Percentage | 0 | ||
| dsr.vw_F_history_IMS_plan_wo_VAT_MUP_for_Olap | sa.SA_SOPTool_vw_F_history_ims_plan | 0 | ||
| pnl.vw_F_MarkUp_Reestr_for_CUBE | sa.SA_SOPTool_vw_F_MarkUp_Reestr | 0 | ||
| DSR.AC | sa.SA_SOPTool_DSR_AC | 0 | ||
| DSR.SalesPlan | sa.SA_SOPTool_DSR_SalesPlan | ID | 0 | |
| DSR.SalesTarget | sa.SA_SOPTool_DSR_SalesTarget | 0 | ||
| DSR.StretchTarget | sa.SA_SOPTool_DSR_StretchTarget | 0 | ||
| DSR.vw_IMS_Region_Plan_for_OLAP | sa.SA_SOPTool_vw_IMS_Region_Plan_for_OLAP | 0 | ||
| DSR.vw_IMS_Target_TOP_LKA_for_OLAP | sa.SA_SOPTool_vw_IMS_Target_TOP_LKA_for_OLAP | 0 | ||
| bi.vw_MSL_by_POS_Type_OLAP | sa.SA_SOPTool_vw_MSL_by_POS_Type_OLAP | 0 | ||
| md.vw_D_ProductHierarchy_Mapping_For_Olap | sa.SA_SOPTool_vw_D_ProductHierarchy_Mapping_For_Olap | 0 | ||
| dsr.vw_IMS_SalesForce_Plan_wo_VAT_MUP_for_Olap | sa.SA_SOPTool_vw_IMS_SalesForce_Plan_wo_VAT_MUP_for_Olap | 0 | ||
| bi.vw_F_CS_IMS_SI_Comment_for_OLAP | sa.SA_SOPTool_vw_F_CS_IMS_SI_Comment_for_OLAP | 0 | ||
| ppt.vw_Star_Actual_for_Olap | sa.SA_SOPTool_vw_Star_Actual_for_Olap | 0 | ||
| md.vw_D_CustomerIn_CustomerInType_for_OLAP | sa.SA_SOPTool_vw_D_CustomerIn_CustomerInType_for_OLAP | 0 | ||
| pnl.vw_BG_Price_for_OLAP | sa.SA_SOPTool_vw_BG_Price_for_OLAP | 0 | ||
| md.vw_D_Employee_Sellin_Matrix_OLAP | sa.SA_SOPTool_vw_D_Employee_Sellin_Matrix_OLAP | ID | 0 | |
| md.vw_D_Employee_Sellin_Hierarchy_OLAP | sa.SA_SOPTool_vw_D_Employee_Sellin_Hierarchy_OLAP | ID | 0 | |
| si_bg.vw_IMS_FRCST_by_Distr_by_SubCh_by_BBandBG_OLAP | sa.SA_SOPTool_vw_IMS_FRCST_by_Distr_by_SubCh_by_BBandBG_OLAP | 0 | ||
| dbo.vw_md_MUP_TC_load_update_for_Olap | sa.SA_SOPTool_vw_md_MUP_TC_load_update_for_Olap | id | 0 | |
| bi.vw_F_TDP_Scenario | sa.SA_SOPTool_bi_vw_F_TDP_Scenario | 0 | ||
| bi.vw_F_ACB_plans | sa.SA_SOPTool_vw_F_ACB_plans | 0 | ||
| DSR.SO_Seasonal_Plan | sa.SA_SOPTool_DSR_SO_Seasonal_Plan | 0 | ||
| DSR.SO_Seasonal_Plan_by_Customer | sa.SA_SOPTool_DSR_SO_Seasonal_Plan_by_Customer | 0 | ||
| dbo.WOS_SPD_Plan | sa.SA_SOPTool_WOS_SPD_Plan | 0 | ||
| scorecard.vw_F_ACB_Infoline | sa.SA_SOPTool_scorecard_vw_F_ACB_Infoline | 0 | ||
| DSR.SO_Bonus_Fact | sa.SA_SOPTool_DSR_SO_Bonus_Fact | 0 | ||
| DSR.SO_Bonus_Scale | sa.SA_SOPTool_DSR_SO_Bonus_Scale | 0 | ||
| dsr.SO_Bonus_Exception | sa.SA_SOPTool_DSR_SO_Bonus_Exception | 0 | ||
| dbo.ProjectSeasonal | sa.SA_SOPTool_dbo_ProjectSeasonal | 0 | ||
| DSR.SalesPlan_Weekly | sa.SA_SOPTool_DSR_SalesPlan_Weekly | ID | 0 | |
| scorecard.vw_Customer_FC_SFA_LAG | sa.SA_SOPTool_scorecard_vw_Customer_FC_SFA_LAG | 0 |
1.14 Source WHTest to SA DWH¶
- Тип: Foreach Loop Container
- Описание: Загрузка данных из источника WHTest
- Цикличность: Foreach Loop Container
Подшаг: Load Source 8 data for table
- Тип: Execute SQL Task
- Процедура: etl.usp_ImportDataFromSQLServer
- Параметры мэппинга данных:
- Batch → User::Batch
- Link_Name → User::Source8_Link_Name
- DB_Name → User::Source8_DB_Name
- TableNameSource → User::Source8_TableNameSource
- TableNameDestination → User::Source8_TableNameDestination
- TableIncludedColumns → User::Source8_TableIncludedColumns
- TableExcludedColumns → User::Source8_TableExcludedColumns
- TableDistinct → User::Source8_TableDistinct
- TableWhere → User::Source8_TableWhere
Используемые объекты:
| Source8_TableNameSource | Source8_TableNameDestination | Source8_TableIncludedColumns | Source8_TableExcludedColumns | Source8_TableDistinct | Source8_TableWhere |
|---|---|---|---|---|---|
| dbo.DP_Budget_data | sa.SA_WHTest_DP_Budget_data | 0 | (Scenario like 'DP%' or Scenario like 'SOP%' or Scenario like 'DR%') and len(Scenario) < 10 and measure like 'IMS' | ||
| dbo.DP_Score_SKU | sa.SA_WHTest_DP_Score_SKU | 0 | |||
| dbo.DP_Budget_data | sa.SA_WHTest_DP_Budget_data13 | 0 | (Scenario like 'Consensus%' or Scenario like 'MBR%') and measure like 'IMS' and Scenario not like '%ACT%' | ||
| dbo.DP_Budget_data | sa.SA_WHTest_DP_Budget_data_SellIn | 0 | (Scenario like 'SOP%') and len(Scenario) < 10 and measure like 'SalesIn' | ||
| dbo.DP_Budget_data | sa.SA_WHTest_DP_Budget_data_SellIn13 | 0 | (Scenario like 'MBR%' or Scenario like 'DR%') and measure like 'SalesIn' and Scenario not like '%ACT%' | ||
| dbo.vw_historical_base_price_list_ZP0X | sa.SA_WHTest_vw_historical_base_price_list_ZP0X | 0 | |||
| dbo.vw_historical_VAT_MWST | sa.SA_WHTest_vw_historical_VAT_MWST | 0 | |||
| dbo.vw_cs_CFR_Reason_for_Olap | sa.SA_WHTest_vw_cs_CFR_Reason_for_Olap | 0 | |||
| dbo.rrh_sl_NetworkPosCodeIdentifier | sa.SA_WHTest_rrh_sl_NetworkPosCodeIdentifier | 0 | |||
| dbo.rrh_sl_TDS_SR_connect | sa.SA_WHTest_rrh_sl_TDS_SR_connect | 0 | |||
| dbo.SAP_TransportationCompanies | sa.SA_WHTest_SAP_TransportationCompanies | ID | 0 | ||
| dbo.migration_chipita_si | sa.sa_CHIPITA_SellIn | 0 | |||
| dbo.migration_chipita_chain_chain | sa.sa_CHIPITA_POSChainRule | 0 | |||
| dbo.migration_chipita_chain_billto | sa.sa_CHIPITA_POSChainBillto | 0 | |||
| dbo.vw_Quota_for_OLAP | sa.SA_WHTest_vw_Quota_for_OLAP | 0 | |||
| dbo.F_Logistic_Inbound_Rates | sa.SA_Whtest_F_Logistic_Inbound_Rates | ID | 0 |
2. Update Safir Documents¶
- Тип: Execute SQL Task
- Описание: Обновление документов SAFIR
- SQL Script: Выполняет обновление данных в таблицах:
- sa.SA_SAFIR_factSellIn
- sa.SA_SAFIR_factDeliveries
- sa.SA_SAFIR_factOrdersIn
Список задействованных объектов¶
1. Базы данных¶
- DWH: KRAFT_DWH_CSL
2. Источники данных¶
- Source1: Safir_RU (KRAFT_DWHSafir_CSL_LINK)
- Source2: OPDB (KRAFT_OPDB_CSL_LINK)
- Source3: MDDB (KRAFT_MDDB_CSL_LINK)
- Source6: SOPtool (KRAFT_SOPtool_CSL_LINK)
- Source7: MRouter (KRAFT_MRouter_CSL_LINK)
- Source8: WHTest (-)
- Source10: Controlling (KRAFT_Controlling_CSL_LINK)
- Source11: Deployment Tool (KRAFT_DeploymentTool_CSL_LINK)
- Source12: Complaints_SAP (KRAFT_Complaints_SAP_CSL_LINK)
- Source13: ASSA_SUPP (KRAFT_ASSA_SUPP_CSL_LINK)
- Source15: BCP (KRAFT_BCP_CSL_LINK)
- Source16: OTM (KRAFT_OTM_CSL_LINK)
- Source17: Safir_RU (KRAFT_DWHSafir_CSL_LINK)
4. Внешние параметры¶
Запуск осуществляется с параметрами конфигурации внутри пакета, через переменные:
- DWH_DB_Name = KRAFT_DWH_CSL
- DWH_Link_Name = KRAFT_DWH_CSL_LINK
- DWH_LoginName = DWHSQLUser
- DWH_LoginPwd = ***
- DWH_ServerName = arrrumosapp17