Название пакета: DWH_ETL¶
Описание¶
Загрузка данных в хранилище KRAFT_DWH_CSL
Схема¶
Список шагов:¶
1. Create new batch identifier¶
- Тип: Execute SQL Task
- Описание: Генерация ID выполнения ETL-процесса
- Процедура: audit.usp_GetBatch
- Параметры мэппинга данных:
- User::Batch
2. Create, Transform Dimensions and Other Tables¶
- Тип: Sequence Container
- Описание: Создание и обработка измерений
2.1 Dimension Date
- Описание: Обработка таблиц измерений даты
- Процедура: etl.usp_DimDate
- Параметры мэппинга данных:
- User::DateSourceViewName
- User::DateTableName
Используемые объекты:
| DateSourceViewName | DateTableName |
|---|---|
| dbo.VIEW_D_Date_KF | dbo.D_Date_KF |
| dbo.VIEW_D_Date_KF_2013 | dbo.D_Date_KF_2013 |
| dbo.VIEW_D_Date | dbo.D_Date |
| dbo.VIEW_D_DatePeriod | dbo.D_DatePeriod |
2.2 Dimension Relationship
- Описание: Генерация связей между измерениями
- Процедура: etl.usp_GenerateDimensionRelationship
- Параметры мэппинга данных:
- User::Batch
2.3 Other Dimension
- Описание: Загружает данные в таблицы измерений
- Процедура: 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_MergeExcludedColumns | DIM_Mode |
|---|---|---|---|---|
| dbo.VIEW_D_Product | dbo.D_Product | ProductCode | 2 | |
| dbo.VIEW_D_CustomerInHierarchy | dbo.D_CustomerInHierarchy | ID_SalesOrganization, ID_DistributionChannel, ID_CustomerIn_BillTo, ID_CustomerIn_ShipTo | 2 | |
| dbo.VIEW_D_CustomerInHierarchyPrimary | dbo.D_CustomerInHierarchyPrimary | ID_CustomerInHierarchy | 1 | |
| dbo.VIEW_D_Invoices | dbo.D_Invoices | ID_SalesOrganization, InvoiceNumber | 2 | |
| dbo.VIEW_D_Deliveries | dbo.D_Deliveries | ID_SalesOrganization, DeliveryNumber | 2 | |
| dbo.VIEW_D_Orders | dbo.D_Orders | ID_SalesOrganization, OrderNumber | 2 | |
| dbo.VIEW_D_MustBeProduct | dbo.D_MustBeProduct | ID_Product, ID_CustomerInHierarchy | 1 | |
| dbo.VIEW_D_MustStockList | dbo.D_MustStockList | ID_Product, ID_CustomerInHierarchy, ID_CustomerInSystem | 1 | |
| dbo.VIEW_D_ProductUomRates | dbo.D_ProductUomRates | 1 | ||
| dbo.VIEW_D_ForecastIMS | dbo.D_ForecastIMS | Code | 1 | |
| dbo.VIEW_D_ProductConsolidation | dbo.D_ProductConsolidation | ID_Plant, ID_Product, ID_ProductConsolidation | 1 | |
| dbo.VIEW_D_StandardAssortment | dbo.D_StandardAssortment | ID_CustomerInHierarchy, ID_ProductFamily | 1 | |
| dbo.VIEW_D_Customer_SA_Exclusion | dbo.D_Customer_SA_Exclusion | ID_CustomerInHierarchy, ID_CustomerInSystem, ID_ProductFamily | 1 | |
| dbo.VIEW_D_Customer_SA_Target | dbo.D_Customer_SA_Target | ID_CustomerInHierarchy, ID_ProductFamily | 1 | |
| dbo.VIEW_D_Customer_SA_Consolidation | dbo.D_Customer_SA_Consolidation | ID_CustomerInHierarchy, ID_ProductFamily | 1 | |
| dbo.VIEW_D_CustomerInSystem | dbo.D_CustomerInSystem | Code | 2 | |
| dbo.VIEW_D_ProductSalesData | dbo.D_ProductSalesData | ID_Product, ID_SalesOrganization, ID_DistributionChannel, ID_DeliveryPlant | 1 | |
| dbo.VIEW_D_EmployeeInventoryProjectionST | dbo.D_EmployeeInventoryProjectionST | Code | 1 | |
| dbo.VIEW_D_ProductPlantStatus | dbo.D_ProductPlantStatus | UID | 1 | |
| dbo.VIEW_D_ProductPlantAvailabilityCheck | dbo.D_ProductPlantAvailabilityCheck | UID | 1 | |
| dbo.VIEW_D_ProductPlantData | dbo.D_ProductPlantData | ID_Product, ID_Plant | 1 | |
| dbo.VIEW_D_ProductPrimaryEAN | dbo.D_ProductPrimaryEAN | EAN | 1 | |
| dbo.VIEW_D_OutlookIMS | dbo.D_OutlookIMS | Code | 1 | |
| dbo.VIEW_D_ForecastShipment | dbo.D_ForecastShipment | Code | 2 | |
| dbo.VIEW_D_Date_KFRU_YearWeek | dbo.D_Date_KFRU_YearWeek | ID_KFRU_YearWeek | 1 | |
| dbo.VIEW_D_DemandPlan | dbo.D_DemandPlan | Code | 1 | |
| dbo.VIEW_D_OutlookSI | dbo.D_OutlookSI | Code | 1 | |
| dbo.VIEW_D_CustomerVendor | dbo.D_CustomerVendor | UID | 2 | |
| dbo.VIEW_D_PurchaseOrder | dbo.D_PurchaseOrder | PurchaseOrderNo | 2 | |
| dbo.VIEW_D_StockLot | dbo.D_StockLot | Code | 2 | |
| dbo.VIEW_D_StockLotProduct | dbo.D_StockLotProduct | ID_StockLot, ID_ProductSKU | 2 | |
| dbo.VIEW_D_StockGTD | dbo.D_StockGTD | Code | 2 | |
| dbo.VIEW_D_CustomerInManager | dbo.D_CustomerInManager | Code | 2 | |
| dbo.VIEW_D_CustomerInHierarchyB | dbo.D_CustomerInHierarchyB | ID_CustomerInHierarchy | 2 | |
| dbo.VIEW_D_ACScenario | dbo.D_ACScenario | Code | 2 | |
| dbo.VIEW_D_CustomerIn_SellOut | dbo.D_CustomerIn_SellOut | DataSource, UID_CustomerIn, DateBegin | 2 | |
| dbo.VIEW_D_ProductEAN | dbo.D_ProductEAN | EAN | 2 | |
| dbo.VIEW_D_Date_YearWeek | dbo.D_Date_YearWeek | ID_YearWeek | 1 | |
| dbo.VIEW_D_CustomerInSystem_SCD | dbo.D_CustomerInSystem_SCD | ID_CustomerInSystem, DateBegin | 2 | |
| dbo.VIEW_D_CustomerInSystemType | dbo.D_CustomerInSystemType | Name | 2 | |
| dbo.VIEW_D_CustomerIn | dbo.D_CustomerIn | Code | 2 | |
| dbo.VIEW_D_EmployeeSellInHierarchy | dbo.D_EmployeeSellInHierarchy | EmployeeUID | 1 | |
| dbo.VIEW_D_EmployeeSellInHierarchy | dbo.D_EmployeeSellInHierarchy | EmployeeUID | 1 | |
| dbo.VIEW_D_EmployeeSellInMatrix | dbo.D_EmployeeSellInMatrix | ID_Employee, ID_CustomerInHierarchy, ID_ProductCategory | 1 | |
| dbo.VIEW_D_ProductBasePriceListOrdersEAN_Period | dbo.D_ProductBasePriceListOrdersEAN_Period | 1 | ||
| dbo.VIEW_D_ShipmentTypeOTM | dbo.D_ShipmentTypeOTM | Code | 2 | |
| dbo.VIEW_D_ShipmentTransportMode | dbo.D_ShipmentTransportMode | Code | 2 | |
| dbo.VIEW_D_ServiceProvider | dbo.D_ServiceProvider | Code | 2 | |
| dbo.VIEW_D_ShipmentLocation | dbo.D_ShipmentLocation | LocationType, Code | 2 | |
| dbo.VIEW_D_OrderRelease | dbo.D_OrderRelease | OrderRelease_gid | 2 | |
| dbo.VIEW_D_Shipment | dbo.D_Shipment | Shipment_gid | 2 | |
| dbo.VIEW_D_OrderRelease_Shipment | dbo.D_OrderRelease_Shipment | ID_OrderRelease, ID_Shipment | 1 | |
| dbo.VIEW_D_ClaimDeviation | dbo.D_ClaimDeviation | Code | 2 | |
| dbo.VIEW_D_ClaimDenialReason | dbo.D_ClaimDenialReason | Code | 2 | |
| dbo.VIEW_D_Claim | dbo.D_Claim | ClaimNumber | 2 | |
| dbo.VIEW_D_CustomerMaterialNo | dbo.D_CustomerMaterialNo | CustomerMaterialNo | 2 | |
| dbo.VIEW_D_Measure | dbo.D_Measure | Name | 2 | |
| dbo.VIEW_D_InboundTransporationRate | dbo.D_InboundTransporationRate | ID_DateFrom, ID_SupplierPlant, ID_Plant, ID_ProductCategory | 2 | |
| dbo.VIEW_D_ProductSeason | dbo.D_ProductSeason | Code | 2 | |
| dbo.VIEW_D_ProductProject | dbo.D_ProductProject | Code | 2 | |
| dbo.VIEW_D_CustomerInProductCustomer | dbo.D_CustomerInProductCustomer | ID_CustomerInRegion, ID_ProductCustomerInBrandGroup | 2 |
3. Execute Package DWH_ETL_SA¶
- Тип: Execute Package Task
- Описание: Загрузка данных из источника SOR в SA слой хранилища данных DWH
- Выполняемый пакет: DWH_ETL_SA
4. SOR¶
- Тип: Execute SQL Task
- Описание: Обновление данных в таблице dbo.H_OOS
5. Execute Package DWH_MDT¶
- Тип: Execute Package Task
- Описание: Загрузка данных MDT
- Выполняемый пакет: DWH_MDT
6. Historical tables¶
- Тип: Sequence Container
- Описание: Обработка Historical таблиц
6.1 H_ClosingStock_Chicago
- Тип: Execute SQL Task
- Описание: Обработка данных Chicago
- Процедура: etl.usp_Load_H_ClosingStock_Chicago
- Параметры мэппинга данных:
- Batch → User::Batch
6.2 H_ClosingStock_Chicago EOW
- Тип: Execute SQL Task
- Описание: Обработка данных Chicago на конец недели
- Процедура: etl.usp_Load_H_ClosingStock_Chicago_EOW
- Параметры мэппинга данных:
- Batch → User::Batch
6.3 H_IMS_Chicago
- Тип: Execute SQL Task
- Описание: Обработка данных IMS Chicago
- Процедура: etl.usp_Load_H_IMS_Chicago
- Параметры мэппинга данных:
- Batch → User::Batch
6.4 H_SellIn_Chicago
- Тип: Execute SQL Task
- Описание: Обработка данных SellIn Chicago
- Процедура: etl.usp_Load_H_SellIn_Chicago
- Параметры мэппинга данных:
- Batch → User::Batch
7. Process Fact tables¶
- Тип: Sequence Container
- Описание: Обработка таблиц фактов
- Цикличность: Foreach Loop Container (Enumerate Fact tables)
7.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_ExcludedColumns | FACT_UseTempTable | FACT_Mode | FACT_FactPartTableName |
|---|---|---|---|---|---|---|
| dbo.VIEW_F_MonthRolling | dbo.F_MonthRolling | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SellOut_PeriodMonth1 | |
| dbo.VIEW_F_Standard_TPR | dbo.F_Standard_TPR | nPartYearMonth | ID | 0 | 1 | dbo.VIEW_F_Standard_TPR_Period |
| dbo.VIEW_F_SAFIR_OrdersIn | dbo.F_SAFIR_OrdersIn | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SalesOrdersHistory_Period | |
| dbo.VIEW_F_SAFIR_Deliveries | dbo.F_SAFIR_Deliveries | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SalesOrdersHistory_Period | |
| dbo.VIEW_F_SAFIR_Invoices | dbo.F_SAFIR_Invoices | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SalesOrdersHistory_Period | |
| dbo.VIEW_F_SAFIR_PurchaseOrders | dbo.F_SAFIR_PurchaseOrders | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SellOut_PeriodMonth1 | |
| dbo.VIEW_F_SAFIR_PurchaseOrderHistory | dbo.F_SAFIR_PurchaseOrderHistory | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SellOut_PeriodMonth1 | |
| dbo.VIEW_F_InvoiceCondition | dbo.F_InvoiceCondition | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SalesOrdersHistory_Period | |
| dbo.VIEW_F_SalesOrdersHistory | dbo.F_SalesOrdersHistory | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SalesOrdersHistory_Period | |
| dbo.VIEW_F_SalesOrdersHistoryCFR | dbo.F_SalesOrdersHistoryCFR | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SalesOrdersHistory_Period | |
| dbo.VIEW_F_PriceDefinitions | dbo.F_PriceDefinitions | nPartYearMonth | 0 | 1 | ||
| dbo.udf_F_Transfer | dbo.F_Transfer | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SalesOrdersHistory_Period | |
| dbo.VIEW_F_InventoryProjectionST | dbo.F_InventoryProjectionST | nPartYearMonth | 0 | 1 | dbo.H_InventoryProjectionST | |
| dbo.VIEW_F_InventoryProjectionSTIndex | dbo.F_InventoryProjectionSTIndex | nPartYearMonth | 0 | 1 | dbo.H_InventoryProjectionST | |
| dbo.VIEW_F_InventoryProjectionSTTotal | dbo.F_InventoryProjectionSTTotal | nPartYearMonth | 0 | 1 | dbo.H_InventoryProjectionST | |
| dbo.VIEW_F_Plan_AC | dbo.F_Plan_AC | nPartYearMonth | 0 | 1 | ||
| dbo.VIEW_F_Plan_SalesPlan | dbo.F_Plan_SalesPlan | nPartYearMonth | 0 | 1 | dbo.VIEW_F_Plan_SalesPlan_PeriodMonth | |
| dbo.VIEW_F_Plan_SalesTarget | dbo.F_Plan_SalesTarget | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SellOut_PeriodMonth2 | |
| dbo.VIEW_F_Plan_StretchTarget | dbo.F_Plan_StretchTarget | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SellOut_PeriodMonth2 | |
| dbo.VIEW_F_ProductBasePriceListOrdersEAN | dbo.F_ProductBasePriceListOrdersEAN | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ProductBasePrice_Period | |
| dbo.VIEW_F_ProductBasePriceList_ZP0X | dbo.F_ProductBasePriceList_ZP0X | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ProductBasePrice_Period | |
| dbo.VIEW_F_ProductBasePriceHistoryEAN | dbo.F_ProductBasePriceHistoryEAN | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ProductBasePrice_Period | |
| dbo.VIEW_F_ProductBasePriceHistory | dbo.F_ProductBasePriceHistory | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ProductBasePrice_Period | |
| dbo.VIEW_F_ProductBasePriceListRetail | dbo.F_ProductBasePriceListRetail | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ProductBasePrice_Period | |
| dbo.VIEW_F_WarehouseMovement | dbo.F_WarehouseMovement | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(6, null, null) | |
| dbo.VIEW_F_WarehouseStock | dbo.F_WarehouseStock | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(6, null, null) | |
| dbo.VIEW_F_WarehouseInbound | dbo.F_WarehouseInbound | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(6, null, null | |
| dbo.VIEW_F_WarehouseOutbound | dbo.F_WarehouseOutbound | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(6, null, null) | |
| dbo.VIEW_F_WarehousePalletsBalance | dbo.F_WarehousePalletsBalance | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(6, null, null) | |
| dbo.VIEW_F_OTM_OrderRelease | dbo.F_OTM_OrderRelease | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(6, null, null) | |
| dbo.VIEW_F_ShipmentCost | dbo.F_ShipmentCost | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(6, null, null) | |
| dbo.VIEW_F_ShipmentDemurrage | dbo.F_ShipmentDemurrage | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(6, null, null) | |
| dbo.VIEW_F_ProductSeason_Product | dbo.F_ProductSeason_Product | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(6, null, null) | |
| dbo.VIEW_F_ProductProject_Product | dbo.F_ProductProject_Product | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(6, null, null) | |
| dbo.VIEW_F_SalesPlanWeek | dbo.F_SalesPlanWeek | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(6, null, null) |
8. Start job Load_SC_Reports_MDR¶
- Тип: Execute SQL Task
- Описание: Запуск задания агента SQL Server Load_SC_Reports_MDR
- SQL запрос: exec msdb..sp_start_job @job_name = 'Load_SC_Reports_MDR'
9. Process Fact tables SFA¶
- Тип: Sequence Container
- Описание: Обработка таблиц фактов SFA
- Цикличность: Foreach Loop Container (Enumerate Fact tables)
9.1 Load data to Fact table
- Тип: Execute SQL Task
- Описание: Загрузка данных в таблицы фактов SFA
- Процедура: 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_ExcludedColumns | FACT_UseTempTable | FACT_Mode | FACT_FactPartTableName | isActive |
|---|---|---|---|---|---|---|---|
| dbo.VIEW_F_Forecast_IMS | dbo.F_Forecast_IMS | nPartYearMonth | 0 | 1 | dbo.VIEW_F_Forecast_IMS_Period | -1 | |
| dbo.VIEW_F_Forecast_IMS_Scenario | dbo.F_Forecast_IMS_Scenario | nPartYearMonth | 0 | 1 | dbo.VIEW_F_Forecast_IMS_Scenario_Period | -1 | |
| dbo.VIEW_F_Forecast_SellIn | dbo.F_Forecast_SellIn | nPartYearMonth | 0 | 1 | dbo.VIEW_F_Forecast_SellIn_Period | -1 | |
| dbo.VIEW_F_Forecast_SellIn_Scenario | dbo.F_Forecast_SellIn_Scenario | nPartYearMonth | 0 | 1 | dbo.VIEW_F_Forecast_SellIn_Scenario_Period | -1 | |
| dbo.VIEW_F_Forecast_Shipment | dbo.F_Forecast_Shipment | nPartYearMonth | 0 | 1 | dbo.VIEW_F_Forecast_Shipment_Period | -1 | |
| dbo.VIEW_F_IMS_IRIS | dbo.F_IMS_IRIS | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | 0 | |
| dbo.VIEW_F_IMS_Chicago | dbo.F_IMS_Chicago | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_IMS | dbo.F_IMS | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SellOut_PeriodMonth1 | -1 | |
| dbo.VIEW_F_SellIn_Chicago | dbo.F_SellIn_Chicago | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_SellIn | dbo.F_SellIn | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_ClosingStock_IRIS_Prepare | dbo.F_ClosingStock_IRIS_Prepare | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_ClosingStock_IRIS | dbo.F_ClosingStock_IRIS | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_Forecast_IMS_SA_KPI | dbo.F_Forecast_IMS_SA_KPI | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_ClosingStock_Chicago | dbo.F_ClosingStock_Chicago | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_ClosingStock_Chicago_FRZ | dbo.F_ClosingStock_Chicago_FRZ | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_IMS_Chicago_FRZ | dbo.F_IMS_Chicago_FRZ | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_SellIn_Chicago_FRZ | dbo.F_SellIn_Chicago_FRZ | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_ClosingStock_Chicago_Corrected | dbo.F_ClosingStock_Chicago_Corrected | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_OutlookIMS | dbo.F_OutlookIMS | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_ClosingStock | dbo.F_ClosingStock | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_ClosingStock_Chicago_WH | dbo.F_ClosingStock_Chicago_WH | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_ClosingStock_SA_KPI | dbo.F_ClosingStock_SA_KPI | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_IMS_SA_KPI | dbo.F_IMS_SA_KPI | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_ClosingStock_WH_SA_KPI | dbo.F_ClosingStock_WH_SA_KPI | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_OutlookSI | dbo.F_OutlookSI | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | -1 | |
| dbo.VIEW_F_OutlookIMS_Full | dbo.F_OutlookIMS_Full | nPartYearMonth | 0 | 1 | dbo.VIEW_F_SellOut_PeriodMonth1 | -1 | |
| dbo.VIEW_F_Forecast_IMS_Lag2 | dbo.F_Forecast_IMS_Lag2 | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(12, null, null) | -1 | |
| dbo.VIEW_F_Forecast_IMS_CustomerIn | dbo.F_Forecast_IMS_CustomerIn | nPartYearMonth | 0 | 1 | dbo.udtf_getMonthPeriod(12, null, null) | -1 |
10. Process Fact tables SFA 1, 2, 3 (отключено)¶
- Тип: Sequence Container
- Описание: Обработка таблиц фактов SFA (отключено - Disabled="True")
- Цикличность: Foreach Loop Container (Enumerate Fact tables)
10.1 Create Indexs
- Тип: Execute SQL Task
- Описание: Создание индексов
10.2 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
Используемые объекты SFA 1, 2, 3:
| FACT_SourceViewName | FACT_FactTableName | FACT_PartColumnDate | FACT_ExcludedColumns | FACT_UseTempTable | FACT_Mode | FACT_FactPartTableName |
|---|---|---|---|---|---|---|
| dbo.VIEW_F_ClosingStock_Chicago | dbo.F_ClosingStock_Chicago | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_ClosingStock_Chicago_FRZ | dbo.F_ClosingStock_Chicago_FRZ | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_ClosingStock_Chicago_Corrected | dbo.F_ClosingStock_Chicago_Corrected | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_ClosingStock | dbo.F_ClosingStock | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_ClosingStock_Chicago_WH | dbo.F_ClosingStock_Chicago_WH | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period |
| FACT_SourceViewName | FACT_FactTableName | FACT_PartColumnDate | FACT_ExcludedColumns | FACT_UseTempTable | FACT_Mode | FACT_FactPartTableName |
|---|---|---|---|---|---|---|
| dbo.VIEW_F_IMS_Chicago | dbo.F_IMS_Chicago | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_IMS | dbo.F_IMS | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_SellIn_Chicago | dbo.F_SellIn_Chicago | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_SellIn | dbo.F_SellIn | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_ClosingStock_Chicago | dbo.F_ClosingStock_Chicago | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_ClosingStock_Chicago_FRZ | dbo.F_ClosingStock_Chicago_FRZ | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_IMS_Chicago_FRZ | dbo.F_IMS_Chicago_FRZ | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_SellIn_Chicago_FRZ | dbo.F_SellIn_Chicago_FRZ | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_ClosingStock_Chicago_Corrected | dbo.F_ClosingStock_Chicago_Corrected | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_ClosingStock | dbo.F_ClosingStock | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period | |
| dbo.VIEW_F_ClosingStock_Chicago_WH | dbo.F_ClosingStock_Chicago_WH | nPartYearMonth | 0 | 1 | dbo.VIEW_F_ClosingStock_Chicago_Corrected_Period |
| FACT_SourceViewName | FACT_FactTableName | FACT_PartColumnDate | FACT_ExcludedColumns | FACT_UseTempTable | FACT_Mode | FACT_FactPartTableName |
|---|---|---|---|---|---|---|
| dbo.VIEW_F_ClosingStock_SA_KPI | dbo.F_ClosingStock_SA_KPI | nPartYearMonth | 0 | 1 | ||
| dbo.VIEW_F_IMS_SA_KPI | dbo.F_IMS_SA_KPI | nPartYearMonth | 0 | 1 | ||
| dbo.VIEW_F_ClosingStock_WH_SA_KPI | dbo.F_ClosingStock_WH_SA_KPI | nPartYearMonth | 0 | 1 |
11. Process Historical tables¶
- Тип: Sequence Container
- Описание: Обработка Historical таблиц
- Цикличность: Foreach Loop Container (Enumerate Historical tables)
11.1 Load data to Historical table
- Тип: Execute SQL Task
- Описание: Загрузка данных в Historical таблицы
- Процедура: etl.usp_LoadHistoryPart
- Параметры мэппинга данных:
- @Batch → User::Batch
- @ViewNameHistory → User::ViewNameHistory
- @TableNameHistory → User::TableNameHistory
- @WhereColumns → User::WhereColumns
- @HorizonLoadData → User::HorizonLoadData
Используемые объекты:
| ViewNameHistory | TableNameHistory | WhereColumns | HorizonLoadData | PartTableNameHistory |
|---|---|---|---|---|
| dbo.VIEW_H_Forecast_IMS_Scenario | dbo.H_Forecast_IMS_Scenario | 10 | ||
| dbo.VIEW_H_Forecast_SellIn_Scenario | dbo.H_Forecast_SellIn_Scenario | 10 | ||
| dbo.VIEW_H_InventoryProjectionST | dbo.H_InventoryProjectionST | Date | -1 | |
| dbo.VIEW_H_InventoryProjectionSTIndex | dbo.H_InventoryProjectionSTIndex | Date | -1 | |
| dbo.VIEW_H_WarehouseStock | dbo.H_WarehouseStock | 1 | ||
| dbo.VIEW_H_WarehouseMovement | dbo.H_WarehouseMovement | 5 | ||
| dbo.VIEW_H_WarehouseInbound | dbo.H_WarehouseInbound | 5 | ||
| dbo.VIEW_H_WarehouseOutbound | dbo.H_WarehouseOutbound | 5 |
12. Process Historical tables Dimension¶
- Тип: Sequence Container
- Описание: Обработка измерений в Historical таблицах
- Цикличность: Foreach Loop Container (Enumerate Historical tables)
12.1 Load data to Historical table
- Тип: Execute SQL Task
- Описание: Загрузка данных в Historical таблицы, измерения
- Процедура: etl.usp_LoadHistoryDim
- Параметры мэппинга данных:
- @Batch → User::Batch
- @ViewNameHistory → User::ViewNameHistory
- @TableNameHistory → User::TableNameHistory
- @NaturalColumns → User::NaturalColumns
Используемые объекты:
| ViewNameHistory | TableNameHistory | NaturalColumns |
|---|---|---|
| dbo.VIEW_H_SAFIR_Plant | dbo.H_SAFIR_Plant | UID |
| dbo.VIEW_H_SAFIR_PlantLocation | dbo.H_SAFIR_PlantLocation | UID |
13. fill audit ETLObjectsProcessStatus¶
- Тип: Execute SQL Task
- Описание: Заполнение статуса обработки объектов ETL (аудит)
14. Finish batch identifier¶
- Тип: Execute SQL Task
- Описание: Завершение выполнения ETL-процесса
15. Lock Monitor¶
- Тип: Execute SQL Task
- Описание: Мониторинг блокировок
Список задействованных объектов¶
1. Базы данных¶
- DWH: KRAFT_DWH_CSL
- MDT: KRAFT_MDT_CSL
2. Соединение с другими пакетами¶
- DWH_ETL_SA
- DWH_MDT
3. Источники данных¶
- Source1: Safir_RU (KRAFT_DWHSafir_CSL_LINK)
- Source2: OPDB (KRAFT_OPDB_CSL_LINK)
- Source3: MDDB (KRAFT_MDDB_CSL_LINK)
- Source4: STAR_Production (KRAFT_STAR_CSL_LINK)
- Source5: IRIS (KRAFT_IRIS_CSL_LINK)
- Source6: SOPtool (KRAFT_SOPtool_CSL_LINK)
- Source7-Source9: MRouter (KRAFT_MRouter_CSL_LINK)
- 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)
- Source14: chicago_kraft_wh (KRAFT_CHGO_CSL_LINK)
- Source15: BCP (KRAFT_BCP_CSL_LINK)
- Source16: OTM (KRAFT_OTM_CSL_LINK)
- Source17: Safir_RU (KRAFT_DWHSafir_CSL_LINK)
4. Параметры подключения, переменные:¶
Запуск осуществляется через файл запуска start_DWH_ETL.bat с параметрами конфигурации внутри пакета, через переменные:
- DWH_DB_Name = KRAFT_DWH_CSL
- DWH_Link_Name = KRAFT_DWH_CSL_LINK
- DWH_LoginName = DWHSQLUser
- DWH_LoginPwd = ***
- DWH_ServerName = arrrumosapp17