MSBI Introduction

Microsoft Business Intelligence Introduction

BI stands for Business Intelligence, it's a set of techniques which helps in gathering, storing, analysing and come up with best information from huge/big data which helps to business decision making.
In other words, BI is Integration of data from different data sources, then Analyse the Integrated data for BI engagement and Representation of the analysed result in the form of reports, dashboards, Charts etc. which helps to understand the data easily.

MSBI stands for Microsoft Business Intelligence, The MSBI suite is composed of tools which helps in providing best solutions for Business Intelligence and Data Mining Queries. This tool uses Visual studio along with MS SQL server. It empower users to gain access to accurate and up-to-date information for better decision making in an organization. It offers different tools for different processes which are required in Business Intelligence (BI) solutions.

MSBI is divided into 3 categories:-

1. SSIS – SQL Server Integration Services – Integration tool
2. SSAS – SQL Server Analytical Services – Analysis tool
3. SSRS – SQL Server Reporting Services – Reporting tool

Microsoft Business Intelligence (MSBI) Architecture

Example: Lets take an example to understand how BI beneficial for an organization: Let’s understand above picture by taking an example of an organization. Let’s take the example :- Adidas. We have outlets of Adidas in most parts of India. Every outlet stores their customer data in their respective database and it’s not mandatory that every outlet is using the same database. Some outlets may have Access database, some might be using Excel sheet or some stores prefer storing their data in simple text files, oracle etc.

Before proceeding ahead with our explanation, we should know “what OLTP is”? It stands for Online Transaction Processing. These are basically the online transactions which is heavy DML operation (Insert, Update, Delete) is performed on database at every Adidas outlet.

Phase 1- “SSIS” After storing daily data of the customers who visited Adidas outlet at different stores, the data is then integrated and saved it in a centralized database. This is done with the help of OLTP component of MS SQL Server. Integration means merging of data from different data stores (i.e. it may be a flat file, Excel sheets, Access, Oracle, etc.), refreshing data in data warehouses and to cleanse data (e.g. -Date format may be different for different outlet’s database, so same format is made to make it even) before loading to remove errors. Now, you must be clear with the Integration concept.

Phase 2- “SSAS” Next step is to analyze the stored centralized data. This huge data is then divided into Data Marts on which analytic process is carried on. Analysis services use OLAP (Online Analytical Processing) component and data mining capabilities. It allows to build multi-dimensional structures called CUBES to pre calculate and store complex aggregations, and also to build mining models to perform data analysis which helps to identify valuable information like what are the recent trends, patterns, dislikes of customers. Business analyst then perform data mining function on multi dimensional cube structure to look data from different perspectives. Multi Dimensional analysis of huge data completes.

Phase 3- “SSRS” Now, represent analysis graphically so that an organization (Adidas) can make effective decision to enhance their revenue, gain maximum profit and to reduce time wastage. So, this is done in forms of Reports, Scorecards, Dashboards, Excel workbooks, etc. This data reports will tell the organization what is the revenue of Adidas in specific time at specific place, where they captured the market, in which part they are lacking and needs to boost up and many other things which end users wish to look into. This reporting is done with a tool SQL Server Reporting Services and completes.