Optimization of Data Warehouse Design and Architecture

A huge number of SCANIA trucks and busses are running on the roads. Unlike the trucks and buses of the past, they are hi-tech vehicles, carrying a lot of technical and operational information that pertains to different aspects like load statistics, driving time, engine-speed over time and much more. This information is fed into an analysis system where it is organized to support analytical questions. Over the period of time this system has become overloaded and needs to be optimized. There are a number of areas identified that can be considered for improvement. However, it is not possible to analyze the whole system within the given constraints. A subset is picked which has been thought of to be sufficient for the purpose of the thesis. The system takes a lot of time to load new data. Data loading is not incremental. There is a lot of redundancy in the storage structure. Query execution takes a lot of time in some parts of the database. The methods chosen for this thesis includes data warehouse design and architecture analysis, end user queries review, and code analysis. A potential solution is presented to reduce the storage space requirements and maintenance time taken by the databases. This is achieved by presenting a solution to reduce the number of databases maintained in parallel and contains duplicated data. Some optimizations have been made in the storage structure and design to improve the query processing time for the end users. An example incremental loading strategy is also implemented to demonstrate the working and idea. This helps in the reduction of loading time. Moreover, An investigation has been made into a commercially available Data warehouse management System. The investigation is mostly based on hardware architecture and how it can contributes to better performance. This portion is only theoretical. Based on the analysis recommendations are made regarding the architecture and design of the data warehouse