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.
- Remote Partitioning (partition can locate in other server)
- Multiple Storage mode selection (MOLAP to ROLAP)
- Write Back option – customer can write back to the model – only for aggregate function other than Sum (create “what-if analysis”
- Aggregations – pre-calculated (can change setting of how much to pre aggregated) need to consider the storage and maintenance required
- Many to many relationships
- Role play dimensions
- 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)
- Merge Partitions, ssms will automatically remove but the visual studio workspace project need to update manually.
- Calculation Template
- Build in Business Intelligent
- Process Index (build or rebuild)
- Partition Slice – know only pull the model slice information and provide fast finding of the right partition to use to calculate
Your Friend, Annie