BI项目star schema设计咨询：共享date dimension及维度字段存储合理性

Hey there! Let's break down your two Star Schema questions step by step—since you're new to BI, I'll keep things concrete with examples so it's easier to follow.

1. Building a Shared Date Dimension for Invoice Dates & Customer Join Dates #

The core idea here is to create a single, centralized Date Dimension table that covers all date-related attributes your users might need. Both your customer dimension and sales fact table will link to this shared dimension, letting users filter by a single date range to pull both new customer counts and total sales.

Step 1: Create the Shared Date Dimension #

First, build a date table that includes all relevant date attributes. We'll use a numeric date_key (like YYYYMMDD format) as the primary key for fast joins:

CREATE TABLE dim_date (
    date_key INT PRIMARY KEY, -- Example: 20240520 for May 20, 2024
    full_date DATE NOT NULL,
    year INT NOT NULL,
    quarter INT NOT NULL,
    month INT NOT NULL,
    month_name VARCHAR(20) NOT NULL, -- e.g., "May"
    day_of_month INT NOT NULL,
    day_of_week INT NOT NULL, -- 1 = Monday, 7 = Sunday (adjust as needed)
    day_name VARCHAR(20) NOT NULL, -- e.g., "Monday"
    is_weekend BOOLEAN NOT NULL
);

Fill this table with every date from your business's start date to a reasonable future date (e.g., 1 year out) using a script or ETL tool.

Step 2: Link Your Customer Dimension & Fact Table #

Update your customer dimension to include a foreign key to dim_date:

CREATE TABLE dim_customer (
    customer_key INT PRIMARY KEY, -- Surrogate key for the dimension
    source_customer_id VARCHAR(50) NOT NULL, -- Original ID from your transaction system
    customer_name VARCHAR(100) NOT NULL,
    date_joined_key INT NOT NULL, -- Links to dim_date.date_key
    -- Add other customer attributes here (email, phone, etc.)
    FOREIGN KEY (date_joined_key) REFERENCES dim_date(date_key)
);

Then update your sales fact table to include its own date key linking to the same dim_date:

CREATE TABLE fact_sales (
    sales_key INT PRIMARY KEY, -- Surrogate key for the fact table
    customer_key INT NOT NULL,
    invoice_date_key INT NOT NULL, -- Links to dim_date.date_key
    amount DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (customer_key) REFERENCES dim_customer(customer_key),
    FOREIGN KEY (invoice_date_key) REFERENCES dim_date(date_key)
);

Step 3: Example End-User Query #

Now users can run a single query to get both new customers and sales for a selected date:

SELECT
    d.full_date,
    COUNT(DISTINCT c.customer_key) AS new_customers,
    SUM(f.amount) AS total_sales
FROM
    dim_date d
LEFT JOIN dim_customer c ON d.date_key = c.date_joined_key
LEFT JOIN fact_sales f ON d.date_key = f.invoice_date_key
WHERE
    d.full_date = '2024-05-20'
GROUP BY
    d.full_date;

2. Storing Names (Instead of IDs) in the Fact Table to Reduce Dimensions #

Short answer: This is almost always a bad idea—here's why, plus the better approach:

Why Storing Names in the Fact Table Fails #

• Data redundancy: A single cashier or category name will repeat across thousands of fact records, wasting storage and increasing the risk of inconsistency (e.g., if a cashier changes their name, you'd have to update every related fact row).
• Lost context: Dimension tables hold more than just names—they can include useful attributes like a cashier's hire date, a category's parent group, or a location's region. If you only store names in the fact table, users can't filter or group by these extra details.
• Slower queries: Comparing strings (like names) is slower than comparing integer surrogate keys, which will hurt performance as your fact table grows.

The Correct Approach: Keep Small Dimension Tables #

Even for low-cardinality attributes (like cashiers or categories), separate dimension tables are worth it. They keep your star schema consistent and flexible for future analysis. Here are examples:

Dim_Cashier

CREATE TABLE dim_cashier (
    cashier_key INT PRIMARY KEY,
    source_cashier_id VARCHAR(50) NOT NULL,
    cashier_name VARCHAR(100) NOT NULL,
    store_location VARCHAR(100), -- Extra attribute for filtering
    hire_date DATE
);

Dim_Category

CREATE TABLE dim_category (
    category_key INT PRIMARY KEY,
    source_category_id VARCHAR(50) NOT NULL,
    category_name VARCHAR(100) NOT NULL,
    parent_category VARCHAR(100) -- Extra attribute for grouping
);

Dim_Location

CREATE TABLE dim_location (
    location_key INT PRIMARY KEY,
    source_location_id VARCHAR(50) NOT NULL,
    location_name VARCHAR(100) NOT NULL,
    region VARCHAR(50),
    country VARCHAR(50)
);

Then update your fact table to link to these dimensions:

CREATE TABLE fact_sales (
    sales_key INT PRIMARY KEY,
    customer_key INT NOT NULL,
    invoice_date_key INT NOT NULL,
    cashier_key INT NOT NULL,
    category_key INT NOT NULL,
    location_key INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (cashier_key) REFERENCES dim_cashier(cashier_key),
    FOREIGN KEY (category_key) REFERENCES dim_category(category_key),
    FOREIGN KEY (location_key) REFERENCES dim_location(location_key)
);

Exception: When It Might Be Acceptable #

Only consider storing names in the fact table if all these are true:

• The attribute has extremely low cardinality (e.g., 2-3 fixed values like "Cash" vs "Card")
• It never changes (no updates to names ever needed)
• It has no additional attributes (no extra context users might want to analyze)

Even then, most BI designers prefer small dimension tables to keep the schema consistent and scalable.

内容的提问来源于stack exchange，提问作者Cloud Engineer