From SQL 2012 release Microsoft introduced Tabular data modeling along with the Multidimensional model. Tabular model is new type of data model that SSAS introduced. Tabular model is used for tabular/relational or Power pivot project. So, many people thinking that why Microsoft have introduced this new model when they already have facility to work with Multi-dimension or relational DB. What are the advantages and dis-advantages to use this over Multi-dimension cube and relation DB? Microsoft also included xVelocity in-memory analytics engine in SQL 2012. Microsoft had introduced this engine in 2010 and it proved itself as game-changer for Microsoft in analysis services. Even I was puzzled before starting analysis on Tabular model. After spending several hour I came up with conclusion that tabular is middle way between Multi-dimension and relational DB, and following points help me to clear my stand for Tabular model.
What is Tabular Model?
As per the Microsoft SSAS Tabular Model definition from MSDN “Tabular models are Analysis Services databases that run in-memory or in DirectQuery mode, accessing data directly from backend relational data sources.” Tabular Model is a columnar DB with high level of performance and compression ration with supporting two mode “in-memory” and “DirectQuery”.
In “In-Query” mode tabular models is a database which include all data in memory or say heap of SQL server. Tabular Model does this with the help of its engine xVelocity. xVelocity empowers Tabular model to put its object and data in-memory with state-of-the-art compression algorithms and provides multi-threaded query execution functionality to it.
“Direct Query” mode works partially with “In-Query” mode. Reason behind providing this mode is to handle big data which are not fit in-memory or when there is no adequate plan of data processing due to data unpredictability. This model also handle calculated tables and columns, row level security and query optimizations.
Why need of Tabular Model?
At the time of introduction of tabular model in SQL 2012, every IT person had question “what is the need of tabular model in current world?” The reason behind this is very simple, to accommodate requirement between MOLAP and Relational database. Missing features of MOLAP and Relation DB creates void giving rise to Tabular Model. Tabular model has functionality combination of both MOLAP and Relational DB. Relational Database supports tables and relationships but at the same time it does not supports KPI, Measures. So, User need to move over to MOLAP, but it is very complex structure, need some expert person to implement it and at the same time it is very true to say that it is a costly to implement. Basically MOLAP is complex, costly, time consuming and has issues with distinct count, at the same time Relation DB does not provide fast access of complex query like MOLAP and in most of the cases its seldom design restricts it to respond quickly to analytics query. Tabular model is very effective and simple. It provides semantic model with cache system that provides performance and scalability. Microsoft made difference between Tabular model and MOLAP which differentiates their language and tools used to define the models. The main reasons behind choosing tabular model are providing better performance, low maintenance & ownership cost; Over and above this it provides simplicity and flexibility in data modeling. Microsoft empowered Tabular Model with DAX and xVelocity engine.
In Next Blog
After understanding of the need of Tabular model in real world, next question in my mind is “what are the pros and cons of this model over available model in current environment?”. We will see that part in my next blog and go further detail, which will help us to identify toward the way of Tabular model in SSAS.
