Case Study 1
Case Study 1
“
“Fundamentals of Fundamentals of Business Business Analytics”Analytics”
RN Prasad and Seema Acharya RN Prasad and Seema Acharya Copyright
Case Study 1
•
•
A m
A multinational sales company has its head
ultinational sales company has its head quarters at New Delhi.
quarters at New Delhi.
••
They sell various products.
They sell various products.
••
Their products are sold
Their products are sold in different countries like Australia, Canada, France,
in different countries like Australia, Canada, France,
Germany and so on.
Germany and so on.
•
•
Th
Th e
e Pr
Pre
es
siide
dent of t
nt of the
he compa
company wants
ny wants s
sal
ale
es
s and o
and orr de
der qu
r quanti
anti ty
ty iinf
nf or
ormati
mation
on
ffor
or e
each quar
ach quar te
ter, month
r, month and day
and day iin a ye
n a year.
ar.
Business Scenario
Business Scenario
“
“Fundamentals of Fundamentals of Business Business Analytics”Analytics”
RN Prasad and Seema Acharya RN Prasad and Seema Acharya Copyright
Identify the entities
Product
Sales Region
Time
Sales Information
Conceptual Modeling
Entities
“Fundamentals of Business Analytics”
RN Prasad and Seema Acharya Copyright 2011 Wiley India Pvt. Ltd. All rights reserved.
Conceptual Modeling
Conceptual Modeling
Sales
Information
Product
Sales Region
Time
“Fundamentals of Business Analytics”
Logical Modeling
Logical Modeling
Facts
Sales
Amount
Order
Quantity
“Fundamentals of Business Analytics”
Logical Modeling
Logical Modeling
Grain Identified
Time – Day
“Fundamentals of Business Analytics”
Logical Modeling
Dimensions
Product
Sales Region
Logical Modeling
Attributes
Product – Name, Color, Size
Sales Region – Region Name
“Fundamentals of Business Analytics”
Logical Modeling
Hierarchies Identified
Year
Quarter
Logical Modeling
Putting the pieces together…
Design the schema
“Fundamentals of Business Analytics”
Logical Modeling
Fact Table
Product Key
Sales Region Key
Time key
Sales Amount
Order Quantity
Product Dimension
Table
Product Key
Product Name
Color
Size
Sales Region
Dimension Table
Sales Region Key
Territory Name
Country
Time Dimension
Table
Case Study 2
Modifying the Scenario
“Fundamentals of Business Analytics”
•
A multinational sales company has its head quarters at New Delhi.
•They sell various products.
•
Their products are sold in different countries like Australia, Canada, France,
Germany and so on.
Th e President of the company wants sales and order quanti ty inf ormation f or
each individual empl oyee and al so teamwi se for ever y quar ter to decide on
appraisal.
Identify the entities that are to be added
Conceptual Modeling
“Fundamentals of Business Analytics”
Product
Sales
Region
Time
Sales
Information
Conceptual Mode
ling
Conceptual Modeling
Establish relationship between the new and existing
entities
Conceptual Modeling
Sales
Information
Product
Sales Region
Logical Modeling
Identify the facts, dimensions, attributes,
hierarchies and grain
“
Fundamentals of Business Analytics”
Logical Modeling
Facts
Sales
amount
Order
Quantity
Logical Modeling
Identify the grain(s)
“Fundamentals of Business Analytics”
Logical Modeling
Grains Identified
Time – Quarter
Logical Modeling
Dimensions
Product
Sales Region
Time
Employee
“Fundamentals of Business Analytics”
Logical Modeling
New Attributes
Logical Modeling
Hierarchies Identified
Year
Quarter
Team
Individual
“Fundamentals of Business Analytics”
Logical Modeling
Logical Modeling
Fact Table
Product Key
Sales Region Key
Time key
Employee Key
Sales Amount
Order Quantity
Product Dimension
Table
Product Key
Product Name
Color
Size
SalesRegion
Dimension Table
Sales Region Key
Territory Name
Country
Time Dimension
Table
Time Key
Quarter
Year
Employee
Dimension Table
Employee Key
Name
Gender
Manager
Case Study 3
•
A multinational sales company has its head quarters at New Delhi.
•
They sell various products which can be categorized and subcategorized
accordingly.
•
Their products are sold in different countries like Australia, Canada, France,
Germany and so on.
The Pr esident of the company wants category wise sales and or der quan ti ty
inf ormati on for each indi vidual employee and also teamwise for ever y quar ter
to vi ew business productivity.
Modified Business Scenario
“Fundamentals of Business Analytics”
Conceptual Modeling
Sales
Information
Product
Sales Region
Employee
Logical Modeling
New Hierarchy Identified
“Fundamentals of Business Analytics”