OLAP Solution for Variance Analysis
Customer Profile
Customer is a defense contractor who manufactures and a global supplier of high security, military oriented products on a project-by-project basis.
Objective
This project entailed creation of two multidimensional cubes to facilitate time series analysis of actual expenditure vs. budgeted expenses and to uncover the underlying trends in the actual expenses incurred.
Description
Two multidimensional cubes were created to enable analysis of the project data being collected through the budget tracking system and data collected through the ERP system that tracks the actual material and labor expenses. The business dimensions and measures were created after careful analyses and discussions with management.
Large amounts of data (around 600,000 rows of data) were cleaned, transformed and loaded into a "Projects Data mart" from the "Actual costs" database. Also, data from the proprietary project management system were exported and loaded into the data mart. All data updates and the cube building processes were automated and scheduled. The deliverables included two cubes, two multidimensional reports (with embedded graphs) and two-dimensional reports.
Duration and Effort
Two persons (one project lead and a developer) worked and completed the entire work in eight weeks.
Technology Utilized
Cognos PowerPlay, Cognos Impromptu, HP UNIX Server and Windows NT 4.0.