« PreviousContinue »
INTERVAL-PARTITIONING METHOD FOR
1. Field of Invention
This invention relates to OLAP (Online Analytical Processing) and Data Warehouse applications, specifically to data structures and algorithms providing better performance and efficiency for these applications by providing a very effective storage and retrieval mechanism for multidimensional data.
2. Description of Prior Art
Data Warehouse and OLAP applications have highlighted the need for a fast way to store and retrieve multidimensional data. To date other attempted solutions have not met this need.
To illustrate the prior art and how it differs from this invention consider sales data dimensioned by zip code, day, and product. Sales data is stored for 50,000 zip codes, 1000 days, and 10,000 products. The sparsity factor is 1% on average so that of the 500,000,000,000 records that would result from all possible combinations of zip code, product, and day only about 5,000,000,000 records actually have associated data and need to be stored in the fact table. In addition to the keys, the record format contains a field for sales so that the record format is represented by FIG. 2Aand FIG. 2B below.
Even with a very efficient table and index design, the space required to store only the records with actual data is at least 5,000,000,000x(2 bytes+2 bytes+2 bytes+4 bytes+4 bytes) or approximately 65.19 gigabytes.
Couple this with the fact that users of the data may want to query the data by any combination of zip code, product, and/or day. They may want the total sales dollars for a given product and day. Or they may want to know the average sales volume per month in a certain zip code for the past 12 months. Any combination of key fields along with any possible values of those key fields may be chosen. The data truly must be multidimensional with efficient and symmetric query access paths.
There is not a good way to store and retrieve this data with today's database technology. Consider the alternatives:
1) Store the data in a heap table (as is commonly done in relational databases) and use some combination of one or more indexes.
The type of index does not matter. They could be traditional B-trees, more sophisticated star-join indexes, bit maps, and/or hashing routines. No matter how efficient the index is, all table pages that contain data satisfying the query must be accessed at least once in order to produce a query result set. If the data for a zip code, for example, is spread thinly over many table pages as opposed to clustered or grouped together in fewer pages, query performance is degraded by the additional I/O that is necessary to retrieve the extra table pages.
In addition, as many developers and DBAs know from experience, the problem can be much more severe than this. A combination of poor optimizer access plans and the use of dense indexes with low data cardinality often causes more harm than good. The optimizer plan can access a given table page many times during the life of the query in lieu of extracting all rows satisfying the query during a single pass of the table page. This might be true in the business example above if the data in the table were sorted by product and all the data for a group of days for all products were queried.
The problem literally can cause a query to run for days. Ironically, via indexes, the query can end up requiring much more time than a single full table scan would have required in the first place.
5 The use of indexes that are managed as heaps in combination with tables that are managed as heaps makes it difficult to combine indexed access with parallel query processing. Since records that have equal key field values can randomly be stored in the heap, it is difficult to partition
10 the table and index data into component pairs that can be run independently.
As if the other problems were not enough, the solution also wastes too much space. Data must be stored in indexes as well as tables. Albeit, some indexes are more frugal in
15 their use of space than others. Any duplication of data, however, is magnified by the fact that tables of this type can range into several terabytes in size.
While this solution is excellent for OLTP applications, it is clearly less than optimal for OLAP applications.
20 2) Take advantage of modem hardware technology and store the data in a non-indexed table and allow parallel processors and high capacity I/O subsystems to do the work.
Obviously, this is too inefficient. No matter what degree of parallelism is used, queries in the example above should
25 not have to access 65 gigabytes of data to retrieve a 10 kilobyte result set.
If 10's or 100's of users are running these type queries on one or more tables, database server resources can be quickly depleted.
This solution causes queries to run too long and uses too many resources. The data should be organized in such a way that data accessed together is stored together. Then queries can perform I/O only for the table pages that are needed to 35 satisfy the query.
3) Store the data in a cube-like data structure as depicted in FIG. 1 below. Many niche OLAP products use a similar structure.
If the data were not sparse, this would work pretty well.
40 It would provide efficient and fast access to the data and, the query access would be fairly symmetrical. But, with the sparse sales data in the example above, approximately 6.5 terabytes of data would be required to store a cube large enough to house the data. This would be 100 times larger
45 and 100 times slower in terms of I/O to access than the original data. In addition, a more subtle problem emerges. If the order in which the data is stored in the cube is by zip code then product and finally time, access may not be so symmetric. Access for the outer-most key fields is fine but,
50 restricting the data by choosing a small subset of days from the possible choice of 1000 days may not yield improvement over a full table scan in query performance. If the data is stored in a database with 16 kilobyte table pages, then to access all the sales data for one day, all data in the entire fact
55 table would have to be scanned because an occurrence of that particular day would exist on nearly every table page. This is in contrast to the fact that the data for one day should constitute about l/1000th of the size of the complete fact table.
60 This solution is not realistic for sparsely populated OLAP fact tables. Attempts have been made to pack sparse fact tables into smaller cubes but, this further limits the symmetric or multidimensional capabilities of the cube.
4) Use clustering and/or partitioning to "divide and con65 quer" the data.
This method works pretty well as long as the data is clustered or partitioned in the same way that it is accessed.
But, query performance is not symmetric with this solution. If the data is accessed in a way that is different, performance can be as bad or even worse than storing the data randomly or in a heap like structure. This is true since clustering or partitioning the data by one set of keys, distributes or 5 disperses data with the same key values that are not part of the cluster or partition or are not in the prefix of the clustering or partitioning key.
Multiple partitions and clusters can be constructed for the same data. This improves query performance since it pro- 1° vides the clustering or partitioning advantage for a wider range of queries. However, each new cluster or partition requires an additional copy of the complete table. Of course this is very expensive in terms of disk space. And in order to make this approach completely general so that queries :5 involving any combinations of key fields from the composite key of the table can take advantage of the technique, an exponential number of clusters or partitions with respect to the number of fields in the key must be built. To be completely effective, each of these clusters must replicate 20 the data.
For generalized, symmetric OLAP access, this method is less than ideal.
5) Use a special approach just for OLAP or GIS/spatial 2J data that combines the best part of the previously mentioned methods.
The most successful method of this type to date has been the grid file.
The grid file actually works very well in terms of query 30 efficiency since it clusters data based on all key fields in the composite key of an OLAP table. Therefore, no matter which combination of key fields and values within those key fields are used to qualify a query, the grid can be used to narrow the number of table pages that must be accessed in 35 order to retrieve the query set. In addition, grid file indexes or scales are usually designed small enough so that they can be stored entirely in RAM memory to further aid query speed.
Never the less, grid files are not without problems. The 40 way in which data is divided leaves large holes in the storage space thus wasting space and slowing queries down by requiring more I/O.
To compound this, grid files also usually store the actual table pages in a heap like fashion. This makes the combi- 45 nation of grid files with parallel processing more difficult to achieve.
This method also leaves much to be desired but, there is a better way. 5Q
OBJECTS AND ADVANTAGES
This invention has been specifically designed for OLAP multidimensional tables. As a result, it solves the problems of the five major approaches mentioned above and provides 55 one very effective method for storing OLAP data.
It does not have the problems associated with the first approach above. It stores the table and indexes all in one data structure. The space requirements for this data structure are no larger than the requirements to store only the table in 60 conventional approaches. No space is needed for indexes. In fact, table pages are packed 100 percent full so that this complete data structure (data and indexes) requires much less space than most heap tables by themselves. It also does not have the problem of dual I/O (index and table). And, the 65 data is organized according to the algorithm or method contained in this invention. This organization is similar to
that of clustering or partitioning except that is truly symmetric and multidimensional. Amazingly, this data structure still allows efficient incremental updates. This is especially true for time-series data where incremental updates for this data structure are very efficient.
This data structure is easy to be divided into parallel components, as in the second approach above, while avoiding the problems of depending only of parallel technology. It is not necessary to query each combination in the Cartesian Product space of all values selected from each dimension in order to perform a star-join. These individual dimension value lists can be divided up to match the organization of this data structure. Each of these subcomponents of the query can be queried, grouped, and aggregated independently. This data structure allows the query to divided into a granularity that matches the number of logical disks that the data is stored across. RAID technology such as RAID-5 actually improves the efficiency of the data structure by making it more symmetric. With RAID technology, queries using any combination of key field values, execute as if all data needed by the query is stored contiguously. The data structure also issues all I/O at the beginning of the query and in disk page order so that thrashing of the disk head is prevented. But most important of all, the data structure allows the query to select only the table pages containing data needed by the query at the same time that it takes full advantage of parallel technology at a maximum level of granularity.
Data is stored in a similar manner as in FIG. 1 below. This invention has all the advantages of a hypercube even when the data has very high levels of sparsity. For high levels of sparsity the symmetric and multidimensional qualities of this data structure are not diminished yet, the data is compacted to occupy a minimum amount of space. Moreover, this invention is actually better than a hypercube since it preserves multidimensionality no matter what order the key fields data are stored. Still further, the degree of multidimensionality and selectivity of key fields can actually be adjusted to suit individual application needs.
This data structure provides the ultimate data locality sought after with the fourth approach of clustering and partitioning. This data structure provides performance virtually as good as if the data were sorted and partitioned by every possible permutation of key fields in the table. But, instead of requiring the data to be replicated a large number of times, this invention does not require any space for indexes. Using this data structure in the business example above is virtually the equivalent of replicating and sorting the data by all the key permutations in FIG. 2C below. Of course this data structure requires no replication.
Finally, this invention is more efficient than the grid file data structure in the fifth approach. No space is wasted when the data is divided by grid scales. All table pages are always 100 percent full. In addition, no additional space is need for indexes. And, as mentioned before, it is well suited toward parallel queries.
This invention truly has the ability to revolutionize the data warehouse and OLAP industry. It is simple to implement and maintain yet provides very powerful features designed just for this type of data.
BRIEF DESCRIPTION OF THE DRAWINGS
FIG. 1 is a type of data structure contains place holders for records for every possible combination of keys in a multidimensional file.
FIG. 2A and FIG. 2B represent data that might be stored in a multidimensional data structure.
FIG. 2C represents a minimum set of sorted copies or permutations of the data that would be required to efficiently query on all possible multidimensional key combinations if the sample data in FIG. 2A and FIG. 2B were stored in B-tree type data structures. 5
FIG. 3 is the internal data structure of a typical embodiment of the Hypergrid.
FIG. 4A, FIG. 4B, FIG. 4C, FIG. 4D and FIG. 4E represent some typical embodiments of the Hypergrid on varies computer hardware configurations. 10
The method contained in this invention partitions multidimensional data to provide efficient query access for Data Warehouse and OLAP (Online Analytical Processing) ^ applications. OLAP data can be conceptually represented by a multidimensional or hypercube structure as depicted in FIG. 1. OLAP data can only be stored in such a hypercube structure if none or only a very small portion of the data is missing or in other words if the sparsity factor is low. If data 2o with a high sparsity factor is stored in a hypercube, a large amount of space is wasted. In short, the Hypergrid allows sparse data to be stored in a multidimensional type data structure such as depicted in FIG. 1. This is done, as the title suggests, by sorting and partitioning the data into intervals 25 of values. Instead of sorting and organizing the data completely by one dimension, it is sorted and partitioned according to a controlled number of intervals for each dimension. For example, if the data to be organized contains 1000 distinct product values and 5000 distinct time values, it 30 would not be sorted by product into 1000 different groups or alternatively by time into 5000 different groups. Instead, it might be sorted and partitioned into 50 equal sized intervals of product values and, within those intervals, 250 intervals of time values. In this way, each dimension assists in the 35 organization of the data and can thereby be used in the query process to eliminate partitions or segments of the data which do not need to be searched for a particular query. This is true no matter which combination of dimensions and selection criteria are used to query the data. The structure and com- 40 ponents of a Hypergrid are defined with the aid of the example below.
A few assumptions are needed to begin the example. A fact table is a set of data based on a composite or compound key. The composite key is usually, but not necessarily, made 45 up of integers. An example is depicted in FIG. 2A. Each component key or field in the composite key of the fact table corresponds to a single field primary key in a dimension table. The relationship between each component key in a fact table and a primary key in a dimension is commonly 50 known as a foreign key relationship in databases.
The example in the previous paragraph only depicts a few records. But, consider the same type data expanded to include 50 states and 50,000 products. Assume data is collected on a daily basis as opposed to a monthly basis. 55 Further, assume that about 3 years or 1000 days worth of data is maintained. With low sparsity, this fact table could contain as many as 2.5 billion records. Alow sparsity factor for a fact table means that all or most possible combinations of composite key values are present in the fact table. Assume 60 the data is 90% sparse or empty (contains only 10% of the possible composite key values) so that there are approximately 250 million records. According to FIG. 2B this data has a three field composite key and one metric or non-key field for a total of four fields. Each of these fields can be 65 represented by a 4 byte integer so that the total number of bytes per record is 16.
With this information, the number of magnetic disk resources utilized can be calculated. This fact table contains 16x250,000,000 bytes or about 3.72 gigabytes of data. If this data is to be stored on a disk drive unit with track sized partition units of 50 kilobytes, 78,125 disk tracks would be required to store the data. In this case disk tracks are chosen to be the unit partition. Disk sectors or pages of other various sizes could also have been chosen as the unit partitions in lieu of the 50 kilobyte disk tracks. In general, with current direct access, I/O (input/output) rotating storage technology, the size of a unit partition should fall inclusively between a disk sector and disk track so that data in a unit partition can be accessed with exactly one physical I/O operation without moving the read/write head. Since the index associated with this data structure is non-dense and an order of magnitude smaller than the data, it should be possible in most cases to retain the index portion of a Hypergrid in RAM (Random Access Memory) for maximum efficiency and speed. In addition, any record in the Hypergrid should be accessible with only one I/O. If multiple records in the query criteria are on one unit partition, that unit partition should be visited no more than one time during the entire query operation.
The Hypergrid partitions the data using all fields in the composite key that have been selected as member fields. This can be any subset of the composite fields. Only fields that are member fields of the composite key can be used by the Hypergrid to selectively eliminate unit partitions that do not need to be searched. This is a result of the way the data is structured or partitioned in the Hypergrid. This partitioning can be done in several ways. The number of partitions can be divided evenly among the member fields. In the case of this example, there would be approximately 42 partitions per field. Note that more than 50 partitions for the state key should not be used since there are only 50 states and more than 50 partitions would not provide additional benefits. Alternatively, the fact table could be analyzed in full or estimated to determine the relative number of distinct data values or relative cardinality for each member field in the composite key compared to the other member key fields in the composite key. In this case there are likely to be about 50 times more products than days and about 20 times more days than states. Therefore, the product field would be assigned about 1000 partitions, the time field would be assigned 20 partitions, and the state field would only be assigned 4 partitions. These partitions could also be assigned using fixed, relative, minimum or maximum weights. For example, the product field could be constrained to have at least twice the number of partitions as the time field. As noted above, each field or component key should always be constrained to have no more partitions than there are distinct values for that key which occur in the data. In summary, the number of partitions for each field could be determined using any combination of analytical and explicit assignments to best fit the given application or situation.
The partitions are used to structure the data in the fact table. These partitions are used to divide the data across the disk tracks, or more generally unit partitions, that are required for storage of the data. In the case cited above, 78,125 would be required. For simplicity, assume the first method is chosen and that each key partitions the data 42 ways.
A Hypergrid is structured as a hierarchy of partitions as depicted in FIG. 3. In FIG. 3 each non-dense index represents a dimesion.
The order of the member fields chosen from the composite key determines the order of partitioning in the Hypergrid. If the order of the fields in the above example is state,