What is SQL Server Analysis Services anyway?

SSAS is distributed as part of the SQL Server stack of tools. This stack has included a variety of tools over the years:

• SQL Server Management Studio
• Data Transformation Services
• SQL Server Integration Services
• SQL Server Reporting Services
• Data Quality Services
• Master Data Services
• Data Virtualization with PolyBase
• Big Data Clusters

Some of these options have been part of SQL Server for years, such as Integration Services, but some are brand new to SQL Server 2019, such as Big Data Clusters. Analysis Services has been in the product line for a long time, having been added in 1998.

SQL Server Analysis Services is not SQL Server

This statement may seem odd, but it is important to understand the place that Analysis Services has in the SQL Server stack and related Microsoft Business Intelligence (MSBI) ecosystem. As the list earlier in the chapter calls out, Analysis Services and many other products have been included in the purchase of SQL Server but are not a relational database management system (RDBMS).

The only component of SQL Server that has no official title is the relational data engine. It is simply referred to as SQL Server. SQL Server directly refers to the capability to store data in tables and use Transact-SQL or TSQL to interact with the data. Relationships, indexes, views, and stored procedures can be used in this engine and are commonly used for transactional systems and data warehouse solutions.

Analysis Services is designed to optimize data for analysis and reporting. Relational systems specialize in managing large amounts of transactions with good performance. Analytic solutions such as Analysis Services are designed to aggregate and query large amounts of data efficiently. While design methodologies such as star schemas are designed to optimize relational systems for analytic workloads, these schemas still require significant optimization within relational systems to match the performance of analytic solutions.

Relational versus analytic workloads

Relational workloads are typically normalized in relational database systems. Normalization involves using a lot of related tables to keep the data changes to a minimum. They are optimized to load data. Analytic workloads are denormalized using large flat tables with minimal relationships. This keeps the work of reading the data to a minimum.

The key takeaway here is that SQL Server refers to the relational database engine. Analysis Services is a separate data storage solution that is optimized for analytic and reporting workloads.

SQL Server Analysis Services through the years

SSAS has a long and interesting history. Personally, I started working with Analysis Services with its first release in 1998. Microsoft did not reinvent the wheel; they acquired another company to accelerate their introduction into analytic server tools or Online Analytical Processing (OLAP) servers. They started the process in 1996 and acquired Panorama Software's development team to begin the development of their new OLAP server product called OLAP Services 7.0, which was shipped with SQL Server 7.0 in 1998.

OLAP Services was a multidimensional database solution. Microsoft rebranded this to Analysis Services with its SQL Server 2000 release. They made significant changes to the multidimensional server and supporting technology in the 2005, 2008, and 2012 releases. The multidimensional server was designed to work with large-scale data on spinning disks. As hardware continued to improve, optimizations for the platform changed and we saw the shift to more memory-optimized solutions.

Enter Power Pivot with SQL Server 2008 R2 and Excel 2010. This is a significant turning point in the Analysis Services story. The Vertipaq compression engine was introduced to the MSBI set of offerings. While technically a part of SSAS, Power Pivot was released to Excel first. This columnar-based in-memory solution laid the groundwork for tabular models in Analysis Services, which is now the preferred option for working with Analysis Services.

Check out the following timeline. It illustrates the key points in the history of Analysis Services and related technologies:

Figure 1.1 – Over 20 years of Microsoft Analysis Services history

Microsoft has focused most of its attention on the tabular model technology in the most recent releases of Analysis Services. This technology is the heart of the Power BI products and is the only model type supported as a native Azure technology.

We will walk through the value of both types of models in Chapter 2, Choosing the SQL Server 2019 Analytic Model for Your BI Needs. Let's take a step back and look at why you would consider using Analysis Services to support your workloads today.