ERP Business Analysts
ERP Data Migration
ERP Reasons to Get One
ERP Forces Changes in Business Rules
ERP One ERP is the same as any other ERP
ERP Security is OXYMORON
ERP The Whole is Bigger than the Parts
Most ERP software products like SAP, PeopleSoft, Oracle, Baan, etc., are really old file based architectures mapped to RDBMS tables with integrity and domain checks based in software application layer and not in the RDBMS. This causes many programming errors and that causes software patches. Most ERP software was designed a generation ago and does not use logical constructs like a relational design and most RDBMS constraints, instead, ERP companies rely on programmers to never make a mistake. Hundreds of thousands of hours went into these systems and so to not lose this investment the same old design is carried forward perpetuating a difficult system.
This is a modest proposal to test integrity and domain using RDBMS constraints and a plea that the ERP companies could actually supply these tests to the customer. This does not fix the problem of ERP software integrity but could be a useful tool. The root of the problem is still the poor mapping of the file based architecture to the RDBMS systems and relying on programmers to write checks constraints, domain constraints and relations in the application software layer.
The ERP systems such as SAP, PeopleSoft, Oracle, Baan, etc., have horrible track records as "patch ware" what I call crusty software that has many patches that are applied each week. The patches repeatedly break other things in domains and integrity and the administration of these products is a continuous round of patching, testing, patching, testing, patching, ..., and maybe some deployment.
The customer administration of this mess is very difficult and usually many patch levels are in testing environments waiting to be installed in the production environment. The software does not usually come with any tests to help the customer deploy this patch ware on the data set the customer uses. Instead the customer usually develops a suite of tests by trial and mostly error on the dataset created when the customer has deployed the system with all its customizations.
Domain constraints to check value domains and referential constraints to check the data in one table that is related to other tables would be a valuable tool for the customer. The ERP company could supply the customer with a set of database constraints that would act as test assertions. The customer could then patch the weak, cruddy ERP application code in a test environment with a copy of production data and apply the database constraints to help verify the patch works. For example, a referential constraint to check that order numbers in the "order shipped" table exist in the "order entered" table is an easy to write constraint that might help ERP customers. A domain constraint could use date range check constraints to check that calendar quarter data is in the quarter date range. Other domain constraints can check subsets of values across other domains and many types of data verification can be quickly and easily done.
The constraint testing may have to occur at stable states during a transaction as many ERP modules will violate referential constraints during a transaction. Even with this problem the testing would be useful and many domain constraints would not have a problem.
RDBMS constraints cannot test all problems of course. And because the mapping of the file based architecture to the RDBMS is poor it may not be able to test some data and a lot of patch testing would not be covered. But constraint testing is at least a start and an easily implemented one that needs no special software or training but uses the existing RDBMS and SQL tools that any customer installation already owns and has the expertise to run.
A friend questioned why there is so little use of constraints in most ERP software, even when they have been widely available for more than 10 years in RDBMS products. One reason may be secrecy, a well defined data model would expose the ERP rules and programming to analysis, it is now a black box bought for a lot of money. More probably the design, transactions and architecture of the ERPs are so hacked that constraints cannot be easily added without a total redesign. Redesign of current modules using better relational design would not sell new licenses, it is the latest acronym of the month that sells like "web enabled CRM!" so as a result the ERP companies just add another layer to the house of cards built on a shaky data model.
Once an ERP company has a customer and the ERP system is in production it is difficult to change to another system the customer is a captive. Maintenance and testing is usually an afterthought to most companies, the level of experience in software engineering of a company buying and implementing an ERP product is far less than a company making software, it is one of the hidden costs of operation and risk that is commonly overlooked by a company implementing an ERP. Another problem is that the ERP companies appear to do little testing of any kind in their own programming shops. I think that like Microsoft it is new "features" not quality that drives ERP development. Customers really buy beta releases and the circle of hell begins, test, patch, test, patch, ....