Overview
Before installing any software product, DBAs and Developers need 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) prerequisites before starting the installation process.
Explanation
Before we move to addressing the key considerations and hardware / software prerequisites, let’s address the basic SSAS questions with this basic FAQ from an Admin’s viewpoint.
SSAS from an Admin’s Viewpoint
Question: Can I install multiple instances 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 database engine or is it similar to SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS) that acts as an end client to data?
Answer: Unlike SSIS and SSRS, SSAS can host databases which contain cubes, facts, dimensions, and measures similar to a SQL Server relational database with tables and views. Dimensions can be considered master data and facts / measures can be considered transactional data.
Question: Does SSAS have multiple server modes or different types of installation options like with SSRS’s Native and SharePoint mode?
Answer: SSAS 2005 had a single OLAP mode, but it has evolved with future releases. 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 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 the SQL Server Data Tools (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: Does SSAS support Replication?
Answer: Analysis Services includes a database synchronization feature that makes two Analysis Services databases equivalent by copying the data and metadata of a database on a source server to a database on a destination server.
Question: Does SSAS support backup and restores like relational SQL Server databases?
Answer: Yes it does.
Question: Does SSAS use scripting similar to T-SQL?
Answer: The query language for SSAS is either MDX or DAX depending upon the server mode.
Hardware and Software Requirements
A detailed specification of the hardware and software requirements are listed on MSDN. Below is a list of a few summarized key considerations while planning a SSAS installation:
- Resources – Memory, processor and disk space requirements are proportional to size and number of database objects like databases, cubes, aggregations or user-base. The resource estimation in this case is similar to SQL Server estimation.
- Availability – SSAS processes data in cubes, dimensions, facts, and aggregations which can be thought of as data containers like tables. In certain conditions, especially during structural changes, the database objects can become unavailable to client applications. Clustering, load balancing, and replication can be employed to address high availability needs.
- Scalability – Scale-up and Scale-out options can be employed to address performance and scalability needs.
Additional Information
