SQL Server Analysis Services Installation

By: Siddharth Mehta

Overview

Before installing any software product, one needs to think about the hardware and software prerequisites as well as have a fundamental understanding of the product eco-system before getting started. In this chapter we are going to develop a basic high level understanding of the SQL Server Analysis Services (SSAS) installation process.

Explanation

Let's address the basic SSAS installation related questions with a few FAQs.

Question: Where can I download SQL Server Analysis Services (SSAS) 2016?
Answer: SSAS is a part of SQL Server 2016. You can download a free full-feature evaluation version for a trial period of 180 days from here.

Question: Which edition of SSAS 2016 should I install?
Answer: Consider reading this tip for a detailed understanding of the options. For the scope of this tutorial, a trial or developer version should be sufficient.

Question: Can I install multiple instance of SSAS on the same server?
Answer: Yes, but SSAS is a very resource intensive application, so multiple instances should be installed only on very high end servers.

Question: Can SSAS store data like the SQL Server Relational Database Engine or is it similar to SSIS and SSRS that acts as an end client to data?
Answer: Unlike SSIS / SSRS, SSAS can host databases which contain cubes, facts, dimensions, and measures similar to a database with tables and views in SQL Server. Dimensions can be perceived as master data and facts / measures as transactional data.

Question: Does SSAS have multiple server modes / different types of installation like SSRS Native and SharePoint mode?
Answer: SSAS 2005 had a single OLAP mode. Since then it has evolved. Analysis Services 2016 can be installed in one of three server modes: Multidimensional and Data Mining (default), Power Pivot for SharePoint, and Tabular. The server mode of an Analysis Services instance is determined during setup when we choose options for installing the server.

Question: What's the difference between the different server modes of SSRS?
Answer: Each server mode is suited for a certain type and scale of requirement. Consider reading this tip for a comparative analysis of the same.

Question: What are the key tools to administer SSAS?
Answer: SQL Server Management Studio (SSMS) is the key tool to administer SSAS. There are also options to administer programmatically using scripting. SSAS also provides many wizards in SSMS as well as SSDT, most of which are used for performance optimization as well as administration purposes.

Question: What is the default port for SSAS?
Answer: The default instance of Analysis Services listens on TCP port 2383 and named instance listens on TCP port 2382, although one can configure the server to listen on a different fixed port.

Question: Does SSAS support multiple authentication methods?
Answer: Connections from any client application (including SSMS) to an Analysis Services instance require Windows authentication (integrated). Kerberos is also supported using impersonation and delegation.

Question: What are the Hardware and Software Requirements to install SSAS?
Answer: A detailed specification of the requirements is listed on MSDN.

Question: What components are required to install SSAS?
Answer: The SQL Server Database Engine is generally required to host a data warehouse which is used as a data source for SSAS. SQL Server Analysis Services is required to be installed in Multidimensional mode and Tabular mode as we are going to cover both the modes in this tutorial. Install any one mode as the default instance and other mode as a named instance.

Question: What are the steps to install SSAS?
Answer: SQL Server Analysis Services Installation is very straight forward. Follow the steps in this tip to install SSAS. The only difference you would find is on the SSAS tab where PowerPivot mode would be visible as you would be installing SSAS 2016 instead of SSAS 2014.

Before you proceed with the next chapter, make sure you have installed SSAS Multidimensional as the default instance and Tabular as the named instance. For any guidance consider reading this tip to install SSAS.

Additional Information

• Consider reading this article for a closer look at requirements and considerations for Analysis Services deployment.
• SSAS supports different features in different editions for different server modes. Consider reading this article for a detailed analysis.