Using Change Logs on Jobs? Do THIS trick to make them better!

Kinetic ERP
, ,
1

Tim’s weekly rant (warning… I am getting on my soapbox):
image

SO, your company insists on having change logs… and you have them on the JOB tables (JobHead, JobMtl, JobOpr, JobOpDtl, JobAsmbl)… and they want to log EVERYTHING.

Then, you might call tech support and complain that your Change Logs are growing too fast, or that you have seen some performance degradation on large orders or jobs, or that MRP crashed last night because your disk ran out of space (because of the change logs). Or, you see an entry in the trace log showing that your Data BPM for change logs is taking longer than you desire. (these are all real stories. Names are changed to protect the innocent).

Well…
Why do you do that… Stop It! (see Stop-it post by yours truly)

There are numerous reasons why you should not log every change to every field in every table… Why you should be MORE SELECTIVE in what you log…
Bad practice examples for change logs:

  1. logging changes to the LastChangeDate or LastChangeTime field. Note that the change log already logs the last date/time… obviously every time you change something the last change date/time will change, so logging it is redundant.
  2. Logging ADDITIONS. in many cases (but not all) there is already a creation date/time field, and so logging all the fields that were “changed” when you added the record is not necessary.
  3. REALLY BAD: logging changes to the job tables on UNFIRM JOBS. This egregious error in logic causes multiple bad things to happen
  • MRP is the only thing that can create unfirm jobs
  • MRP does multiple changes to all unfirm jobs each run, and this slows down MRP tracking changes to a job that is unfirm and will be deleted tomorrow when you rerun MRP
  • Change logs can get incredibly large especially if you have large MOMs (BOM + BOO/Routing) because MRP will create a log entry for each material added, each operation added, and then when the job is scheduled, all the entries are touched again, so therefore the change logs are hit again.

BEST practise for JOBS (if you must track changes) would be to only track changes for jobs that are FIRM. I know that this can get tricky, because change log for the job tables (JobMtl, JobOpr, JObOpDtl) would need to lookup the JobHead to find out the status, but this is very important to do.

“How do you do this?” you ask?.. well, Change logs are created in Data BPMs, and you can add a CONDITION widget at the beginning of the BPM that checks the record BEFORE creating a change log entry… just check to make sure that you need to create the log entry before doing so.

OH… and in the change log widget itself, to be more selective about the fields you are tracking… maybe track changes to the fields that are “important” to track. Part numbers, quantities, Hours, Operation ID, etc. but not “every” field, and especially not the last modified date/time.

Of course, this last rule applies to ALL tables, not just the Job tables.

  • customer: track name, address, salesperson, credit hold, credit limit, etc
  • orders: track customer, part #, quantity, commission rate, unit cost, discount… but dont bother tracking the order number and line item because those cannot change.
  • PO and QUOTE… same as orders

OK… end of rant… I will get off of my soapbox, and return back to work.
Soapbox is all yours:
image

4 Likes
2

image

Thanks Tim, I will.

Application authors insist on logging to the server and even the same database as the application.

Well…

Why do we do that… Stop it!

There are numerous reasons why we should not log changes to same server and/or database:

  • Lack of Immutability
  • Bloated Database Size
  • System Failures lose logs
  • Have to grant developers access to servers which violates the least-privilege rule

BEST practice for logging:

  • After a durable write to local storage, provide a sink to log to remote immutable storage
  • Do not log sensitive information
  • Provide access to the logs, metrics, and traces to devs and users on the remote sink instead of the server

Monitoring is a good DevOps practice.

End of rant. Next!!!

2 Likes
3

Thanks @timshuwy

This is causing me to analyze our current change tracking, the thoughts on unfirm jobs is particularly eye opening for me personally.

I myself would find similar write ups on other tables helpful (If there is enough there to talk about)

Once the ‘damage’ is done, what options do we have to clear/archive existing change data that is old enough to no longer be relevant?

4

Unfirm jobs is really the only big heavy hitter that affects MRP speed. but anything that does mass updates to tables could be affected by change logs.
Also, some Apps like Order Entry can have reduction in performance if you log every change to every field in OrderHed, OrderDtl, and OrderRel. Again, I recommend only tracking what NEEDS to be tracked. ie…

  • OrderHed: track Customer, Terms, Salespersons, shipto, billto, but not all the other ancillary fields like order total that cannot be manually changed. Also, no need to track the order number because it will never change.
  • OrderDtl: Track Part, description, qty, price, discount, commission. No need to track the Order Number and line number, because it will never change.
  • OrderRel: Track ShipDate, Qty, Site, Warehouse… maybe a few others, but again… keep it minimal. If you do change shipping address at the release, and/or do one time shipto, then maybe track those too. Again, no need to track the order number, line number and release number as those will never change, but it will cause extra work if you choose to track them.

I have found that when queried, most people CAN be properly selective… if you go to your users and say “you can track 10 fields, which 10 do you want”, you will find that they might only want 6.

AND in all the above cases, only track CHANGES (RowMod = “U” as a filter)
Here is an example of OrderRel: Note how I dont track the SHIPPED qty… this is a system updated field, and there is a history already tracked in the packslip already… no need to have an additional change log for this change.

image
image613×593 17.2 KB

And here, you can see my condition to only look at UPDATED rows: You of course could put additional filters here to only log updated rows IF they also have certain field values set:

image
image542×506 28.2 KB

1 Like
5

You can also add a check to see if MRP is making the change and don’t log if it is - I figure if I’m troubleshooting MRP I’m into MRP logs instead of change logs. We added testing for GetMethod MrpExpCD - if it is Mrp then don’t even run the update.
Jenn

1 Like
6

Excellent advice Tim! It never occurred to me to add condition widgets before the Change Log widget… will be implementing that asap.

1 Like