Introduction to Microsoft Business Intelligence (MSBI)

Business Intelligence

Business Intelligence is techniques 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 analysed it's considered to be meaningfull 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:


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 analyse 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 its 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 purpose.

Loads it into the final target (database, more specifically, operational data store, data mart, or data warehouse).

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.



The following provides a working 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 use 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 take the 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 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)
• Score card
• Slice, dice, drill down functionalities
• Good performance
• Security and so on.

The following shows the differences between On-line Transaction Processing (OLTP) and On-line 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 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 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 format.
• 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.