Data Quality

At eprentise, we believe data quality is defined as complete, consistent, and correct data that is accessible to both internal and external users as they need it. Though the benefits to cleansing master data have been quantified extensively in the literature, current Master Data Management (MDM) initiatives as undertaken by many companies are the tip of the iceberg, only focused on the identification of duplicate customers. eprentise Data Quality software is very tightly integrated with Oracle E-Business Suite and begins the process of achieving enterprise-wide complete, consistent and correct data at the underlying data infrastructure level, and then percolates changed data everywhere it is used in the EBS database. That means that reports and extracts of data (for a data warehouse or to import into another system), even on historical data, are consistent, that there is no reconciliation between different modules, organizations, or database instances, and that there is no maintenance required to keep the data in sync. eprentise Data Quality software may be used in a single database or across multiple databases.

When analyzing a database, eprentise starts by understanding all database objects and making them consistent. Through eprentise Metadata Analysis process, every table and column is analyzed. If there are two different databases, a comparison is run to determine if indexes, triggers, packages, forms, tables, columns, and constraints are the same across the database instances. If they are not the same, a target is defined and the source database objects take the characteristics of the target database. Additionally, Metadata Analysis has a “rules tester” that validates every constraint against every row of data. For example, if there is a unique constraint, and there is more than one record existing in the database with the data that is supposed to be unique, the constraint is broken. If there is a foreign key relationship and the column in the related parent row is null, the constraint is broken. Particularly in a situation where databases are going to be merged, all objects must be consistent across all the databases to be merged. After “fixing” the database objects so that they are complete and consistent across databases, eprentise begins to work on the data by categorizing the EBS data into four different levels.

Seed Data – data that comes with the application when it is installed such as standard currencies. The currency is installed (based on the user selection) at the time of install. As is the case with currency, some seed data may vary by localization or version of the application that is installed, among other factors. Seed data is embedded in the application and generally can’t be changed.

Configuration Data – parameters for the application that are set up by the user. Configuration data may include a chart of accounts, a supplier’s payment terms, or a list of diagnostic codes. For example, the user may configure the units of measure or quantity to display as “doz” while another instance may have this same property defined as “dz”. Configuration data is used everywhere in the system. Transaction data relies on configuration and master data.

Master Data – generally speaking, this constitutes customers, suppliers, employees and products – the building blocks of a company’s information system that is specific to the business of that company.

Transaction Data – records of the operation of the business processes. Examples of transactions include orders, invoices, or payment records, payroll, expense reports, or sales, etc.

Beginning at the seed data level, eprentise Metadata Analysis compares the seed data across database instances. Seed data might be different because different patch levels or different localizations were applied to the database. Again, the objective is to make all data consistent. With this in mind, eprentise determines whether data exists in the source, but not in the target, exists in the target but not in the source, is in both the target and the source, but is different, or whether the seed data in the source and target are identical. eprentise either adds data to the target, changes the data so that it is consistent, or deletes identical data.

After the seed data analysis is complete, eprentise compares all the configuration data. When looking at the set-up data, the eprentise comparative reports facilitate analysis of considerations such as “Does the user know which choice to select from a drop-down list”, “Is the data format consistent everywhere?”, “Is the data only reflected in one place?” and “Is the meaning of the choice clear and consistent?” When a user selects the calendar “Period 7” anywhere in the system, does it have the same start and end date? Are the meanings of “Net 30” the same everywhere? Are phone numbers in the same format? Are there redundant choices for units of measure like “pound”, “lb”, and “#” that may have the same meaning? Identifying data differences and inconsistencies are only the first step in the process toward achieving enterprise data quality. eprentise has built in standardization rules to incorporate standard abbreviations and punctuation into the cleansing of data. Users may also import lists of corporate standards or ISO standards into the standardization process. Finally, after all the configuration data is standardized, eprentise resolves duplicate configuration data and percolates the changed data everywhere in the E-Business Suite. That means that even old invoices or orders will be changed to reflect the cleaned and transformed configuration data.

Stepping through the eprentise Data Quality solution, the next step is to identify and resolve duplicate master data. Duplicates exist wherever two or more database records represent the same real-world entity (for example, a customer or a product). The business problem that duplicates present is that the database is telling the enterprise that different real-world entities exist corresponding to these different records. The redundancy and inconsistency among the different records leads the enterprise to treat that single real-world entity redundantly and inconsistently, which causes various kinds of damage to customer relations and the bottom line.

A set of two or more database records that all represent a single real-world entity is called a duplicate set. When duplicate management is complete, all of the duplicate sets have been detected and resolved. A duplicate set is resolved when all of its records have been merged to create a single new database record, which thereafter will be the one and only representation of the one real-world entity. Later in the eprentise duplicate management process, each duplicate set will be resolved into a single record. This new single record replaces all duplicate records everywhere in the E-Business Suite. A duplicate rule is made up of all the criteria that groups the records in a given entity type into duplicate sets. eprentise Data Quality understands all the data everywhere in EBS and allows you to identify duplicate criteria using any combination of attributes from any module or table at any level of the data. For example, duplicate customers may not be identified only by name or even name and taxpayer number, but when you consider a billing address or a telephone number, or even a contact name, you may find that customers are the same. Similarly, determining that in a particular city, an address of 100 Main St South is a duplicate of 100 S. Main Street may provide more granular information to assist in identifying duplicate master data.

Once the duplicate sets are identified, then business differences among the duplicate records have to be resolved. Customers should have the same credit limits, discounts, and pricing policies everywhere in the system. Product catalogs should have the same descriptions, inventory items should have the same costing methods, and the same assets should have the same depreciation schedules.

Identification of potential duplicates is only one piece of solving data quality issues. The real problem is making sure that the data everywhere in the database is consistent, correct, and complete. In one database, John Smith may be customer number 100. In another database, Mary Doe is customer number 100. If you determine that Mary and John are not duplicates, if merging databases all of the ID numbers need to be changed. Since the ID number is used as a primary key for thousands of tables in E-Business Suite, changing the primary key and changing all the referenced data is a major undertaking, Making a change to an identifier incorrectly or in the wrong sequence risks corrupting the relational integrity of the entire database. eprentise uses its built-in knowledge base to maintain the correct sequence of changes and to pinpoint all the places where the data needs to be changed. Once the seed data, configuration data, and master data are cleaned, eprentise verifies that all of the changes are made to every transaction, everywhere in the system. The enterprise data in EBS is complete, consistent, correct, and available to the users.