Optimized Design of Materialized Views in a Real-Life Data Warehousing Environment

In this paper, we describe the design of a data warehousing system for an engineering company ‘R’. This system aims to assist users in retrieving data for business analysis in an efficient manner. The structural design of this data warehousing system employs the dimensional modeling concepts of star and snowflake schemes. Furthermore, frequently accessed dimension keys and attributes are stored in various summary views (materialized views) in order to minimize the query processing cost. A cost model was developed to enable the evaluation of the total cost and benefit involved in selecting each materialized view. Using the cost analysis methodology for evaluation, an adapted greedy algorithm has been implemented for the selection of materialized views. This algorithm takes into account all of the cost variables associated with the materialized views selection method, including query access frequencies, base-data update frequencies, query access costs, view maintenance costs and the availability of the system’s storage. The algorithm and cost model have been applied to a set of reallife database items extracted from company ‘R’. By selecting the most cost effective set of materialized summary views, the total cost of the maintenance, storage and query processing of the system is optimized, thereby resulting in an efficient data warehousing system.