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.
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
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
Database/ Data Warehouses
2. Query data
using T-SQL Programming.
using T-SQL Programming.
3. SQL Server
Administration
Administration
4. Data
Backup/Restoration
Backup/Restoration
5. Disaster
Recovery
Recovery
6. SQL Server
Agent Jobs
Agent Jobs
7. Security
Management
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.
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.
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.
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:
Server Analysis Services provides the following benefits:
1.
Pre-aggregation
of data
Pre-aggregation
of data
2.
Data
in Multi-dimensional format
Data
in Multi-dimensional format
3.
Can
create Data Mining Structures/Models
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.
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:
