Tuesday 13 March 2012

Microsoft Business Intelligence - Distinguish between SSAS, SSRS & SSIS

I originally decided to develop my career path in Business Intelligence. To be honest, I am not a great coder. But I am very interested in everything related to databases, information systems and analyzing. Therefore, though BI is absolutely a ideal place for me to start.

Last week, my boss asked me to distinguish SSRS, SSAS and SSIS which somehow are called similarly with each other and are all BI services developed by Microsoft. I searched on the Internet, and figured out that there are also a lot of people having the same question. So spending one day to get my answer.

Before understanding what are SSRS, SSAS and SSIS, you might need to know Business Intelligence Development Studio (BIDS) and SQL Server Management Studio (SSMS) which both are tools from Microsoft for managing database, doing tasks related to data processing. Whereas:

-  BIDS is a place that you could develop and deploy BI projects. BIDS run in Visual Studio environment. Therefore, when you open BIDS, you will see the interface is exactly the same with when you develop a new VS project. However, you do not need install VS in order to use BIDS. You could install and run BIDS seperately with VS also.

- SSMS is a database management system, which is the "head office" of Microsoft SQL Server.

1. SSAS: SQL Server Analysis Service

    - Is a part of Microsoft SQL Server, which is a database management system.

    - In SSAS, the database is organized in cubes (dimensions and measures).

    - Developers use BIDS to develop and deploy Analysis Services projects. After that they could use SSMS to manage the Analysis Services databases instantiated from these projects.

Using BIDS to create a new Analysis Service project



      The screen how to develop a Analysis Service project in BIDS

 

After deploy a Analysis Service project in BIDS, you could use SSMS to open and manage the Analysis Service database

2. SSRS: SQL Server Reporting Services

- Is as server-based report generation software system. It can be used to prepare and deliver a variety of interactive and printed reports.

- SSRS uses Microsoft SQL Server databases or Microsoft SQL Server Analysis Services databases as a data source for reports.

- Using BIDS to develop and deploy reports.




Using BIDS to create a new Report Server Project

3. SSIS: SQL Server Integration Services

- Is a tool that is used to perform ETL operations (extract, transfrom and load data). It means it mainly is used to transform data from a database to another database.

- SSIS packages is created, deployed and executed in BIDS. However, you could execute the SSIS packages in SSMS as well.

Using BIDS to create a new Integration Service Package


Developing an Integration Service package in BIDS


After creating an Integration Service package in BIDS, you could use SSMS to execute the package

So I hope this blog could help you guys to understand to whole BI tools provided by Microsoft. Thank you for reading !!!

Kelly