Patrick Miskill’s series on ERP management continues with a closer look at how the most powerful tool you can get for managing your system, the DMT, works alongside the most important feature, the Method of Manufacture, or MOM. That’s right, love your MOM is stll the best advice ever. 

The Epicor ERP system, like similar systems from SAP or Oracle, contains hundreds of tables in a single relational database.  The database is almost irrelevant; however, the logical “rules” regarding how the data exists in the database is critical.  Generally speaking, ERP systems consist of simple code (e.g. Unit of Measure) tables, then more complex entity tables (e.g. Suppliers) and finally transaction‐based tables.  All these data must follow rules for “referential integrity” and the underlying database engine must keep track of the data and enforce the rules whether we are talking a few dozen rows of data in a single table or millions of rows of data in hundreds of tables.

The Ease Of Epicor DMT

The beauty of these “back door” tools is that they are quick and easy to load and run. And they also provide instant feedback to the user as to the program’s progress. Either a row insert succeeds or fails.  You can test a single row in just a few seconds; it if fails, the Data Migration Tool provides an error log telling you why it failed.  Error messages can be somewhat cryptic, but usually, it is straight­‐forward.  E.g. you are missing a required column (e.g. the “Company” column). And when all else fails, you can always go enter a row via the standard product then look at the resulting data in the underlying table(s) to deduce what the business object requires. Each DMT template lists the required and optional columns with a basic description of the data. As long as you follow the “rules” the data loads from the spreadsheet, just as if you keyed it manually into the system.

Some data relations, however, are quite complex and even the data template from DMT will cause you “instructional anxiety” (that feeling you get when you read an IKEA instruction guide and say, “What the heck?”). Rule­‐of‐thumb:  Just follow the rules, get one data set to load OK, then attempt a larger data set until you get it right.  Big mistake: Run a spreadsheet with tons of rows without testing and end up with garbage in the system, or better yet, a crashed system!

Case‐in‐point:  Method of Manufacture 

Epicor stores the Method of Manufacture (MOM) for a part as a series of operations and materials linked to the finished goods Part and Revision. It is the “how­‐to” cookbook to make something as simple as a screwdriver or as complex as a 747. It may consist of a single Operation with one or two raw material parts or it can be multiple sub­‐assemblies, each with their own operations and materials. It provides not only the “how­‐to” instructions to build something but it also contains estimates for the labor and burden rates to perform the operations.  These estimates are critical to any manufacturer since they provide the baseline for each manufacturing job in determining how well the actual product conforms to the method and whether the actual costs are in line with the estimated costs.

In the Epicor Production Management module, before a single MOM is created, all dependent data must be loaded into the system.  Example, all raw material parts must be entered, each with a given unit cost. All machine resources used in the manufacturing process must be loaded and activated.  All labor resources (Employees) must be entered along with their labor and burden rates. All operations must be defined and assigned one or more resources. It is an enormous engineering task to get these data entered and all the relationships established properly so that the Epicor ERP system’s production jobs can begin tracking and documenting each step of the MOM, including capturing all related costs for management review.  When done correctly, a company can then take advantage of the “Holy Grail” feature of ERP: MRP (Manufacturing Resource Planning, where the system can analyze demand and forecast part, labor and machine requirements for meeting that demand).

Of course just as in life, “things change” like the cost of raw materials, labor rates and then you always have “Acts of God.”  For example when the earthquake off the coast of Japan struck in 2011, the resulting tsunami wiped out many manufacturers along the western coast of the country, leaving companies like Toyota in a serious bind as their “just­‐in‐time” processes ran out of parts and sub­-assemblies needed to manufacture cars and trucks. Imagine the sleepless nights that production managers must have had, re­-engineering their MOMs and seeing their MRP rules flushed down the proverbial toilet!

About Encompass Solutions

Encompass Solutions is a business and software consulting firm that specializes in ERP systems, EDI, and Managed Services support for Manufacturers and Distributors. Serving small and medium-sized businesses since 2001, Encompass modernizes operations and automates processes for hundreds of customers across the globe. Whether undertaking full-scale implementation, integration, and renovation of existing systems, Encompass provides a specialized approach to every client’s needs. By identifying customer requirements and addressing them with the right solutions, we ensure our clients are equipped to match the pace of Industry.


Industry veteran Patrick Miskill is bringing us a series of three blog posts about the nuts and bolts of the ERP system, namely what the deal is with data migration tools. For those of you who have been craving pure technical detail, these are for you. For those of you whose eyes glaze over in the face of phrases like ‘logical hierarchy’ or ‘object‐oriented‐design,’ don’t worry! Patrick lays it out as clearly as you’ll ever read it. 

After thirty years in the IT field, it becomes a lot harder remembering all of the software you’ve laid your eyes and hands on. I have used databases from Microsoft, IBM, Oracle, and other companies—some that don’t exist today, or have been swallowed up by the others, or some that no one remembers at all. One thing has remained the same though: Input‐Process‐Output.  Support tools come and go, getting relabeled, or “improved”, or put out to pasture, and I’ve learned to not believe the hype about some tools: anything other than Input‐Process­‐Output is usually lipstick on a pig. If it’s fancy with fancy words and it’s supported completely by the vendor it might be the real deal; otherwise, it’s junk and a complete waste of your valuable time.

Encompass, Epicor, And Data Migration Tools

Encompass is an Epicor Certified Platinum Partner because Epicor and its Data Migration Tool (DMT) live up to the hype. It’s used for not only loading data but for also updating and deleting existing Epicor data, and makes transitioning and overhaul incredibly easy. In a nutshell, the DMT uses spreadsheet templates that work just like the way data is organized within the Epicor ERP system. If you can use Excel (and who can’t?) you can load thousands of thousands of lines of information into the ERP.

Outside of the nutshell, well…

The DMT is designed in a logical hierarchy that mimics the data relations in the ERP system. It also takes advantage of the object‐oriented design of Epicor ERP and calls the very same programs (“business objects”) to complete a transaction, whether it is loading a single row into a simple code table, or loading thousands of rows into dozens of related tables. A user loads the raw data into an Excel spreadsheet following the “rules” for data formatting then runs the specific DMT program for that relation.  DMT reads the rows of Excel-based data and one‐by‐one, loads the data into Epicor ERP.

The bottom line: DMT is fully supported by Epicor and over the past 4 years I’ve worked with it, it gets better and easier to use. Nothing else in my thirty years has come close. DMT is absolutely essential to any Epicor ERP customer.

For my most recent real-world example that shows how flexible DMT is, I helped a client that had an on­‐going need to update their Method of Manufacture/MOMs based on business decisions related to the raw materials they used to build fabric belts. Let me know how fun this sounds: Imagine manufacturing a fabric belt 84 inches wide over 3,000 feet in length; the raw materials (giant spools of nylon and/or polyester thread) can weigh over two tons and can consume 100 times the length of the final product.

As market conditions changed and different raw materials were chosen to build the belts, the MOMs would have to be updated to reflect that change.

The nightmare I’m sure you’re imaging never came to be, thanks to the Epicor DMT. From loading open invoices to performing regular updates, data migration tools vastly simplified this time-­‐consuming and manually-­‐intensive task.  And, as more systems move to the cloud and IT resources become more of a commodity, it is inevitable that a company relies upon a technically savvy user (yes, we all know how many hats management expects us to wear!) to take on the role of “Dr. Data.”  DMT is built for exactly this purpose; it allows any user to easily insert, update or delete data in the underlying database without having to use the standard product programs.

About Encompass Solutions

Encompass Solutions is a business and software consulting firm that specializes in ERP systems, EDI, and Managed Services support for Manufacturers and Distributors. Serving small and medium-sized businesses since 2001, Encompass modernizes operations and automates processes for hundreds of customers across the globe. Whether undertaking full-scale implementation, integration, and renovation of existing systems, Encompass provides a specialized approach to every client’s needs. By identifying customer requirements and addressing them with the right solutions, we ensure our clients are equipped to match the pace of Industry.


Database Health Check: Epicor Data Scrubber Utility

It’s good practice to check that your database health is at an appropriate level on a periodic basis. Over time, it’s possible to build up orphaned records, detail records that contain values that do not total to summary records, and other things that can cause performance and data integrity issues.

As long as you are current on maintenance, Epicor supply a tool that lets you check that your database is in tip-top condition, and will provide programs to resolve any issues that are uncovered. This is recommended not only before an upgrade to ensure things go smoothly but on a periodic basis for optimal database health.

The data scrubber utility is a collection of commonly applied data fixes, which the support technical team has gathered together into a package to do a “health check” of a customer’s system. They are continuously adding to it as they find additional opportunities.

The process:

  • The customer would log an issue requesting from support, we can do this under our customer’s site ID
    or Run the program against the database of choice
  • Each sub-routine will stop when it finds an invalid record and create an entry in a log file, then move on to the next routine (no tracking of the specific errors at this point)
  • Generates a log file which gets sent back to support.  Support will then send the relevant fix programs back to the customer
  • The customer runs each fix program, which will show all the relevant records and provide a chance to fix all, some or none of the records. This gives the customer enough information to troubleshoot the cause of the data problem and to fix it at source

This collection of assessment algorithms is new in the last few months, but the ‘fix it’ programs have been available individually for some time now.

An assessment should be considered as a maintenance routine and should be run for database health for all customers periodically, anyone going through a Business Process Review and anyone considering an Upgrade.

We will consider using this utility to get ahead of any E10 upgrades for database health and troubleshooting. We want to ensure that our customers upgrade with ‘good’ data. For larger databases, it may take a good couple of days, but it is really worth it!

The utility was discussed in several sessions at Epicor’s Insights 2015, so many of our customers will have heard about it and maybe asking themselves questions.

About Encompass Solutions

Encompass Solutions is a business and software consulting firm that specializes in ERP systems, EDI, and Managed Services support for Manufacturers and Distributors. Serving small and medium-sized businesses since 2001, Encompass modernizes operations and automates processes for hundreds of customers across the globe. Whether undertaking full-scale implementation, integration, and renovation of existing systems, Encompass provides a specialized approach to every client’s needs. By identifying customer requirements and addressing them with the right solutions, we ensure our clients are equipped to match the pace of Industry.