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

Creating a custom report in CRM 2011 by BIDS

Hi,

Long time no post on my blog. Actually, sometimes I wonder whether I should continue this blog anymore or not... No one really care to this one... :D

Anws, still on my way study everything about CRM: javascript, plug-in, silverlight, BI, reporting... A lot of things.. And till now I'm quite happy with what I studied. At least I began knowing what can do with CRM, and how to do that,etc. which are good when you meet the customers :D.

Today, regarding a demand from people on Microsoft CRM forum for knowing how to create a custom report in CRM. Then I decided to wirte this blog to describe step by step how to achieve this purpose.

Firstly, you need to have the following things in order to create and deployed a report successfully in CRM:
- Business Intelligence Development Studio (BIDS) which run on Visual Studio platform.
- Ensure that your CRM includes Microsoft Dynamics CRM Reporting Extensions.
- Your CRM reporting server URL (which you define when setting up CRM system)

Here is step by step:

- Step 1: Open your BIDS. Remember that BIDS run in VS environment, then you will see the VS screen openned up.

- Step 2: Creating a new Reporting project

- Step 3: In Solution Explorer --> Right Click Report --> Create new report --> you will see the Report Wizrad pop up

- Step 4: Creating a Data Source for your report. Data Source is like a link which connects your report to the database storing your data. In order to define a Data Source, you need to know your Database Server and Database Name:

-Step 5: After defining the Data Source, clicking Next --> Query Builder window will pop up. Here is the place that you build queries to retrieve your data.

-Step 6: Click Query Builder --> Click Add Table icon --> Choose all tables that you need (in this example, I chose Account Table )

 
- Step 7: After adding tables, selecting fields that you want to view in the report. You will see the equivalent query to your select below.

- Step 8: Click Next until the end of the dialog and put name of report inside. Now you have a report interface like that:

The left hand-side is all things included in your report. You could see "Name","Address".. fields in the report content.

- Step 9: Now, if you want to add some more parameters to filter your report result. You could go to "Parameter"  --> Add New

- Put available values for the parameter. Here I would like to put the "Account Name" field

- Step 10:  Now the report is nearly done. Then you "Build" the report.
                 After that, need to Deploy the report into your CRM Reporting Server. To do so, open Properties, add "TargetReportServer" by your Reporting Server URL:

- Step 11: Go to your CRM --> Workplace --> Report --> Add new report --> Add existing report --> put the "RDL" file in your report project into the CRM

- Step 12: Testing your report by opening it ! Here you will see the filter condition which is "Account Name" we created before and the result when running report.

Please feel free leave your comment if you dont understand any step. :D. And Please comment a bit if you find this blog is useful :P. Many thanksssss