Dispatch Data Analysis Using Microsoft SQL Server

Database Creation, Data Exploration, Data Modelling and Business Analysis

Introduction

This project involved the analysis of Dispatch and sales data using Microsoft SQL Server. The objective was to establish a structured database, explore the dataset, create dimension tables, develop relationships between tables, and perform SQL queries to investigate product performance, customer information, sales transactions, and geographical distribution.

The project provided practical experience in data preparation, database management, data modelling, and querying relational databases to extract useful business information.

The analysis was conducted using SQL Server Management Studio (SSMS), with the original dataset imported from a CSV file.

Database Creation and Data Import

The first stage of the project involved creating a database in Microsoft SQL Server to serve as the storage environment for the Superstore dataset.

The database was created using the following SQL statement:

CREATE DATABASE DispatchDB;

This command established a new database named DispatchDB, which was used to store the imported data and subsequent analytical tables.

Importing the CSV file

The dataset was imported using SQL Server’s Import Flat File functionality. This process involved the following steps:

1. Right-clicking the newly created database in SSMS.
2. Selecting Tasks and then Import Flat File.
3. Selecting the CSV file to be imported.
4. Reviewing the data preview to confirm that the records and columns were correctly recognised.
5. Reviewing and modifying column names and data types where necessary.
6. Completing the import wizard.
7. Confirming that the import process completed successfully.
8. Refreshing the database to access the newly created table.

The imported dataset was stored in the table dbo.Dispatch.

The data was subsequently accessed using:

SELECT *
FROM dbo.Dispatch;

This query retrieved all records and columns from the imported dataset and provided an initial view of the available information.

Dispatch Analysis

Data Exploration and Preparation

Data exploration was performed to understand the structure and contents of the dataset before conducting further analysis.

The initial exploration involved examining the records in the Dispatch table and identifying relevant attributes for analytical purposes.

The dataset contained information relating to dispatch transactions, products, customers, dates, geographical locations, quantities, and sales amounts.

The following SQL queries were used to investigate specific records and understand the characteristics of the data.

Filtering products

Products with a ProductID greater than 103 were retrieved using:

SELECT *
FROM dbo.DimProduct
WHERE ProductID > 103;

This query was designed to examine products based on their identification numbers.

Filtered Products

Filtering sales transactions

Transactions with a SalesAmount greater than 30,000 were retrieved using:

SELECT *
FROM dbo.Dispatch
WHERE SalesAmount > 30000;

This helped identify higher-value transactions within the dataset.

Filtered Sales Transactions

Analysing transaction quantities

Transactions involving quantities greater than or equal to two were extracted using:

SELECT *
FROM dbo.Dispatch
WHERE Quantity >= 2;

This query enabled the examination of transactions involving multiple units of products.

Transactions Quantity Analysis

These filtering operations demonstrated the use of SQL’s WHERE clause to isolate records based on specific business conditions.

Data Modelling and Dimension Table Creation

Following the initial exploration, the dataset was organised into dimension tables to support structured analysis.

Data modelling involved separating descriptive information, such as product names, customer details, dates, and locations, from the main transaction dataset.

This approach supports a dimensional data structure similar to a star schema, where a central fact table is connected to descriptive dimension tables.

Product Dimension

The Product Dimension table was created to store product identifiers and product names.

SELECT DISTINCT
    ProductID,
    ProductName
INTO DimProduct
FROM dbo.Dispatch;

The SELECT DISTINCT statement was used to retrieve unique product combinations, reducing repeated product records in the dimension table.

Dispatch Product Table

The resulting table, DimProduct, provides descriptive information about products and supports product-level analysis.

Customer Dimension

The Customer Dimension table was created to organise customer information.

SELECT
    CustomerID,
    CustomerName
INTO DimCustomer
FROM dbo.Dispatch;

This table contains customer identifiers and names.

Separating customer information into a dimension table makes customer details easier to access and supports customer-related analysis.

Dispatch Customer Table

However, the query should use SELECT DISTINCT or another appropriate deduplication method if the intention is to create one record per customer.

Date Dimension

A Date Dimension table was created to support time-based analysis.

SELECT DISTINCT
    Order_Date,
    YEAR(Order_Date) AS Year,
    MONTH(Order_Date) AS MonthNumber,
    DAY(Order_Date) AS DayNumber,
    LEFT(DATENAME(MONTH, Order_Date), 3) AS MonthName,
    LEFT(DATENAME(WEEKDAY, Order_Date), 3) AS DayName
INTO DimDate
FROM dbo.Dispatch;

This table contains the order date and several derived date attributes:

• Year: Identifies the calendar year.
• MonthNumber: Represents the month numerically.
• DayNumber: Represents the day of the month.
• MonthName: Displays the abbreviated month name.
• DayName: Displays the abbreviated weekday name.

Dispatch Date Table

These attributes support the organisation of transactions by year, month, and day.

The date dimension is particularly useful for identifying temporal patterns and conducting future monthly, quarterly, or yearly analyses.

Location Dimension

A Location Dimension table was created to organise geographical information.

SELECT DISTINCT
    City,
    State
INTO DimLocation
FROM dbo.Dispatch;

The table contains unique city and state combinations.

Dispatch Location Table

This dimension supports geographical analysis by enabling transactions to be examined according to their locations.

Data Modelling and Table Relationships

The data modelling process involved identifying how the transaction table could be connected to the dimension tables.

Get Dumoteyim Abiye-Suku’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The Dispatch table served as the central transaction dataset, while the dimension tables provided descriptive information.

The intended relationships were:

• Dispatch to DimProduct through ProductID.
• Dispatch to DimCustomer through CustomerID.
• Dispatch to DimDate through Order_Date.
• Dispatch to DimLocation through geographical attributes.

These relationships allow transactional records to be enriched with descriptive information from the dimensions.

Relationship

The resulting model is conceptually similar to a star schema, which is widely used in analytical databases and business intelligence reporting.

Note: The supplied SQL script demonstrates joins between Dispatch and the Product and Location dimensions. It does not contain explicit foreign key constraints or a complete implementation of all four relationships. The data modelling section therefore describes the intended analytical structure rather than claiming that every relationship was formally established.

SQL-Based Business Analysis

After creating the dimension tables, several SQL queries were executed to investigate the dataset and extract business information.

6.1 Geographical Analysis

The Location Dimension table was retrieved using:

SELECT *
FROM dbo.DimLocation;

This query displayed the available geographical records.

Dispatch Location Table

To investigate locations within Lagos State, the following query was used:

SELECT *
FROM dbo.DimLocation
WHERE State = 'Lagos';

This analysis was designed to identify the cities associated with Lagos State and support geographical exploration.

Lagos State Analysis

Additionally, all dispatch transactions associated with Lagos State were retrieved using:

SELECT *
FROM dbo.Dispatch
WHERE State = 'Lagos';

This query provided transaction-level information for the selected state.

Lagos Transactions

Customer Analysis

Customer records were sorted alphabetically using:

SELECT *
FROM dbo.DimCustomer
ORDER BY CustomerName ASC;

The query organised customer names in ascending alphabetical order.

Customer Analysis

Reverse alphabetical ordering was performed using:

SELECT *
FROM dbo.DimCustomer
ORDER BY CustomerName DESC;

Customer Analysis 2

The first five customer names were retrieved using:

SELECT TOP 5 CustomerName
FROM dbo.DimCustomer;

These queries demonstrated sorting and record selection techniques that can be applied when examining customer information.

Top 5 Customers

Product Analysis

Products were sorted alphabetically by name:

SELECT *
FROM dbo.DimProduct
ORDER BY ProductName ASC;

Product Analysis

The products were also arranged by ProductID in descending order:

SELECT *
FROM dbo.DimProduct
ORDER BY ProductID DESC;

Product Analysis 2

These queries provided an organised view of the product catalogue and demonstrated how product records can be retrieved according to different sorting criteria.

Product Performance Analysis Using JOIN

An INNER JOIN was used to combine transaction information with product descriptions.

SELECT
    D.DispatchID,
    P.ProductName,
    D.Quantity,
    D.SalesAmount
FROM Dispatch AS D
INNER JOIN DimProduct AS P
    ON D.ProductID = P.ProductID;

This query retrieved the dispatch identifier, product name, quantity, and sales amount.

Product Performance Analysis

The JOIN operation enabled product descriptions to be accessed alongside transaction information, making the results more meaningful and readable.

A specific product analysis was conducted for Laptop Stand:

SELECT
    P.ProductID,
    P.ProductName,
    D.SalesAmount
FROM Dispatch AS D
INNER JOIN DimProduct AS P
    ON D.ProductID = P.ProductID
WHERE P.ProductName = 'Laptop Stand';

This query isolated transactions involving Laptop Stand and returned their corresponding sales amounts.

Laptop Stand Analysis

It demonstrates how SQL joins and filtering can be combined to investigate the performance of an individual product.

Location-Based Performance Analysis Using JOIN

The Location Dimension table was joined to the Dispatch table to retrieve geographical information alongside transaction records.

SELECT
    D.DispatchID,
    L.City,
    L.State,
    D.SalesAmount
FROM Dispatch AS D
INNER JOIN DimLocation AS L
    ON D.City = L.City;

This query was intended to retrieve dispatch identifiers, cities, states, and sales amounts.

The JOIN illustrates how location information can be integrated with transaction data to support geographical analysis.

Location-Based Performance

However, joining only on City may create incorrect or duplicated matches if the same city name appears in multiple states. A more reliable relationship would use both City and State.

Conclusion

The Dispatch analysis project provided practical experience in creating and managing a relational database using Microsoft SQL Server.

The process began with database creation and CSV import, followed by data exploration and the identification of relevant attributes. Dimension tables were then created to organise product, customer, date, and location information.

The project also demonstrated how SQL filtering, sorting, and JOIN operations can be used to investigate sales transactions, product information, customer records, and geographical distribution.

Overall, the project established a foundation for structured business analysis and demonstrated the importance of database organisation and data modelling in preparing data for analytical reporting.

The resulting database structure and SQL queries provide a starting point for more advanced analysis, including sales aggregation, customer segmentation, product ranking, and geographical performance reporting.

Recommendations for Further Analysis

Future improvements could include:

• Calculating total sales and quantity sold by product.
• Identifying the top-performing products and customers.
• Analysing monthly and yearly sales trends.
• Comparing sales performance across states and cities.
• Calculating average transaction value.
• Establishing primary keys and foreign key relationships.
• Developing a Power BI dashboard using the SQL database.

These enhancements would expand the project from exploratory SQL querying into more comprehensive business intelligence analysis.