SQL Exploratory Data Analysis (EDA) Project

This project provides a structured SQL Exploratory Data Analysis (EDA) framework designed to inspect, profile, and extract actionable business insights from relational data warehouses. By mastering dimensional modeling concepts—specifically distinguishing between Dimensions and Measures—data analysts can systematically evaluate data boundaries, compute high-level business KPIs, perform dimensional slicing, and construct performance rankings using T-SQL.

1. The Data Analytics Spectrum & Purpose of EDA

In enterprise data analytics, projects generally fall into three distinct maturity tiers. Understanding where Exploratory Data Analysis fits within this pipeline clarifies its role as the bridge between raw architecture and high-level reporting.

Data Warehousing (Structure & ETL)
   |
   | Organizes, cleans, and structures raw data
   v
Exploratory Data Analysis (Profiling & Insights)
   |
   | Uncovers data boundaries, distributions, and baseline KPIs
   v
Advanced Analytics (Segmentation, Trends & Reporting)
   |
   | Solves complex business problems via window functions & forecasting

Analytics Spectrum Comparison

2. Project Setup & Database Provisioning Strategies

To perform EDA, the data infrastructure must first be initialized. In Microsoft SQL Server (T-SQL), there are three primary methods for provisioning the baseline DataWarehouseAnalytics database.

Provisioning Methods Comparison

3. The Fundamental Mental Model: Dimensions vs. Measures

The foundational prerequisite for effective data analysis is categorizing every column in a database as either a Dimension or a Measure.

Classification Rules Matrix

1. Dimension: Categorical, textual, temporal, or identifier values used to filter, slice, and group data. Answers "By What?" (e.g., Sales by Country, Orders by Year).
2. Measure: Quantitative numerical values that can be mathematically aggregated (SUM, AVG, MIN, MAX). Answers "How Much?" or "How Many?".

Edge Cases & Derived Classifications

• Identifier Keys (Customer_Key, Product_Key): Though stored as numeric integers, aggregating them (e.g., AVG(customer_key)) produces meaningless results. Therefore, numeric IDs are categorized as Dimensions.
• Dates (BirthDate, OrderDate): Direct date values are non-numeric and act as temporal Dimensions. However, calculating the duration between a date and the current date creates a numerical value (Age), which can be aggregated as a Measure (e.g., AVG(Age)).

4. Database Metadata Exploration (Schema Auditing)

When onboarding to an unfamiliar database, query system metadata tables directly rather than manually navigating Object Explorer interfaces.

The system schema INFORMATION_SCHEMA provides dynamic view access to table structure definitions and data dictionary details.

Inspecting All Database Tables & Views

-- Query information schema to audit all tables and views in the database
SELECT 
    table_catalog,
    table_schema,
    table_name,
    table_type
FROM INFORMATION_SCHEMA.TABLES;

Inspecting Column Attributes for a Specific Table

-- Drill down into column metadata for a target table
SELECT 
    column_name,
    data_type,
    character_maximum_length,
    is_nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'dim_customers';

5. Dimension Exploration & Granularity Profiling

Dimension profiling maps out the qualitative domain of the business. The goal is to identify unique categories, spot null entries, and evaluate dimensional hierarchies.

Formula:

SQL

Copy

SELECT DISTINCT <dimension_column> FROM <table>;

Geographic Distribution Profiling

-- Uncover all unique countries represented in the customer base
SELECT DISTINCT 
    country 
FROM gold.dim_customers;

Product Hierarchy & Granularity Inspection

Understanding the organizational hierarchy (Category -> Subcategory -> Product Name) clarifies the distinct levels of analytical granularity available.

-- Inspect product hierarchy relationships and sort logically
SELECT DISTINCT 
    category,
    subcategory,
    product_name
FROM gold.dim_products
ORDER BY category, subcategory, product_name;

6. Temporal Exploration (Date Boundaries & Time Spans)

Temporal profiling establishes the historical coverage of the dataset. It answers critical scope questions regarding operational start dates, total history length, and demographic ranges.

Formula:

SQL

Copy

SELECT 
    MIN(<date_column>) AS start_boundary,
    MAX(<date_column>) AS end_boundary 
FROM <table>;

Order Date Boundaries & History Span

-- Determine earliest/latest order dates and total operational span
SELECT 
    MIN(order_date) AS first_order_date,
    MAX(order_date) AS last_order_date,
    DATEDIFF(year, MIN(order_date), MAX(order_date)) AS order_range_years,
    DATEDIFF(month, MIN(order_date), MAX(order_date)) AS order_range_months
FROM gold.fact_sales;

Customer Demographic Boundary Profiling

-- Profile customer age boundaries using derived mathematical measures
SELECT 
    MIN(birthdate) AS oldest_birthdate,
    MAX(birthdate) AS youngest_birthdate,
    DATEDIFF(year, MIN(birthdate), GETDATE()) AS oldest_customer_age,
    DATEDIFF(year, MAX(birthdate), GETDATE()) AS youngest_customer_age
FROM gold.dim_customers;

7. Measure Exploration & Unified Business KPI Reporting

Measure profiling evaluates global aggregate metrics without dimensional slicing. It defines the macroscopic scope of business operations (e.g., total revenue, volume sold, active customer base).

Individual Core KPI Queries

-- Calculate global business revenue
SELECT SUM(sales_amount) AS total_sales FROM gold.fact_sales;

-- Calculate global physical volume sold
SELECT SUM(quantity) AS total_quantity FROM gold.fact_sales;

-- Calculate average unit selling price
SELECT AVG(price) AS average_price FROM gold.fact_sales;

-- Determine total order lines vs. distinct purchase transactions
SELECT 
    COUNT(order_number) AS total_order_lines,
    COUNT(DISTINCT order_number) AS total_distinct_orders
FROM gold.fact_sales;

Note on COUNT vs. COUNT(DISTINCT): The transaction table stores individual line items. A single transaction with 3 purchased items shares one order_number across 3 rows. Running COUNT(order_number) yields total line items (~60,000), whereas COUNT(DISTINCT order_number) accurately reflects unique customer checkout events (~27,000).

Consolidated KPI Summary Report via UNION ALL

Instead of executing disconnected queries for high-level metrics, consolidate key metrics into a clean, standardized name-value summary report using UNION ALL.

-- Consolidated High-Level Executive KPI Report
SELECT 'Total Revenue' AS measure_name, SUM(sales_amount) AS measure_value FROM gold.fact_sales
UNION ALL
SELECT 'Total Quantity Sold', SUM(quantity) FROM gold.fact_sales
UNION ALL
SELECT 'Average Selling Price', AVG(price) FROM gold.fact_sales
UNION ALL
SELECT 'Total Distinct Orders', COUNT(DISTINCT order_number) FROM gold.fact_sales
UNION ALL
SELECT 'Total Catalog Products', COUNT(product_key) FROM gold.dim_products
UNION ALL
SELECT 'Total Registered Customers', COUNT(customer_key) FROM gold.dim_customers
UNION ALL
SELECT 'Active Purchasing Customers', COUNT(DISTINCT customer_key) FROM gold.fact_sales;

8. Magnitude Analysis (Breaking Measures by Dimensions)

Magnitude analysis compares aggregate measure values across different dimensional categories. This process splits macro-level metrics into actionable components.

Formula:

SQL

Copy

SELECT 
    <dimension_column>, 
    AGGREGATE_FUNCTION(<measure_column>) AS measure_alias
FROM <fact_table>
LEFT JOIN <dim_table> ON <join_key>
GROUP BY <dimension_column>
ORDER BY measure_alias DESC;

Slicing Customer Volume by Country

-- Evaluate customer concentration across geographic markets
SELECT 
    country,
    COUNT(customer_key) AS total_customers
FROM gold.dim_customers
GROUP BY country
ORDER BY total_customers DESC;

Slicing Product Catalog & Cost Metrics by Category

-- Measure inventory depth and average product cost per category
SELECT 
    category,
    COUNT(product_key) AS total_products,
    AVG(cost) AS average_cost
FROM gold.dim_products
GROUP BY category
ORDER BY average_cost DESC;

Slicing Revenue across Normalized Dimensions via Joins

When fact measures (e.g., sales_amount) and dimension descriptors (e.g., category) reside in separate tables, join the fact table to the dimension table using a LEFT JOIN to prevent accidental record loss.

-- Analyze total revenue generated per product category
SELECT 
    p.category,
    SUM(f.sales_amount) AS total_revenue
FROM gold.fact_sales f
LEFT JOIN gold.dim_products p
    ON f.product_key = p.product_key
GROUP BY p.category
ORDER BY total_revenue DESC;

-- Slicing total item quantity sold across geographic markets
SELECT 
    c.country,
    SUM(f.quantity) AS total_units_sold
FROM gold.fact_sales f
LEFT JOIN gold.dim_customers c
    ON f.customer_key = c.customer_key
GROUP BY c.country
ORDER BY total_units_sold DESC;

9. High vs. Low Cardinality in Dimensional Slicing

Cardinality refers to the number of unique values contained within a dimension column. Recognizing dimension cardinality directly impacts how result sets are interpreted and displayed.

Cardinality Impact Summary

10. Ranking Analysis: Identifying Top and Bottom Performers

Ranking analysis orders dimensions based on aggregate performance metrics to isolate top-tier performers from low-tier operational bottlenecks.

Formula:

SQL

Copy

-- Top N Pattern (T-SQL syntax)
SELECT TOP (N)
    <dimension_column>,
    SUM(<measure_column>) AS total_measure
FROM <fact_table>
GROUP BY <dimension_column>
ORDER BY total_measure DESC; -- Use ASC for Bottom N

Method 1: Direct Sorting via TOP Clause

-- Find the Top 5 products generating the highest revenue
SELECT TOP (5)
    p.product_name,
    SUM(f.sales_amount) AS total_revenue
FROM gold.fact_sales f
LEFT JOIN gold.dim_products p 
    ON f.product_key = p.product_key
GROUP BY p.product_name
ORDER BY total_revenue DESC;

-- Find the Bottom 5 lowest-performing products by revenue
SELECT TOP (5)
    p.product_name,
    SUM(f.sales_amount) AS total_revenue
FROM gold.fact_sales f
LEFT JOIN gold.dim_products p 
    ON f.product_key = p.product_key
GROUP BY p.product_name
ORDER BY total_revenue ASC;

Method 2: Advanced Window Ranking Functions (ROW_NUMBER)

For complex reporting scenarios where secondary slicing or dynamic sub-filtering is required, use window functions like ROW_NUMBER() or RANK() wrapped in a CTE or Subquery.

-- Rank products by revenue using Window Functions and filter top 5 via Subquery
SELECT 
    product_name,
    total_revenue,
    revenue_rank
FROM (
    SELECT 
        p.product_name,
        SUM(f.sales_amount) AS total_revenue,
        ROW_NUMBER() OVER (ORDER BY SUM(f.sales_amount) DESC) AS revenue_rank
    FROM gold.fact_sales f
    LEFT JOIN gold.dim_products p 
        ON f.product_key = p.product_key
    GROUP BY p.product_name
) AS ranked_products
WHERE revenue_rank <= 5;

11. Real-World Edge Cases, Pitfalls & Debugging Workflows

1. Inaccurate Order Counts from Line-Item Granularity

• Problem: Querying COUNT(order_number) over counts line items rather than unique checkouts.
• Fix: Use COUNT(DISTINCT order_number) when reporting transactional volume.

2. Slicing Drops Records Due to Missing Foreign Keys

• Problem: Using INNER JOIN discards fact transactions if a product key or customer key is missing or orphaned in the dimension table.
• Fix: Use LEFT JOIN originating from the primary fact table (fact_sales) to retain all metric values, leaving unmatched dimension attributes as NULL.

3. Presence of Uncategorized Data (NULL Dimensions)

• Problem: Aggregation outputs display NULL row buckets (e.g., 7 catalog items with no category).
• Fix: Identify orphaned records during the EDA profiling phase to trigger ETL data-cleaning updates before generating analytical models.

-- Audit query to locate orphaned product keys lacking assigned categories
SELECT * 
FROM gold.dim_products 
WHERE category IS NULL;

Summary and Key Takeaways

• Strict Categorization Dualism: Every column in relational analytics must be categorized as a Dimension (used to filter/group) or a Measure (used to quantify/aggregate).
• Metadata Auditing First: Begin EDA programmatically by querying system dynamic views (INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS) to profile database objects without relying on GUI menus.
• Granularity Mapping: Use SELECT DISTINCT across dimensional fields to establish natural category hierarchies (Category -> Subcategory -> Product Name).
• Temporal Boundary Profiling: Always evaluate data freshness and historical depth using MIN(), MAX(), and DATEDIFF() on primary timestamp fields.
• Unified KPI Summary Reporting: Consolidate disparate aggregate measurements into a single high-level key-value metric report using standard UNION ALL statements.
• Safe Dimensional Joins: Preserve underlying metric integrity during dimensional slicing by default-joining from Fact tables to Dimension tables via LEFT JOIN.
• Deduplicated Order Metrics: Avoid miscalculating transactional frequency by differentiating between line-item count (COUNT) and transaction count (COUNT(DISTINCT)).
• Flexible Performer Ranking: Utilize TOP (N) queries for quick baseline sorting, and switch to Window Functions (ROW_NUMBER() OVER (...)) inside CTEs or subqueries when building modular analytics pipelines.