Situations to use Multidimensional SSAS model over Tabular Model

4154_tabular_vs_multidimensional

I was introduced with SSAS modeling about 5 years ago. At that time, our team is recommended by Microsoft to use their new modeling technology called Tabular SSAS modeling. Since then, our team started to build multiple tabular models, and those models are growing larger and larger with more complicated calculations. We love tabular modeling because it uses xVelocity engines (Vertipaq – in memory analytic engine and memory optimized ColumnStore index) which means it is super fast because of the in memory storage and it is mulch easier to implement because it uses relational modeling structure which we are familiar with, the script language DAX is kind of like Excel formula to start, and we don’t need to consider set, MDX calculations, aggregations, and storage modes etc. which makes Multidimensional Modeling very complex and difficult to learn.

However, with more complicated requirements come in, we are facing some challenges with current Tabular modeling solution.

As of recently, I started to prepare Microsoft BI certification and started to look into Multidimensional modeling. I found out that we may be able to leverage some of its capabilities which already exists in multidimensional modeling which has been exist so many years. I listed some of those capabilities which are not exist in Tabular model yet.

  1. Remote Partitioning (partition can locate in other server)
  2. Multiple Storage mode selection (MOLAP to ROLAP)
  3. Write Back option – customer can write back to the model – only for aggregate function other than Sum (create “what-if analysis”
  4. Aggregations – pre-calculated (can change setting of how much to pre aggregated) need to consider the storage and maintenance required
  5. Many to many relationships
  6. Role play dimensions
  7. Customized Drill-through and other actions (for older version of Tabular we can use BIDS helper to do it. But, for tabular in SQL server 2016 version, it is disabled)
  8. Merge Partitions, ssms will automatically remove but the visual studio workspace project need to update manually.
  9. Calculation Template
  10. Build in Business Intelligent
  11. Process Index (build or rebuild)
  12. Partition Slice – know only pull the model slice information and provide fast finding of the right partition to use to calculate

Thanks,

Your Friend, Annie

 

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: