SQL Server Analysis Service (SSAS)

What is SQL Server Analysis Service and what is it used for?

SQL Server Analysis (SSAS) is an analytical data engine tool in Microsoft SQL Server. This was released back in December 2010. Over the years, Microsoft has released multiple services and new editions that are related to data warehousing and business intelligence (BI), such as Analysis Services, Integration Services, and Reporting Services. SSAS is used by organizations to analyze information that is spread out over multiple databases. It is mainly used by IT professionals.

SASS: What is OLAP/MOLAP?

Included in SSAS, is a group of OLAP (online analytical processing) and data mining capabilities. MOLAP, which stands for Multidimensional Online Analytical Processing, is considered the traditional version of OLAP. Unlike a relational database, MOLAP utilizes a specialized storage system called optimized multi-dimensional array storage to store data with SSAS. This storage method, along with multidimensional indexing and caching, contributes to MOLAP's reputation for fast query performance within SSAS. Additionally, MOLAP is known for its compactness, making it particularly suitable for handling low-dimension data sets.

How to install SQL Server Analysis Service

SQL Server Analysis Service should be installed by using the SQL Server Installation Wizard. When going through the SQL Server installation via the Installation Wizard, the user will find multiple tabs on the left side. From those options, “Feature Selection” should be chosen. Here, there is the option for SSAS. The box that is in front of Analysis Service, should be clicked. After that, the user selects “Analysis Service Configuration”, which also on the left-hand side and is a few options underneath Feature Selection. Then, the user should click on Server Configuration at the top. Next, the user has the option to choose for either Multidimensional Mode or Tabular Mode. The tabular mode is the default storage for tabular models and uses the VertiPaq in-memory analytics engine. Multidimensional mode uses MOLAP as the default storage for models deployed to Analysis Services.

How to license SQL Server Analysis Service

There are two types of licensing available for SQL Server: Server + CAL and Licensing per Core. The preferred option for smaller businesses is the Server + CAL model. For large (or soon to be large) businesses, the Licensing per Core model is the most cost-efficient option. Next to that, the SQL Server Enterprise is only available through Licensing per Core. SQL Server Standard is available through both licensing models.

The Server + CAL model means that the user buys the server (s) and also buys CALs. For this, the company will need Device CALs (this gives access to the server on 1 device) or User CALs (this gives access to the server for 1 user). Which one should be acquired, depends on the number of devices and users that are present within the company. The most cost-efficient option is to acquire the CALs for which one you have the least. For example, if the company has 50 users and 75 devices, it will require fewer user CALs than device CALs and therefore be the most cost-efficient option. If the company has 50 devices and 75 users, it will require fewer device CALs than user CALs and therefore be the most cost-efficient option. To use the Licensing per Core model, the total number of physical cores within the server should be counted, and then purchase the fitting number of core licenses, which are sold in packages of two.

What’s new in SSAS 2022?

In comparison to SSAS 2019, some improvements and additions have been made to improve the user experience. Horizontal Fusion is included, this is a query execution plan optimization that is aimed at lowering the number of data source queries required to return and generate results. Parallel Execution Plans for DirectQuery enable the SSAS to analyze DAX queries against a DirectQuery data source and identify independent storage engine operations. SSAS 2022 also supports Power BI models with DirectQuery connections to data in SSAS 2022. Next to that, there are also three discontinued features in SSAS 2022: Power Pivot for SharePoint, 1100 and 1103 compatibility in Tabular, and Data Mining in Multidimensional.