In this article, we are going to clarify the fundamentals of Business Intelligence (BI) concepts with their benefits from the data utilization point of view, and explore the SQL Server-focused Business Intelligence solutions by practical examples and illustrations.
Before we discuss the particular issues related to SQL Server, let’s define the basic ideas of business intelligence.
What is Business Intelligence?
According to Wikipedia, business intelligence comprises strategies and technologies for data analysis of business information in enterprises. The ability to properly react based on that information retrieved is central to business intelligence.
According to Robert Caine, business intelligence is all about transforming knowledge into informed actions. It is almost impossible to gain insights into the data saved in data stores that were primarily designed to process transactions.
We can simply say that BI can help us to improve the productivity and profitability of the business. It gives us trustworthy and actionable insights into the data that is not normally visible during day-to-day operations.
What is SQL Server Business Intelligence?
SQL Server BI stack refers to using Microsoft’s products developed to provide BI solutions. The most recent example of SQL Server Business Intelligence Edition is the cloud-powered business intelligence solution called Azure Synapse Analytics by Microsoft.
Is Competitive Intelligence the Same as Business Intelligence?
Competitive intelligence (CI) is not the same as business intelligence (BI) because BI focuses on an organisational internal system totally, while CI is more about the whole environment outside the internal system.
In simple words, BI is empowering internal systems by collecting all data from different departments into one location and analyzing it for better decision-making. CI gathers and analyses data from the environment outside the internal system, particularly about competitors. CI aims to understand and overcome the shortcomings of an existing system (functioning) in the organization.
What are the Benefits of Business Intelligence?
- Combining data from different sources and departments into one location for better analysis and reporting.
- Storing historical data in the BI system for better analysis as compared to transactional systems which only comprise of current data.
- Getting instant analysis results from specialized structures in place (normally it would take ages to process the data by traditional systems of transactional databases).
- Exposing the BI system to trusted internal and external users for real-time analysis.
- The BI system becomes the central point of contact for any type of analysis, reporting, and (data) modeling.
- Adding more capabilities to the BI system without worrying about the impact left.
- Although the BI system relies on the transactional system data to collect and store it, once it gets the data, it becomes totally independent. It won’t require the transactional system running to process the data.
- The BI system can help to understand problems in existing transactional systems, especially during the quality check phase.
- Leveraging built-in time intelligence functions to know more about sales, growth, and profits in comparison to the traditional transactional system, which is not recommended for reporting either.
- Using machine learning algorithms to see prospects beyond your data.
- A good BI system helps to understand ever-changing business needs by alerting potential dangers and informing of potential gains in time.
What is a Transactional System (OLTP)?
We’ve mentioned the traditional transaction system earlier. But what is it?
The transactional system is a typical system running in the organization to handle daily operations. It is not capable of handling analysis and reporting tasks.
It is also called OLTP or Online Transactional Processing System. It records business interactions resulting from day-to-day business and manages transactional data.
OLTP databases are traditionally-looking databases optimized for the following operations:
- Adding new data;
- Modifying existing data;
- Deleting existing data;
- Searching existing data;
- Viewing existing data.
The above operations are commonly known as CRUD (Create, Read, Update, Delete) operations.
For example, we have a database about customers, products, and orders. It is an OLTP database. It gets updated daily in several ways for many reasons, including the following:
- A new customer is added to the database.
- A new product is added to the database.
- A customer buys a product.
- A product is updated to reflect its correct stock value.
- Etc.
What is the OLAP System?
OLAP is an Online Analytical Processing system that represents a specific flavor of BI with data cubes used to process analysis and reporting requests at extremely fast speed.
OLAP databases can be called cubes; they initially receive data from the data warehouse databases through special processes.
Unlike OLTP, OLAP systems are optimized for analysis and reporting rather than for data manipulation activities.
Our traditional Customer, Order, and Product database turns into this specialized database by modifying tables and adding new tables to match the analysis and reporting purposes.
How is Business Intelligence Applied?
Business Intelligence requires using particular SQL Server BI tools, technologies, and processes to achieve business objectives. It includes gaining insight into the data for making better decisions and improving business.
There are many ways to apply BI. One of them is to build the data warehouse business intelligence solution.
What is a Data Warehouse Business Intelligence Solution?
The data warehouse is an infrastructure that is designed to apply business intelligence for analysis, reporting, and research. It is a good example of engaging BI to improve business processes.
A typical data warehouse business intelligence solution consists of the following components:
- Data Warehouse database (SQL Server database or Azure SQL database).
- Data Extraction, Transformation, and Loading processes (ETL/ELT).
- Data Cubes (Multidimensional) / Data Models (Tabular Models).
- Analysis and reporting technologies (Connecting to the Data Models in Excel, Power BI reports, SQL Server Reporting Services (SSRS) business intelligence tools.
What is a Data Warehouse Database?
A data warehouse database is a specific database that is distinguished by the following aspects:
- Consists of staging and BI tables.
- Unifies (merges) data from different sources (transactional databases, Excel, Flat Files, etc.)
- Contains special tables other than source tables which help in finalizing the data.
- Consists of dimensions and facts tables.
What is ETL/ELT?
ETL stands for Extract, Transform, and Load. ELT stands for Extract, Load, and Transform. ETL/ELT is a process used for the following operations:
- Extract (get) the data from different sources, e.g., transactional systems.
- Transform the data by combining it in one location and modifying it further without changing contents to make it better adjusted for analytical purposes.
- Load the data into the data warehouse final tables designed in a specially structured manner to enhance the BI capabilities.
Principle of ACT
Finally, we can understand BI in terms of the principle of ACT (Aggregation, Correlation, Trends). This principle was first mentioned in my LinkedIn article long ago, and it still holds its standing.
Aggregations (A of Principle of ACT):
It means aggregated or summed up data, as we are mostly interested in aggregated figures when it comes to analysis and reporting. Examples of aggregated data are:
- Average Sales
- Sum of Sales
- Average Refunds
- Sum of Refunds
- Average Net Sales
Therefore, our final BI-powered solution is optimized to provide aggregated data rather than individual rows. That is how relevant technologies and practices support this feature.
Correlations (C of Principle of ACT):
Correlations define how you correlate two items of interest to your business. For instance, correlations tell us that customers are not buying helmets when they are buying bicycles even though the helmet is an essential safety gear for cycling.
In other words, we can see the next most missed potential-buying opportunity when our customers buy bicycles.
Trends (T of Principle of ACT)
Trends tell us in which direction our business is heading, what areas should be urgently addressed, and what areas are excellent.
You can look at the trends of sales and understand the customers’ most important buying needs based on the trends you see with the help BI-powered solution.
Congratulations! You have successfully learned the basics of business intelligence. Also, you got familiar with core concepts and practices used in building business intelligence solutions.
Things to Do
Now that you understand the basics of BI, you can improve your skills further by doing the following exercises:
- Create a sample SQL database called BI with a table called ACT having the following columns (ACTId, ACTName, ACTDetails). Populate this table with the three principles discussed in the article.
- Create a sample OLTP database based on the example from the beginning of the article, with Customer, Product, and Order tables.
- Create a table for Aggregations in the sample BI database. Check if you could add to it such aggregations as the sum of sales and average sales based on the OLTP database tables discussed above.
