Guide to Form T661 with example report

The technological advancements you were trying to achieve with this work were required for:

240 What technological advancements were you trying to achieve? (Maximum 350 words)

The technological objective of this project was to improve data warehouse management techniques by concentrating on the compression of relational database tables. At the time this work began, numerous database compression methods were available and many of these had been commercialized in larger software applications. However, practically all of the methods relied on data being uniformly distributed and static in nature.

By contrast, the overwhelming proportion of data entering data warehouses could not be assumed to be uniformly distributed and was almost certainly dynamic in character. We assumed that conventionally available data compression methods, such as the loss-less dictionary approach, could be surpassed by developing methods that would exploit the unique properties of those data sets that were not uniformly distributed and were dynamic. A technological advancement was therefore sought in this project through the development of data compression algorithms based on an analysis of the dynamic character and non-uniform distribution of the data sets entering the data warehouse. This work generated new technological knowledge regarding:

* the discovery and use of column value frequency of initial tables rows to create a block-based compression dictionary;
* the use of a table-wide list of most frequent values for the compression dictionary;
* the restriction of query/update/refresh operations to compressed blocks rather than entire tables;
* the organization and control of compression dictionaries in the buffer cache when calls are made to uncompress multiple blocks.

The performance of the various prototypes developed in this work was benchmarked using a number of measures based on CPU utilization and data throughput for operations including parallel load, delete/update operations, full table scan, and table access by row ID. One additional outcome of this work was that the dynamic, non-uniform data compression method developed here actually provided performance improvements for data backup and recovery operations when applied to very large databases in excess of 2.5 million rows (1.3 GB) such as those encountered in data warehouses.
[320 words]
                    

242 What technological obstacles/uncertainties did you have to overcome to achieve the technological advancements described in Line 240? (Maximum 350 words)

There were a number of specific technological obstacles that drove the systematic investigations described further.
We were looking for an appropriate methodology of modeling our dynamic, non-uniform data distribution in real data for the purposes of the compression prototypes.

There were no methodologies, techniques, or models available to us to characterize dynamic, non-uniform data. Our review of available techniques revealed in the early phase of the project that we had to undertake investigation leading to the development of a dataset model suitable to reflect in an efficient way our specific dataset characteristics. The second technological shortcoming was that we did not know and we could not find any technique or methodology related to the data compression, which would specifically deal with this data model related to dynamic, non-uniform data. We realized that if we develop a suitable model to characterize dynamic, non-uniform data then we would find no established techniques to be applied to the data compression aspect that would effectively and efficiently exploit the general features of this abstract data model previously mentioned. The effectiveness of each feature had to be verified in terms of data integrity and benchmark performance comparisons. Once a series of candidate compression algorithms became available the subsequent technical shortcomings were associated with the possibility of implementing a dynamic compression technique for dataset additions and/or updates on a batch basis. Finally, we were planning to develop an acceptable and valid methodology of setting up some general rules related to an optimal data table compression-block size applicable to both the initial data set analysis and the dynamic analysis. We felt that such a relationship should exist and we decided to undertake an investigation to be able to prove it. We also realized that such methodology is not readily available so we would have to address this issue and develop a technique potentially leading to determining an optimal data-block size.
[314 words]

244 What work did you perform in the tax year to overcome the technological obstacles/uncertainties described in Line 242? (Summarize the systematic investigation) (Maximum 700 words)

Following a review of available software methods and dataset characterization techniques, beginning in March 2008 the first phase of the investigations focused on the analysis of a very large data set (known to be dynamic with a non-uniform distribution) in relational database form. This analysis involved a number of investigations, using selected well-known methods in software engineering, with the aim of creating a generalized model of a data set. This also included the extraction of a number of dataset-specific conclusions regarding row and column correlations and distributions, some of which are briefly outlined above in the technological advancements section. At the end of this first phase we found that a reasonably accurate data set model could be created. This was further tested and the data set model accuracy was verified and validated against several concrete smaller-sized relational databases available to us in the data warehouse.

In the second phase, starting in May 2008, a number of compression methods were developed in prototype forms to exploit the general features of the data model. Each prototype carried a set of specific assumptions regarding how the dataset characteristics might be exploited and each was subsequently verified for integrity and then benchmarked for performance. This benchmarking was done through measures of CPU utilization and data throughput for parallel load, delete/update operations, full table scan, and table access by row ID. In direct support of this work, several test scripts were written to test the compression algorithm. Although the development of these scripts included no significant technological challenge, they were necessary to benchmark the new algorithms and determine the most appropriate solution. The benchmarking results were documented and are available for further review if requested.

The third phase was carried out in June and July 2008. Three candidate compression algorithms were modified to include an implementation of several different dynamic compression techniques for dataset additions and/or updates. Each of these again had the data integrity verified and performance benchmarked, the latter now including update/refresh-specific performance measures. In August 2008, a final prototype was selected for widespread commercial implementation ending this aspect of the experimental development.

During October 2008 the implemented prototype was used to determine whether or not an optimal data table compression-block size could be determined by both the initial data set analysis and the dynamic analysis. However, this work failed to establish that such a relationship existed and was subsequently abandoned, ending the project in November 2008.

As part of this effort the Company engaged an outside contractor for a period of two months to extend the data compression method to a wider range of common data warehouse operations in September 2008. Included in this work was an exploration into use of the implemented compression prototype for data backup and recovery operations. As the result of this work it was found out and further documented that the prototype provided measurable performance improvements when applied to very large databases in excess of 2.5 million rows (1.3 GB) such as those typically encountered in data warehouses. Subsequent investigations revealed that this was primarily due to the construction of the compression dictionary rather than the data blocks.
[521 words]