Koen Verbeeck (8/26/2014)
OLAP is good for aggregated data, but doesn't perform well for more detailed queries.
In my opinion, the DW is read-only (except during ETL of course), so it should be able to handle some traffic.
I believe for canned reports, the choice between SQL or MDX (e.g. SQL Server vs SSAS) is a personal choice. Except if you need some functionality that one tool has and the other hasn't. For self-service browsing, SSAS is preferred.
Yepyep, should always be read only outside of the ETL system. I think it's more or less how the DW is being used that makes me raise the question. Simplistic or even discovery queries are fine. But, when it comes to very complex queries that include a high amount of dimensions and facts that also include millions or sometimes hundreds-of-millions of records, the DW takes a pretty big hit.
Due to that, I do everything I possibly can to massage and prep the data in batch after it comes into the DW to optimize those detailed queries. I guess it's more work for me, but my objective is to reduce the downtime for the end user when doing reports. I'm achieving that goal while also reducing the amount of traffic to the DW. So, it all works out in the end.