Arranging fact table records in a data warehouse to improve query performance

Abstract

This paper examines strategic arrangement of fact data in a data warehouse in order to answer analytical queries efficiently. Usually, the composite of foreign keys from dimension tables are defined as the fact table's primary key. We focus on analytical queries that specify a value for a randomly chosen foreign key. The desired data for answering a query are typically located at different parts of the disk, thus requiring multiple disk I/Os to read them from disk to memory. We formulate a cost model to express the expected time to read the desired data as a function of disk system's parameters (seek time, rotational latency, and reading speed) and the lengths of foreign keys. For a predetermined disk page size, we search for an arrangement of the fact data that minimizes the expected time cost. An algorithm is then provided for identifying the most desirable disk page size. Finally, we present a heuristic for answering complex queries that specify values for multiple foreign keys.