Chapter 1: Business Need On Bi

Download as pdf or txt
Download as pdf or txt
You are on page 1of 56

BUSINESS INTELLIGENCE

CHAPTER 1: BUSINESS NEED ON BI

Delivered To: VIIT (4th Year)


Delivered By: Sushil Shinde

Credenca Data Solutions Pvt. Ltd.


Office # 301, Tower S4,
Magarpatta Cybercity, PUNE 411013
+91 88 05 99 2525 (Mob)
+91 20 25363592 (Landline)
Scenario 1: 1 DMart Store

• D Mart Store Manager wants to understand:


✓ How much are the sales?

✓ How much are the expenses?

✓ Which products are the top performing product categories and


products? Which are bottom performing product categories and
products?

✓ Which brands have good demand?

✓ What are the profits?

✓ Behaviour Analytics

✓ Customer Journey Analytics

✓ Marketing Analytics

8/27/2020 Business Intelligence


Scenario 1: 1 DMart Store

Sales

Finance Inventory
Retail
Store

Supply
People
Chain

8/27/2020 Business Intelligence


Scenario 2: DMart Company
• Scope:
• Regional Manager
• Zonal Manager
• India Head
• CEO – Global
• Higher complexities
• Different requirements from different users

8/27/2020 Business Intelligence


8/27/2020 Business Intelligence
8/27/2020 Business Intelligence
8/27/2020 Business Intelligence
BI for Industries and Departments

Industries

AUTO AND AUTO PHARMACEUTICAL RETAILERS


COMPONENT MANUFACTURER
MANUFACTURER

SALES
MARKE

BANKS ADVERTISEMENTS EDUCATION

BI is required
AGRICULTURE OIL AND GAS

8/27/2020 Business Intelligence


Decision Making Based on Data

NUMBERS AND FACTS BASED ON

• Enterprises today are driven by data.


• It sheds light on what is unknown, it reduces uncertainty, and it turns decis
• But whether it’s Big Data or just plain old data, it requires a lot of work befo

8/27/2020 Business Intelligence


BI Complexities
Data Deluge

• Earlier, only Sales & Purchases data was available and analysed / reported.

• Data is coming in all forms from all areas of organizations.

• Data Volume, Variety, And Velocity

• It is inconsistent, incomplete, outdated, unformatted, and riddled with errors.

• Raw data needs integration, design, modeling, architecting, and other work before it can be
transformed into consumable information.

Taming the Analytics

• Enterprises still need leaders and decision-makers with intuition, they depend on data to
validate their intuitions.

• People at all level need information

• Organizations want to do all sorts of analytics -> advanced, predictive, diagnostics etc

Processes and Methodologies

• Handling, processing this data needs new generation tools and technologies.

• They need right set of processes and methodologies

• They need right skillsets – communication, analytical, project management etc


8/27/2020 Business Intelligence
Businesses may be awash with data, but that does not
necessarily mean they have useful information

8/27/2020 Business Intelligence


Too m

www.CartoonStock.com.

8/27/2020 Business Intelligence


What is Business Intelligence?

Definition

A set of methodologies, processes, architectures, and technologies that transform


information used to enable more effective strategic, tactical, and operational ins

Objective of BI: For Decision Making


Business Objective of BI: Taking Informed Decisions (Based on Data)

IT Objective of BI: Enabling Access to Information (Access to Data)

Processing
Data
Data

Use the raw


transactional data
generated from
source systems
8/27/2020 Business Intelligence
DIKW Pyramid

In the business world, knowledge is not just power. It is the


lifeblood of a thriving enterprise. Knowledge comes from
information, and that, in turn, comes from data.

8/27/2020 Business Intelligence


BI Strategy

• To make the most of the power of BI, an enterprise needs a strategy b


with and use data.
• Designing a data architecture that enables reporting, analytics, predictive

• Architecting a BI portfolio

• Architecting solutions with data discovery, data visualization, and in-mem

• Enabling operational and analytical BI

• Designing and implementing analytical sandboxes and hubs

• Creating data and analytical governance programs

• Creating shared BI metadata environments.

8/27/2020 Business Intelligence


“Data is the raw material of everything firms do, b

material— something to deal with, something to r

a petri dish. No more! Some say that data is the ne

oil is too limiting.

Data is the new sun: it’s limitless and touches ever

for your organization to serve customers better th

heavily in building a next-generation customer da

and profits in the age of the customer. Data is an a

line on the balance sheet right alongside property

8/27/2020 Business Intelligence


Summary

• BI is the need of all industries and departments

• A set of methodologies, processes, architectures, and technologies that transf


information used to enable more effective strategic, tactical, and operational

• Objective of BI: For Decision Making

• Data Volume, Variety, And Velocity

• Analytics of all types needed

• BI Strategy is required for proper implementation, adoption and improvement

8/27/2020 Business Intelligence


BUSINESS INTELLIGENCE

CHAPTER 2: DATA SOURCES

Delivered To: VIIT (4th Year)


Delivered By: Sushil Shinde

Credenca Data Solutions Pvt. Ltd.


Office # 301, Tower S4,
Magarpatta Cybercity, PUNE 411013
+91 88 05 99 2525 (Mob)
+91 20 25363592 (Landline)
Data and Information

Data:
Not u
into in

Inform
decisi
What is a database?

• Database is a collection of data that is stored in a structured way and organize


managed easily.

• Table: Object in which data is stor

• Data is stored in tables in row and

• One or multiple tables is called as

• Common examples: SQL Server, O

Database
Data Collection

Store

Store Stor

Store

Example of PoS: DMART


Transaction Entry in Database

Order Date Order ID Customer Name Product Qty


10-Aug-20 101 Sushil Shinde Parle G 4
10-Aug-20 102 Pankaj Vargude Maggie 6
10-Aug-20 102 Pankaj Vargude Coca Cola 1
10-Aug-20 103 Kiran Gadhave Rice 1
10-Aug-20 103 Kiran Gadhave Wheat 1
10-Aug-20 103 Kiran Gadhave Amul Ghee 1

Transaction Systems / Operational Syste


Transactions in multiple tables
Order from Apps?
Organizational Systems

• ERP

• CRM
Oracle
• Exce

• Inho

SQL Server
Data Sources

• Organizations have all different types of data sources used


for different purposes.
• For example, for ERP an organization would be using SAP
and for CRM it would be using ZohoCRM.
• Many organizations use Excels to maintain different types of
data
Data Sources Category

INTERNAL
Within organizations, managed by the organization. Outside o
Faceboo
Primavera, Excel, SAP, HR systems, IoT system
Data Sources Storage

ON PREMISE
Within organizational Premise. Managed by On

Organization
Data Sources Category

INTERNAL

On Premise Cloud On
What is the Cloud?

Cloud Computing or “Cloud”


“Cloud computing is the on-demand
availability of computer system resources,
especially data storage (cloud storage)
and computing power etc”
Data Issues and Preparation

Data Issues Data Prepa

• Data will have inconsistencies • You will

• Data will have errors • Use data

• Data will be unstructured or unformatted • Keep on

• Data will have missing values • Sum up

• And all other sorts of problems. • And all o


Summary

• Database is collection of raw data stored in multiple tables in t

• Examples: Microsoft SQL Server, Oracle, MySQL

• Organizations have Transactional systems and other types of s

• Data source Category: Internal and External

• Data Storage: On Premise and Cloud


BUSINESS INTELLIGENCE

CHAPTER 3: DATA PREPARATION

Delivered To: VIIT (4th Year)


Delivered By: Sushil Shinde

Credenca Data Solutions Pvt. Ltd.


Office # 301, Tower S4,
Magarpatta Cybercity, PUNE 411013
+91 88 05 99 2525 (Mob)
+91 20 25363592 (Landline)
BDW, Do you cook?
What is data preparation?

Raw Data.
Can you dir
What is data preparation?

You need to process and convert


your raw data into ingredients
Your Output

Output.. Something that you can eat!

Once th
fast, eas
deliciou
multiple

Don’t fee
Data Preparation is the key step

• Raw data has all forms of issues – errors, missing values, inconsistencies, not in

• Convert raw data into useable format using tools and software

• Keep in mind: Garbage In, Garbage Out.


Preparing Data: Data in two tables

Example: Two tables in Microsoft SQL Server

Month Budget

2
Table 1
2
Month Budget Cost

Join 1

Month Cost

Table 2
Preparing Data: Data in Two Databases

2
Month Budget Cost

Join 1

Microsoft SQL Server


Preparing Data: Data in two tables

Analytics considers Excel sheet as a Table


Month Budget

2
Table 1
Excel Month Budget Cost 2

Month Cost

Table 2
Microsoft SQL Server
Preparing Data: Data in Two Tables

Month Finished Good Cost Sale


Jan-20 M1 10 12
Jan-20 M2 20 15
Jan-20 M3 30 14

Month Raw Material Cost Sale


Jan-20 M1 10 12
Append Jan-20 M2 20 15
Jan-20 M3 30 14
Feb-20 M2 12 15
Feb-20 M3 13 18
Feb-20 M4 14 20
Feb-20 M1 15 22
Month Finished Good Cost Sale
Feb-20 M2 12 15
Feb-20 M3 13 18
Feb-20 M4 14 20
Feb-20 M1 15 22
Preparing Data: New Calculations

Profit = Sale - Cost

Month Raw Material Cost Sale Month Raw


Jan-20 M1 10 12 Jan-20
Jan-20 M2 20 15 Jan-20
Jan-20 M3 30 14 Jan-20
Feb-20 M2 12 15 Feb-20
Feb-20 M3 13 18 Feb-20
Feb-20 M4 14 20 Feb-20
Feb-20 M1 15 22 Feb-20
Preparing Data: Removing Inconsistencies

Inconsistency

Month Raw Material Cost Sale Profit M

Jan-20 M1 10 12 2 Ja

Jan-20 M2 20 15 -5 Ja

January 20 M3 30 14 -16 Ja

Feb-20 M2 12 15 3 F

1Feb 2020 M3 null 18 #VALUE! F

Feb-20 M4 14 20 6 F

Feb-20 M1 15 22 7 F

Null (null is actually a blank value in database. It is not zero.)


Preparing Data: Transposing

Month Jan-20 Feb-20 Mar-20 Apr-20


Transpose
Sale 11 9 9 5

Cost 18 6 6 5
When Data is Ready (Prepared)

Cleaned, Filtered, Aggregated, Organiz

You can do your cooking (Analysis) faster and


Data Preparation

• All modern analytics tools and software have inbuilt data preparation
• Example:

✓ Excel also have PowerPivot and PowerQuery

✓ Microsoft Power BI, Tableau, QlikView

• Dedicated software and tools just for data preparation


• Example: Microsoft SSIS, Talend, Informatica etc

• Everything is drag and drop (no need of coding)

• Systems are automated -> Data refreshes automatically. No need of e

• It takes 60% to 70% time to prepare data.


Check your system properties

8/27/2020 Business Intelligence


32 bit or 64 bit?

8/27/2020 Business Intelligence


Download Microsoft Power BI: https://www.microsoft.com/en-u

8/27/2020 Business Intelligence


Download as per your system is 64 bit or 32 bit. After download your s

8/27/2020 Business Intelligence


Similarly download Microsoft
Access Database Engine

Download Link:
https://www.microsoft.com/en-
us/download/details.aspx?id=54920

8/27/2020 Business Intelligence


Downloaded Files Before Next Session

• You should have:


• Microsoft Power BI Desktop set up file downloaded

• Access Database Engine set up file downloaded –> 2016 & 2010

8/27/2020 Business Intelligence

You might also like