Sladescross's Blog

Blogging about Sharepoint related stuff

SQL Server BISM Tabular Model January 11, 2013

Filed under: BISM,DAX,Tabular — sladescross @ 8:13 pm

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)

http://www.mssqltips.com/sqlservertip/1563/how-to-implement-proactive-caching-in-sql-server-analysis-services-ssas/

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
About these ads
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 63 other followers