Efficient maintenance of basic statistical functions in data warehouses

Highlights

• Significant improvements in maintaining the statistical functions inside a data warehouse.

• Reduce the maintenance time from minutes to seconds.

• Efficiently maintaining basic statistical functions inside a data warehouse contributes to firm performance.

• Can also be applied to maintain a distributed data warehouse or a data mart.

Abstract

In general, some simple but very meaningful statistical functions are often used to retrieve valuable summary information in corporate databases. However, it is not uncommon that such information is obtained from computerized information systems which spend a great deal of time calculating the large volume of collected data. In practice, such data is usually stored in a data warehouse in which a large number of summary tables or materialized aggregate views are built in order to improve the system performance. Upon changes, most notable new transactional data are collected from various data sources, and all summary tables in the data warehouse that correspond to the transactional data must be updated accordingly. Since the number of summary tables that need to be maintained is often large, efficiently maintaining these is thus a critical issue for managing a data warehouse. In this study, an efficient maintenance approach to enhance the performance of a data warehouse is proposed, in which some additional auxiliary tables are kept inside a data warehouse with the role of improving the maintenance processes of some statistical functions, such as MIN, MAX, MEAN, and MEDIAN. Finally, a comparative analysis is performed to verify the effectiveness of the proposal method.

Introduction

Data warehouses are often huge systems which require a very high level maintenance. Any reorganization of the business processes and the source systems may require the data warehouse to change. Updates are often needed in these situations, and more resources are required for maintenance of a data warehouse rather than its development. Views are computed and stored in a database to allow efficient querying and analysis of the data, and these are known as materialized views. In order to keep these up to date, it is necessary to maintain the materialized views in response to the changes at the sources. In general, incrementally maintaining a view can be significantly cheaper than recomputing it from scratch, especially if the size of view is large compared to the size of the changes. The problem of finding such changes in the views based on changes in the base relations is known as the view maintenance problem. Moreover, since statistical functions often play a very important role in the online analytical process (OLAP) in data warehouses, some simple but very meaningful functions are often used to retrieve information. In order to make statistical analysis operations more effective, it is necessary to keep them up to date, which often requires a lot of time and resources. The objective of this study is thus to propose an approach to maintain statistical functions in a data warehouse more efficiently, in which some supplementary data are kept with a legacy role. The auxiliary data are organized in a specific format that makes them very efficient in response to changes in the base tables. In other words, when a base table is changed, the data warehouse will use these auxiliary data to maintain the view instead of reaccessing the entire base table, significantly improving the maintenance processes.

Since data warehouses often contain huge amounts of data, in order to keep performance smooth and constant, maintenance of a data warehouse is crucial. [16] suggested an efficiency model for data warehouse operations which contains two major processes (refresh processing and query production), and concluded that only few organizations can obtain good efficiency for both processes. While companies may be successful in implementing multi-terabyte data warehouses, it is still difficult to translate this investment into useful business knowledge or positive business results. Computing and/or materializing a complete data cube is often expensive, in both time and space, and difficult to conduct online. Research has proposed different approaches for dealing with this problem of querying OLAP data cubes [4], [14], [19]. However, as changes are made to the data sources, the warehouse views must be updated to reflect this. According to [3], [6], the updated view can be either recomputed from scratch or obtained by using incremental maintenance techniques. However, in today's warehouses, the changes to the sources are often not detected and propagated to the warehouse immediately. To efficiently deal with changes to the materialized views in relational and deductive database systems, [5] presented a counting algorithm that tracks the number of alternative derivations for each derived tuple in a view. By first deleting a superset of the tuples and rederiving some of them, a recursive view can be maintained incrementally. This method was described as the delete and rederive (DRed) algorithm.

For data warehouses that provide global access, even a short period of downtime may not be acceptable. [1] proposed two algorithms called SWEEP and Nested SWEEP to deal with this issue. Both algorithms work without requiring that the data warehouse is quiescent to incorporate the new views. A multi-agent system (MAS) that achieves immediate incremental view maintenance was developed by [21]. [7] proposed an algorithm for efficiently computing updates to views without reaccessing the materialization of views. [10] proposed a propagation approach to deal with view maintenance tasks. [9] proposed an approach which employs an extendable multidimensional array to maintain the increment of data cubes with the aim to avoid recomputing the entire data cube. [13] proposed an efficient algorithm which can maintain a data cube using only a subset 2ⁿ delta cuboids.

Incremental maintenance has been studied by [3], [22]. In spite of their positive results, view maintenance is still complicated by the fact that the sources are decoupled from the warehouse, so that traditional incremental view maintenance may exhibit anomalies. In this situation, warehouse self-maintainability is desirable. This is the ability of a warehouse to maintain itself without “help” from the underlying databases, i.e., it based only on reported changes to the underlying databases [12]. Auxiliary data is used in order to update materialized views, without accessing the base relation. Research on the construction of auxiliary tables for self-maintainability can be seen in [2], [8], [15], [17], [18], [20]. According to [12], warehouse independence with respect to queries is the ability of the warehouse to answer queries posed to the underlying sources from the warehouse views. Similarly, warehouse independence with respect to updates is the ability of a warehouse to maintain itself based only on reported changes in the underlying sources.

Based on the aforementioned discussion, in this study an efficient maintenance approach to enhance the performance of a data warehouse is proposed. The main objective of this study is to avoid reloading all the data in DW completely when calculating some statistical functions is required, no matter if the data are generated from real-time operations or periodical refreshment. In this paper, we maintain some supplementary tables (e.g., auxiliary tables, materialized views) in the DW to keep track of each update, so that the values of required statistical functions, such as MIN, MAX, MEAN, and MEDIAN, can be instantly calculated without a full reloading task. The proposed incremental maintenance approach is statistically proved to be significantly faster than the traditional one (completely reloading and recomputing), especially when the volume of the original data is much larger than that of the changes. Therefore, the proposed approach can be employed in both a periodic data refresh policy or an OLAP environment. This paper is organized as follows: Section 2 considers the maintenance of a data warehouse, Section 3 depicts the development of the proposed approach, Section 4 demonstrates the effectiveness of the proposed approach by performing a numerical experiment, and finally, Section 5 presents the concluding remarks.