http://www.jamesserra.com/archive/2012/04/sql-server-2012-multidimensional-vs-tabular/
I would recommend going with Tabular if possible, as it is better to use for these reasons:
- It uses your existing relational model, so there is usually no need to create a star schema (which usually means using ETL to create new dimension and fact tables in a Data Mart or Data Warehouse). Complex DAX may require a star schema
- It uses DAX, which is much easier to use than MDX, and least for the basics (but mastering DAX and optimizing DAX is hard)
- It uses xVelocity/Vertipaq, which is much faster than Multidimensional
- It is faster to develop
- It’s less expensive to use in terms of time, resources and skill requirement
- You can extend the data model without reprocessing the whole database by using calculated columns. Instead it requires a much faster “Process Recalc”
- In situations where a multidimensional model requires the use of snapshots (i.e. quantity totals by day), tabular is better because it can avoid snapshots by making up-to-date calculations at query time (thanks to its speed because the data is in memory)
- In situations where a multidimensional model requires a distinct count (i.e. how many new distinct customers this month), tabular is better because it stores data in a way that discount count is very fast (writing a measure vs changing the data model and reprocessing the data)
SSAS supports three storage modes:
- MOLAP – stores detailed data and aggregations in a compressed, proprietary format; i.e. a complete copy of the data is made but query performance is excellent
- HOLAP – stores aggregations same as MOLAP, detailed data is accessed as required from the relational data source
- ROLAP – accesses detailed data and aggregations from the relational data source
Some DAX References
•Chris Webb’s blog. 6 lessons about DAX start here
