Effects of aggregation and data size on query performance and memory requirements of a Data Warehouse

Aggregation is a key technique to enhance query response time in a large data warehouse. This paper analyzes its effects on query performance and memory requirements. A grocery shop data warehouse has been designed for simulation purpose using Red Gate Data Generator tool along with the Business Intelligence Development Studio of Microsoft SQL Server 2008. For a desired performance gain, the requirements on aggregate and its impact on memory usage has been studied. The effects of variation in size of base cube of a Data Warehouse on memory requirement and performance gain have also been analyzed.