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:
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
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 an 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:
The following shows the differences between Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP).