May 2011
How accurate and consistent is the data in your business system? Have you ever attempted to validate this?
These are not just hypothetical questions. They reflect incredibly thorny issues in various types of business systems projects. For example, in an Enterprise Resource Planning (ERP) implementation, the success of the project is often driven by the accuracy of the materials and operations in the product routings. The best ERP system in the world cannot overcome inaccurate or inconsistent data.
Another example comes from data warehouse (DW) and business intelligence (BI) projects. We have performed several of these recently, and the data being warehoused and analyzed proves "the devil is in the details."
Many people assume data is easy to manipulate because it is stored in databases. This assumption is true if one person has done all the data entry and maintenance for the entire life of a business system – in other words, the people (a single person), process (data entry and maintenance), and 
technology (business system) have been exactly the same, forever.
In real life, such conditions are rare. Various people move in and out of positions, and multiple positions may enter and maintain data. The technology in use today will likely have carried forward the data from prior business systems. These systems will certainly have had different capabilities and data structures, and there is often poor conversion of data between systems. The processes used by the people will vary widely – processes are wised to respond to changed circumstances, different systems over the years will require different processes, and each person will naturally interpret and perform things differently.
Thus, the accuracy and consistency of data is very much a product of the people, process, and technology in effect at a particular point in time. Until a transaction is processed, things are in a state of flux. Once the transaction is recorded, the data is frozen in the database – like a fossil in layers of sandstone. The output of the people, process, and technology remains, but the context is harder to interpret and reconstruct as time passes.
We have seen all this in several recent DW and BI projects. A large block of data records will be well-aligned with current practices – and then the data records suddenly change, indicating we have hit another combination of people, process, and technology. We always hit many different layers of data records in a typical project.
I have come to view each layer of data records as an unedited chapter in a book having multiple authors. The book is intended to follow a consistent, accurate storyline. Sometimes, the authors follow the same conventions and style – in this case, editing is easy. Often, each author's conventions diverge widely, making editing very hard.
Imagine going to press with an unedited manuscript – it would be a mess. No one would want to read such a book. The story, grammar, and punctuation would all change from chapter to chapter. An editor takes all this and makes it consistent.
The same situation occurs loading unedited data into a data warehouse. You may be able to shoehorn the data into a common structure – and it would be completely worthless because of the inconsistent layers of data records.
Editing data is hard and time-consuming. Various technology tools can help identify inconsistent or inaccurate data – yet it always comes back to a person making judgments to edit these differences and get data to tell its story. Once data has been edited, it can be loaded into the data warehouse – the edited manuscript can now be printed. The edited data in the DW can then be used by various BI tools to produce the "executive summary" of the book, revealing new insights and trends to executives and managers.
What is the data stored in your business systems trying to tell you? Is it speaking to you consistently and accurately, or does it need help to get it to clearly tell its story?
Todd L. Herman
Client Project Update:
I am pleased to provide you with an update on our client projects. Projects we have completed over this period are...
Managing ERP System Implementation
Continued managing the implementation of this client's ERP system, identifying and addressing data quality and data conversion issues, managing tasks and expectations for client and vendor personnel, bridging and supplementing the vendor's product-specific knowledge, and proposing options to resolve technical and process issues affecting the budget and timeline.
Analyzing Data to Enhance Revenues
Analyzed the records of several municipalities against those of utility companies providing services to identify additional franchise tax, resulting in greater revenue for the municipalities.
Identify Causes of Unclassified Variances
Reviewed the processes and systems used to capture and classify production and sourcing variances in this apparel company, identifying many potential causes of unclassified variances and recommending changes to better capture these, and also proposing changes to the Information Systems and Operations Accounting policies to promote a consistent and controlled environment for all transaction processing.
Integrating Credit Cards to Business Systems
Analyzed the desired business processes, worked with credit card reader and processing vendors, and developed the related routines to integrate credit card acceptance into the client's order processing and financial systems, helping this health care company better respond to patient desires for multiple payment options.
Analyzing Managed Care Recoveries
Assessed available data and design options to analyze net amounts recovered from various health insurance providers, and developed prototypes of the routines and reports to perform these, helping this medical services company better assess service profitability and improve price negotiations.
Develop a Data Warehouse & Design Reporting Options
Oversaw all data loading and testing, and identified and resolved data consistency and similar issues, flushing out and addressing process and systems issues -both historically to correct incorrect information, and prospectively to prevent these and similar future errors -while working closely with end users and the Information Technology group.
Future projects we are planning include...
Managing ERP System Implementation
Complete managing the first phase of this client's ERP system implementation, identifying and resolving data conversion and process issues, ensuring smooth navigation of the final tasks, rolling out new processes to the users, and quickly addressing post-implementation issues.
Helping Local Governments Find Revenue
Develop sophisticated multi-database screening, matching, and cross-referencing routines to simultaneously evaluate multiple assessment criteria, identifying additional revenue opportunities for local governments.
Technician Efficiency & Patient Self Check-In
Document and review the existing workflow for patient-facing technicians in this medical services company, identifying process and technology improvements, including options to allow patients to check themselves in and to enter insurance and payment information.
MRP System Revisions for Offshore Production Changes
Work with client line-of-business and systems personnel to identify and resolve several errors in cost sheet calculations and inventory transfer processing in their Material Requirements Planning (MRP) system, reviewing infrequently used programs with complex operations and calculations to find small discrepancies, and helping the client better control inventory movement.
Develop a Data Warehouse & Design Reporting Options
Complete all data loading into the data warehouse and Online Analytical Processing (OLAP) cube, develop and roll out various Business Intelligence (BI) options -including visualization, reporting, and summary dashboard -for this project, and resolve post-implementation issues, delivering many new easy-to-use metrics, analyses, and drill-down/drill-around capability to executives and managers, helping them meet strategic and operational goals more efficiently and effectively.
Business Needs Guide System Selection
Manage the effort to identify key business and user needs in an up-to-date Enterprise Resource Planning (ERP) system to replace several fragmented and outdated systems -including a custom-developed shop floor system, an accounting system, a purchasing system, and a payroll system -helping this manufacturer better assess its options and providing objective advice on these.
