Migrating from Oracle Discoverer to Microsoft SSRS

One of America's top private colleges successfully migrates from Oracle Discoverer to Microsoft SQL Server Reporting Services to enhance its Financial and Human Resources reporting capabilities.

Project Scope

As part of a larger data source migration project, they sought to phase out Oracle Discoverer in favor of Microsoft SSRS. Migrating to SSRS made the most sense both from a time and cost standpoint since the platform was already installed and being used by other departments.

The college still needed to figure out how many reports were currently in the system, along with the most efficient way to extract and convert the reports to SSRS. Additionally, the project had limited funding and needed to be completed in a matter of months.

ennVee was brought in to manage and see the migration project to completion. We started by identifying and assessing the usage of each Discoverer report, so the college could determine which reports were essential to the business and needed to be migrated to SSRS. Afterward, we used our in-house ennSight tool to automatically extract the SQL and business logic of each report, before migrating and testing the converted reports in SSRS.

Key Requirements:

• Extract and convert 500-750 Oracle Discoverer reports (comprised of 408 worksheets) to Microsoft SSRS.
• Migrate the reports to SSRS "as-is" by creating a mirror image of the existing Discoverer report.
• Unit tests each report before making the reports available in SSRS for the client's users to test and approve.

Discoverer to SSRS Migration Approach

After conducting a pre-migration assessment (step 1), we divided the Discoverer to SSRS conversion project into two phases, or "batches" of reports. This was done to ensure that the reports were converted within the designated timeline and budget specified by the college.

Phase 1 & 2: Migrate reports from Oracle Discoverer to SSRS

1. Conduct a detailed assessment of the existing Discoverer environment to identify:
   1. All reports, workbooks, worksheets, files, folders, and business areas.
   2. The report owner and department/business unit.
   3. Any duplicate, triplicate, or obsolete reports so the business users can choose which reports migrating to SSRS.
   4. The complexity of the SQL in each report and generate an effort estimate to migrate each report to SSRS.
2. Automatically extract the business logic for each report (using ennSight)
3. Migrate/convert each report to SSRS using the extracted business logic
   1. This was done using the extracted business logic.
   2. No changes were made to the reports, so the output in SSRS was a mirror image of the existing report in Discoverer.
4. Unit test each report in SSRS using the available data for each report
5. Deploy reports to Production in SSRS after each converted report is tested and approved by the business users.

Phase 3: Data Source Migration

Converting the Discoverer reports to SSRS was the first piece of the client's separate Oracle to SQL Server data source migration project. Essentially, the converted reports needed to be modified to map to the new database that was created.

Benefits of Migrating from Discoverer to SSRS

• All Discoverer reports were successfully migrated to SSRS in 8 weeks without any loss of data or rebuild.
• The college realized substantial savings in terms of time and cost by automating the reports' SQL extraction process using the ennSight tool.
• Financial and Human Resources business users now have the capabilities to build more intuitive reports in SSRS, with the option to augment the analytics or visualizations by transferring the reports into Microsoft Power BI.

Check out our other Discoverer blogs for migration case studies and tips.