TMS Issue Tracking - QA and Production

Issue Summary

Issue List

Presort MercuryGate data in Snowflake

After repeatedly juggling the idea of feeding Presort MercuryGate data into Snowflake (similar to what we've done for GEC), we’ve received a decision to proceed with this initiative, despite the Blue Yonder migration being in the close horizon.

Following a few recent calls and discussions where the Presort team has been trying to map existing data for their BI dashboards and analysis, we identified that one of the missing components to their puzzle is TMS data. On 01/12 Kristi Jones requested that we go ahead with this data extraction of Presort MercuryGate data into Snowflake, mirroring what we've done for GEC.

Kristi said she will put in a request for a Presort transportation DW in Snowflake.


  • Kayvan Shadpour
  • Jan 16 2023
  • Resolved
  • Attach files
  • Kayvan Shadpour commented
    February 06, 2023 16:34

    From: Kayvan Shadpour
    Sent: Friday, February 3, 2023 12:52 PM
    To: Kristi Jones <Kristine.Jones@pb.com>; David Mejia <david.mejia@pb.com>; Ishan Trikha <Ishan.Trikha@pb.com>; Irina Ashurova <irina.ashurova@pb.com>
    Cc: TMSCore <TMSCore@pb.com>
    Subject: RE: Presort MercuryGate data in Snowflake

    David & Ishan,

    I feel Presort would also benefit from having the same GEC views where X1 & X3 are also mapped to the load and carrier performance tables.
    (i.e. CROSS_BU_DD_DB_PROD.TRANSPORTATION.TMS_CARRIER_INVOICES_VW and CROSS_BU_DD_DB_PROD.TRANSPORTATION.TMS_LOAD_PERFORMANCE_VW).

    Regards,

    Kayvan

    Attachments Open full size
  • Kayvan Shadpour commented
    February 06, 2023 16:33

    From: David Mejia <david.mejia@pb.com>
    Sent: Friday, February 3, 2023 10:42 AM
    To: Kristi Jones <Kristine.Jones@pb.com>; Kayvan Shadpour <Kayvan.Shadpour@pb.com>; Ishan Trikha <Ishan.Trikha@pb.com>; Irina Ashurova <irina.ashurova@pb.com>
    Cc: TMSCore <TMSCore@pb.com>
    Subject: Re: Presort MercuryGate data in Snowflake

    Hi Kristi,

    Presort TMS is live. Please see objects below:

    FDR_DB.FDR_TMS.PRESORT_ARRIVAL_AT_DEL_X1
    FDR_DB.FDR_TMS.PRESORT_ARRIVAL_AT_PICKUP_X3
    FDR_DB.FDR_TMS.PRESORT_CARRIER_INVOICE
    FDR_DB.FDR_TMS.PRESORT_CARRIER_PERFORMANCE
    FDR_DB.FDR_TMS.PRESORT_LOAD_PERFORMANCE
    FDR_DB.FDR_TMS.PRESORT_PLANNED_COST

    Please let us know how you current access TMS objects so we can arrange for your team to have access to this.

    PS: The tables should contain data from February 2023 onwards only.

    Thanks,

    David

    Attachments Open full size
  • Kayvan Shadpour commented
    January 23, 2023 14:23

    From: David Mejia <david.mejia@pb.com>
    Sent: Tuesday, January 17, 2023 1:20 PM
    To: Kayvan Shadpour <Kayvan.Shadpour@pb.com>; Ishan Trikha <Ishan.Trikha@pb.com>; Irina Ashurova <irina.ashurova@pb.com>
    Cc: TMSCore <TMSCore@pb.com>; Kristi Jones <Kristine.Jones@pb.com>
    Subject: Re: Presort MercuryGate data in Snowflake

    Hi Kristi,

    Just had a discussion with Kayvan and Ishan. Since Ishan built the initial data ingestion methodology and applied all the related (QA to maintain data quality and business rule) and since I also require all available data for PBs Transportation domain, we are going to re-using existing approach and start populating a separate list of tables specifically for Presort. By separating tables, we avoid any data issues that might arise and impact upstream, such as Network Ops team, BI, and data science team were they have built dashboards and data models.

    Once we have them ready, we will share the tables to your Presort DB, or use them from FDR_TMS schema, or copy the data to your own preferred DW (duplicating data copy). In either case, by duplicating our current approach we are able maintain data integrity because Ishan has put a lot of effort to fix and clean the data in the past.

    Kayvan and Ishan will have to scope on how long the work will need and then we can provide you an ETA.

    Let me know if you have any questions.

    Thanks,

    David

    Attachments Open full size
  • Kayvan Shadpour commented
    January 16, 2023 16:16

    Communicated with stake holders, outlining following next steps:

    • Snowflake team to confirm when Presort’s DW is ready

    • I will verify the draft data fields and tables and with Kristi & Ishan

    • I will create a new set of jobs to pull Presort data from MercuryGate. As mentioned to Kristi, there would be no customization (other than filtering Presort only data), considering the short period of time this solution is going to serve, as we work towards migrating to Blue Yonder TMS.

    • I will provide a set of sample Presort files to build the tables in Snowflake

    • Run cadence is expected to be similar to GEC’s (every 30 minutes, 24x7)

    • Output files are assumed to be:

      • Shipment

      • Load

      • X1

      • X3

      • Planned Cost

      • Carrier Invoice

    Attachments Open full size

Related issue