Dimension Modelling and Analysis: Latino Car Garage

Access the full notebook here

Project Overview

For this project, I designed a data model and conducted analysis for a fictional auto service center, Latino Car Garage. I used MySQL to build the data model based on the garage's receipt data and connected it locally to Jupyter Notebook, allowing me to perform in-depth analysis on real-world metrics like customer billing, vehicle servicing, and parts inventory.

The Model Design

I created a star schema model, with an Invoice Fact Table at its center, and surrounding dimension tables to provide detailed context for each transaction. Here’s an overview of the structure:

1. Invoice Fact Table: The main table includes key metrics that track each transaction, such as:

   • Invoice ID, Date, Due Date: To identify each transaction and track payment schedules.

   • Customer ID, Vehicle ID, Service ID, Part ID: Foreign keys linking to the related dimension tables.

   • Quantity, Hours, Amount, Tax Rate, Total Labor, Total Parts, Sales Tax, Total Amount: Financial details to understand the cost breakdown of each job.

2. Customer Dimension: Tracks customer details to personalize service records, including:

   • Customer ID, Name, Address, Phone Number, Email

3. Vehicle Dimension: Stores vehicle details for efficient record-keeping and trend analysis:

   • Vehicle ID, Make, Model, Year, Color, VIN, Registration Number, Mileage

4. Service DimensionHolds the details of each service offered, including:

   • Service ID, Service Description, Hourly Rate

5. Part DimensionContains information on parts used, with:

   • Part ID, Part Name, Unit Price

6. Date Dimension: A versatile table to slice data by time periods, storing:

   • Date ID, Full Date, Year, Month, Day, Weekday

Star Schema ERD

Connecting to Jupyter Notebook for Analysis

With the model set up in MySQL, I connected it to Jupyter Notebook using the '%sql' magic command for data analysis. This setup allowed me to run SQL queries and perform advanced calculations directly in the notebook, generating insights into things like:

• Customer spending and behaviour

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

• Identifying any trends in service requirements based on vehicle age

• Total revenue generated from each job

  etc..

from the Author's notebook

Conclusion

This project provided a practical, hands-on approach to database design and analytics, combining data modeling with SQL and Jupyter Notebook for actionable insights. By organizing the data in a star schema, I made it easy to query and analyze, supporting Latino Car Garage in understanding customer needs, managing parts, and optimizing service offerings.