New to SQL Server Tabular model? Here are few things you want to consider.

With SQL server 2012, Microsoft introduced new analysis service model called SSAS Tabular model and it became a huge hit among BI and Data Analytic Solution providers.From that day to today, Tabular model has evolved a lot ,and People have been using it for BI Solutions, scale from small to large solutions, around the world. However, Personally, I had not involved in a Tabular model Project up until very recently. Since I started to work in my first major scale BI project using SSAS Tabular Model, I thought of sharing my experience so that someone who is new to Tabular will gain something out of it.

At the very beginning of the project, I was not sure whether we must go for more mature SSAS Multidimensional model or should we chose latest SSAS Tabular mode. Before choosing Tabular as the SSAS component in the project, I googled about any limitation in Tabular and I found this very important document which provide a  comparison between of Tabular with Multidimensional model. I believe that you can get a good understanding about what you can, can’t do using Tabular model comparing to Multidimensional model by reading this document. I also believe that having that understanding is very important if you are to start a industrial level project using Tabular model.

Here is the link:

https://docs.microsoft.com/en-us/sql/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas

Since I was not convinced to chose Tabular after all those readings, I asked from the expert panel in SQL Saturday 639,(I was privilege to participate it, I must say) , which had experts from different regions, which one I should be choosing? Tabular or Multidimensional?  What they asked from me was, if I am to choose a car, will you chose a latest ,fastest car or old reputed car. The obvious answer from me was I like the latest and fastest car(Who don’t right ?). Then I asked from them are there any limitations you guys have faced when using Tabular in large scale projects and they said there were no such scenarios. Since my architect was fully convinced by Tabular model, and since Experts says I should go for Tabular , We chose tabular as our SSAS model for the first time.( In fact first time for the whole team). These are our experiences.

1. Compare to Multidimensional, It’s easy and fast

Implementing a SSAS model using Tabular is much easier than Multidimensional and it’s faster too. You don’t have to create aggregations or attribute relationships and You will not need to create dimensions as all the tables are treated equally inside the model. Only thing you have to do is to import tables in your data warehouse and create relationships (only if those relationships are not defined in Data warehouse level). Creating calculated tables, columns are very straightforward and you can easily get used to options and features in Tabular Model.

2. With DAX, there are lots of places where it can go wrong.

Just like MDX in multidimensional model, DAX is used to create calculations inside Tabular model and DAX is something we all had to learn since neither of us had in depth knowledge about it, apart from the exposure in using Power BI . From my experience in the project, what I felt is DAX is not mature as MDX and there are lots of places which could lead to massive performance issues if you chose wrong syntax in a wrong place. The reason for this is , in DAX, you can write same calculation in few different ways and you will see a massive performance difference between those calculations. Apart from that,your thinking pattern when writing DAX is totally different to the thinking pattern in writing MDX and you will need some time to understand the differences.If you understand Evaluation Context and Filter Context fully, then you will be in good hand when writing DAX queries.

3.Memory is important, But CPU can be critical.

Before starting this project, we were under the impression that it’s memory we need to worry, and we must have good amount of memory when we move into production. So we all decided that in order to save memory, we need to create calculated measures as much as we can, instead of creating calculated column since calculated columns consume more memory in RAM. However, when we write complex calculations and when our data load increase, we noticed that CPU reach 90%-100% time to time and sometimes it hangs there for certain time. This is all because Formula engine of Tabular model is single threaded per query and when you execute complex query against large dataset, it take some times to execute and return the dataset.

Later we found this grate article by written by Marco Ruso regarding what to consider when selecting hardware to Tabular model. I wish we had read this before start the project. I recommend you to read this before deciding on the hardware spec of your project.

https://www.sqlbi.com/articles/choose-the-right-hardware-for-analysis-services-tabular/

4. Tabular works best in Star Schema.

When you design you data warehouse, I think you should consider Start Schema if you are to chose Tabular as SSAS model. The reason is when you have large dimensions and if your architecture is snow flex, you might face some performance issues, especially if you have to enabled BI-directional filtering. When you have large dataset, it’s ideal if you can make your design Star as much as possible.

5. Mind that Tabular model does not support aggregations.

When we implement Multidimensional model, and if we face performance issue, the first thing come to my mind is can’t we create aggregations in cube? shouldn’t we run usage based optimization wizard to create some aggregations? That has helped me a lot when there is performance bottleneck in the solutions for fast few years. However, You have to understand that Tabular model does not support aggregations and when you face a performance bottleneck, you have to find alternative approaches.

  • Revisit your design.
    • Tabular Model has been using for long time and people have used it to handle terabytes of data.Don’t doubt the capability of the tool. So I think that if there is a performance issues in your project, first thing you should do is to revisit your model design and check whether are there any issues in it.
  • Check your DAX queries.
    • DAX plays a major role in performance of Tabular Model solutions and a simple mistake in a DAX calculations will compromise the performance of your project drastically. My opinions is don’t write DAX queries just because it do your job, rather find the optimized way of doing it. You can do some googling and there are plenty of blog post and articles about DAX performance issues and what to use and what not to use in DAX.
  • Check your hardware spec.
    • You might have to check you hardware spec when you troubleshoot performance issues. It might not be up to you to optimized the design or queries, may be it’s hardware which causes performance issues.

Since this article become longer that I expected, I thought of writing another blog post to share some more thoughts. Because the project is not fully completed yet, I think I will have lot more to add in that blogpost.

Thank you for reading and cheers !

Leave a Reply

Please log in using one of these methods to post your comment:

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