SQL Server
In a recent Business Intelligence class, we delved into the concept of designing aggregations in your cube. In a multi-dimensional database, pre-processed aggregations provide the performance we need when performing ad-hoc queries against a huge database containing business trend information. The relational data warehouse contains the same data but suffers performance-wise because the SQL engine expects inserts, updates and deletes to be occurring. The complex number-crunching is performed repetitively across perhaps billions of rows of data. That’s going to make your queries slow. The anlysis services engine takes advantage of the fact that we only load data typically once a day and the rest of the time the data is read-only. It can pre-process these aggregations. The cost is extra storage but that’s a fair trade-off for performance. You can allow SSAS to calculate the default aggregations and you can add your own. There is the Design Aggregations wizard that you can use when you are building the cube. Then once you have started using the cube with your users you can then use the Usage-based Aggregation wizard that will take into account the actual queries submitted previously to the cube. In SSAS 2008 there is a new tab in the Cube Designer, entitled appropriately: Aggregations. This tab helps identify the aggregations currently in the cube and has a new Advanced view to keep track of the individual aggregations. Here’s a great article that summarizes the options available in 2008 including the extra add-in “Aggregations Manager”: (scroll down to the bottom of the article for the good stuff): Recommended practices for SQL Server Analysis Services aggregations, by By Baya Dewald http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1365109,00.html cheers Brian
