Patrick Miskill’s technical series on ERP tools wraps up today with a look at all the variables you’ll run into when building your ERP system and how learning about your errors is even more important than learning about your successes.
My actual experience with a manufacturer of fabric belts for large conveyor systems (Fenner Dunlop Americas) saw two uses of DMT: One for a major upgrade, a second for updating their Method of Manufacture/MOMs. In the ﬁrst case, we used DMT scripts to upload data from an older version of Epicor to a newer one. Similar to a new installation, we pulled data out of the source system and formatted it in several dozen spreadsheets for uploading to the new target system. Knowing how the data was structured in the old Epicor system was a big help. For new Epicor customers, extracting and massaging the data from a non‐Epicor legacy system is quite a project; in such cases DMT is an invaluable tool.
In the standard product, an engineer would have to run a “where‐used” report to determine which part revisions used the raw material; it could be just a few dozen or it could be over 100. Each part revision would require changing in the Engineering Workbench, one by one. It is a time consuming and manually intensive process. So we developed a process starting with a Business Activity Query (BAQ) which went out and extracted only the MOMs aﬀected by the change. The BAQ was developed speciﬁcally for subsequent use in DMT, i.e. column headings matched those required by DMT, we extracted all columns required by DMT (because we already knew the “rules” for MOMs) and only those optional columns we needed to update. We then entered Excel column formulas speciﬁc to the change(s). E.g. if the old raw material was a polyester yarn and the new material was a nylon yarn, unit weights and other factors speciﬁc to the textile industry (ever hear of a “twist contraction” variable?) could be entered into a formula that resulted in the new variables for the MOM.
Once the extract and validation of the Excel data was completed, it was simple to format the ﬁnal output for use by DMT. Testing a few rows in a copy of the LIVE database, we could easily validate the ﬁnal updated MOMs. In fact, we could use the original BAQ extract programs to look at the updated data in the TEST system and quickly evaluate the results. Once we got the process down, we turned what would have been a 100+ hours of manual updates in Epicor to an electronic process that ran in just a few hours. Once documented, the process could be reviewed, validated (the client was ISO compliant) and repeated as needed.
Of course like any software tool, DMT has its quirks. We learned the hard way that despite how illogical a data template may appear, it was critical to supply the required information in exactly the format requested. And interpreting the error logs is sometimes an exercise in the art of editing. I highly recommend acquiring and using the free “Notepad++” data editing tool; it can help you read a jumbled up error log ﬁle and it is essential to always check the ﬁnal DMT ﬁle you create from Excel, prior to uploading it to DMT. A common mistake is to save an Excel spreadsheet as a comma‐separated variable ﬁle and the resultant ASCII ﬁle has extraneous commas and/or columns or just plain garbage. And if your data (e.g. Descriptions) contain commas, you must enclose them in double‐quotes (or just use a wacky character as the delimiter‐even safer).
Just remember that “with great power comes great responsibility…”. So before you change data in a system, make darn sure you’ve chosen the correct target database, since DMT will not issue any warning whatsoever if you update LIVE instead of TEST (not that I’ve EVER made that mistake!) and will merrily chug along, processing the data with the full expectation that the operator knows what the target database should be.
This is why it is CRITICAL to always test your DMT script before you run it in a production environment. Note that if you don’t have a recent copy of LIVE for test purposes, you should seek out technical services that can assist you (usually by taking a copy of your most recent backup of LIVE and bringing it up on a similar environment) this is something that Encompass Solutions as an Epicor certified partner does on a regular basis and offers as a managed service.