Query performance has always been an issue in the world of business intelligence (BI), and many BI users would be happy to have their reports load and render quicker. Traditionally, the best way to achieve this performance (short of buying a bigger database) has been to build and maintain aggregate tables at various levels to intercept certain groups of queries to prevent repeat queries of the same raw data. Also, many BI tools pull data out of databases into their own memory, into “cubes” of some sort, and run analyses off of those extracts.
Downsides of Aggregates and Cubes
Both of these approaches have the major downside of needing to maintain the aggregate or cube as new data arrives. In the past, that has been a daily event, but most warehouses are now being stream-fed in near real-time. It’s not practical to continuously rebuild aggregate tables or in-memory cubes every time a new row arrives or a historical row is updated.