Microsoft SQL Server Business Intelligence - What, Why and How - Part 1

By:   |   Comments (1)   |   Related: > Analysis Services Development


Because of the recent crisis or surge in competition, it has even become more important for organizations to optimize its resources and stay competitive. This has led Business Intelligence to become one of the top priorities for many organizations, especially for top management responsible for strategic decision making. But what is Business Intelligence? How it can help an organization in optimizing their utilization of resources?  How can it help the organization remain competitive?  Check out this tip to learn more.


There is a vast amount of data scattered inside as well as outside the organization which makes it nearly impossible to gain insight and infer strategic decisions.  Based on this scattered data, it is difficult to make decisions in a timely manner and without losing opportunities to competitors. Business Intelligence can be used to make the right information available at right time to the right people.

Business Intelligence (BI) is a set of processes, tools, technologies and methodologies which consolidate or integrate data from different data sources and make the transformed data available at right time so that timely, better informed and strategic decisions can be made. BI also provides different tools to quickly analyze the consolidated information in a variety of reporting tools.

In a nutshell, BI provides a mechanism to consolidate raw data scattered across functional areas and transform the data into meaningful information in order to gain insight for driving strategic or tactical decision making.

Why Business Intelligence

Almost every organization has different functional areas, each having different lines of business with associated applications to cater to the needs of that individual functional area. This scattered data across different functional areas poses challenges (or even risks) in making strategic decisions on time.  Therefore, the business needs a mechanism to consolidate data from all of these heterogeneous data sources into a central repository in a consistent format so that it helps business users with:

  • Fast and fact based strategic, tactical, informed decision making in timely manner
  • Aligning the organization towards its key objectives and keep track of progress over the period of time
  • Decreasing operational costs and improve operational efficiency
  • Sharing information with customers / suppliers / partners to win their confidence
  • Understanding customer behavior by customer segmentation for market penetration or for a product launch
  • Increasing revenue by winning over its competitors
  • Improving operational efficiency of each operational area, for example to understand what your true manufacturing costs are and how to optimize them
  • Identifying cross-selling and up-selling opportunities for the business
  • Understanding how the business is doing over a period of time, i.e. where it has been, where it is now and where it is going

Who needs Business Intelligence

In my opinion based on what I have seen in the past, Business Intelligence is not only for top level executives and business decision makers, but can also be leveraged by Business Analysts for analyzing patterns or trends.  Further, BI can help middle level management, Line Managers or workers for improving operational efficiency.  Finally, BI can be used by external customers\vendors to understand the business and gain confidence in it.

Business Intelligence vs. Data Warehousing vs. Data Marts

A Data Warehouse (DW) or Enterprise Data Warehouse (EDW) is the central database repository in which current and historical data gets consolidated from different heterogeneous data sources in order to facilitate historical and trend analysis reporting. There are different approaches or methodologies for designing a data warehouse.  Two of the most common approaches are from Ralph Kimball, which focuses on bottom-up approach and Bill Inmon, which focuses on top-down approach. I will talk about these approaches in a little more detail in my next tips in this series.

A Data Mart is a consolidation of data for just one business area whereas a Data Warehouse or Enterprise Data Warehouse is collection of one or more Data Marts providing a central data repository for the overall organization.

Some people or vendors interpret Business Intelligence as a reporting solution which pulls data from a consolidated data repository or data warehouse whereas other people consider Business Intelligence as a broader category and under its umbrella Data Warehousing, Analytical reporting and data mining falls. Based on my experience, Business Intelligence is a broader area, which covers the data warehouse (data integration), Analysis (OLAP), different ways of reporting (information delivery) and data mining (predictive analysis).

Business Intelligence Vs. Data Warehouse Vs. Data-Mart

Data mining, sometimes referred to as Knowledge Discovery, is the process of analyzing and discovering trends and patterns from the data available in Data Warehouse. Data mining is used to analyze the data from many different dimensions, to categorize it, and to summarize the relationships identified in order to find correlations or patterns.  For example, have you ever noticed, when searching for a book on it gives information about other different books you would be interested based on your search pattern? This is one of the features of data mining. This is a very broad topic and I will talk about this in detail in my next tips in this series.

Business Intelligence vs. Business Analytics vs. Predictive Analysis

Historical analysis means reviewing the past and analyzing the changes which happened over a period of time. For example, how sales have been, say, over the period of last 5 years, what was year over year growth\decline, which was most the profitable year or product, etc.

Predictive Analysis is focused on predicting the future using mathematical analysis and based on identified trend or patterns available in the data warehouse. Typically, these patterns cannot be discovered by traditional data exploration because the relationships are too complex or because there is too much data. To learn more about Microsoft Data Mining solutions click here.

Some people and vendors have started using Business Analytics lately for getting insight into the current data and to predict the future.  Whatever terms or nomenclature you use, the fact remains that the business needs have to be met and it must allow the business to make informed decisions in a timely manner for operation efficiency, predictive analysis and to stay competitive.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, July 25, 2014 - 2:56:05 PM - Sunny Back To Top (32890)

Another great post! Thanks for sharing your knowledge. Keep it up!

get free sql tips
agree to terms