SQL Server Analysis Services Complementary Technologies




By:
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.


Last Update: 4/7/2016




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools