How to Capture Transactional Data Changes in Domo

tpmonahan

We are loading sales order and purchase order data via Workbench from our ERP to report on booked orders, open orders, etc. However, our sales orders may be opened for a long period of time and frequently change. For example, the customer may revise their quantities ordered throughout the month prior to shipment. The only thing that changes is the value of the 'Quantity' field on the sales order line item.

 

As a complete newbie, I'm looking for advice as to the best way to capture these trasactional changes? I would prefer not to do a full dataset replacement during each load as the dataset is fairly large (and growing) and we would like to have relatively frequent updates. If I append data, I'm afraid I will get duplicates.

 

Any thoughts on the best way to handle this?

Domo_Diesel

Hi tpmonahan,

 

If you want to avoid a full replace, then you could try snapshotting while appending.  This way, using a dataflow, for each unique order/id you could select the latest batch date or the max batch id.  This will pull only the latest data for each unique order/id, giving you accurate quantity amounts.  

 

Let me know if you want assistance setting this process up.

 

Best,

 

Clarke

Domo_Diesel

Hi tpmonahan,

 

If you want to avoid a full replace, then you could try snapshotting while appending.  This way, using a dataflow, for each unique order/id you could select the latest batch date or the max batch id.  This will pull only the latest data for each unique order/id, giving you accurate quantity amounts.  

 

Let me know if you want assistance setting this process up.

 

Best,

 

Clarke

kshah008

@tpmonahan, did Clarke's response help answer your question?

tpmonahan

Thanks, Clarke. We'll try that out and see how things go.

canioacaputo

Personally I would want to track when a customer changes.  That too could be interesting to see and answer the question how ofter do our customers change thier orders.  To do this you would just make a history table that tracks the changes.  So, you have a current table that holds the current most recent data, then using Domo's Redshift you set up some sql that appends data to a second table that is capturing changes in the order amounts and captures the date.

 

You shouldn't have to do a full data set replacement, you would just do what's called an "append".

 

Domo's suggestion of selecting data on batch ID is workable but you clutter up your database and this will create problems later on when you want to do historical analysis.  

 

Hope that helps.