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.

Figure 1: BI as an Iceberg

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.

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

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
• 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.

Figure 5: 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

Figure 6: SSIS 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.

Data Regions and Maps in SSRS

A data region is a layout element that displays data from a single dataset. Data region types include,

• Table
• Matrix
• List
• Chart
• Gauge

Map is a special type of data region because it can display data from two datasets, one that contains spatial data and one that contains analytical data.

Types of Maps

• Basic Map
• Colour Analytical Map
• Bubble Map
• Basic Line Map
• Analytical Line Map
• Basic Marker Map
• Bubble Marker Map
• Analytical Marker Map

More information can be found at MSDN.

Types of Reports

• Drilldown reports
• Subreports
• Drillthrough reports
• Linked reports
• Clickthrough reports

More information can be found at MSDN.

References

• Microsoft Developer Network. 2016. SQL Server Integration Services (SSIS). [ONLINE] Available at: https://msdn.microsoft.com/en-us/library/ms141026.aspx.
• Microsoft Developer Network. 2016. Analysis Services (SSAS). [ONLINE] Available at: https://msdn.microsoft.com/en-us/library/bb522607(v=sql.120).aspx.
• Microsoft Developer Network. 2016. Reporting Services Concepts (SSRS). [ONLINE] Available at: https://msdn.microsoft.com/en-us/library/bb630404.aspx.
• Microsoft Developer Network. 2016. MDX Query Fundamentals (Analysis Services). [ONLINE] Available at: https://msdn.microsoft.com/en-us/library/ms145514.aspx.
• Microsoft Developer Network. 2016. Data Mining Extensions (DMX) Reference. [ONLINE] Available at: https://msdn.microsoft.com/en-us/library/ms145514.aspx

Authors: Sujeewa Fernando & Prasantha Liyanage