By: Siddharth Mehta
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
- SSAS installation is very straight-forward. Follow this SQL Server 2014 installation article to install SSAS.
- Consider reading this article for a closer look at requirements and considerations for Analysis Services deployment.
