BUSINESS INTELLIGENCE (BI)

Business Intelligence (BI) refers to the combination of technologies, software, applications, and
processes that help organizations collect, store, integrate, analyze, and present business data for
effective decision-making and strategic planning.

Business Intelligence converts raw business data into useful information that helps managers make
better business decisions.

Framework of Business Intelligence

The Business Intelligence Framework explains how an organization transforms raw data into valuable
business knowledge.

It consists of different components that work together to support business analysis and strategic
planning.

Components of the Business Intelligence Framework

The framework contains four major components.

1. Data Warehouse
Definition

A Data Warehouse is a special database or central repository that stores large amounts of data
collected from different sources. It is specially designed to support decision-making by providing
organized, accurate, and historical data.

Simple Explanation

A data warehouse collects data from different departments such as Sales, Finance, Marketing, and
HR.
It stores the data in one place.

Managers use this data to prepare reports, analyze business performance, and make better
decisions.

It can range from simple reporting systems to complex analytical systems.

Main Features

Central storage of organizational data.

Supports business intelligence and decision-making.

Stores historical as well as current data.

Provides fast query and report generation.

Integrates data from multiple sources.

Processes in a Data Warehouse (ETL)

1. Data Extraction

Collecting data from different sources such as databases, Excel files, websites, or ERP systems.

Example: A supermarket collects sales data from all its branches.

2. Data Transformation

Cleaning, correcting, and converting data into a common format.

Example: Changing date formats from MM/DD/YYYY to DD/MM/YYYY and removing duplicate
customer records.

3. Metadata

Metadata means "data about data."

It explains the source, structure, and meaning of the stored data.

Example: A table named Customer contains fields such as Customer ID, Name, Age, and City. This
information is metadata.

4. Data Loading

Loading the transformed data into the data warehouse.

Example: After cleaning sales data from all branches, the data is stored in the central warehouse for
analysis.

Real-Life Example

A company like Amazon receives customer orders from its website, mobile app, and physical stores.
All this information is collected, cleaned, and stored in a data warehouse. Managers use it to:

Identify best-selling products.

Analyze customer buying patterns.

Forecast future demand.

Improve business decisions.

2. Business Analytics
Definition

Business Analytics (BA) is the combination of applications, technologies, skills, and operations used
to analyze business data. It helps organizations study past business performance and make better
business decisions for the future.

Simple Explanation

 Business Analytics collects and analyzes business data.

 It helps managers understand past performance.

 It identifies trends and patterns in data.

 It supports business planning and decision-making.

Features of Business Analytics

 Analyzes historical business data.

 Helps in decision-making.

 Provides clear business insights.

 Identifies business trends and patterns.

 Improves business planning and performance.

Business Analytics Tools

Business Analytics provides different software tools for:

 Generating on-demand reports.

 Performing data analysis.

 Answering business queries.

 Supporting Online Analytical Processing (OLAP).

What is OLAP?

OLAP (Online Analytical Processing) is a technology used to analyze large amounts of business data
quickly from different perspectives.

Example:
A sales manager compares product sales by month, region, and product category using OLAP.

Business Users and Graphical Tools

Business users use:

 Charts

 Graphs
  Dashboards

 Trend analysis

These tools help users:

 Evaluate business performance quickly.

 Identify growth or decline.

 Understand performance patterns easily.

Example:
A company uses a dashboard to monitor daily sales, customer satisfaction, and profit.

Categories of BI Techniques and Tools

1. Reporting and Queries

 Used to generate reports and answer business questions.

 Helps managers understand business performance.

Example:
Monthly sales report showing total sales in each branch.

2. Advanced Analytics

 Uses statistical and predictive techniques to solve business problems.

 Helps forecast future trends.

Example:
Predicting next month's product demand based on previous sales.

3. Data, Text, and Web Mining

 Extracts useful information from data, documents, and websites.

 Finds hidden patterns and relationships.

Example:
An online shopping website analyzes customer reviews to identify customer preferences.

Real-Life Example

A supermarket uses Business Analytics to:

 Analyze daily sales.

 Find best-selling products.

 Predict customer demand.

 Improve inventory management.

 Increase profits through better planning.

3. Business Performance Management (BPM)

Definition

Business Performance Management (BPM) is the process of defining, executing, monitoring, and
controlling an organization's business strategy to achieve its objectives.

The Balanced Scorecard methodology forms the basis of BPM.

Explanation

BPM helps an organization:

 Define business goals.

 Execute business strategies.

 Monitor organizational performance.

 Compare actual performance with objectives.

 Improve overall business performance.

BI analysis, reports, and queries are used to support BPM.

Objectives of BPM

 Improve organizational performance.

 Monitor Key Performance Indicators (KPIs).

 Align business strategies with goals.

 Support better decision-making.

 Increase efficiency and productivity.

Example

A supermarket sets a goal to increase monthly sales by 15%.

 Target: 15% sales growth

 Actual Growth: 10%

 BPM Analysis: Sales are below target.

 Action Taken: Launch promotional offers and improve marketing to achieve the target.

4. User Interface (UI)

Definition

The User Interface (UI) is the part of a Business Intelligence (BI) system through which users interact
with data. It helps users view, analyze, and understand business information in an easy and
meaningful way.

There are mainly two types of user interfaces:

i) Dashboards

Definition
A dashboard is a screen that displays important business information in a simple and easy-to-
understand format. It shows trends, exceptions, and Key Performance Indicators (KPIs) to help
managers make quick decisions.

Features

 Displays business information visually.

 Shows trends and patterns.

 Highlights exceptions or problems.

 Displays organizational performance using KPIs.

 Supports quick decision-making.

Example

A sales dashboard may display:

 Total Sales: ₹50 Lakhs

 Monthly Revenue Growth: 12%

 Customer Satisfaction: 90%

 Top-Selling Product: Laptop

Managers can instantly understand the company's performance from one screen.

ii) Visualization Tools

Definition

Visualization tools convert complex data into visual formats such as charts, graphs, maps, cubes, and
dashboards, making data easier to understand.

Common Visualization Tools

 Charts (Bar, Line, Pie)

 Graphs

 Multidimensional Cubes (OLAP Cube)

 Geographic Information Systems (GIS)

 Virtual Reality (VR) Visualizations

Advantages

 Easy to understand complex data.

 Identifies trends and patterns.

 Improves business decision-making.

 Makes reports attractive and interactive.

Example
A retail company uses a GIS map to identify the states with the highest sales. Managers can decide
where to open new stores based on this visualization.

Difference Between Dashboard and Visualization Tools

Dashboard Visualization Tools

Displays business performance in one screen. Converts data into visual formats.

Focuses on KPIs and trends. Focuses on charts, graphs, maps, and cubes.

Used for monitoring performance. Used for analyzing and presenting data.

Advantages of Business Intelligence (BI)

1. Employee Authorization

Meaning:
Business Intelligence allows employees to access simple and relevant data. This helps them analyze
information quickly and make better decisions without depending on others.

Benefits:

 Faster decision-making

 Better employee performance

 Easy access to reports and dashboards

 Improved business planning

Example:
A sales executive uses a BI dashboard to check daily sales and immediately decides which product
needs more promotion.

2. Link Various Employees for Competent and Successful Processing of Data

Meaning:
BI connects employees from different departments by sharing the latest information through a
common system or the internet.

Benefits:

 Easy communication

 Better coordination

 Real-time information sharing

 Faster decisions

Example:
The Sales, Finance, and Inventory departments use the same BI dashboard to monitor product stock
and sales.

3. Easy Teamwork and Allocation

Meaning:
Business Intelligence improves teamwork by allowing employees, managers, clients, and partners to
share information securely.

Benefits:

 Better collaboration

 Easy data sharing

 Improved management efficiency

 Centralized information

Example:
A project manager shares performance reports with all team members through a BI portal.

4. Communicating Business Intelligence to the Whole Organization

Meaning:
BI helps every department understand business goals using reports, dashboards, and planning tools.

Benefits:

 Better organizational planning

 Common understanding of business objectives

 Attractive business expansion

Example:
The CEO shares monthly performance dashboards with all department heads.

5. Evaluate and Improve Inputs

Meaning:
BI collects and analyzes data to improve business performance and decision-making.

Benefits:

 Better analysis

 Accurate information

 Easy data integration

 Smart business planning

Example:
A supermarket studies customer buying habits and increases the stock of fast-selling products.

6. Improved Association

Meaning:
BI helps employees and management work towards common organizational goals.

Benefits:

 Better coordination
  Performance monitoring

 Effective decision-making

 Improved accountability

Example:
HR and Production departments use BI reports to improve employee productivity.

7. Reduced Training Requirements

Meaning:
Modern BI tools are user-friendly, so employees require less training.

Benefits:

 Reduced training cost

 Easy to learn

 Saves time

 Higher productivity

Example:
A new employee quickly learns to use a simple dashboard instead of complex software.

8. Transport Refined Investigation and Reporting

Meaning:
BI provides detailed reports and Key Performance Indicators (KPIs) that help organizations monitor
performance.

Benefits:

 Better reporting

 Performance measurement

 Goal achievement

 Quick response to problems

Example:
A bank tracks the number of loans approved each month using BI reports.

Disadvantages of Business Intelligence

1. Large Amount of Historical Data

Meaning:
BI mainly uses past data. Sometimes old information may not match current market conditions.

Example:
Customer preferences have changed, but the company still depends on old sales data.

2. High Cost
Meaning:
Implementing BI software is expensive, especially for small and medium businesses.

Example:
A small retail shop cannot afford expensive BI software.

3. Difficulty in Implementation

Meaning:
Installing BI systems requires skilled professionals and proper planning.

Example:
A company needs IT experts to install and maintain a data warehouse.

4. Disordering of Commercial Set-ups

Meaning:
Improper implementation of BI may disturb existing business operations.

Example:
Employees become confused when switching from manual reports to BI dashboards.

5. Limited Applications

Meaning:
Some organizations cannot fully use BI because of limited budget, infrastructure, or technical
knowledge.

Example:
A small local business continues using Excel instead of a complete BI system.

6. Time-Consuming Implementation

Meaning:
Implementing a complete BI system may take several months.

Example:
A manufacturing company spends nearly one year building its BI platform.

Applications of Business Intelligence

1. Measurement

Meaning:
BI measures business performance using Key Performance Indicators (KPIs) and benchmarking.

Uses:

 Performance measurement

 Goal tracking

 Business evaluation

Example:
Amazon measures daily sales, delivery time, and customer satisfaction.
2. Logical

Meaning:
BI supports logical and data-driven decision-making using techniques like data mining, predictive
analytics, business process modeling, and statistical analysis.

Example:
Netflix recommends movies based on customers' previous watching history.

3. Reporting / Enterprise Reporting

Meaning:
BI generates strategic reports for managers using tools such as OLAP, Executive Information Systems
(EIS), and Data Visualization.

Uses:

 Strategic reporting

 Executive decision-making

 Data visualization

Example:
A company prepares monthly profit and sales reports for top management.

4. Collaboration / Collaboration Platform

Meaning:
BI enables departments inside and outside the organization to share information electronically.

Uses:

 Data sharing

 Team collaboration

 Better communication

Example:
Sales and Marketing departments share customer data through a common BI platform.

5. Knowledge Management

Meaning:
BI helps organizations collect, organize, share, and use knowledge for better decision-making and
continuous learning.

Uses:

 Knowledge sharing

 Regulatory compliance

 Employee learning

 Best practice management

Example:
A hospital stores treatment procedures and shares them with doctors through a knowledge
management system.

Difference between Business Intelligence (BI) and Business Analytics (BA)

Business Intelligence (BI) Business Analytics (BA)

Refer to BI is a set of technologies, BA is the use of statistical
processes, and tools that methods, technologies, and
convert raw data into useful analytical techniques to study
information for better past data, predict future trends,
business decisions. It mainly and improve business decisions.
helps in monitoring Orientation
business performance and
identifying opportunities
Focuses on past and present business present and future by
performance. predicting outcomes.
Types of Questions What happened? and What Why did it happen?, What will
is happening now? happen?, and What should be
done?
Methods Reporting, KPI monitoring, Predictive modelling, data
dashboards, scorecards, mining, text mining, statistical
OLAP, ad hoc queries, analysis, simulation,
business reports. optimization.
Tools SAP, Cognos, MicroStrategy, Tableau, QlikView, JasperSoft,
SAS. Revolution R Enterprise.
Knowledge Generation Manual (users analyze Automatic (system generates
reports). insights using analytics).

Users Business managers, Data scientists, business

executives, and business analysts, IT professionals, and
users. business users.
Easy Example A supermarket dashboard Analytics predicts that 700
shows that 500 packets of packets will be sold tomorrow
milk were sold yesterday. because of a festival

Spreadsheet Modelling
Introduction

What is a Spreadsheet?

A spreadsheet (electronic spreadsheet) is a software application that stores data in the form of rows
and columns. It is used to enter, edit, save, print, calculate, and analyze data. It also helps to create
reports and graphs quickly.

Definition

A spreadsheet is an electronic worksheet consisting of rows and columns used for storing, organizing,
calculating, and presenting data.

Features
  Data entry and editing

 Automatic calculations using formulas

 Data storage

 Sorting and filtering

 Graphs and charts

 Printing reports

Worksheet and Workbook

A worksheet is a single electronic sheet where data is entered, and calculations are performed.

A workbook is a collection of worksheets saved as one file.

Example

A company maintains:

 Sheet 1 – Sales

 Sheet 2 – Purchase

 Sheet 3 – Salary

All three worksheets together form one workbook.

Rows and Columns

 Rows are horizontal and identified by numbers (1,2,3...)

 Columns are vertical and identified by letters (A,B,C...)

The intersection of a row and a column forms a Cell.

Example:

A B C

1 Name Age

2 Anu 20

Here, B2 is the cell containing "Anu".

Cell Address

Each cell has a unique address.

Examples:

 A1

 B5

 C10

 D25

The address is formed by Column Letter + Row Number.

Cell Pointer

The selected cell is called the Cell Pointer.

Example:
If B4 is selected, the border appears around B4.

Examples of Spreadsheet Software

 Microsoft Excel

 Google Sheets

 Easy Spreadsheet

Purpose of Spreadsheets
Spreadsheets are widely used in business, education, banking, accounting, engineering, and
research.

1. Storing Data

Large amounts of data can be stored in rows and columns.

Example

Roll No Name Marks

101 Rahul 89

102 Meena 92

2. Performing Calculations

Excel performs calculations automatically using formulas.

Example
A B C

40 50 =A1+B1

Result = 90

Formula:

=A1+B1

3. Presenting Data

Spreadsheets display data using charts.

Types of charts:

 Bar Chart

 Line Chart

 Pie Chart

 Scatter Chart

 Bubble Chart

Example

Monthly Sales

Month Sales

Jan 20000

Feb 25000

Mar 30000

This data can be shown as a bar chart.

4. Gaining Insight

Businesses analyse data using spreadsheets.

Examples:

 Sales analysis

 Profit analysis

 Budget preparation

 Expense tracking

Example

A company compares monthly expenses.

Month Expense

Jan ₹30,000

Feb ₹25,000

Mar ₹35,000

The manager can easily identify the month with the highest expense.

5. Planning

Spreadsheets help managers make future decisions.

Examples:

 Production planning

 Budget planning

 Investment planning

Spreadsheet Modelling
Definition

Spreadsheet modelling is the process of solving business or mathematical problems using

spreadsheet formulas instead of writing algebraic equations.

It helps users make decisions quickly and accurately.

Advantages

 Easy to understand

 Automatic calculations

 Immediate error checking

 Flexible

 Easy to modify

 Saves time

Example 1: Product Mix Model

A company manufactures two products.

 Profit
Product

Basic ₹80

XP ₹129

Decision:

 Number of Basic computers to produce

 Number of XP computers to produce

Spreadsheet formulas calculate:

 Total profit

 Labour hours

 Constraints

Example 2: Outsourcing Decision

A company wants to decide whether to manufacture or outsource a product.

Given

Manufacturing cost

Fixed Cost = ₹50,000

Variable Cost = ₹125 per unit

Supplier Cost = ₹175 per unit

Production = 1500 units

Manufacturing Cost

= Fixed Cost + Variable Cost × Quantity

= 50,000 + (125 ×1500)

= ₹237,500

Outsourcing Cost

=175 ×1500

= ₹262,500

Since the manufacturing cost is lower,

Decision: Manufacture the product.

Usage of Spreadsheet
Major application areas:

 Finance

 Marketing

 Accounting

1)Spreadsheet in Finance

Some of the most common finance Applications include,

1. Bond Underwriting

Used for:

 Investment analysis

 Bond pricing

 Financial calculations

2. Net Present Value (NPV)

Used to evaluate investment projects.

Example

Investment = ₹40,000

Future Cash Flow = ₹47,678

NPV

=47,678−40,000

=₹7,678

Since NPV is positive, the project should be accepted.

2) Spreadsheet in Marketing

Applications

1. Survey Coding

Survey responses are entered into rows and columns.

Example

ID Gender Age

1 Male 22

2 Female 21

2. Lead Generation

Marketing managers analyze:

 Customer leads

 Sales performance

 Return on investment

Example
Month Leads Sales

Jan 120 45

Feb 150 60

This helps evaluate marketing performance.

3) Spreadsheet in Accounting

Used to maintain:

 Income

 Expenses

 Cash Book

 Trial Balance

 Profit & Loss Account

Features

 Automatic calculations

 Password protection

 Tax calculation

 Customer and vendor analysis

 Cash flow forecasting

Example: Trial Balance

Account Debit Credit

Cash ₹20,000 -

Capital - ₹20,000

Total Debit = Total Credit

Advantages of Spreadsheet
 Easy to use

 Saves time

 Reduces calculation errors

 Automatic calculations

 Data analysis

 Graphical presentation

 Supports business decisions

  Easy data storage and retrieval

Datasets
Introduction

Definition

A dataset is a collection of related data stored together. It contains separate pieces of information
that can be managed and processed as one unit by a computer.

A dataset can be:

 Accessed individually.

 Accessed as a group.

 Managed as one complete entity.

Example

A company's employee database contains:

 Employee Name

 Employee ID

 Salary

 Department

 Contact Number

This collection of related information is called a dataset.

Data Cleaning
Definition

Data cleaning is the process of identifying and correcting inaccurate, incomplete, duplicate, or
inconsistent data to improve data quality.

Purpose of Data Cleaning

 Remove errors

 Correct wrong values

 Fill missing values

 Remove duplicate records

 Improve data quality

Data Cleaning Includes

  Format checking

 Completeness checking

 Reasonableness checking

 Limit checking

 Detecting outliers

 Correcting inconsistencies

 Validation using rules and standards

Example

Before Cleaning After Cleaning

Anil Sharma Anil Sharma

Anil Sharam Anil Sharma

Age = 250 Age = 25

1) Missing Data

Definition

Missing data means some values are not available in the dataset.

It occurs because:

 People skip questions

 Data entry mistakes

 Information is not recorded

Example

Name Age Salary

Arun 25 30000

Biju — 35000

Age is missing for Biju.

Types of Missing Data

1. Missing Completely at Random (MCAR)

Definition

Missing values occur completely by chance and are not related to any variable.

Example
A survey form is lost accidentally.

Key Point

 Random missing

 No bias

 Analysis remains unbiased

2. Missing at Random (MAR)

Definition

Missing values depend on another observed variable but not on the missing value itself.

Example

Young people are less likely to answer income questions.

Missing depends on age, not income.

3. Missing Not at Random (MNAR)

Definition

Missing values depend on the missing value itself.

Example

People with very high income do not disclose their salary.

Missing values depend on salary itself.

2) Missing Data Handling

Methods used to handle missing values

1. Ignore the Values

Delete records containing missing values.

Example

If only one record has missing data, remove it.

2. Fill with Average or Mode

Replace missing values using:

 Mean (Average)

 Mode (Most frequent value)

Example

Marks:
50, 60, 70, __, 80

Average = 65
Replace missing value with 65.

3. Fill Using Attribute Mean (Binning)

Replace missing values with the average of the corresponding group.

Example

Income Groups

Low Income → Mean Population = 250

If one value is missing in this group, replace it with 250.

4. Predict Missing Values

Predict using statistical methods such as:

 Decision Tree

 Bayes Theorem

Example

If Age and Experience are known, Salary can be predicted.

3) Noisy Data

Definition

Noisy data means meaningless or incorrect data.

It reduces analysis accuracy.

Causes

 Hardware failure

 Programming errors

 OCR errors

 Typing mistakes

 Spelling mistakes

Example

"Kerla" instead of "Kerala"

Age = 250

Noise Data Handling

1. Locate Duplicate Records

Find repeated records.

Example

Anil Sharma
Anil Sharam

Both represent the same person.

2. Locate Incorrect Attribute Values

Find impossible values.

Example

Weight = 0 kg

Blood Pressure = 0

These are incorrect values.

3. Data Smoothing

Reduce noise using different techniques.

Techniques

(a) Binning

Group similar values into bins.

Example

Data:
4, 8, 9, 15

Mean = 9

Replace all values with 9.

Types:

 Smoothing by Bin Means

 Smoothing by Bin Medians

 Smoothing by Bin Boundaries

(b) Regression

Regression fits a mathematical equation to predict values.

Types

Linear Regression

 One independent variable.

Example:
Predict Salary using Experience.

Multiple Linear Regression

 More than one independent variable.

Example:
Predict Salary using Experience and Education.

(c) Clustering

Groups similar objects into clusters.

Objects outside the cluster are called Outliers.

Example

Students grouped according to marks.

One student scoring extremely low appears as an outlier.

Steps Involved in Data Cleaning

Step 1: Discrepancy Detection

Find errors due to:

 Poor data entry forms

 Manual entry mistakes

 Data decay

 Different data formats

 System errors

 Wrong use of data

 Data integration issues

 Field overloading

Example

Date entered as:

 01/04/1986

 1986/04/01

Different formats create inconsistency.

Rules Used

1. Unique Rule

Every value should be unique.

Example:
Employee ID should not repeat.

2. Consecutive Rule

There should be no missing values within the sequence.

Example
Roll Numbers:
101,102,104

Roll No. 103 is missing.

3. Null Rule

Specify how missing values should be recorded.

Example

 Numerical field → 0

 Character field → Blank or NULL

Tools Used in Data Cleaning

1. Data Scrubbing Tools

Used to:

 Correct spelling mistakes

 Detect errors

 Match duplicate records

Example

"Trivandrum" and "Thiruvananthapuram" are matched as the same place.

2. Data Auditing Tools

Used to:

 Analyze relationships

 Detect unusual data

 Identify outliers

 Perform statistical analysis

Example

Finding students whose marks are unusually high or low.

Step 2: Transformation

Definition

After identifying errors, the data is transformed into the correct format.

Transformation is done using:

 Data Migration Tools

 ETL (Extract, Transform, Load) Tools

Example
Before:
Post

After:
Designation