Overview of SQL Server BI Model

SQL Server Business Intelligence Model basically consists of four main services i.e. SQL Server Relational services, Integration Services, Analysis Services and Reporting Services. This BI model is almost same in SQL Server 2005/2008/2008R2/2012.  Each service has its own importance in the BI Model. Below we will see how and where each service fit in the BI Model.

Fig- showing SQL Server BI Model

•  SQL Server Relational Engine:Relational engine is a place where we store our data. (Databases or data warehouses). SQL Relational Engine is controlled and managed by SQL Server (MSSQLServer) or SQL Server (Named Instance) services. Following are the tasks and activities, we can perform with SQL Server Relational Engine :

1.      Hosting of Database/ Data Warehouses

2.      Query data using T-SQL Programming.

3.      SQL Server Administration

4.      Data Backup/Restoration

5.      Disaster Recovery

6.      SQL Server Agent Jobs

7.      Security Management

Data Flow:

Relational Engine => SSIS => SSAS => SSRS

Relational Engine => SSIS => Other Relational Engine (DB/DW) => SSRS

Relational Engine => SSRS

•  SQL Server Integration Services: SSIS provides the facilities of extraction, transformation and loading of data from one place to another place. By using SSIS, We can extract and load the data from/to various data sources e.g. SQL Server, Oracle, TeraData, MS-Access, MS-Excel, SharePoint, Flat file etc.

SQL Server Integration Services uses SSIS Packages to perform ETL operations. SSIS package is a single unit which contains all the data transformation definition as well as advance control logics. SSIS packages saves definition in .dtsx format.

SQL Server Integration Services refreshes databases and data warehouses that can be directly consume by either SSRS for Reports or SSAS for maintaining pre-aggregated data for analysis and reporting purpose.

Data Flow:

SSIS => SSAS => SSRS

SSIS => DB/DW (Relational Engine) => SSRS

• SQL Server Analysis Services: Analysis Services basically deals with data aggregation and multi- dimensional data presentation unlike table which contains 2 dimension data. Pre-aggregation is a very strong feature of SQL Server Analysis Services and due to this feature, SQL Server become very popular in the market. SQL Server Analysis services use CUBE for pre-aggregation and storing of aggregated data. SSAS is controlled and managed by SQL Server Analysis Services.

Data Flow:

SSAS => SSRS

SSAS => Excel

SSAS => Web

SQL Server Analysis Services pulls data from SQL Relational Engine (DB/DW) using SSIS/T-SQL and these can be utilizes by SSRS for reporting purpose.

SQL Server Analysis Services provides the following benefits:

1.      Pre-aggregation of data

2.      Data in Multi-dimensional format

3.      Can create Data Mining Structures/Models

4.      Multi-Dimensional data can be utilized for analysis of historical data and based on that we can predict the future challenges.  So here we can say that SSAS is very helpful in providing decision support.

           

• SQL Server Reporting Services: SSRS basically used to design, deploy and manage the reports. Using SSRS, we can create Tabular, Matrix, Charts, Maps and many highly interactive reports. Report definition is save in .rdl format. SSRS uses two virtual directories (ReportServer & Reports) for communication.

SSRS => Tabular, Matrix, Charts & Map reports

• Development Tools in BI Model: The Development tool for achieving all types of MS BI requirement is “Business Intelligence Development Studio” . By using BIDS, We can create SSIS packages, SSAS cubes, and data mining models and SSRS reports. We need to create separate projects in BIDS for creating packages, Cubes and Reports.

•         Management Tools in BI Model:

The single tool for managing all these services is SQL Server Management Studio (SSMS). Select the appropriate Service type for connection:

 

Below is all the services connected in Management studio: