The Situation:

 

Over the years multiple databases had been introduced into the customer’s enterprise due to acquisitions and application development. Most of these databases were on different RDBMS such as Informix, Oracle, SQL Server, and even Access in some cases. Each department had created their own reporting solutions using various technologies such as Excel, Oracle Reports, and Crystal reports with assistance from IT department. For any changes to the existing reports, users were forced to request IT resource time. This caused a huge overhead in terms of maintenance costs, in addition to a severe security risk concerning sensitive consumer data that could be accessed by multiple users across divisions without centralized control of any sort. In addition, some reports caused significant load over production databases causing noticeable delays during business hours.

 

Solution Requirements:

 

• A central location for all company data from Informix, Oracle, and SQL data sources.

• Oracle Cube Views for improved query performance leading to faster reports.

• Streamlined process for reports.

• Single technology implementation for all enterprise reporting.

• Reduce licensing cost and overhead for disparate technologies.

• Users did not have to request IT for most of their minor to mid level report changes.

• Better control over “Who gets to see what data and Report” by centralized authorization and management process.

The Solution:

 

The CCS team recommended an aggregation of all enterprise-wide data to a centralized data warehouse. This would consolidate all reporting functionality to a single source/technology. The team performed a detailed analysis of all existing reports across divisions as well as identified the associated data elements. This analysis also provided insight to establishing the appropriate data model for the warehouse.

 

Custom ETL was written to extract the data from the source databases and populate the centralized database. Subsequently, Oracle Business Intelligence tool (Discoverer) was deployed and configured to re-create the existing reports. Oracle Application server and database instances were part of the customer’s original enterprise, so in-house expertise was already established for maintenance purposes, however, CCS provided training material and tutorials for the users to acquaint them with the new BI tool and features.