In 2014, EDW department faced serious issues as data warehouse was quickly running out of the available capacity. The capacity reserves recommended for optimal performance of the Teradata have been breached resulting in rapid performance degradation. Analysis and prediction of growth showed that the data warehouse will soon run out of space. With the new projects on the way, a call had to be made - a call to start shrinking the largest tables in order to recover the so much needed space. A few enthusiastic individuals within the team started to carry out the task but even though they managed to keep DWH operational, the manual nature of this task has proved to be time-consuming and also negatively impacted DWH during standard working hours.
ATO Tool has been successfully deployed in the summer of 2014 and passed both Proof of Concept (POC) and Pilot Run (PR), during which it has proven to be extremely useful and efficient. It was set to run on the weekends, when the DWH is quiet in order to minimize the impact on users. As part of the POC, the entire production Data Warehouse has been quick-scanned to identify candidates with the highest potential savings. In the Pilot Run, these tables were analyzed more precisely and users got to learn how to benefit from the tool. Here are some important statistics...
This is the net time during which the tool has been actively running. The ATO was tested on both AIB's Teradata 13.10 and Teradata 14.
The total number of production tables, which have been analyzed in the POC/Pilot Run window.
The total size of possible space recovery suggested by the ATO tool. Given the size of the AIB's production Data Warehouse at the time, this was over 22% of the overall DWH capacity.
The average percentage of space saved per table.
This number represents the highest amount of space recovered per single table. The new size of the table was equal to 62% of the original size.
"That’s been fantastic, and can't be overstated that we would be having to make far more serious decisions right now without this effort."
Conor O'Toole