Designing a Dimensional Model for Sales Analysis and SQL Queries

Designing a Dimensional Model for Sales Analysis and SQL

Queries

To design the dimensional model, we must define a Fact Table and multiple Dimension Tables

that together represent the data in a way that’s optimized for analysis

1. Identify the Fact Table

• The Fact Table is where the measurable, quantitative data (facts) will be stored. For

myscenario:

o Fact Table Name: Fact_Sales

o Purpose: To store sales data, including sales amounts for both services and parts.

2. Identify Dimension Tables

• Dimension Tables contain descriptive attributes that relate to the facts in the Fact Table.

Here are the dimensions based on mydata:

▪ Other useful time attributes (e.g., Quarter, DayOfWeek)

3. Establish Relationships

• Each fact in the Fact_Sales table will be linked to its related dimensions via foreign keys.

4. Create the Tables in MySQL Workbench

• Create Dimension Tables First:

• Create the Fact Table:

5. Populate the Tables

6. Test and Refine

• Run some sample queries to ensure that the relationships between the fact and dimension

tables are correctly set up and returning the expected results.

Create a new EER (Enhanced Entity-Relationship) Diagram in MySQL Workbench.

To analyze sales performance by customer, vehicle brand/model/year, and shop location

across Western Canada.

1. Total Sales by Customer

This query will give the total sales (both services and parts) for each customer.

2. Total Sales by Vehicle Brand/Model/Year

3. Total Sales by Shop Location

This query will show the total sales for each shop location.

4. Sales Breakdown by Customer and Vehicle

This query provides a more detailed breakdown of sales by customer, further categorized by

vehicle brand, model, and year.

5. Sales Performance by Location and Date

This query allows to analyze sales performance by location, broken down by date (e.g., month,

quarter, or year).

6. Sales of Services vs. Parts by Location

This query separates the sales of services and parts by shop location.

7. Top Customers by Sales Volume

This query identifies the top customers based on their total sales volume

8. Sales by Vehicle Type Across Locations

This query shows the sales performance of different vehicle types (brand/model/year) across

various locations.

Logical Decisions

1. Fact and Dimension Separation:

• Fact_Sales table captures the core transactional data (sales) and is linked to

dimension tables that provide additional context (customer, vehicle, job, part,

date).

2. Granularity:

• The fact table’s granularity is at the level of individual sales transactions. Each

row corresponds to a single sale, which can be a service or a part.

3. Normalization:

• Dimension tables are designed to be normalized to avoid data redundancy. For

example, customer and vehicle information is stored once in their respective

tables and linked through foreign keys.

4. Flexibility:

• The model supports flexible queries for analysis. By linking facts to dimensions,

we can easily analyze sales by different attributes such as customer, vehicle

make/model/year, job type, and date.

5. Performance:

• Using a star schema (fact table in the center surrounded by dimension tables)

ensures efficient querying and reporting. Joins between the fact table and

dimension tables are straightforward and fast.

6. Extensibility:

• The model can be easily extended to include additional dimensions or facts if

needed. New attributes can be added to dimension tables without affecting the

overall structure.

Part B

Using an extended data of the Sales receipt that can be accessed here, answer the following

business questions using SQL.Objectives

1. Data Ingestion and Preparation

• Import the provided CSV files into a relational database.

• Ensure that the data is clean, properly formatted, and indexed for efficient querying.

2. Data Indexing

Create indexes on columns that are frequently used in WHERE clauses or JOIN conditions to

improve query performance.

Customer Analysis

Step 3: Answer Business Questions Using SQL

1. Total Sales by Customer

Determine Average Spending of Customers

Step 4: Analyze Frequency of Customer Visits

Analyze Patterns by Time Period (e.g., Monthly Visits)

1. Vehicle Analysis:

• Calculate the average mileage of vehicles serviced.

•

• Identify the most common vehicle makes and models brought in for service.

• Analyze the distribution of vehicle ages and identify any trends in service requirements

based on vehicle age.

1. Job Performance Analysis:

• Determine the most common types of jobs performed and their frequency.

• Calculate the total revenue generated from each type of job.

• Identify the jobs with the highest and lowest average costs.

1. Parts Usage Analysis:

• List the top 5 most frequently used parts and their total usage.

• Calculate the average cost of parts used in repairs.

• Determine the total revenue generated from parts sales.

Optimization Recommendations

• Based on my analysis, provide actionable recommendations to optimize operations, such

as:

Identifying underperforming services that may need improvement or marketing efforts.:

which is balance tires

• Suggesting parts that should be kept in higher stock due to frequent usage.: Cv-Axel is

the most demanded part

• Proposing customer loyalty programs for top-spending customers: Jennifer Robinson .