Oracle E-Business Suite (EBS) R12 is a significant new version with valuable new features and capabilities. Although there is an upgrade path from EBS 11i to R12, most companies reimplement R12 and migrate the data from their 11i instance. Reimplementation can be a complex project but it also gives them the option to improve their implementation.

When transitioning from EBS R12.1 to R12.2 companies generally perform an inplace upgrade. One of our customer was upgrading from EBS R12.1 to R12.2 and wanted to verify that the upgrade did not cause any issues to the data in their data warehouse. While testing the data warehouse and the dashboards can help identify data issues during the upgrade, it is important to test the data in the EBS R12 instance from the backend. This type of testing is called database testing.

Database Testing for Reimplementation (eg. 11i to R12 transition)

Database (or Backend) testing from the reimplementation project is similar to the testing of Data Migration projects where data gets migrated from a legacy application to a new application.

The main goals of the Reimplementation testing are :

  • Verify that the data has been fully migrated from 11i to the R12 instance. Most customers want to perform 100% data validation which may be required in regulated industries such as Pharma and Financial.
  • Verify the referential data integrity after the migration from 11i to R12 instance. For example, some of the records in the child table may get mapped to a different parent record or become orphan records during the migration.
  • In case of any data cleanup during the migration verify that the R12 data is following the data accuracy and consistency rules laid out for the cleanup effort.

Our ETL and Data testing tool, ETL Validator comes with several different types of test cases and test plans for simplifying and automating the Database testing for reimplementation project (or data migration testing)

  • Query Compare Test Case : Makes it easy to compare large volumes of data between 11i and R12 databases (backend).
  • Data Profile Test Case : Compares the aggregates (or checksum) for the data between 11i and R12 databases.
  • Foreign Key Test Plan : Validates the referential integrity of the migrated data in the R12 database.
  • Data Rules Test Plan : Validates the migrated data for data accuracy and consistency rules in the R12 database.

Database Testing for Inplace Upgrades (eg. R12.1 to R12.2 upgrade)

When performing an inplace R12 upgrade, it is important to understand the impact on the data because of the upgrade and perform regression tests on it. Another aspect of the testing is to verify the impact of the R12 upgrade on the ETL and the Data Warehouse. One way to test data for inplace upgrades is by taking snapshot of the data prior to the upgrade and comparing the snapshot with the data post upgrade. The snapshot can be of entire data in the tables or results of the SQL queries on the R12 database depending on the volume of the data in the tables. Any differences found between the snapshot data and the post upgrade data need to be analyzed and validated.

ETL Validator can be used for performing the database (or backend) testing of r12 inplace upgrades using its Baseline & Compare capabilities. Baseline and Compare is an unique feature of ETL Validator which allows the user to take a snapshot of the database query results and compare the latest result with the snapshot to identify the differences. ETL Validator also provides a Baseline and Compare wizard to automatically generate test cases for multiple tables using one easy to use wizard.