Need to create an integrated operational data store (ODS) and a reporting system that will provide a single view of data across the enterprise.

The repository will combine disparate data sources into a single area and will include a metadata solution to describe the data with a consistent definition across the enterprise. The business intelligence solution will allow users to explore the data with freedom, accuracy, and speed

The goal of the Data-Mart Migration project is to move the current data-mart residing on an Oracle database to a SQL Server database. The migration effort includes changes to the ETL process, converting reports from Business Objects to MS SQL Reporting Services and the core database from Oracle to SQL Server 2000.

The solution is implemented in multiple phases. For the first phase, the processes were migrated “as is” with minimal functional enhancements. In addition to that changes were to make the ETL process dynamic on the year from which data is extracted (either 2002 or 2005).

The data-mart environment consists of 5 tables; 4 dimensions and 1 fact. The reports are developed using MS SQL Reporting Services. Earlier the reports were generated using Business Objects. The ETL process consists of PL/SQL code and materialized views. The ETL processes perform a full refresh of the data mart on a monthly basis.

1. Huge amount of savings in terms of acquiring/maintaining Business Objects Licenses
2. Simplified Monthly Refresh Process
3. Improved Performance
4. Broad spectrum of users started using slicing and dicing features of SQL Reporting Services

Source Systems: Corporate legacy systems, Oracle databases

Hardware:

GUI/Front-end: ASP.Net with Reporting Services, C#, XML, Jscript

Database: Oracle 9i, SQL Server 2000

Reporting: MS SQL 2000 Reporting Services

Project Methodology: IIDM (Iterative & Interactive Development Methodology)

Project Management: Microsoft Project 2003

Configuration Management: Microsoft Visual Source Safe 6.0

Problem logs and Change Management: Microsoft Exchange 2000 & 2003