CASE STUDY #4

CPT Corporate Data Warehouse Solution

A National Telecommunications Corporation

CPT Global was a strategic partner responsible for a Corporate Data Warehouse solution. This work covered the formulation and implementation of a standard data warehousing strategy that was seen as critical to ensure the realization of both a tactical and strategic business benefit. Integral to this strategy was the design and build of both the logical and physical schemas for the company’s Corporate Data Warehouse. CPT ensured the inclusion of data warehouse best practices that set a solid, performance-focused foundation for future data warehouse development and created sufficient scalability to meet the expected data volume growth.

Data warehouse size

• Loading a consolidated view of up to 30 gigabytes of raw data each day; and 

• Total data warehouse approximately 6 terabytes.

Data was sourced from multiple source systems

• Legacy systems including DB2 and IMS; 

• Variety of Unix applications; 

• Manual inputs; 

• Independent 3rd party feeds; and 

• Warehouse specific performance tuning and reviews.

Logical to physical schema design and translations

Data validation, cleansing, scrubbing and referential integrity processing is done within the RDBMS via an SQL and/or PL/SQL programming interface.

End-to-end architecture activities included

• Conceptual design; 

• Logical and physical design; 

• Data extraction, transformation and load (ETL); 

• Backup, Recovery and Data purging strategy; 

• Capacity Planning; and 

• Batch and Online Performance tuning.
   

Establishment of Stress and Volume Test processes and environment Reporting

• Performance tuning support for Online user interfaces such as Express, Business Objects, DSS Microstrategy, Cool:Gen; and 

• Coding and tuning specified batch reporting.

Benefits

The creation of a data warehousing strategy that exceeded business performance requirements. A solid, performance-focused foundation for future data warehouse development with sufficient scalability to meet the expected data volume growth. The Data Warehouse development plans were delivery focused with speed-to-market, a critical success factor, and have been utilized as a model for other data warehouse developments. The implementation of change was able to co-exist with current business plans.