In previous blog, We have seen What is Tabular model and Why need of tabular model?
In that we have seen that Tabular model introduce by Microsoft from SQL 2012. Tabular model put SSAS in front seat of Data modeling, Data mining and BI World. Now not going in much detail of my previous blog, let move in this blog.
After understanding of Tabular model requirement in real world, next question that arises in my mind is that what are the pros and cons of this over Multi-dimension cube and relation DB and what are the overall benefits of Tabular model.
Vs. Multi-dimension
| Multi – dimensional Model | Tabular Model |
| Relationship is very complex | 1:N relationship and same rule applied as per relational DB. |
| Represent by huge Data | Data is comparatively smaller. |
| MDX is used and have more features then DAX | Learning curve is much easier of DAX then MDX. (but mastering DAX and optimizing DAX is hard) |
| All features are available in Multi-dimensional cube. | Write-back, Actions, Custom Assemblies, Custom Rollups, Custom Drill-through Actions, Linked objects, or Translations are not supported in tabular model |
| Memory is depend upon the data available in cube and its structure. | Tabular model must be fit in memory along with caching and distribution are available. That means more data = more memory.
|
| MOLAP store data in compressed format,
Pre-calculates aggregates and uses bitmap indexes. |
Tabular works better in case of distinct count compare to MOLAP (i.e. how many new distinct patient this month) because its store data in a way that produce very fast result. |
| This type of modeling used when requires complex modeling or the case of large dataset. | Due to availability of xVelocity/Vertipaq, it performance is much faster than MOLAP. |
| Power view is not supported right now. | To use power view this is the best way (currently it supports only this model). |
Vs. Relation DB
| Relation Database | Tabular Model |
| This type is used for transactional processing data that means of OLTP. | This type is used for Analysis data over OLTP i.e. how many customer in month. |
| To access the data we need to use SQL and it’s easy to lean | To access this data we need to use DAX and it’s easy up to some extent. (but mastering DAX and optimizing DAX is hard) |
| Result output is depend on indexes, size and other parameter. | Result output is majorly depend RAM. |
| It is not supported Measures and KPIs | It is only use to provide reports and analysis. |
| Less memory required to use this but the size of database may be vary. | It use more memory compare to the relation Database mode as it’s put it all data in memory. |
| To use this model is the goals to confirm rule of normalization. | It is depend upon the requirement of what kind of analysis is required. |
| It is volatile and time variant | It is non-volatile and time variant. |
| Detail level of transactions Data are store. | It is still depend upon the reports and analysis but, still it is summarized compare to OLTP. |
| Compression level is very low. | Compression level is high. |
| Data stored in row model | It is use column store structure. |
Conclusion
After sending of the several hours, I come on conclusion that move of introducing tabular model is wise and game changer for Microsoft. Microsoft really know how to deal with the user requirements and knows that there was missing bridge between Relation Database Model and MOLAP model. By introducing Tabular Model Microsoft create bridge between these. By introducing xVelocity engine in SSAS and built Tabular Model on that, Microsoft provide grate tools to user which provide better solution with good speed and performance. Tabular mode provide user to create relation between tables like Relation Database Model and KPI, measure for MOLAP. It also provide the facility to customization with the help of DAX language which is very similar to SQL and user not need to do much muscular exercise to learn but at the same time if user want to expertise in that need to provide more attention . Tabular model also store its data in columnar store structure which help it to provide result much faster. By using the xVelocity engine it can achieve grate compression level. Tabular works better in case of distinct count compare to MOLAP.
