Comparing the Differences Between Microsoft SSAS,SSIS and SSRS

Microsoft complements its relational database engine, SQL Server, with several add-on services that manage different aspects of enterprise business intelligence and information processing such as data integration, reporting and analysis. While SQL Server can work without these services, they add value by boosting SQL Server’s ability to serve as a solid information platform for building complete business intelligence solutions. Services include the SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS) and SQL Server Analysis Services (SSAS). SSAS, SSIS and SSRS are core products of the SQL Server 2008 R2 version, but they differ significantly in the services they provide as part of the full server suite.

Edisons of SQL Server 2008 R2

One important issue and distinction is that not all services are included in the various Microsoft SQL Server 2008 R2 editions. The six SQL Server Editions for SQL Server 2008 R2 are Datacenter, Enterprise, Standard, Web, Workgroup and Express. Integration Services are included in the Datacenter and Enterprise editions. Reporting Services is included in all editions of SQL Server 2008 R2. However, full reporting services are limited in the Standard, Web Workgroup and Express editions. Lastly, Analysis Services is available in the Datacenter, Enterprise and Standard editions with advanced analytical functions in the Enterprise and Datacenter editions only. SQL Server PowerPivot for SharePoint, which is part of the basic Analysis Services package, is not included in the Standard edition.

SQL Server Integration Services

The SQL Server Integration Services (SSIS) is the data-warehousing arm of the SQL Server 2008 R2 suite — equipped with improved Extract, Transform and Load (ETL) capabilities. It acts as a vehicle for transporting data from one data source to another and, if necessary, modifying the data. The Import and Export Wizard, SSIS Designer, and SSIS API Programming are the three components of the SSIS platform for initiating the data integration process. The Import and Export Wizard just transports data from source to destination but does not offer data modification capabilities. The SSIS Designer is a part of the Business Intelligence Development Studio that is used to create and maintain integration services packages. SSIS API Computer module allows you to code SSIS packages using any number of programming languages.

SQL Server Reporting Services

The SQL Server Reporting Services (SSRS) is a set of reporting tools, including the Report Builder, Report Designer, Report Manager, and Report Server, that operate together via a Web interface to create succinct interactive reporting solutions for print or the Web. The SSRS components Report Builder and Report Designer are used to create reports. Report Builder is a basic tool that allows an information worker or business user to quickly build reports without having to grasp the data’s core structure. The Report Designer is a developer’s tool because it complicates the creation of custom reports. Understanding the Business Intelligence Development Studio shell in Visual Studio as well as the data’s underlying structure is required to use this tool. The Report Server is the core process engine in SSRS that manages the processing and delivery of reports using processors, according to Microsoft. The Report Manager is a Web-based administration tool for controlling reporting services.

SQL Server Analysis Services

The SQL Server Analysis Services, or SSAS, is a multidimensional analysis tool that features Online Analytical Processing, powerful data mining capabilities, and deeper dimensions to business information within a relational database. Multidimensional analysis is an OLAP technique that produces the ability to analyze large quantities of data by storing data in axes and cells instead of the traditional relational two-dimensional view in rows and columns. By creating an instant connection to backend data using familiar applications like Microsoft Excel and SharePoint for analysis, visual presentation, and collaboration, SSAS puts predictive analytic capabilities in the hands of information workers.

Difference Between Power BI Report Server and Power BI Service | How to Choose?