Introduction

Welcome to the Dimensional Modelling Docs — a shared, practical source of truth for building dimensional (Kimball-style) data warehouses and marts. It collects the concepts, definitions, and reference models we rely on when designing facts and dimensions, written to be useful both to the analytics engineers on the team and to the LLMs that assist them.

Why dimensional modeling

Source systems like SAP are built to run the business — thousands of normalised tables optimised for fast, safe transactions. They are not built to analyse the business. Asking “what did we sell, to whom, by product line, this quarter?” against raw source tables means joining dozens of tables, decoding cryptic keys, and reconciling inconsistent values — slow to write, slow to run, and easy to get wrong.

Dimensional modeling reorganises that same data around how the business actually asks questions. Facts hold the measurements (amounts, quantities); dimensions hold the descriptive context (customer, product, date) you slice and group by. The result is a model that is:

• Understandable — business users recognise the structure without a data dictionary.
• Fast — star schemas are optimised for the read-heavy, aggregate queries that reporting needs.
• Consistent — conformed dimensions mean “revenue by business unit” reconciles across every report.

In short: we do dimensional modeling so the business can answer its own questions quickly, correctly, and in the same language everywhere.

Why this guide

There are many excellent Kimball books and tutorials, but we kept hitting the same gaps. They were:

• Not written for LLMs — hard to feed to an assistant as ground truth.
• Light on concrete examples — strong on theory, thin on the exact SQL and edge cases you actually meet in production.

So this guide is deliberately pragmatic: short explanations backed by real examples we have encountered while developing facts and dimensions in production.

Who is this for

This documentation is designed to be accessible and valuable across different roles and experience levels:

• Data & Analytics Engineers (Junior to Senior): To learn the technical standards, transformation patterns, and key strategies (such as hash hybrids and SCD2) required to build robust pipelines.
• Data Analysts: To understand how our data marts are structured, how to query facts and dimensions correctly, and how to utilize business-facing views.
• Business Stakeholders: To grasp the core concepts of our data models and align on shared terminology (like grain, dimensions, and facts) when defining data requirements.

How this fits with the Playbook & Templates

This guide does not exist in isolation. It acts as the conceptual bridge between our high-level processes and our low-level code templates. Here is how you can navigate the ecosystem:

• The Playbook: Covers the “how-to” of our delivery process, CI/CD, and overall architecture.
• This Guide: Covers the “what” and “why” of our dimensional models (Core Concepts, Transformations, Patterns, and Conventions).
• Templates & Reference Catalogues: For practical, everyday implementation, refer to our Dimensions Template and Facts Template. They provide concrete examples based directly on the rules defined in this guide.

Dimension

A dimension is a table that provides the descriptive context around the measurable events stored in a fact table. Where facts answer “how much?” or “how many?”, dimensions answer “who, what, where, when, and why?” — they hold the attributes analysts use to filter, group, and label results. Together with facts they form the star schema. See Dimension Tables (Kimball Group) for the original definition.

What is a dimension

A dimension table describes a single business entity — a customer, a product, a sales territory, a date. Each row is one member of that entity, and the columns are the textual or low-cardinality attributes that describe it.

• Wide and shallow. Dimensions typically have many columns but relatively few rows compared to facts. Denormalize attributes into the dimension rather than snowflaking them into sub-tables.
• Attributes are the filters and labels. Anything you would group by or put on a report axis belongs in a dimension (e.g. category, region, segment).
• One row per member version. With history tracking, a single business entity can occupy several rows over time — see Slowly Changing Dimension.
• No fact-to-fact context here. Keep numeric, additive measures in the fact table; the dimension holds context, not metrics.

erDiagram
    dim_customer ||--o{ fact_invoice_lines : "customer_sk"
    dim_customer {
        bigint customer_sk PK "surrogate key"
        string customer_id "natural key"
        string customer_name "attribute"
        string segment "attribute"
        string region "attribute"
    }

Natural Keys

The natural key is the identifier the entity already carries in the source system — a customer_id from the CRM, an SKU from the product catalogue, an order number from the OLTP database.

• Carries business meaning and may be reused, recycled, or reformatted by the source.
• Can be composite (several columns) and can change format over time.
• Stored on the dimension (often suffixed _nk) so a row can be traced back to its source record.
• Not used as the primary key of the dimension: a single natural key can map to many historical versions of a row once you track history.

customer_id = "CUST-00417"

Surrogate Keys

The surrogate key is a meaningless, warehouse-generated integer that serves as the primary key of the dimension. Every row — including every historical version of an entity — gets its own surrogate key, and fact tables join to the dimension through it.

• Typically a monotonically increasing BIGINT or identity column; carries no business meaning.
• Decouples the warehouse from source-key changes and enables Type 2 history.
• Reserve special values (e.g. -1 Unknown, -2 Not applicable) so fact foreign keys never have to be NULL.

Convention: name surrogate keys <entity>_sk, e.g. customer_sk, product_sk, date_sk.

customer_sk = 1048576   -- primary key of one specific version of a customer row

For the full set of key types and the conventions we use for each, see Kimball Keys Definitions.

See also

• Star Schema — how dimensions surround a fact table
• Kimball Keys Definitions — natural, surrogate, durable, and foreign keys
• Slowly Changing Dimension — tracking attribute history
• Grain — the atomic level of a fact table

References

• Dimension Tables — Kimball Group
• Fact Tables and Dimension Tables — foundational definitions
• Surrogate Keys — why warehouses generate their own keys

Fact

tba

Grain

The grain of a fact table is the precise meaning of a single row — the level of detail it records.

In practice: Declare the grain in business terms before choosing dimensions or facts (“one row per order line”, “one row per daily account balance”). Every dimension and measure on the table must be true at that grain; mixing grains in a single table is the most common dimensional modelling mistake.

Example: fact_sales at the grain one row per product per order line — so quantity and extended_amount are recorded per line, never per whole order.

See also: Standard Cost (declares its grain as an explicit key tuple)

Star Schema

A star schema is the foundational pattern in dimensional modelling. It organizes data into a central fact table surrounded by dimension tables, forming a star-like shape when visualized. The fact table holds measurable events (e.g. orders, shipments, invoices), while the dimensions provide the context around those events (e.g. customer, product, territory). See Star Schema (Kimball Group) for the original definition.

This structure is optimized for analytical queries. Because every dimension joins directly to the fact table, queries are predictable — analysts always know where to look for metrics (facts) and where to look for filters and groupings (dimensions).

Example Star Schema Diagram

erDiagram
    dim_sales_territory ||--o{ fact_invoice_lines : "sales_territory_sk"
    dim_product_hierarchy ||--o{ fact_invoice_lines : "product_hierarchy_sk"
    dim_industry ||--o{ fact_invoice_lines : "industry_sk"

    fact_invoice_lines {
        string sales_territory_sk FK
        string product_hierarchy_sk FK
        string industry_sk FK
        decimal quantity
        decimal amount
    }
    dim_sales_territory {
        string sales_territory_sk PK
        string territory_code_nk
        string territory_name
        string region
    }
    dim_product_hierarchy {
        string product_hierarchy_sk PK
        string product_code_nk
        string product_name
        string category
    }
    dim_industry {
        string industry_sk PK
        string industry_code_nk
        string industry_name
        string sector
    }

Rules

One fact table per star schema

Each star schema should contain exactly one fact table at one grain. The grain is the business definition of the measurement event that creates a fact record — it should always start at the lowest, most atomic level. If you need to combine metrics from different business processes (e.g. sales and inventory), build separate star schemas rather than merging everything into a single fact table. This keeps each schema focused and avoids grain conflicts. See Four-Step Dimensional Design Process and Keep to the Grain for more on defining grain.

Do not join dimensions to other dimensions

In dimensional modelling, joining a dimension to another dimension is known as an outrigger dimension. While the Kimball methodology technically allows outriggers, they are rarely necessary and they complicate the SQL logic — queries become harder to read, maintain, and optimize. As Kimball notes, outriggers should be used sparingly, and in most cases correlations between dimensions should be demoted to a fact table where both dimensions are represented as separate foreign keys. See also Design Tip #105: Snowflakes, Outriggers, and Bridges.

If you find yourself wanting to join two dimensions together, use a factless fact table instead. A factless fact table captures the relationship between dimensions as a fact at its own grain — it has no numeric measures, only foreign keys to the dimensions involved. This keeps the star schema clean and the joins predictable. See Design Tip #133: Factless Fact Tables for Simplification for practical examples.

Always use left joins, fact on the left

When joining dimensions to the fact table, always use left join with the fact table on the left side. This ensures that every fact record is preserved in the result, even if a matching dimension record is missing.

• a missing dimension match usually indicates a data quality issue — the left join makes these gaps visible rather than silently dropping rows
• if you use inner join instead, you risk losing fact records and underreporting metrics without realizing it

Keep aggregate calculations in the reporting layer

The star schema should store atomic, grain-level data. Derived calculations like percentages, ratios, running totals, and year-over-year comparisons belong in the reporting or semantic layer — not in the fact table itself.

• storing pre-aggregated values in facts makes them inflexible — they can’t be re-sliced by dimensions they weren’t originally grouped by
• let the reporting tool handle aggregation so that analysts can drill down to the detail when needed
• the one exception is additive measures (e.g. quantity, amount) — these belong in the fact table because they can be meaningfully summed across any dimension. Semi-additive measures (e.g. balances) can be summed across some dimensions but not all, and non-additive measures (e.g. unit prices, ratios) should never be summed directly

For more on aggregate tables as a performance optimization, see Aggregate Fact Tables (Kimball Group).

References

• Dimensional Modeling Techniques — Kimball Group — complete list of techniques
• A Dimensional Modeling Manifesto — the original case for dimensional modelling
• Fact Tables and Dimension Tables — foundational definitions
• Kimball Dimensional Modeling Techniques (PDF) — comprehensive reference document

Kimball Keys Definitions

In the Kimball dimensional modelling approach, keys are the backbone that connect fact tables to their dimensions and that let us track history correctly. This page defines the key types you will encounter and the conventions we use for each.

Quick reference

Natural key

The identifier an entity carries in the source system — for example a customer_id from the CRM, an SKU from the product catalogue, or an order number from the OLTP database.

• Carries business meaning and may be reused or recycled by the source.
• Can be composite (several columns) and can change format over time.
• Not used as the primary key of a dimension, because a single natural key can map to many historical versions of a row (see surrogate keys).

customer_id = "CUST-00417"

Durable (supernatural) key

A warehouse-assigned, never-changing identifier for a business entity. While surrogate keys change with every new version of a row, the durable key stays constant for the lifetime of the entity.

• Use it to group all historical versions of the same entity.
• Survives source-system migrations and natural-key reformatting.
• Sometimes called a persistent or supernatural key.

customer_durable_key = 90231   -- one value for CUST-00417 across all versions

Surrogate key

A meaningless, warehouse-generated integer that serves as the primary key of a dimension table. Every row in the dimension — including every historical version of an entity — gets its own surrogate key.

• Typically a monotonically increasing integer (BIGINT) or an identity column.
• Carries no business meaning; never expose it to end users as a “real” id.
• Decouples the warehouse from source-key changes and enables Type 2 history.

Convention: name surrogate keys <entity>_sk, e.g. customer_sk, product_sk, date_sk.

customer_sk = 1048576   -- primary key of one specific version of a customer row

Why a surrogate key instead of the natural key?

1. Slowly changing dimensions (SCD Type 2). When an attribute changes we add a new row with a new surrogate key, preserving the old version.
2. Performance. Single-column integer joins are faster and smaller than wide or composite natural keys.
3. Insulation. Source-system key changes don’t ripple into facts.
4. Late-arriving / unknown members. Reserved surrogate values can represent “Unknown” or “Not applicable” rows.

Foreign key

The column in a fact table that stores a dimension’s surrogate key, forming the join between the fact and that dimension.

• One foreign key per dimension the fact relates to.
• Always points at a surrogate key, never at a natural key.
• Should be enforced (logically, at minimum) so every fact row resolves to a valid dimension row — including the special “Unknown” member.

fact_sales.customer_sk  -->  dim_customer.customer_sk

Degenerate dimension

A dimension key that lives in the fact table itself because it has no interesting attributes of its own and therefore no separate dimension table. Classic examples: invoice number, order number, transaction id.

• Useful for grouping the line items of a single operational document.
• Stored as a column on the fact, often suffixed _id or _number.

fact_invoice_lines.invoice_number = "INV-2026-008812"

Special dimension members

Reserve a handful of surrogate key values for rows that don’t map to real source records, so that fact foreign keys never have to be NULL:

Putting it together

erDiagram
    dim_customer ||--o{ fact_sales : "customer_sk"
    dim_customer {
        bigint customer_sk PK "surrogate key"
        bigint customer_durable_key "durable key"
        string customer_id "natural key"
        string customer_name "attribute"
        date valid_from_to "SCD2 validity + is_current"
    }
    fact_sales {
        bigint customer_sk FK "to dim_customer"
        bigint product_sk FK "to dim_product"
        bigint date_sk FK "to dim_date"
        string order_number "degenerate dimension"
        decimal quantity_amount "additive facts"
    }

A fact row joins to a specific version of a customer via customer_sk. To analyse an entity across all its versions, group on customer_durable_key. To trace a record back to the source, use the customer_id natural key.

Prepare

Prepare is the first transformation in the pipeline. It turns raw application tables into clean staging tables that are ready for the later steps. You should never build facts and dimensions directly on top of raw tables — always stage first.

flowchart LR
    raw[Raw application tables] --> prepare[Prepare → staging]
    prepare --> join[Join per source]
    join --> union[Union all sources]
    union --> keys[Keys → surrogate keys]
    style prepare fill:#ffd54f,stroke:#f57f17,stroke-width:2px

Rules

Staging tables are 1:1 with raw tables

Each staging table maps to exactly one raw application table, with the same grain and (broadly) the same set of rows. Prepare is about cleaning, not reshaping — stay close to the original table so it remains easy to trace a staged row back to its source.

Clean, don’t aggregate

Typical SQL used to build staging tables:

• CAST — fix data types (text dates → DATE, numeric strings → DECIMAL).
• TRIM — strip stray whitespace from text fields.
• SELECT — pick and rename the columns you actually need.
• WHERE — drop obviously invalid rows (e.g. soft-deleted records, test data).

Avoid GROUP BY and aggregation at this stage. Changing the grain here makes the downstream join and union steps harder to reason about. Keep staging atomic and let aggregation happen later, in the reporting layer.

See also

• Join — consolidate prepared tables per source
• Grain — why staging keeps the raw grain

Join

Join is the second transformation. Once the raw tables are cleaned into staging tables, there are usually many of them. We join those staging tables into a smaller number of consolidated, per-source tables.

flowchart LR
    raw[Raw application tables] --> prepare[Prepare → staging]
    prepare --> join[Join per source]
    join --> union[Union all sources]
    union --> keys[Keys → surrogate keys]
    style join fill:#ffd54f,stroke:#f57f17,stroke-width:2px

Rules

Join per source

Build one joined table per source system. If there are three sources, you should end up with three joined tables — not one giant join across everything.

• Different sources have different data structures, grains, and key conventions. Joining them all at once produces a big, messy join that is hard to read and debug.
• Consolidating per source first keeps each join focused and predictable, and isolates source-specific quirks before everything is brought together in the union step.

Keep the target grain in mind

Join staging tables up to the grain of the entity you are building (the fact or dimension). Use left join so you don’t silently drop rows when a lookup is missing — a missing match is usually a data-quality signal worth surfacing.

See also

• Prepare — the staging tables that feed this step
• Union — combine the per-source joined tables
• Grain — the level each joined table targets

Union

Union is the third transformation. The per-source joined tables all describe the same kind of entity (e.g. customers, invoice lines) but come from different systems. Union stacks them into a single combined table.

flowchart LR
    raw[Raw application tables] --> prepare[Prepare → staging]
    prepare --> join[Join per source]
    join --> union[Union all sources]
    union --> keys[Keys → surrogate keys]
    style union fill:#ffd54f,stroke:#f57f17,stroke-width:2px

Rules

Keep a source_name column

Every record must carry a source_name column identifying the system it came from. When rows from multiple sources are combined, this column preserves the lineage of each record so you can:

• trace any row back to the source that produced it,
• filter or group metrics by source, and
• debug discrepancies between sources.

Align columns before unioning

A union requires every input to share the same column set, order, and types. Make sure the prepare and join steps have already reconciled column names and data types across sources, so the union is a clean stack rather than a place to patch up mismatches.

See also

• Join — the per-source tables that feed the union
• Keys — add surrogate keys to the unioned table

Keys

Keys is the final transformation. After sources are prepared, joined, and unioned, we add the surrogate keys that make each row uniquely addressable and that fact tables join on. See Kimball Keys Definitions for the full set of key types.

flowchart LR
    raw[Raw application tables] --> prepare[Prepare → staging]
    prepare --> join[Join per source]
    join --> union[Union all sources]
    union --> keys[Keys → surrogate keys]
    style keys fill:#ffd54f,stroke:#f57f17,stroke-width:2px

Rules

Hash the natural key

The recommended way to generate a surrogate key is to hash the natural key of the table — whether it is a fact or a dimension.

• Hashing produces a stable, deterministic value: the same natural key always yields the same surrogate key, so re-runs are reproducible and idempotent.
• It is easy to explain and audit — the key is a direct function of the business identifier rather than an opaque sequence number.
• For composite natural keys, concatenate the parts (with a separator) before hashing so the combination stays unique.

customer_sk = hash(customer_id)
invoice_line_sk = hash(invoice_number || '|' || line_number)

Convention: name surrogate keys <entity>_sk and the source identifier they are built from <entity>_nk (natural key).

Where keys live

• On a dimension, the surrogate key is the primary key — one per row, including each historical version (see Slowly Changing Dimension).
• On a fact, store the surrogate keys of the related dimensions as foreign keys, plus the fact’s own key built from its natural key.

See also

• Kimball Keys Definitions — natural, surrogate, durable, and foreign keys
• Dimension — how surrogate keys serve as the dimension primary key
• Union — the step that feeds this transformation

Unknown Members in Dimensions

An unknown member is a predefined row in a dimension table used when a fact record cannot be linked to a valid dimension record.

Instead of leaving the dimension key in the fact table as NULL, the fact points to the unknown member.

For example:

A fact record with no valid customer would use:

customer_sk = 0

Why an unknown member is needed

A fact record may fail to match a dimension for several reasons:

• the source key is missing
• the source key is invalid
• the dimension record has not been loaded yet
• data arrives in a different order across pipelines
• the source system contains inconsistent or incomplete master data
• the relationship is not applicable for a specific record

Using an unknown member allows the fact record to remain in the model while clearly indicating that the dimensional relationship could not be resolved.

This is generally preferable to:

• dropping the fact record
• leaving the foreign key as NULL
• creating joins that require special NULL handling
• silently assigning the record to an incorrect dimension member

Standard rule

Every dimension referenced by a fact table should contain an unknown member.

When a valid dimension member cannot be found, the fact table should use the surrogate key of the unknown member.

The unknown member should be added in the final dimension model as a predefined technical row, normally using UNION ALL with the regular dimension records. This ensures that the row is recreated consistently whenever the dimension is built or refreshed. Separate post-load inserts should be used only where the dimension loading strategy makes the union pattern impractical.

The recommended approach is to use an integer-based surrogate key, generated using an agreed hash or another deterministic integer key-generation function.

The standard unknown surrogate key should be:

0

The same key-generation approach must be applied consistently across dimensions and facts.

Depending on the data platform and its limitations, other approaches may also be used. For example, a platform may provide a different key-generation function or require a text-based hash such as MD5.

The selected approach should remain deterministic and reproducible for the same business key and should be defined as part of the modeling standards.

For example:

WITH

dimension_data as (
    SELECT
        HASH(customer_number, source_system) as customer_sk,
        customer_number,
        customer_name,
        source_system
    FROM source_data
),

unknown_member as (
    SELECT
        0 as customer_sk,
        'UNKNOWN' as customer_number,
        'Unknown Customer' as customer_name,
        'SYSTEM' as source_system
)

SELECT
    customer_sk, 
    customer_number, 
    customer_name, 
    source_system 
FROM unknown_member

UNION ALL

SELECT
    customer_sk, 
    customer_number, 
    customer_name, 
    source_system 
FROM dimension_data
;

Example fact lookup

When building the fact table, the dimension lookup should use a LEFT JOIN.

If no matching dimension member is found, the unknown surrogate key is assigned using COALESCE.

SELECT
    HASH(f.billing_document_number, f.billing_item_number) as billing_sk,
    f.billing_document_number,
    f.billing_item_number,
    COALESCE(d.customer_key, 0) as customer_sk,
    f.net_amount
FROM staging.billing_items as f
LEFT JOIN dimensions.dim_customer as d
    ON f.customer_number = d.customer_number
    AND f.source_system = d.source_system
;

The LEFT JOIN keeps the fact record even when the dimension lookup fails.

The COALESCE replaces the missing surrogate key with the unknown member key.

Do not use NULL foreign keys

Foreign keys from facts to dimensions should normally not be NULL.

For example, avoid:

customer_sk = NULL

Use:

customer_sk = 0

This provides several benefits:

• fact-to-dimension joins remain simple
• unmatched records remain visible in reports
• aggregations do not silently lose records
• data quality issues can be measured
• BI tools handle the relationship consistently

Example query:

SELECT
    d.customer_name,
    sum(f.net_amount) as net_amount
FROM facts.fct_billing_item as f
JOIN dimensions.dim_customer as d
    ON f.customer_key = d.customer_key
GROUP BY
    d.customer_name;

Records without a resolved customer will be grouped under:

Unknown Customer

Without an unknown member, an inner join could remove these fact records from the result entirely.

The use of an unknown member prevents record loss, but it does not remove the need to investigate unresolved dimension relationships. Monitoring and handling such cases should be covered by the data quality standards.

Unknown members in SCD Type 2 dimensions

An unknown member in an SCD Type 2 dimension should normally be a permanent technical row.

Example:

The unknown member:

• should cover the full supported technical validity range
• should not expire
• should not receive new versions
• should not be changed by the normal SCD comparison logic
• should be excluded from standard source-driven inserts and updates
• should remain marked as the current version

The dates 1900-01-01 and 9999-12-31 are technical validity boundaries. They should not be interpreted as actual business dates.

When resolving an SCD Type 2 relationship, the fact load should first attempt to find the dimension version valid at the fact event date.

If no valid historical dimension version is found, the fact is assigned to the permanent unknown member.

The unknown row does not need to be matched through the effective-date conditions. Its surrogate key is assigned as the fallback after the regular historical lookup fails.

Date dimensions

A date dimension may also contain an unknown member.

For example:

If the source date is missing or invalid, the fact can use:

date_key = 0

Do not replace an unknown date with an arbitrary real date such as:

1900-01-01

unless that date is explicitly used as the documented unknown member.

Using a real calendar date without clearly identifying it as technical can lead users to interpret it as an actual business date.

Late-arriving members

tba

Degenerate Dimension

Also known as: DD

A business identifier stored directly on a fact table that has no attributes of its own, and therefore no separate dimension table.

In practice: It usually identifies the operational transaction or document a fact row came from — an invoice number, order number, or ticket id. Keeping it on the fact lets you group the line items of a single document without a join.

Examples:

• fact_invoice_lines.invoice_number = "INV-2026-008812" — one invoice spans many line-item fact rows but needs no dim_invoice.
• standard_cost_amount = 3,4 - standard cost can be used as a degenerate dimension.

Common pitfalls:

• Don’t build a dimension table for it just to “be consistent” — with no descriptive attributes, it stays on the fact.
• If you later discover real attributes (status, channel), promote it to a proper dimension and replace the degenerate key with a surrogate-key foreign key.

See also: Kimball Keys Definitions

Outrigger

tba

Resolution engines (map_)

A resolution engine — named with the map_ prefix — is an ETL object that turns a messy code from a source system into the clean key a dimension uses, before the fact links to that dimension.

Most facts don’t need a map_ at all. They already carry a clean natural key and join the dimension directly — that is the normal case. A map_ is only for the exceptions, where the source code isn’t clean: one field might hold two different kinds of code, or a code that only makes sense with extra context, or a value buried inside a parent-child tree. A map_ handles those cases — it does the untangling once, in one place, so every fact that needs the same resolution gets the same answer.

Not a dimension, not a fact

A map_ is a supporting ETL object rather than a part of the dimensional model itself. Facts and dimensions are what the model is made of; a map_ is machinery that helps build them. Knowing that tells you where it belongs.

• Not a fact — it records no business event and holds no measures. It resolves a code, it doesn’t measure anything.
• Not a dimension — it is never filtered, grouped, or shown on a report. Users don’t see it; only the load uses it.
• A back-room tool — it lives in the ETL layer, before facts are loaded. Nothing downstream joins to it; only its output — a clean key — flows on.

If you ever want to show a map_ on a report, that’s the sign you actually needed a dimension, and the attributes belong there instead.

Why it exists

Without a map_, the same untangling has to be rewritten inside every fact that needs it. The copies slowly drift apart, the logic is buried where no one can test or reuse it, and if the source format changes you have to fix every fact instead of one object.

A map_ keeps that rule in one place, as the single source of truth. Think of it as a translator between the source system and the warehouse: the fact no longer has to understand the source’s messy codes — it just receives clean keys.

The three shapes

Almost every map_ is one of three patterns.

Code-type resolution

One field holds two kinds of code, told apart by something you can detect, like length or prefix. The engine spots the type and resolves each to the same key.

Example: an outlet field holding either a 5-digit store code or a 2-digit cluster code.

-- map_outlet
SELECT
    o.outlet_code,
    CASE WHEN LENGTH(o.outlet_code) = 5
         THEN o.outlet_code            -- store: use as-is
         ELSE lead.cluster_store_nk    -- cluster: its lead store
    END AS store_nk
FROM stg_outlets o
LEFT JOIN map_cluster_lead lead
    ON lead.outlet_code = o.outlet_code;

One row per operational input, one clean key out:

Context-dependent resolution

The code isn’t unique on its own; it becomes unique only when combined with another field. That field can be a simple piece of context or a second full-fledged code — either way, the engine combines them into one clean key.

Example: a product code that repeats across catalogues and is unique only within one.

-- map_product
SELECT c.catalogue_id, c.product_code, x.global_product_nk
FROM stg_catalogue_lines c
JOIN map_catalogue_crossref x
    ON  x.catalogue_id = c.catalogue_id
    AND x.product_code = c.product_code;

Hierarchy walk-up

The source is a parent-child tree of uneven depth, and you need a leaf resolved up to a set level. The engine climbs the tree and stops at that level.

Example: any node in an org tree resolved up to its department.

-- map_department: climb until the ancestor is a department
WITH RECURSIVE walk (node, ancestor) AS (
    SELECT node, node FROM stg_org_edges
    UNION ALL
    SELECT w.node, e.parent
    FROM walk w
    JOIN stg_org_edges e ON e.child = w.ancestor
)
SELECT node, ancestor AS department_nk
FROM walk
WHERE ancestor IN (SELECT dept_code FROM stg_departments);

Resolve before the key, never in the fact

Resolution that is complex or reused should be handled before the fact, rather than repeated inside each one. The fact should normally consume a clean natural key instead of untangling source codes itself.

One part of this is not a matter of preference: the fact does not build its own keys. It joins the dimension on the natural key and takes the surrogate key from there. The key is created once, in the dimension, and the fact only ever takes a copy.

flowchart LR
    src[Source code] --> map[map_ resolution engine]
    map --> nk[Clean natural key]
    nk --> join[Fact joins the dimension on the natural key]
    join --> fk[Fact foreign key]

In the fact load, that is one step — read the resolved key, join the dimension, fall back to Unknown when nothing resolves:

SELECT
    s.*,
    COALESCE(d.store_sk, unk.store_sk) AS store_sk
FROM stg_sales s
LEFT JOIN map_outlet m ON m.outlet_code = s.outlet_code
LEFT JOIN dim_store d  ON d.store_nk    = m.store_nk
CROSS JOIN (SELECT store_sk FROM dim_store WHERE store_nk = 'UNKNOWN') unk;

Keeping resolution and key assignment as separate steps is what lets you check a resolution on its own, reuse it across many facts, and re-run the load without surprises. A fact that resolves codes inline puts these jobs back together and brings back every problem the map_ was built to remove.

Two things to get right

• Persisted where it is shared. A map_ is normally a view or table rather than a step hidden inside one model, so the resolution can be inspected and reused. For simple logic used in a single place, keeping it inline can be reasonable — once two or more models need it, or the logic gets complex, it earns its own object.
• Deterministic. The same inputs must always produce the same key. Those inputs are not only the code — a resolution may also depend on source system, company, effective date, or other context. What matters is that the full set of inputs always resolves the same way, whenever it runs. A rule that could give a different answer on re-run is a bug, not a resolution engine.

Common Pitfalls

• Fan-out. If a resolution returns more than one row per input code, the join multiplies fact rows and silently inflates every measure. Guarantee one output per input.
• Resolving downstream. Pushing the logic into a view or the BI tool lets every consumer redo it, and the answers drift. Resolve once, in the ETL.
• No unknown handling. An unresolved code must land on a reserved Unknown member, so the row survives and the gap is visible — never a NULL an inner join can drop.
• Copying per fact. The same logic pasted into several facts is the exact drift a map_ prevents. One object, many consumers.
• Building one where none is needed. If a fact already carries a clean natural key, it joins the dimension directly — wrapping that in a map_ adds a layer that resolves nothing. Reach for a map_ only when the source code actually needs untangling.

Related

• Keys & Unknown member — the surrogate-key step a map_ feeds
• Prepare · Join · Union — the staging steps before resolution
• Naming conventions — the map_, vw_, _sk, _nk prefixes

Composite keys

tba

Secured Views

A secured view is a governed database view that exposes an approved fact or dimension through a secured/access schema.

Its purpose is to provide controlled access to modeled data by selecting approved columns and, where needed, applying access filters such as region, entity, country, or business area.

A secured view does not replace dimensional modeling. It exposes fact and dimension structures from the internal modeling layer to approved consumers without giving them direct access to the physical facts and dimensions schemas.

A secured view may:

• select specific columns
• apply access-related filters
• provide stable object names for users and BI tools

A secured view should not contain additional modeling logic, calculations, complex transformations, deduplication logic, or joins. Those should be handled earlier in the modeling layer.

A secured view should not join facts and dimensions for enrichment. It should expose either a fact or a dimension, not create a flattened business object. The only exception is a join required purely for access filtering. If the security attribute, such as region or entity, sits on a dimension rather than on the fact, the secured view may join to that dimension only to apply the access filter. Columns from the joined dimension should not be exposed unless they are part of the approved secured view definition.

Separating the modeling layer from the publishing layer allows the physical model to evolve without breaking reports. Secured views become a stable contract between the warehouse and its consumers.

The main assumption is that modeled facts and dimensions are stored as physical tables in dedicated modeling schemas, for example:

facts
dimensions

End users should not query these schemas directly. Instead, users should access curated views created on top of these tables in user-facing schemas.

The goal is to:

• keep business logic centralized in modeled fact and dimension tables
• expose only approved columns to users
• support different access scopes, for example, global vs regional access
• avoid unnecessary table duplication
• keep naming simple and understandable for business and technical users
• prevent the access layer from becoming another modeling layer.

Layer Definitions

Modeling Layer

The modeling layer contains physical tables created as part of the dimensional model.

Examples:

facts.fct_billing_cogs
facts.fct_bookings
dimensions.dim_calendar
dimensions.dim_customer
dimensions.dim_material

This layer should contain the actual business logic, calculations, joins, transformations, surrogate keys, conformed dimensions, and reusable fact definitions.

End users should generally not have direct access to this layer.

Access Layer / Publishing Layer

The access layer contains views that expose approved modeled data to users.

Examples:

finance.fct_billing_cogs
finance.dim_calendar
finance_emea.fct_billing_cogs
sales.fct_bookings
sales_emea.fct_bookings

The access layer is responsible for:

• exposing approved columns
• applying access-related filters, for example, region or entity filters
• giving users stable, business-friendly entry points
• separating global and restricted access scopes where needed
• exposing approved fact and dimension views through secured/access schemas

The access layer should not become another modeling layer.

View Design Principles

Secured views should be thin and predictable.

A secured view should only contain:

• explicit column selection
• simple column renaming where needed for user clarity
• filtering conditions required for access scope, for example, region, entity, country, or business area
• optional comments/documentation on exposed columns

A secured view shouldn’t contain:

• SELECT *
• undocumented business calculations
• joins between facts and dimensions, except when it’s necessary for access control
• joins between facts
• hidden transformation logic
• deduplication logic
• grain-changing logic
• complex CASE expressions, unless they are purely technical and approved as an exception.

Recommended pattern:

create view finance_emea.fct_billing_cogs as

SELECT
    f.billing_document_sk,
    f.billing_date_sk,
    f.sold_to_customer_sk,
    f.material_sk,
    f.sales_organization_sk,
    f.document_currency_sk,
    f.cogs_amount_doc,
    f.cogs_amount_lcy,
    f.cogs_amount_eur
FROM facts.fct_billing_cogs f
JOIN dimensions.dim_sales_organization o
    ON o.sales_organization_sk = f.sales_organization_sk
WHERE o.region_code = 'EMEA';

The view should make the access scope clear, but the calculation of cogs_amount_doc, cogs_amount_lcy, or cogs_amount_eur should already happen in the modeled fact table.

The selected columns should follow the agreed fact design. The fact structure itself is defined in the fact modeling documentation.

Common Pitfalls

• Putting undocumented business logic into the access layer. Calculations, joins, and transformations belong in the modeling layer.
• Using SELECT *. Explicitly selecting columns prevents downstream reports from changing when the modeled table evolves.
• Joining facts and dimensions for enrichment inside secured views. A secured view should expose a fact or a dimension, not create a flattened business object. Joins are allowed only when required for access filtering.
• Materializing filtered copies of fact tables before measuring whether views actually present a performance issue.
• Exposing physical modeling schemas directly to users instead of publishing curated views.

Access Pattern

Schema-Level Access by Domain and Scope

The recommended default is to create separate access-layer schemas by business domain and access scope.

Examples:

finance
finance_emea
sales
sales_emea
operations
operations_emea

Example views:

finance.fct_billing_cogs
finance.fct_booking

finance_emea.fct_billing_cogs
finance_emea.fct_booking

In this approach:

• finance contains globally accessible finance views
• finance_emea contains finance views restricted to the EMEA region
• access can usually be granted at the schema level
• all views inside a schema should follow the same access rules.

This approach is easier for users to understand and easier to govern.

A user can clearly see that:

finance.fct_billing_cogs

means global finance access, while:

finance_emea.fct_billing_cogs

means finance data limited to EMEA.

It also reduces the risk of mixing global and restricted views in one schema.

Dimension Views

Dimensions can also be exposed through secured views.

A secured dimension view should follow the same principles as a secured fact view:

• expose only approved columns
• apply access filters where required
• avoid additional modeling logic
• avoid exposing the physical dimensions schema directly to users.

If a dimension is broadly reusable and non-sensitive, it may be exposed through a shared or common access schema. If a dimension requires domain-specific or regional restrictions, it should be exposed through the relevant secured/access schema.

The naming and structure of shared dimension access schemas should follow the access schema strategy documentation.

Naming

Secured views should generally keep the same object name as the approved fact or dimension they expose.

Example:

facts.fct_billing_cogs
finance.fct_billing_cogs
finance_emea.fct_billing_cogs

If the access scope is already clear from the schema name, avoid repeating it in the view name.

Preferred:

finance_emea.fct_billing_cogs

Less preferred:

finance.fct_billing_cogs_emea

Performance Considerations

Views do not physically duplicate data. A view is usually a stored query definition on top of an underlying table. This allows multiple secured views to expose different subsets of the same modeled object without creating additional physical copies of the data.

For example, a single modeled fact:

facts.fct_billing_cogs

can be exposed through multiple secured views:

finance.fct_billing_cogs
finance_emea.fct_billing_cogs
finance_apac.fct_billing_cogs

without physically duplicating the full fact table.

This approach is preferable to creating separate filtered tables, such as:

facts.fct_billing_cogs_global
facts.fct_billing_cogs_emea
facts.fct_billing_cogs_apac

unless there is a clear performance- or platform-specific reason to materialize them.

Important Performance Notes

The actual performance depends on the database engine and optimizer.

In most modern SQL engines, simple views with column selection and filters can perform well because the optimizer can push filters down to the underlying table.

However, performance should still be tested for large fact tables, especially when:

• the underlying table is very large
• many users query the same views concurrently
• BI tools generate inefficient SQL
• access filters are complex
• the underlying table is not partitioned or clustered appropriately
• the database platform charges heavily for scanned data.

Recommended Practice

Do not duplicate tables by default.

Start with thin access-layer views on top of modeled tables.

Materialize filtered tables only when there is evidence that:

• view performance is not acceptable
• the platform cannot optimize the view properly
• concurrency creates a real bottleneck
• storage cost is lower than the repeated query cost
• the filtered object is reused heavily by many consumers.

Materialized copies should be treated as an exception and documented.

Summary

The preferred architecture is to model once and publish through controlled, thin views.

Facts and dimensions should remain in dedicated modeling schemas. Users should access data through business-friendly access-layer schemas.

The access layer should be organized by business domain and, where needed, by access scope.

Recommended example:

facts.fct_billing_cogs
dimensions.dim_calendar

finance.fct_billing_cogs
finance_emea.fct_billing_cogs
finance.dim_calendar

This approach keeps the model reusable, reduces data duplication, supports governance, and remains understandable for both technical and business users.

Slowly Changing Dimension

Also known as: SCD

A dimension whose attribute values change occasionally over time, together with the technique chosen for whether to keep or overwrite the prior values.

In practice: Pick a strategy per attribute, not per table:

• Type 1 — overwrite the value; no history kept.
• Type 2 — add a new row with a new surrogate key and an effective-date range (valid_from / valid_to / is_current); full history preserved.
• Type 3 — keep a “previous value” column alongside the current one; limited history.

Example: A customer moves city. Type 1 overwrites the city; Type 2 closes the old row (valid_to) and inserts a new current row, so historical facts still join to the address that was true at the time.

See also: Kimball Keys Definitions · Standard Cost

Naming (sk, code, number, is, vw, map)

tba

Key strategy (hash hybrid, surrogate keys)

tba

Standard Cost

Type: Dimension (per-unit cost rate) · Primary home: dim_standard_cost (SCD Type 2) · Also surfaced on: dim_product (current value only, Type 1)

Summary

The standard cost is the predetermined, planned unit cost of a product, it typically includes these costs:

• materials
• labour
• allocated overhead

Standard costs are calculated per material, typically during a periodic cost roll (often annually or quarterly). It is used for inventory valuation, margin reporting, and variance analysis against actual cost.

Standard Cost is not the price the customer pays and not the actual cost incurred.

Natural Key

Standard Cost is per unit of product, the natural key tends to be:

• Plant ID
• Material Number
• Effective Date (period in which the cost is valid)

In the warehouse this natural key maps to a cost_durable_key (stable per plant + material across every cost version) and a per-version standard_cost_sk surrogate key.

Schema

One row per: plant_id + material_number + cost version (effective period).

Source & lineage

ERP.COST_MASTER  ──┐
ERP.BOM_ROLLUP   ──┼──> stg_standard_cost ──> dim_standard_cost
ERP.COST_PERIODS ──┘                              │
                                                  └──> dim_product.standard_cost (current only, Type 1)

The cost roll job lands a new effective period; the staging model derives standard_unit_cost, closes the prior period’s cost_effective_to, and assigns the new standard_cost_sk.

How to use it

Current standard cost of a product

SELECT plant_id, material_number, standard_unit_cost, currency_code
FROM   dim_standard_cost
WHERE  is_current;

Margin — the dimensional way (fact carries the cost surrogate key)

The ETL stamps each fact row with the standard_cost_sk for the version in effect on the transaction date, so this is a plain equi-join that is already point-in-time correct — no date logic needed at query time.

SELECT  s.order_number,
        s.sale_date,
        s.extended_revenue,
        s.quantity * sc.standard_unit_cost            AS standard_cost_of_sale,
        s.extended_revenue
          - s.quantity * sc.standard_unit_cost        AS standard_margin
FROM    fact_sales         s
JOIN    dim_standard_cost  sc
  ON    sc.standard_cost_sk = s.standard_cost_sk;     -- point-in-time resolved at load

If the fact has no cost SK — point-in-time range join

When a fact only carries the natural key, match each row to the cost version that was active when it happened — never to the current cost:

SELECT  s.order_number,
        s.quantity * sc.standard_unit_cost AS standard_cost_of_sale
FROM    fact_sales        s
JOIN    dim_standard_cost sc
  ON    sc.plant_id        = s.plant_id
 AND    sc.material_number = s.material_number
 AND    s.sale_date >= sc.cost_effective_from
 AND    s.sale_date <  sc.cost_effective_to;          -- half-open interval

Purchase price / cost variance (standard vs actual)

SELECT  p.plant_id,
        p.material_number,
        SUM(p.actual_unit_cost   * p.quantity)        AS actual_cost,
        SUM(sc.standard_unit_cost * p.quantity)       AS standard_cost,
        SUM((p.actual_unit_cost - sc.standard_unit_cost) * p.quantity) AS variance
FROM    fact_purchase_receipts p
JOIN    dim_standard_cost      sc
  ON    sc.standard_cost_sk = p.standard_cost_sk
GROUP BY p.plant_id, p.material_number;

Common Pitfalls

• Standard cost is a dimension, not a fact — because its amounts are non-additive. standard_unit_cost is a per-unit rate: summing it across products, plants, or periods is meaningless (SUM(standard_unit_cost) answers no real question). You look it up and multiply by a fact quantity (quantity × standard_unit_cost) to get an additive measure — the cost of sale — which belongs in the fact/query, not here. Modelling these rates as a fact table is what tempts that erroneous SUM.
• Always join point-in-time, never to the current cost. Resolve the version at ETL into standard_cost_sk, or range-join on the natural key + date (above). Using dim_product.standard_cost (the current value) to value historical sales silently restates past margins every time a cost roll runs.
• Use a half-open interval [from, to) (>= from AND < to). Closed intervals (BETWEEN) double-count on the boundary day when one version ends and the next begins.
• standard_cost on dim_product is Type 1 (overwrite). It exists only for convenience / current-state lookups. It carries no history — don’t report trends from it.
• Currency and UoM are part of the cost. Don’t sum or compare standard_unit_cost across rows with different currency_code or uom_code. Convert first.
• Standard ≠ actual ≠ average ≠ list price. Keep cost types in separate, clearly named attributes. Mixing them is the single most common reporting error.
• Cost-roll timing. A roll dated the 1st but loaded on the 5th leaves a 4-day gap if effective dating isn’t backfilled. Validate that max(cost_effective_to) for the prior version meets min(cost_effective_from) of the next with no gap or overlap.
• Missing cost for a product. New materials may sell before a standard cost is rolled. Provide a -1 “Unknown cost” member (see Kimball Keys Definitions) and a data-quality check, rather than producing NULL margins.

Related

• Kimball Keys Definitions — natural, durable, and surrogate keys used above.
• fact_sales, fact_purchase_receipts — consumers; carry standard_cost_sk.
• dim_product — carries the current-value convenience copy (Type 1).

Change history / SCD

dim_standard_cost is an SCD Type 2 dimension with effective dating. Each cost roll closes the current row (cost_effective_to, is_current = false) and inserts a new current row with a fresh standard_cost_sk. Facts reference the version in effect at their transaction date via that standard_cost_sk. The dim_product.standard_cost convenience copy is Type 1 (overwrite, no history).

Sales Territory

Type: Dimension · Primary home: dim_sales_territory (SCD Type 2) · Also surfaced on: fact_invoice_lines (FK sales_territory_sk)

Summary

dim_sales_territory maps each salesperson (also called a sales rep or sales district) to the nested chain of sales territories they roll up through, flattened into one row per salesperson. It lets you aggregate sales, quota, and commission at any level of the territory tree — from a single rep up to global sales — with a plain GROUP BY.

It is not a geography dimension: the levels are an internal sales hierarchy, not postal/administrative geography (a rep’s territory need not match where customers live). The salesperson is the leaf of this dimension, not a full employee/HR dimension.

Natural Key

One row represents one salesperson for an assignment period (SCD Type 2); there is exactly one current row per salesperson. The natural key is the source salesperson identifier, made unique per version by the effective date:

Natural Key Fields:

• Sales Person Id

The territory tree is balanced and fixed at 6 levels, flattened onto each salesperson row — the standard Kimball treatment for a fixed-depth hierarchy (no bridge table needed). Level 1 is the root (Global Sales); level 5 is the most granular territory; the salesperson is the leaf below level 5:

L1  Global Sales
└── L2  EMEA
    └── L3  Italy
        └── L4  Northern Italy
            └── L5  District 512 – Milan
                └── Salesperson  Maria Rossi (REP-00417)

Schema

A worked row for the example above:

Source & lineage

CRM.SALES_REP        ──┐
CRM.TERRITORY_TREE   ──┼──> stg_sales_territory ──> dim_sales_territory
HR.REP_ASSIGNMENTS   ──┘                                │
                                                        └──> fact_invoice_lines.sales_territory_sk (FK, resolved at load)

TERRITORY_TREE is a parent→child recursive table. The staging model walks it, flattens the five territory levels onto each salesperson, and — on a reassignment (reorg, rep moves district) — closes the prior row’s valid_to and inserts a new current row with a fresh sales_territory_sk.

How to use it

Sales rolled up to any territory level

Because the hierarchy is flattened, grouping at any level is a plain GROUP BY — no bridge, no recursion:

SELECT  st.territory_l2_name        AS area,
        SUM(s.net_amount)           AS net_sales
FROM    fact_invoice_lines            s
JOIN    dim_sales_territory   st ON st.sales_territory_sk = s.sales_territory_sk
GROUP BY st.territory_l2_name
ORDER BY net_sales DESC;

The full territory chain for one salesperson

SELECT  salesperson_name,
        territory_l5_name, territory_l4_name, territory_l3_name,
        territory_l2_name, territory_l1_name
FROM    dim_sales_territory
WHERE   is_current
  AND   salesperson_id = 'REP-00417';

Point-in-time: attribute each sale to the territory in effect then

The ETL stamps each fact_invoice_lines row with the sales_territory_sk that was active on the sale date, so this equi-join is automatically point-in-time correct — a sale stays with the rep’s territory at the time, even after a later reorg:

SELECT  st.territory_l3_name        AS country,
        DATE_TRUNC('quarter', s.sale_date) AS qtr,
        SUM(s.net_amount)           AS net_sales
FROM    fact_invoice_lines            s
JOIN    dim_sales_territory   st ON st.sales_territory_sk = s.sales_territory_sk
GROUP BY st.territory_l3_name, DATE_TRUNC('quarter', s.sale_date);

Common Pitfalls

• Mind the level direction. Level 1 = Global (root), level 5 = most granular.
• Salesperson ≠ person. The leaf is a sales role/district. One human may cover several districts, and a district may pass between people over time. Keep this dimension at the territory-assignment grain; model the individual separately if HR attributes are needed.
• Unassigned reps. New reps may book sales before territory setup. Point the fact FK at a -1 “Unknown territory” member (see Kimball Keys Definitions) rather than leaving the FK NULL.

Related

• Kimball Keys Definitions — surrogate, durable, and natural keys plus the special “Unknown” member used above.
• Slowly Changing Dimension — the SCD Type 2 pattern this dimension uses for reassignments.
• fact_invoice_lines — primary consumer; carries sales_territory_sk.

Change history / SCD

dim_sales_territory is an SCD Type 2 dimension. Each territory reassignment closes the current row (valid_to, is_current = false) and inserts a new current row with a fresh sales_territory_sk; the salesperson_id natural key stays constant so all of a rep’s history can be grouped together. Facts reference the version in effect at their transaction date via sales_territory_sk.

Invoice Lines

Type: Fact — transaction · Grain: one row per invoice line · Primary home: fact_invoice_lines

Summary

fact_invoice_lines records the billed detail of customer invoices — one row for each product line on each invoice. It is the backbone for revenue, discount, tax, and margin reporting, sliced by customer, product, date, and sales territory.

It is not an invoice-header fact: whole-invoice charges (freight, invoice-level discounts) are not repeated on every line — keep those in a separate header fact or allocate them down to the line, or you will double-count.

Grain

One row per invoice line — a single product line item on a single invoice.

grain = (invoice_number, invoice_line_number)

invoice_number and invoice_line_number are degenerate dimensions (identifiers with no dimension table of their own); together they uniquely identify a row.

Schema

Measures & additivity

Margin is derived, not stored: net_amount − standard_cost_of_sale. Because both inputs are additive, margin can be summed at any level.

Source & lineage

ERP.INVOICE_HEADER ──┐
ERP.INVOICE_LINE   ──┼──> stg_invoice_lines ──> fact_invoice_lines
ERP.FX_RATES       ──┘

The staging model joins header to line, then resolves each foreign key. The SCD Type 2 keys (customer_sk, sales_territory_sk, standard_cost_sk) are looked up as of the invoice date, so every line carries the dimension version that was in effect when it was billed — point-in-time correct (see the pitfalls).

How to use it

Net sales by month and sales area

SELECT  d.year_month,
        st.territory_l2_name          AS area,
        SUM(f.net_amount)             AS net_sales
FROM    fact_invoice_lines   f
JOIN    dim_date             d  ON d.date_sk            = f.invoice_date_sk
JOIN    dim_sales_territory  st ON st.sales_territory_sk = f.sales_territory_sk
GROUP BY d.year_month, st.territory_l2_name;

Standard margin by product category

SELECT  p.product_category,
        SUM(f.net_amount)                            AS net_sales,
        SUM(f.standard_cost_of_sale)                 AS standard_cost,
        SUM(f.net_amount - f.standard_cost_of_sale)  AS standard_margin
FROM    fact_invoice_lines f
JOIN    dim_product        p ON p.product_sk = f.product_sk
GROUP BY p.product_category;

Average selling price (the non-additive measure, done right)

-- weighted average, NOT AVG(unit_price)
SELECT  product_sk,
        SUM(net_amount) / NULLIF(SUM(quantity), 0) AS avg_unit_price
FROM    fact_invoice_lines
GROUP BY product_sk;

Common Pitfalls

• Never SUM(unit_price) (or AVG it). It is a per-unit rate — non-additive. Compute an average as SUM(net_amount) / SUM(quantity).
• Header vs line grain. Whole-invoice charges (freight, invoice-level discounts) belong to the header, not each line. Repeating them per line double-counts — allocate them to lines or keep a separate header fact.
• Dimension fan-out. Joining to a dimension at a coarser grain or through a multi-valued bridge multiplies rows and inflates the measures. Join on the line’s own surrogate keys, and pre-aggregate the fact before any 1-to-many join.
• Point-in-time keys. customer_sk, sales_territory_sk, and standard_cost_sk are the versions in effect at the invoice date. Don’t re-derive them from the current dimension row, or history is restated on every reorg / cost roll.
• Returns & credit notes. Credits arrive as negative quantity / amounts. Keep the sign convention consistent so SUM nets correctly; don’t silently filter them out of margin.
• Currency. Amounts are in currency_code (document currency). Convert to a single reporting currency before summing across currencies.
• Unknown members. A line that can’t resolve a dimension points at the -1 “Unknown” member (see Kimball Keys Definitions), never a NULL FK.

Related

• Standard Cost — supplies standard_cost_sk / standard_unit_cost behind standard_cost_of_sale.
• Sales Territory — supplies sales_territory_sk for territory rollups.
• Grain and Kimball Keys Definitions — the concepts this fact builds on.
• dim_date, dim_customer, dim_product — the remaining conformed dimensions.

Change history / load pattern

fact_invoice_lines is a transaction-grain fact, loaded insert-only: each invoice line is written once and never updated. Corrections and returns flow in as new (often negative) lines rather than edits, preserving an auditable history. Late-arriving invoices are appended with their historical invoice_date_sk, and their SCD Type 2 keys resolve to the version that was current then (or the -1 Unknown member until the dimension member appears).

SCD, grain, degenerate dim, conformed dim, etc.

tba