Business Intelligence Best Practices part 2 - Utilizing ETL precalculations

My second article in this series of blog posts about Business Intelligence and Data Warehouse best practices is about why you should move calculations from SSAS (Analysis Services) to relational database when possible and how to do it.

You may have seen SSAS cubes with thousands of lines of MDX code to do calculations and business logics. These may impress your boss or colleagues, but how about maintainability and performance? They not only slow down your SSAS server and frustrate your end users, because they cause SSAS to use cell-by-cell calculations instead of efficient block calculations, but also disable features such as drill-throughs. By moving calculations to the ETL process instead, they only get executed once and you get a much cleaner structure.