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.