Introduction to Microsoft Business Intelligence (MSBI)

Business Intelligence

 
Business Intelligence is a technique for transforming data into information. This information helps to make quick decisions.
 
From the Wikipedia:
 
Business intelligence (BI) is the set of techniques and tools for the transformation of raw data into meaningful and useful information for business analysis purposes. BI technologies are capable of handling large amounts of unstructured data to help identify, develop, and otherwise create new strategic business opportunities.
 
The difference between data and information
 
Data is row material for analysis. Data is always related to transactions or events. Once the data is analyzed it's considered to be meaningful information.
 
MSBI Tools
 
Microsoft provides some tools to transform your business data into information. We can use these tools with the interface of Visual Studio.
 
With the release of SQL Server 2012, Business Intelligence Development Studio (BIDS) was renamed to SQL Server Data Tools (SSDT).
 
SQL Server Data Tools (SSDT)
 
The following provides a brief description of BI Tools:
 
BI Tools
 
Image Source: Google
 
Definition of Database
 
From the Wikipedia:
 
A database is an organized collection of data. The data are typically organized to model aspects of reality in a way that supports processes requiring information.
 
Database Management Systems (DBMSs) are specially designed software applications that interact with the user, other applications, and the database itself to capture and analyze data. A general-purpose DBMS is a software system designed to allow the definition, creation, querying, update and administration of databases.
 
ETL (Extracts, Transform, Load) Tools
 
ETL means that it takes the data from various source locations, maybe as a different data format (for example SQL, txt, xls, and so on) and store this data into a destination (Data Warehouse).
 
From the Wikipedia:
 
In computing, Extract, Transform and Load (ETL) refers to a process in database usage and especially in data warehousing that:
 
Extracts data from homogeneous or heterogeneous data sources.
 
Transforms the data for storing it in a proper format or structure for querying and analysis purposes.
 
Loads it into the final target (database, more specifically, operational data store, data mart, or data warehouse).
 
ETL Working Model
 
ETL Working Model
 
LIST OF ETL TOOLS
 
In this URL you will get the list some ETL tools.
 

SSIS, SSAS, and SSRS

 
These are the three tools we are using for ETL, Analysis, and reporting in MSBI.
 
SSIS SSAS and SSRS
 
The following provides a working model of an MSBI Project:
 
model of a MSBI Project
 

SQL Server Integration Service (SSIS)

 
SSIS is the ETL tool from Microsoft.
 
Integration Services is a platform for building high-performance data integration and workflow solutions, including extraction, transformation, and loading (ETL) operations for data warehousing.
 
We can process the data from various locations and various formats (source locations) and save the data into a centralized repository as a Data Warehouse/Data Mart (destination).
 
It includes graphical tools and wizards for building and debugging packages.
 

Data Warehouse and Data Mart

 
This is commonly used for reporting and business analysis purposes. This system is actually the output of integrated data from multiple sources and stored into a centralized repository. The Data warehouse stores the current and historical data, so it is easy to generate trend reports, predictive analysis, and comparison reports. It's very helpful for the top management to make quick decisions about the business.
 
A Data Mart means that it's a small part of a Data Warehouse and indicates only a single part (for example sales or finance). It always holds more summarized information.
 

SQL Server Analysis Service (SSAS)

 
This is the process of converting two dimensional (rows and columns/OLTP) data into a multi-dimensional data model (OLTP). This will help you to analyze the large volume of data.
 
Some of the advantages:
  • Multi-dimensional analysis
  • Key Performance Indicator (KPI)
  • Scorecard
  • Slice, dice, drill-down functionalities
  • Good performance
  • Security and so on.
The following shows the differences between Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP).
 
OLTP OLAP
Storing the current data (always a production environment) Storing historical and current data from multiple locations
Perform all DML (create, update, read, delete) Perform only Read
High Availability Flexible access to data
Normalized database De-normalized with fewer tables because of less performance with a large volume of data.
Data will update frequently Periodically update the Data
 

SQL Server Reporting Service

 
Microsoft SQL Server Reporting Services (SSRS) is an enterprise reporting platform supporting traditional and interactive reports delivered over the web or through custom applications. It supports various data sources like two dimensional and multi-dimensional.
 
The following are some features of SSRS:
  • Retrieve data from a different source
  • Web-based access to reports
  • Support for Ad-hoc reporting
  • Report builder helps to customize the reports for end-user.
  • Easy subscriptions options
  • Export functionality with lots of formats.
  • Display reports in various ways like tabular, Chart, Gauge, and so on.

Conclusion

 
In this article, we explored the basic idea of Microsoft Business Intelligence.