An efficient workload-based data layout scheme for multidimensional data

Abstract

Physical data layout is a crucial factor in the performance of queries and updates in large data warehouses. Data layout enhances and complements other performance features such as materialized views and dynamic caching of aggregated results. Prior work has identified that the multidimensional nature of large data warehouses imposes natural restrictions on the query workload. A method based on a “uniform” query class approach has been proposed for data clustering and shown to be optimal. However, we believe that realistic query workloads will exhibit data access skew. For instance, if time is a dimension in the data model, then more queries are likely to focus on the most recent time interval. The query class approach does not adequately model the possibility of multidimensional data access skew. We propose the affinity graph model for capturing workload characteristics in the presence of access skew and describe an efficient algorithm for physical data layout. Our proposed algorithm considers declustering and load balancing issues which are inherent to the multidisk data layout problem. We demonstrate the validity of this approach experimentally.

Introduction

Data warehouses are naturally organized using the multidimensional schema model for supporting complex analytical decision support, OLAP, and data mining applications. The multidimensional schema model consists of a number of logical dimensions, e.g., date, product, location. It also models hierarchies on each dimension, e.g., month, quarter, year on the date dimension. The multidimensional schema is also called a star schema since the main or fact table is at the center and the dimension tables are spokes connecting to the main table using primary key–foreign key relationships. OLAP and warehousing applications typically pose queries that select subregions of the overall multidimensional space and perform aggregations along the hierarchy definitions. In a large data warehouse, the physical layout (placement) and clustering of multidimensional data greatly influence performance since the large I/O costs incurred are dominant. In addition, clustering can improve the performance of batched updates and database utilities such as reorg. Choosing an appropriate clustering is crucial for a high performance data warehouse.

In general, data layout can be considered to be an optimization problem using a cost function based on response times and/or throughput of a workload. It encompasses the issues of clustering for minimizing seek times, declustering for obtaining I/O parallelism, and load balancing in a multiquery and multidisk environment. While there is a substantial body of literature on these issues [3], [7], [9], [17], [23], most past work has not considered the data layout issues arising from a multidimensional data model. One exception is the recently proposed query class approach to modeling the distribution of the query workload in a multidimensional data warehouse [10]. This approach recognizes that the workload is constrained by the model and hence the data layout can be optimized more effectively. We are in agreement with this assumption that the workload follows the model. However, the query class approach uses a simplifying assumption that a query is uniformly likely to access any subspace of the multidimensional space. We expect real-world applications to demonstrate skew in the access of the multidimensional data. For example, although a warehouse contains the last two years worth of data, many of the queries are likely to be biased towards analyzing the most recent data, i.e, latest month compared to last month or latest quarter compared to last quarter. The query class approach does not capture this type of access skew and hence can result in suboptimal clustering of the data.

In this paper, we investigate a new approach to the data layout problem in order to address the more realistic and generalized model of a multidimensional query workload. We explicitly model the effects of data access skew in the queries. We construct a data structure called the affinity graph and use it as input to an efficient algorithm for data layout that encompasses clustering, declustering, and load balancing issues for a multidisk environment. In the field of object oriented databases, techniques similar in spirit have been used where the relationship between objects is captured in an object graph. This graph is then used as input to algorithms which output optimal clusterings for the layout of objects on disk [22].

The affinity graph structure is space-efficient and our data layout algorithm is time-efficient and provides a close to optimal solution. We are able to adapt our technique to include the query class model for those data areas which do not exhibit any special data access skew. We have validated the algorithm by performing simulation experiments and varying the type of data access skew, the number of dimensions and the size of the data blocks accessed. Our results show that the affinity graph algorithm is better suited for the real-world multidimensional queries that exhibit data access skews as well as change gradually over time. Our work is complementary to work done on choosing a set of views to materialize given a space or maintenance cost constraint. Even for a fixed set of materialized views, performance will vary depending upon the physical organization of these views. Our focus is on the layout of the fact table of a star schema which impacts the performance of most queries in a warehousing environment.