Detailed Report for Sales Analysis Dimensional Model

?

Introduction

This report outlines the creation of a dimensional model designed to analyze the sales performance of car repair centers. The data model is based on a sample invoice provided and focuses on the sales of both services and parts by customer, vehicle brand/model/year, and shop location across western Canada. The goal is to extract meaningful insights using SQL that can help the business optimize its operations, improve customer satisfaction, and increase profitability.

?

Overview

???? Key Information from the Invoice:

• Customer Details: Name: Jennifer Robinson Address: ABC Power Tools, 126 Naim Ave, Winnipeg, MB, R3J ?3C4 Phone: 204-771-0754
• Vehicle Information: Make: BMW Model: X5 Year: 2012 VIN: CVS123456789123-115z Mileage: 16495
• Job Performed: Diagnose front wheel vibration: 0.5 hours at $125/hour Replace front CV Axle: 3.5 hours at $125/hour Balance tires: 1 hour at $125/hour
• Parts Used: Part Number 23485: CV Axle, Quantity 1, Unit Price $878.67 Part Number 7777: Alignment, Quantity 1, Unit Price $45 Part Number W187: Wheel Weights, Quantity 4, Unit Price $12

?

• Invoice Details: Invoice Number: INV-00-12345 Date: September 10, 2023 Due Date: October 10, 2023
• Shop Location: Latino Garage Winnipeg North, Canada.
• ??????? Year of Establishment: 1971

?

Facts and Dimensions Tables for Sales Analysis

Facts:

• Service Charges (Total Labour): Total cost of services provided.
• Parts Charges (Total Parts): Total cost of parts used.
• Total Sales: Sum of service charges and parts charges.
• Sales Tax Rate: 13% per charge

Dimensions:

• Customer: Attributes related to the customer details.
• Vehicle: Attributes related to the vehicle.
• Job: Details of the job performed.
• Parts: Details of the parts used.
• Date: Transaction dates.
• Location: Shop locations.

?

Fact Table

?

?? Sales Fact

• Invoice_ID: Primary key.
• Date_KEY: Foreign key to Date
• Customer_Key: Foreign key to Customer
• Vehicle_Key: Foreign key to Vehicle.
• Total_Labour: Total service charge.
• Total_Parts: Total parts charge.
• Sales_Tax_Rate; Tax % of sales charge
• Total_Sales: Sum of service and parts charges.
• LOCATION_KEY: Foreign key to Location.

Dimension Tables

?? Customer_Details

• Customer_ID: Primary key.
• Customer_FirstName
• Customer_LastName
• Customer_Address

?

Vehicle

• Vehicle_ID: Primary key.
• Make: Vehicle make.
• Model: Vehicle model.
• Year: Vehicle year.
• VIN: Vehicle Identification Number.
• Color: The color of the Vehicle
• REG_Key: Unique Identifier for Vehicle Registration.
• Mileage:

Job Performed

• Job_ID: Primary key.
• Description: Description of the job.
• Hours: Number of hours spent on the job.
• Rate: Hourly rate for the job.
• Amount: Price of the job done.

Part Used

• Part_Key: Primary key.
• Part_Number: Part number.
• Part_Name: Name of the part.
• Quantity: The number of parts used
• Amount: The price of the part multiplied by the quantity

?

Date

·???????? Date_Key: Primary key.

·???????? Date: Transaction date.

·???????? Due_date

·???????? Year: Year of the transaction.

·???????? Month: Month of the transaction.

·???????? Day: Day of the transaction.

?

Location

Location_Key: Primary key.

Shop_Name: Name of the shop location.

?

ER Diagram Overview

1. Fact Table: Sales Fact includes foreign keys linking to each dimension table and measures for service charges, parts charges, and total sales.
2. Dimension Tables: Each dimension table contains attributes relevant to its category, such as customer details, vehicle details, job details, part details, date, and location information.

?

ER Diagram

?

MySQL Workbench Steps

1. Create Tables: Create Sales Fact and dimension tables (Customer details, Vehicle, Job, Parts, Date, Location, and Invoice).
2. Define Primary Keys: Assign primary keys to each table.
3. Establish Foreign Key Relationships: Link foreign keys in Sales Fact to primary keys in dimension tables.
4. Ensure Referential Integrity: Validate relationships between tables to maintain data consistency.

?

Logical Explanations

• Fact Table Design: The Sales fact table centralizes all sales-related metrics, ensuring comprehensive and efficient analysis of sales data.
• Dimension Table Design: Each dimension table provides context and categorization for the sales data, allowing for detailed analysis by various attributes (e.g., customer, vehicle, job, part, date, location).
• Relationships: Foreign keys in the Sales fact table link to primary keys in dimension tables, maintaining referential integrity and ensuring accurate data analysis.

Conclusion

This dimensional model effectively supports sales performance analysis for car repair centers. The model enables detailed and flexible analysis by organizing data into fact and dimension tables, helping the business optimize operations, improve customer satisfaction, and increase profitability. The ER diagram and table descriptions provide a clear and comprehensive representation of the data model, ensuring ease of use and scalability for future analysis needs.