Enterprise data warehouse
CMC designed a data warehouse to collate information from purchase orders, customer orders and payment details, and data marts for each of the three, all based on an IBM mainframe

Project
Data warehousing, Data modeling

Client
Procurement and Supply Corporation, Shelton, Connecticut

The Procurement and Supply Corporation located at Shelton, Connecticut, wanted a data warehouse and data marts. The final deliverable, which was a data model of the enterprise data warehouse, had about 75 tables. The three data marts' data models had about 20 tables each.

The client's requirement was to have the enterprise data warehouse in a normalised form and data marts in a de-normalised form. The OLTP system was on IBM mainframes. The enterprise data warehouse was designed to collate information from purchase orders, customer orders and payment details modules, from the IBM mainframe OLTP system. Separate data marts were then designed for the three departments.

The data modelling task was to take attributes from the modules from the OLTP system in the 0 normal form, model them to the 3rd normal form and then selectively de-normalise them for the enterprise data warehouse. The star schemas were then modelled for the purchase orders, customer orders and payment details data marts that would draw data from the enterprise data warehouse.

Technical architecture
Technical Environment: Oracle 8i, Oracle Designer / 2000 6I
1.1.1.1