agentleFS
santoshkanthetysantoshkanthety/powerbi-agent/skills/powerbi-star-schema-modeling/SKILL.md

powerbi-star-schema-modeling

Skill3 starsChanged 7 months agoMITView on GitHub

Star schema design, dimensions, facts. Use when the user mentions: star schema, snowflake schema, dimensional modeling, Kimball, fact table, dimension table, surrogate key, grain, SCD, conformed dimensions, data mart, data warehouse, DW design, ERD, relationship design.

The file

We split this skill into the rules it gives an agent

5 rules. About 0 are common, 5 are distinctive, and 0 are very specific to this project.

  • ≈0

    Common · estimate

    Many files like this have a rule of this kind.

  • ≈5

    Distinctive · estimate

    This author's own choices. Worth a look.

  • 0

    Very specific

    Tied to their project's names, files or tools. Probably skip.

  • Common
  • Distinctive
  • Very specific
  1. Distinctive:

    Before drawing any tables, ask:

    line 18
  2. Distinctive:

    Use when: History doesn't matter (e.g., fixing a typo in a name)

    line 84
  3. Distinctive:

    Use when: Need to report "as of" historical facts

    line 90
  4. Distinctive:

    Use when: Need both current value AND original value on same row

    line 100
  5. Distinctive:

    Rule of thumb: Use star schema for Power BI DirectLake/Import models.

    line 117

≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

Skill: Data Warehouse Modelling — Star & Snowflake Schema

Trigger

Activate when the user mentions: star schema, snowflake schema, dimensional modeling, Kimball, fact table, dimension table, surrogate key, grain, SCD, conformed dimensions, data mart, data warehouse, DW design, ERD, relationship design

What You Know

You are a Kimball-trained dimensional modelling expert with 20+ years implementing data warehouses across retail, finance, healthcare, and manufacturing. You know when to use star vs snowflake, how to handle slowly changing dimensions, and how to design for both query performance and semantic model elegance.

The Fundamental Rule: Design for the Business Question First

Before drawing any tables, ask:

  1. What is the business process? (Sales, Inventory, Orders, Claims)
  2. What is the grain? (One row per invoice line, per day, per transaction)
  3. What dimensions describe it? (Who, What, Where, When, Why, How)
  4. What facts are measured? (Amount, Quantity, Duration, Count)

Star Schema (Default Choice for Power BI)

              dim_date ───────────┐
              dim_customer ───────┤
              dim_product ────────┤──── fact_sales
              dim_store ──────────┤
              dim_promotion ──────┘

Fact Table Design

CREATE TABLE fact_sales (
    -- Surrogate keys (FK to dims)
    date_key        INT NOT NULL,
    customer_key    INT NOT NULL,
    product_key     INT NOT NULL,
    store_key       INT NOT NULL,
    -- Degenerate dimension (no dim table needed)
    invoice_number  VARCHAR(20),
    -- Measures (additive preferred)
    sales_amount    DECIMAL(18,2),
    quantity_sold   INT,
    cost_amount     DECIMAL(18,2),
    discount_amount DECIMAL(18,2),
    -- Metadata
    _loaded_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Additivity Rules:

  • ✅ Fully additive: sum across all dimensions (sales_amount, quantity)
  • ⚠️ Semi-additive: sum across some dims only (account balances — sum across time is wrong)
  • ❌ Non-additive: never sum (ratios, percentages — compute in DAX)

Dimension Table Design

CREATE TABLE dim_customer (
    customer_key    INT PRIMARY KEY,  -- Surrogate key
    customer_id     VARCHAR(20),      -- Natural/source key (for debugging)
    customer_name   VARCHAR(100),
    email           VARCHAR(200),
    -- Geography hierarchy
    city            VARCHAR(50),
    state           VARCHAR(50),
    country         VARCHAR(50),
    region          VARCHAR(50),
    -- SCD Type 2 columns
    effective_date  DATE,
    expiry_date     DATE,
    is_current      BOOLEAN DEFAULT TRUE,
    -- Metadata
    _source_system  VARCHAR(20),
    _loaded_at      TIMESTAMP
);

Slowly Changing Dimensions (SCD)

SCD Type 1 — Overwrite (No History)

Use when: History doesn't matter (e.g., fixing a typo in a name)

UPDATE dim_customer SET email = 'new@email.com' WHERE customer_id = 'C001';

SCD Type 2 — Track Full History (Most Common)

Use when: Need to report "as of" historical facts

-- Expire old record
UPDATE dim_customer SET expiry_date = TODAY(), is_current = FALSE
WHERE customer_id = 'C001' AND is_current = TRUE;
-- Insert new record
INSERT INTO dim_customer VALUES (new_surrogate_key, 'C001', ..., TODAY(), '9999-12-31', TRUE);

SCD Type 6 — Hybrid (Type 1 + 2 + 3)

Use when: Need both current value AND original value on same row

ALTER TABLE dim_customer ADD COLUMN current_region VARCHAR(50);  -- Type 1 (always current)
-- region column stays historical (Type 2), current_region always updated (Type 1)

Star vs Snowflake: Decision Guide

FactorStar SchemaSnowflake Schema
Power BI performance✅ Optimal⚠️ Extra joins
Storage efficiency❌ Some redundancy✅ Normalized
ETL complexity✅ Simple❌ More complex
Hierarchy depth✅ Flattened✅ Preserved
Large dimensions (>50 cols)✅ FineConsider split
RecommendationDefaultOnly for deeply nested hierarchies

Rule of thumb: Use star schema for Power BI DirectLake/Import models. Snowflake only when dimension tables exceed ~200 columns or hierarchy reuse is critical.

Date Dimension — Always Build Yours

CREATE TABLE dim_date (
    date_key            INT PRIMARY KEY,   -- YYYYMMDD
    full_date           DATE,
    day_of_week         INT,               -- 1=Monday
    day_name            VARCHAR(10),
    day_of_month        INT,
    day_of_year         INT,
    week_of_year        INT,
    iso_week            INT,
    month_number        INT,
    month_name          VARCHAR(10),
    month_short         CHAR(3),
    quarter_number      INT,
    quarter_name        VARCHAR(6),        -- 'Q1 2024'
    year_number         INT,
    fiscal_year         INT,               -- Adjust offset for your business
    fiscal_quarter      INT,
    is_weekend          BOOLEAN,
    is_holiday          BOOLEAN,
    holiday_name        VARCHAR(100),
    is_business_day     BOOLEAN,
    -- Rolling period flags (recalculate daily)
    is_last_30_days     BOOLEAN,
    is_last_90_days     BOOLEAN,
    is_current_month    BOOLEAN,
    is_current_year     BOOLEAN
);

Anti-Patterns to Avoid

  • ❌ Many-to-many fact-to-dimension joins (use a bridge table)
  • ❌ Storing calculated/derived columns in fact tables (do it in DAX)
  • ❌ Multiple grains in one fact table (split into separate facts)
  • ❌ Using source system natural keys as DW primary keys
  • ❌ NULL foreign keys in facts (use an "Unknown" dimension member: key = -1)
  • ❌ Skipping a date dimension (always have one, always mark your date columns)

CLI Commands

# Generate a complete date dimension for a date range
pbi-agent model generate-date-dim --start 2015-01-01 --end 2030-12-31 --fiscal-month-offset 6

# Validate star schema design in a connected model
pbi-agent model validate-schema --check star-schema

# List all relationships and flag many-to-many
pbi-agent model relationships --flag-m2m

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

  • It worked
  • Partly
  • It didn't
What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.