Short response to Sanju Bansal about BI and Performance
“The truth is, there’s a good way to model data in a database… but the way business users want to see the data is far different than that.” (Bansal, 2010)
I disagree. This is the exact reason why Data Warehouses need to be modeled using dimensional modeling techniques that are designed around how the business wants to see the data and are setup for fast query performance. With the data already transformed into the format users are expecting to see it with natural hierarchies and relationships built in, queries are simple and much faster.
There is also the potential to pre-calculate all or most of the aggregations into a multi-dimensional database structure. I have done this countless times with Microsoft’s SQL Server Analysis Services and routinely am able to query fact tables with 20M+ rows in milliseconds.
In my opinion BI interfaces trying to make queries faster is a valiant effort however, without a solid dimensional data structure modeled after the way users want to see the data there is only so much they will be able to accomplish.