SQL Server Analysis Services Complementary Technologies

By: Siddharth Mehta

Overview

While just about every technology serves a particular purpose, often other complementary technologies are required for the end to end solution. For SQL Server Analysis Services (SSAS), these technologies include the Relational Database Engine, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), SharePoint, Power Pivot, Excel Services, PerformancePoint Services, etc. As an SSAS administrator, one often needs to understand the system requirements along with technological dependencies of the end to end solution. We will briefly look at how some of the other technologies complement SSAS.

Explanation

Below is a list of some of the technologies which would typically complement SSAS:

Database Engine - Front-end applications generally store data in tables hosted on databases. Relational data warehouses are also hosted in relational database environments. These may act as data sources for a OLAP data mart. If the requirement is that the OLAP solution would use these data sources, you may want to have the Database Engine installed on the same server, so that an OLTP Database / Data Warehouse can be hosted on the same server. Some modes of SSAS like ROLAP need real time access to the relational databases, which mandates installation of the Database Engine.

SQL Server Integration Services - An end-to-end solution may include one or more OLTP databases, data warehouses as well as data marts. Many systems also employ automated house-keeping activities of SSAS database objects. SSIS is the ETL (Extract Transform Load) technology that acts as the data transfer engine for movement of data from one source to another. Also, SSIS includes a set of controls that can perform administrative functions on SSAS database objects. SSIS is installed as a shared service and a SSAS solution may need SSIS at some point in time as part of the end-to-end solution.

SQL Server Reporting Services - Data in a cube is typically stored in a pre-aggregated format, but often this data is not analyzed in raw format using SQL Server Management Studio (SSMS).  This data is usually viewed using a reporting tool such as SSRS. SSRS supports querying SSAS cubes / dimensions / facts, and this queried data can be reported with SSRS reports.

SharePoint - Most of the reporting technology stack like SSRS, Power Pivot, Excel Services and PerformancePoint services are hosted via SharePoint. Also, SharePoint has other features which allow connection to SSAS. Take time to analyze the dependency of the reporting tools required for the solution, and plan accordingly for your installation.