Just to be clear, there are two projects here. One is to automate the whole of data collection. That's a long-term goal, and not needed for the MSFT project.
The MSFT project is to put some part of our world into the Azure cloud. What we chose to do is build a small dashboard that displays status of data loading for one customer (a customer-specific view) or all customers (a G4 view.) For that we just need to pick one customer and make sure their ETL packages log details as they go. From there we push that data into a cloud db and display some views over it. From: Paul de Prado Sent: Tuesday, April 24, 2012 8:55 AM To: Imthiyaz Ahmed Cc: Bill Baker; Bill Baker; Barrie Pike Subject: RE: Automatically invoking ETL Hi Imthiyaz, I suspect there is more to it. Can you please review the ETL packages to let me know if the ones for each file run the same stored procedures over and over again? While the sprocs being run do not take much time, we should of course clean it up if needed. If so, we must move those stored procedures into a new SSIS package and remove them from all of the file-loading packages for each CAM retailer and manufacturer. Also, I know that some of the files will be loaded manually to get a head start, especially when files are late or processing is going slowly. Do you still do that for NOM? We need to account for whether Bill can wait for the files to accumulate before running them, or if we should have a job that loads them as soon as they are downloaded. Lastly, what tests do you run after the ASDA and Morrisons files are downloaded from their web sites to ensure the files are not bad ones? Adding Bill and Baz to the email thread so they can chime in with questions as needed... Thanks, Paul ________________________________ From: Imthiyaz Ahmed Sent: Tuesday, April 24, 2012 8:22 AM To: Paul de Prado Subject: RE: Automatically invoking ETL Hi Paul, I expect that this log was taken yesterday when we load data for last 3 or 4 days. So that many number of time the package should run. If it is a single day data then this redundancy will not be there. Thanks, Imthiyaz Ahmed ________________________________ From: Paul de Prado Sent: Tuesday, April 24, 2012 10:00 AM To: Imthiyaz Ahmed Subject: FW: Automatically invoking ETL Hi Imthiyaz, Please comment on the points I raise below regarding the NOM ASDA Package processing. It appears as though we are redundantly running many steps for NOM ASDA on every file load. I do not believe this is really necessary. Can we consolidate the steps? I need an answer on this on Tuesday as Bill Baker needs the information for a project with Microsoft. Thanks, Paul From: Paul de Prado Sent: Tuesday, April 24, 2012 12:28 AM To: 'Bill Baker' Cc: Barrie Pike Subject: RE: Automatically invoking ETL Bill, Ideally, that would be the case for the example below that the out-dented packages run the indented packages below them. However, there are some exceptions driven by the retailers being inconsistent in file download availability. In the example below of NOM: NOM Morrison Package Processing kicks off all jobs indented below it once the Morrisons files are received. NOM ASDA Package Processing could kick off all jobs indented below it once all the ASDA files are received. However, in daily practice G4 India is kicking off file loads for each of the ASDA files as they are received to save time: * Sales file * Stock file * TraitedValid file * Waste file That said, the current flow shown in the logs for NOM ASDA is not as I would expect. I am going to review this process with Imthiyaz on Tuesday morning to determine which steps are being run redundantly, though. This should save time and possibly eliminate the need to do manual loads of NOM ASDA files to save time each day. I will update you on the answer. Thanks, Paul From: Bill Baker Sent: Monday, April 23, 2012 10:57 PM To: Paul de Prado Cc: Barrie Pike Subject: RE: Automatically invoking ETL Yeah, those are HCODEs, about the last remaining artifact of COM. Steve is getting me some documentation on the Task Manager, that's have the equation. My big mystery area is this: In a multi-part ETL, let's say the one below: * How many packages does the automation driver kick off? * Do the 'out-dented' packages run the indented packages below them? * How do we know that we're good to run the next package in the sequence? G4_NOM EPOS (Morrisons, then ASDA) NOM Morrison Package Processing Master_ETL_Morr Fact_POS_Traited_R_Morr_Insert Fact_POS_TradingSKUs_R_Morr_Insert Fact_POS_TradingStores_R_Morr_Insert Fact_GenBaseline_Morr Fact_POS_LostOpps_Morr_Insert Fact_UnderPerform_R_Morr_Insert Fact_ActiveDistrPoints_Morr_Insert Fact_MapPromos_ActiveDistr_R_Morr Fact_MapDepots_ActiveDistr_Morr Fact_MapPromos_Traited_Morr NOM Morr Dimension Processing NOM Asda Package Processing NOM Asda Package Processing NOM ASDA Package Processing NOM ASDA Sales File Processing Stg_Fact_POS_Sales_C_WMUK_Cleanse G4_Portal_MFG_G4_MDM_Mas_Location_Store_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Affluence_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Country_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_County_Asda_upsert Portal_MFG_G4_MDM_Mas_Store_Format_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Internal_Fascia_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_NlsnRegionDes_Asda_upsert Portal_MFG_G4_MDM_Mas_Store_Range_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Region_Des_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_SDR_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_SubFormat_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_AsdaFormat_Asda_UPsert Portal_MFG_G4_MDM_Mas_Store_TVRegionDes_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Type_Asda_Upsert G4_Portal_MFG_G4_MDM_Mas_Prod_Sku_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_STD_PS_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_UpMarket_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_Variety_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_SizeDesc_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_ProfileGroup_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_Supplier_Asda_Upsert Dim_Store_C_WMUK_Affluence_UPSERT Dim_Store_C_WMUK_Country_UPSERT Dim_Store_C_WMUK_County_UPSERT Dim_Store_C_WMUK_Format_UPSERT Dim_Store_C_WMUK_Internal_Fascia_UPSERT Dim_Store_C_WMUK_NlsnRegionDes_UPSERT Dim_Store_C_WMUK_Range_UPSERT Dim_Store_C_WMUK_RegionDes_UPSERT Dim_Store_C_WMUK_SDR_UPSERT Dim_Store_C_WMUK_SubFormat_UPSERT Dim_Store_C_WMUK_AsdaFormat_UPSERT Dim_Store_C_WMUK_TVRegionDes_UPSERT Dim_Store_C_WMUK_Type_UPSERT Dim_Store_C_WMUK_UPSERT Dim_Prod_C_WMUK_UpMarket_UPSERT Dim_Prod_C_WMUK_STD_PS_UPSERT Dim_Prod_C_WMUK_Variety_UPSERT Dim_Prod_C_WMUK_SizeDesc_UPSERT Dim_Prod_C_WMUK_ProfileGroup_UPSERT Dim_Prod_C_WMUK_Upsert Dim_Prod_C_WMUK_Supplier_UPSERT Fact_POS_Sales_C_WMUK_Insert NOM ASDA Stock File Processing Stg_Fact_POS_Stock_C_WMUK_Cleanse G4_Portal_MFG_G4_MDM_Mas_Location_Store_Asda_Stock_Upsert Portal_MFG_G4_MDM_Mas_Store_Affluence_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Country_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_County_Asda_upsert Portal_MFG_G4_MDM_Mas_Store_Format_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Internal_Fascia_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_NlsnRegionDes_Asda_upsert Portal_MFG_G4_MDM_Mas_Store_Range_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Region_Des_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_SDR_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_SubFormat_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_AsdaFormat_Asda_UPsert Portal_MFG_G4_MDM_Mas_Store_TVRegionDes_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Type_Asda_Upsert G4_Portal_MFG_G4_MDM_Mas_Prod_Sku_Asda_Stock_Upsert Portal_MFG_G4_MDM_Mas_Sku_ProfileGroup_Asda_Stock_Upsert Portal_MFG_G4_MDM_Mas_Sku_Supplier_Asda_Stock_Upsert Portal_MFG_G4_MDM_Mas_Sku_STD_PS_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_UpMarket_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_Variety_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_SizeDesc_Asda_Upsert Dim_Store_C_WMUK_Affluence_UPSERT Dim_Store_C_WMUK_Country_UPSERT Dim_Store_C_WMUK_County_UPSERT Dim_Store_C_WMUK_Format_UPSERT Dim_Store_C_WMUK_Internal_Fascia_UPSERT Dim_Store_C_WMUK_NlsnRegionDes_UPSERT Dim_Store_C_WMUK_Range_UPSERT Dim_Store_C_WMUK_RegionDes_UPSERT Dim_Store_C_WMUK_SDR_UPSERT Dim_Store_C_WMUK_SubFormat_UPSERT Dim_Store_C_WMUK_AsdaFormat_UPSERT Dim_Store_C_WMUK_TVRegionDes_UPSERT Dim_Store_C_WMUK_Type_UPSERT Dim_Store_C_WMUK_UPSERT Dim_Prod_C_WMUK_UpMarket_UPSERT Dim_Prod_C_WMUK_STD_PS_UPSERT Dim_Prod_C_WMUK_Variety_UPSERT Dim_Prod_C_WMUK_SizeDesc_UPSERT Dim_Prod_C_WMUK_ProfileGroup_UPSERT Dim_Prod_C_WMUK_Upsert Dim_Prod_C_WMUK_Supplier_UPSERT Fact_POS_Stock_C_WMUK_Upsert NOM ASDA TraitedValid File Processing Stg_Fact_POS_TraitedValid_C_WMUK_Cleanse G4_Portal_MFG_G4_MDM_Mas_Location_Store_Asda_TraitedValid_Upsert Portal_MFG_G4_MDM_Mas_Store_Affluence_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Country_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_County_Asda_upsert Portal_MFG_G4_MDM_Mas_Store_Format_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Internal_Fascia_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_NlsnRegionDes_Asda_upsert Portal_MFG_G4_MDM_Mas_Store_Range_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Region_Des_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_SDR_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_SubFormat_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_AsdaFormat_Asda_UPsert Portal_MFG_G4_MDM_Mas_Store_TVRegionDes_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Type_Asda_Upsert G4_Portal_MFG_G4_MDM_Mas_Prod_Sku_Asda_TraitedValid_Upsert Portal_MFG_G4_MDM_Mas_Sku_ProfileGroup_Asda_TraitedValid_Upsert Portal_MFG_G4_MDM_Mas_Sku_STD_PS_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_UpMarket_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_Variety_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_SizeDesc_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_Supplier_Asda_Upsert Dim_Store_C_WMUK_Affluence_UPSERT Dim_Store_C_WMUK_Country_UPSERT Dim_Store_C_WMUK_County_UPSERT Dim_Store_C_WMUK_Format_UPSERT Dim_Store_C_WMUK_Internal_Fascia_UPSERT Dim_Store_C_WMUK_NlsnRegionDes_UPSERT Dim_Store_C_WMUK_Range_UPSERT Dim_Store_C_WMUK_RegionDes_UPSERT Dim_Store_C_WMUK_SDR_UPSERT Dim_Store_C_WMUK_SubFormat_UPSERT Dim_Store_C_WMUK_AsdaFormat_UPSERT Dim_Store_C_WMUK_TVRegionDes_UPSERT Dim_Store_C_WMUK_Type_UPSERT Dim_Store_C_WMUK_UPSERT Dim_Prod_C_WMUK_UpMarket_UPSERT Dim_Prod_C_WMUK_STD_PS_UPSERT Dim_Prod_C_WMUK_Variety_UPSERT Dim_Prod_C_WMUK_SizeDesc_UPSERT Dim_Prod_C_WMUK_ProfileGroup_UPSERT Dim_Prod_C_WMUK_Upsert Dim_Prod_C_WMUK_Supplier_UPSERT Fact_POS_TraitedValid_C_WMUK_Insert Master_ETL_ASDA DM_Prod_LOAD Dim_Prod_LOAD DM_Cust_ASDA_LOAD Dim_Cust_LOAD Fact_GenBaseline_ASDA_All Fact_POS_TradingStores_R_WMUK_Insert Fact_POS_LostOpps_Insert_All Fact_UnderPerform_R_WMUK_Insert_All Fact_POS_BookStockError_Insert_All Fact_ActiveDistrPoints_ASDA_Insert Fact_OOSDistrPoints_ASDA_Insert Fact_MapDepots_Stock_ASDA Fact_MapPromos_Stock_ASDA Fact_MapPromos_Waste_ASDA Fact_MapDepots_Waste_ASDA G4_NOM ASDA Dimension Processing G4_NOM Asda Cube Processing Cube_TieBack_Check_Asda_AvgOf21Days Paul needs to investigate the flow below and above - very redundant! Stg_Fact_POS_Waste_C_WMUK_Cleanse G4_Portal_MFG_G4_MDM_Mas_Location_Store_Asda_Waste_Insert Portal_MFG_G4_MDM_Mas_Store_Affluence_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Country_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_County_Asda_upsert Portal_MFG_G4_MDM_Mas_Store_Format_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Internal_Fascia_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_NlsnRegionDes_Asda_upsert Portal_MFG_G4_MDM_Mas_Store_Range_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Region_Des_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_SDR_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_SubFormat_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_AsdaFormat_Asda_UPsert Portal_MFG_G4_MDM_Mas_Store_TVRegionDes_Asda_Upsert Portal_MFG_G4_MDM_Mas_Store_Type_Asda_Upsert G4_Portal_MFG_G4_MDM_Mas_Prod_Sku_Asda_Waste_Insert Portal_MFG_G4_MDM_Mas_Sku_ProfileGroup_Asda_Waste_Insert Portal_MFG_G4_MDM_Mas_Sku_STD_PS_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_UpMarket_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_Variety_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_SizeDesc_Asda_Upsert Portal_MFG_G4_MDM_Mas_Sku_Supplier_Asda_Upsert Dim_Store_C_WMUK_Affluence_UPSERT Dim_Store_C_WMUK_Country_UPSERT Dim_Store_C_WMUK_County_UPSERT Dim_Store_C_WMUK_Format_UPSERT Dim_Store_C_WMUK_Internal_Fascia_UPSERT Dim_Store_C_WMUK_NlsnRegionDes_UPSERT Dim_Store_C_WMUK_Range_UPSERT Dim_Store_C_WMUK_RegionDes_UPSERT Dim_Store_C_WMUK_SDR_UPSERT Dim_Store_C_WMUK_SubFormat_UPSERT Dim_Store_C_WMUK_AsdaFormat_UPSERT Dim_Store_C_WMUK_TVRegionDes_UPSERT Dim_Store_C_WMUK_Type_UPSERT Dim_Store_C_WMUK_UPSERT Dim_Prod_C_WMUK_UpMarket_UPSERT Dim_Prod_C_WMUK_STD_PS_UPSERT Dim_Prod_C_WMUK_Variety_UPSERT Dim_Prod_C_WMUK_SizeDesc_UPSERT Dim_Prod_C_WMUK_ProfileGroup_UPSERT Dim_Prod_C_WMUK_Supplier_UPSERT Dim_Prod_C_WMUK_Upsert Fact_POS_Waste_C_WMUK_Reference_INSERT Fact_POS_Waste_C_WMUK_Insert Master_ETL_ASDA DM_Prod_LOAD Dim_Prod_LOAD DM_Cust_ASDA_LOAD Dim_Cust_LOAD Fact_GenBaseline_ASDA_All Fact_POS_TradingStores_R_WMUK_Insert Fact_POS_LostOpps_Insert_All Fact_UnderPerform_R_WMUK_Insert_All Fact_POS_BookStockError_Insert_All Fact_ActiveDistrPoints_ASDA_Insert Fact_OOSDistrPoints_ASDA_Insert Fact_MapDepots_Stock_ASDA Fact_MapPromos_Stock_ASDA Fact_MapPromos_Waste_ASDA Fact_MapDepots_Waste_ASDA G4_NOM ASDA Dimension Processing G4_NOM Asda Cube Processing _______________________________________________ NOTICE: THIS MAILING LIST IS BEING SWITCHED TO GOOGLE GROUPS, please register and consider posting at https://groups.google.com/forum/#!forum/neo4j Neo4j mailing list User@lists.neo4j.org https://lists.neo4j.org/mailman/listinfo/user
