• Blog Home
  • Tech Talk
    • Best Practices
    • Java
    • .NET
    • Mobile
    • UI/ UX
    • Systems Engineering
    • Quality Assurance
  • ClubM

Sign in

  • Mazarin Corporate Site »
Mazarin Blog
stay connected
Join us on Facebbook! Follow Us on Twitter! Subscribe to our RSS Feed!
Mar
30
2016
Tech Talk

MS SQL Server BI (Business Intelligence)

Author Article by Sujeewa Fernando    Comments No Comments

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

w

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

Related Post

All You Need To Know About DevOps
What is Docker ? Getting Started with Docker
How to use SoapUI NextGenPro to test RESTful web services?
Mobile Payment Using NFC – Near Field Communication
Importance of Big Data and Managing Data with Elasticsearch
Mazarin Foodies 2014
Let’s move to NoSQL Databases with MongoDB – Mazarin
Mazarin Aurudu Ulela 2015
Tags: BI, BI Flow, Mazarin, MS SQL Server BI, MS SQL Server Business Intelligence, SQL Server Analysis Services, SQL Server Integration Service, SQL Server Reporting, SSAS, SSIS
Did you enjoy reading this article? Share it! Share on Facebook Tweet this! Bookmark on Delicious StumbleUpon Digg This!

Related Posts

  • Serverless Architecture with AWS Lambda
  • Let’s move to NoSQL Databases with MongoDB – Mazarin
  • Without Redux and with Redux application state behaviorProductive Development With React Redux
  • Elements of CultureCompany Culture
avatar

About the Author: Sujeewa Fernando

Leave a comment

Click here to cancel reply.

CAPTCHA
Refresh

*

Follow Us on Twitter!

Related Post

Sass and LESS: An Introduction to CSS Preprocessor...
Azure Functions – Learn more about it
Firebase – Mobile Application Development &#...
Serverless Architecture with AWS Lambda
Let’s move to NoSQL Databases with MongoDB &...
Productive Development With React Redux
Beginners’ Guide to CSS (CSS for dummies)
Company Culture
What is Docker ? Getting Started with Docker
Hybrid Mobile App Development with Ionic and Angul...
Test Automation of Mobile Applications using Appiu...
What Power BI Can Do – Major Benefits
Data Mining using SQL Server Analysis Server
Learn Cucumber Test Automation with Ruby Core Fram...
How to Succeed With Designing Scalable Web Apps
Importance of Big Data and Managing Data with Elas...
An Introduction to Node.js – Kickstarter
How To Start Cloud Computing with AWS
What is NFC – The Ultimate Guide
5 Principles: How To Use Lean Startup Towards A Su...
Avatars by Sterling Adventures

Team Mazarin

A team of individuals dedicated to share common goals and vision of the company. Mazarin's endowed team consists of Managers, Software Engineers, User Interface Engineers, Business Analysts, Finance and Administration. We are a blend of quality people. We strive to maintain the open culture and work in close association. The way we work enables everyone to contribute while feeling contented sharing opinions and ideas to deliver the best software solutions.

Read More

Mazarin © 2023. All Rights Reserved.