Merging Legacy and New POS Dataset

imelendez · November 2019

Hello, Experts!

I am reaching out in hopes that I get some guidance on the best approach for building out an ETL.

Our company is undergoing a point of sale transition and things are going well. We currently have legacy data in one dataset and new data flowing into Domo separately. My CEO is wanting to see week by week sales data for the year for both datasets stitched together. Essentially, he wants visibility for data all time by week regardless of data coming from different datasets.

What I have done:

I have created a stored proc on my legacy dataset to crank out the weekly data from the beginning of this year to now. When a store stops using the legacy system no new data is written to the database so the data does not appear in the result set of the query in my stored proc. I have this dataset pumping into Domo via WB.

I have instructed our new POS partner to create the same stored proc but from the time we went live with the new system and basically mimic the same data. We are outputting this via MySQL via SSH connector.

I have also created an ETL where I am bringing in the legacy dataset as an input and the new POS dataset as input then joining them together after I filter out then join from our new POS dataset if a store is live (a flag in new dataset) and then joining to the legacy dataset to output the data I want to see.

My two joins are both inner joins.

Am I headed in the right direction or is there a better way to do this? Ultimately, what I want to see is something like this (or maybe it needs to be presented differently):

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 001 | 1 | 14,000 | 0 |

-----------------------------------------------------------------------------------------

| 001 | ... | ... | ... |

-----------------------------------------------------------------------------------------

| 001 | 45 | 0 | 20,000 |

-----------------------------------------------------------------------------------------

Merging Legacy and New POS Dataset

Categories