MS SQL Server BI (Business Intelligence)

Business Intelligence (BI) is like an iceberg; below the waterline is what you cannot easily see and which is the mass of data that is relevant and important for your business. Rising over the water is more readily seen and recognised and it is the BI platform for analysis and reporting.

So simply BI is about having the right data, at the right time, to enable the right decisions.

BI is a set of techniques and tools that transforms raw operational data into meaningful and actionable information that creates business value and improves business performance.

To get in depth knowledge on Microsoft business intelligence, enrich your skills on msbi online training

BI Flow

Figure 2 shows you the BI involvement in different states and its flow. In a company the raw data is in different data sources or formats like CSV, Excel, RDBMS, CRM and data in the cloud. Data in such raw form is not meaningful to the decision makers. Hence the purpose of a BI tool in a business is to process and transform the raw data from multiple data sources and formats into information. By reviewing and analysing that information the users can get knowledge. Based on the knowledge they can take better decisions and finally better decisions translates into more money or profit to the business.

Companies can use BI tools to achieve the following:

To perform predictions
To understand patterns
For better decision making
Financial performance
Process optimisation

Basic Components of BI

There are four components of BI,

Gathering data
Storing data
Analysing data
Providing access to data

Figure 3: How SQL Server meets BI

MSSQL SEVER provides three components to achieve the basic components of BI:

SSIS (Integration Services) provides tools to extract, transform, and load (ETL) data
SSAS (Analysis Services) provides tools to build multidimensional databases, develop data mining models, and query engine from cubes. Learn more from Msbi course
SSRS (Reporting Services) acts as a tool to build reports and a portal to deploy reports so end users can run reports

Note: Business Intelligence Development Studio (BIDS) is the Visual Studio development environment for SQL BI

Figure No. 4 shows the SQL Server BI data flow

Figure 4: Shows the SQL Server BI data flow

SQL Server Integration Service (SSIS)

SQL Server Integration Service(SSIS) is a platform for building enterprise level data integration and data transformation solutions, SSIS can be used to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data and managing SQL Server objects and data.

ETL Process in SSIS

The process of extracting data from source systems and dumping it into the data warehouse is commonly called ETL which stands for Extraction, Transformation and Loading. Figure 5 illustrates the ETL process.

Extract data from the external data sources such as files, DBMS, OLAP, line-of-business systems, CRM system, web services and SharePoint lists.

Transform the data. This includes cleansing the data and converting it to an OLAP-friendly data model. The OLAP-friendly data model traditionally consists of dimension and fact tables in a star and closely maps SSAS’s dimensional model (SSAS stands for SQL Server Analyses Services).

Load the data so that it can be quickly accessed by querying tools such as reports. In practice this implies processing SSAS cubes.

Basic components of SSIS

There are three basic components in SSIS:

Package

A package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables, parameters and configurations that you assemble using either the graphical design tools that SQL Server Integration Services provides or build programmatically. The figure no 6 shows a package

Control Flow

A control flow defines a workflow of tasks to be executed

Data Flow

A data flow defines a flow of data from a source to a destination. This is where the ETL process comes into the picture.

SQL Server Analysis Services (SSAS)

Analysis Services is an online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports and other data visualisation tools.

It allows creation of “cubes” and cubes can be considered as highly optimised pivot tables. Numerical data is held in “fact” tables (e.g. inventory count, dollars) and attributes are held in “dimension” tables (e.g. products, countries), Multidimensional expressions (MDX) language is used to query SSAS cubes.

Sample code for MDX:

SELECT [FLATTENED] [TOP <n>] <select expression list>
FROM <model> | <sub select> [NATURAL] PREDICTION JOIN
<source data query> [ON <join mapping list>]
[WHERE <condition expression>]
[ORDER BY <expression> [DESC|ASC]]

Data Mining Extensions (DMX) language is used to query Data Mining models built on SSAS cubes.

Sample code for DMX:

SELECT
    { [Measures].[Sales Amount],
        [Measures].[Tax Amount] } ON COLUMNS,
    { [Date].[Fiscal].[Fiscal Year].&[2002],
        [Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS
FROM [Adventure Works]
WHERE ( [Sales Territory].[Southwest] )

End users can query cubes using Excel or data can be fed to others applications like SSRS reports and data mining models

SQL Server Reporting Services (SSRS)

SQL Server Reporting Services (SSRS) is a server-based reporting platform that allows us to create and manage a wide variety of different types of reports and deliver them in a range of formats.

I hope you to reach a conclusion about SSIS and SSRS services. You can learn more through Msbi online training Hyderabad