Is SQL Server Analysis Services (SSAS) Dead?

I get this question a lot from clients. The answer is a definitive, No! SQL Server Analysis Services (also referred to as SSAS) still has a place in the data analysis world.

What You Need to Understand About SQL Server Analysis Services

If you are unfamiliar with SQL Server Analysis Services, it’s part of the Microsoft SQL Server ecosystem. SQL Server Analysis Services is installed as an on-premises solution. While the SQL Server engine was designed to excel at Online Transactional Processing (OLTP), that is to write lots of transactions fast, it wasn’t designed to do Online Analytical Processing (OLAP), that is to read and aggregate high volumes of data. That’s where SQL Server Analysis Services comes in.

SQL Server Analysis Services supports tabular and multidimensional models. Tabular models are stored in memory, where multidimensional models are stored on disk. 

They each have their own query language as well. Tabular models use DAX (data analysis expressions) and multidimensional models use MDX (multidimensional expressions). Each model type has its own place in the data analysis world.

Tabular models are a good solution if you don’t have very large volumes (terabytes) of data. 

If you do have very large volumes of data, multidimensional models are a tried-and-true solution. Microsoft has offered an OLAP engine since it first released OLAP Services back in 1998. In 2000, it was renamed to Analysis Services. When originally released, only multidimensional models were supported, tabular models were introduced in 2012.

Why Analysis Services

If you want to be effective in your data analysis or business intelligence projects, you will need an OLAP engine. Business intelligence projects usually start with building a data warehouse to drive decision support and business analytics. The next step is to create an enterprise grade semantic data model on top of that data warehouse. 

This is where the power of SQL Server Analysis Services comes in. You can use Analysis Services projects to create that semantic data model, choosing either tabular or multidimensional models depending on your volume of data. By creating that semantic model, you are setting your organization up to be able to use client applications, like Power BI or Excel, and start getting insights into your data.

Why not just skip the OLAP engine and go straight to Power BI or Excel? Those client applications do a great job of aggregating data but may not be scalable if you have very large volumes of data. Sure, Power BI supports Direct Query (though not for all data sources), but it usually comes in the form of a trade-off with sub-optimal performance for the data consumer.

When you create your enterprise semantic data model in SQL Server Analysis Services, specifically using a multidimensional model, on top of your data warehouse, you are harnessing the superpower of Analysis Services to do the heavy lifting for you. It queries that large volume of data from your data warehouse, aggregates it, and stores it on disk in the form of a multidimensional database, allowing you to focus getting insights from your data.

You can also create your semantic data model using tabular models. They are only limited by the amount of memory you have installed on your SQL Server Analysis Services machine. 

Experience tells me they cannot handle terabytes of data gracefully, but they can handle up to several hundred gigabytes without issue if you are using a well-formed dimensional model. Tabular models use columnar compression, meaning that data is compressed on a column-by-column basis, allowing you to have far more “pre-compression” data. This is a good thing since all that data will be stored in memory!

If you are thinking about using tabular models, you might be asking, “Why not use Power BI datasets?”. The reason is there are limits to the size of your models in Power BI. Without paying any extra for Power BI Premium (dedicated) capacity, the largest model is restricted in size to ten gigabytes, in addition to that the largest model you can upload is one gigabyte. So you will start with a smaller amount of data, then let it grow to that max of ten gigabytes.

This is why, when clients ask me, “Is SQL Server Analysis Services Dead?”, I say no. SQL Server Analysis Services is such a powerful tool for combining business intelligence and data warehouse efforts and is perfect for volumes of data ranging from very small to very large.

Monitoring and Maintenance

Because SQL Server Analysis Services is an on-premises solution, it needs to be treated like any other database solution in your data environment.

Just like your OLTP databases, your OLAP databases need monitoring and maintenance. These are the databases that you are using to make business decisions, so to say they are important is probably an understatement. 

Fortified is here to help you with those monitoring and maintenance needs. We can help you assess your environment and provide a monitoring solution with WISdom.