Problem
Statement:
Let us say that the goal is to analyze sales
for an automobile company e.g. : Maruti Suzuki. We want to build a data
warehouse that will allow the user to analyze automobile sales in a number of
ways. The output information from the data warehouse shall facilitate the
following types of analysis on the sales of the car.
1.
How many Alto cars are sold in the state of
Karnataka, during the marriage seasons?
2.
Which car maximized the profits for the
hatchback sections? Were these profits less than or more than compared to the
last quarter?
3.
Which locations shall more manufacturing units
be setup to cater to the demands?
We shall try to identify the business dimensions, analyzing the
problem statement.
1. Product :
a. What kind
of cars Maruti Suzuki is manufacturing? Examples would be Maruti 800, Maruti
Zen, Wagon R, SX4, Swift and Alto.
b. What would
be the product line of each of these cars? – For instance, hatchback, sedan,
SUV, Sports or Luxury. Where would each of the products fall into?
c. What are
the colors (interior as well as exterior) in which each model is available in?
d. The first
model year of each model?
2.
Dealer :
a. Name of
the Dealer?
b. Location
of the dealer? i.e. State and City
c. Single
Brand flag? Does the dealer sells only
Maruti Suzuki cars exclusively or he sells cars of Toyota, Ford as well? This
can be answered in Yes or No hence we use a flag.
d. Date of
first operation?
3.
Customer Demographics :
a. Name of
the Customer?
b. Gender?
c. Income
range?
d. Marital
Status?
e. Vehicles
owned?
4.
Payment Method :
a. Finance
Type – Bought on Loan or Paid fully?
b. Term of
Loan in month.
c. Agent
facilitating the finances
d. Interest
rate of the loan
5.
Time: This is a common dimension. It can be
interpreted as follows in the context of the problem.
a. Date
b. Month
c. Quarter
d. Year
e. Day of
month
f.
Season –{Winter,Summer,Autumn,Spring}
g. Holiday
Flag
But using these business dimensions, what exactly are the users
analyzing? What numbers are they analyzing? The numbers the users analyze are
the measurements or metrics that measure the success of their departments.
These are the facts that indicate to the users how their departments are doing
in fulfilling their departmental objectives. In the case of the automaker,
these metrics relate to the sales. These are the numbers that tell the users
about their performance in sales. These are numbers about the sale of each
individual automobile. The set of meaningful and useful metrics for analyzing
automobile sales is as follows:
1.
Actual sale price - The
price at which a particular was sold to the customer
2.
MSRP sale price - Manufactured Suggested retail price of a product is the price which the
manufacturer recommends that the retailer sell the product. The intention was
to help to standardize prices among locations. While some stores always sell
at, or below, the suggested retail price, others do so only when items are on
sale or closeout/clearance.
3. Dealer
add-ons
4. Dealer
credits
5. Dealer
invoice
6. Amount of
down payment by the customer
7. Amount
financed
Information package diagram
PRODUCT
|
DEALER
|
CUSTOMER DEMOGRAPHICS
|
PAYMENT METHOD
|
TIME
|
Model Name
|
Name of the dealer
|
Customer Name
|
Finance Type
|
Year
|
Product Line
|
Location of the dealer
|
Marital Status
|
Term (Months)
|
Quarter
|
Launching Year
|
Single brand flag
|
Gender
|
Agent Name
|
Month
|
Interior colors
|
Date of First operation
|
Income range
|
Interest Rate
|
Date
|
Exterior Colors
|
Vehicles owned
|
Holiday Flag
|
Season
|
|
FACTS : Actual Sale Price, MSRP Price, Dealer add-ons , Dealer
credits, Dealer Invoice , Amount Financed , Amount of down payment , Quantity
Sold
|
||||
Information
package: Automaker Sales
No comments:
Post a Comment