Most slow dashboards and conflicting numbers in meetings come from the same root cause: the data model was never designed around the business. A semantic model fixes this. It sits between raw data and the people who consume it, and gives everyone one consistent, business-friendly view of the data.
This post covers how to understand your domain, which schema types you can choose from, and how to design a model that performs well.
What Is a Semantic Model?
A semantic model is a layer that translates technical data structures into business concepts. It defines:
- Entities such as Customer, Product, Order, and Date
- Relationships between those entities
- Measures, such as Total Revenue, Profit Margin, and Active Customers
- Hierarchies, such as Year → Quarter → Month → Day
- Business rules, such as "revenue excludes cancelled orders"
Tools like Power BI, Tableau, Looker, Cube, and dbt Semantic Layer all rely on this idea. If the model is well designed, report builders don't need to know SQL, and everyone gets the same answer to "What was last month's revenue?"
Step 1: Understand the Domain First
The most common mistake is opening the database and starting to build tables. Good modeling starts with conversations, not code.
1. Identify the business processes
A business process is something the company does that generates data: placing an order, shipping a product, handling a support ticket, processing a claim. Each process usually becomes a fact table.
2. Collect the business questions
Ask stakeholders what decisions they need to make. For example:
- Which products have the highest margin by region?
- How has customer retention changed over the last 12 months?
- Which sales reps are below target this quarter?
Every question points to a measure (what to calculate) and dimensions (how to slice it).
3. Define the grain
Grain is what one row in a fact table represents, for example "one line item on one order" or "one customer per day." Decide it before anything else. Mixing grains in one table is the biggest source of wrong numbers in analytics.
4. Build a business glossary
Get written definitions for terms like active customer, revenue, churn, and conversion. Departments often define the same word differently. Settle this early, because the semantic model has to encode one agreed definition.
5. Profile the source data
Check for nulls, duplicates, inconsistent keys, and data quality issues. Find out how data changes over time, because that decides whether you need history tracking (see SCDs below).
6. Create a bus matrix
A bus matrix is a simple grid with business processes as rows and shared dimensions as columns. It shows which dimensions are shared across processes and helps you design conformed dimensions.
| Process | Date | Customer | Product | Store | Employee |
|---|---|---|---|---|---|
| Sales | ✓ | ✓ | ✓ | ✓ | ✓ |
| Inventory | ✓ | ✓ | ✓ | ||
| Returns | ✓ | ✓ | ✓ | ✓ | |
| Support Tickets | ✓ | ✓ | ✓ | ✓ |
Step 2: Core Building Blocks
Fact tables store measurable events: sales amount, quantity, clicks. They are typically narrow, very long, and mostly numeric, with foreign keys to dimensions.
Dimension tables store descriptive context: customer name, product category, region. They are typically wide and shorter, with many text attributes.
Types of facts
- Transactional: one row per event (an order line)
- Periodic snapshot: one row per period (daily account balance)
- Accumulating snapshot: one row per process lifecycle, updated as stages complete (order placed → shipped → delivered)
- Factless: records that an event happened with no numeric measure (student attended class)
Slowly Changing Dimensions (SCDs)
- Type 1: overwrite old values (no history)
- Type 2: add a new row with effective dates (full history). This is the most common choice for analytics.
- Type 3: keep the previous value in an extra column (limited history)
Step 3: Schema Types and When to Use Each
1. Star Schema
One central fact table connected directly to denormalized dimension tables.
Dim_Date
|
Dim_Customer — Fact_Sales — Dim_Product
|
Dim_Store
Pros: Simple, fast queries (fewer joins), easy for business users, works best with BI engines.
Cons: Some redundancy in dimensions.
Use when: This is your default choice for most reporting.
2. Snowflake Schema
Dimensions are normalized into sub-tables (Product → Subcategory → Category).
Pros: Less storage, easier to maintain hierarchies in one place.
Cons: More joins, slower queries, more complex for report builders.
Use when: Dimensions are very large, or hierarchies are shared and change often.
3. Galaxy (Fact Constellation) Schema
Multiple fact tables share conformed dimensions.
Fact_Sales ──┐ ┌── Fact_Returns
├─ Dim_Product ┤
Fact_Inventory ┘ └── Dim_Date (shared)
Pros: Models several business processes and allows cross-process analysis (sales vs. returns).
Cons: Needs strict discipline around conformed dimensions.
Use when: An enterprise-wide model covers multiple departments.
4. Flat / One Big Table (OBT)
Everything is pre-joined into one wide table.
Pros: Very fast reads, simple for ad-hoc analysis, works well on columnar warehouses.
Cons: Heavy redundancy, hard to maintain, risk of wrong aggregation when grains are mixed.
Use when: You have a small, focused use case or a performance-critical dashboard.
5. Normalized (3NF) and Data Vault
These are best for the integration/storage layer, not for reporting. Data Vault (hubs, links, satellites) handles auditability and changing sources well. Build star schemas on top of them for consumption.
Quick Comparison
| Schema | Query Speed | Simplicity | Storage | Best For |
|---|---|---|---|---|
| Star | ★★★★★ | ★★★★★ | Medium | General BI reporting |
| Snowflake | ★★★ | ★★★ | Low | Large, hierarchical dimensions |
| Galaxy | ★★★★ | ★★★ | Medium | Multi-process enterprise models |
| Flat/OBT | ★★★★★ | ★★★★ | High | Single-purpose dashboards |
| 3NF / Data Vault | ★★ | ★★ | Low | Integration layer, not reporting |
A typical modern architecture is Raw → Integration (3NF/Data Vault) → Semantic layer (Star/Galaxy) → Reports.
Step 4: Design Principles for Efficient Reporting
1. Prefer star schema by default. Fewer joins mean faster queries and simpler logic.
2. Always declare the grain. Write it in the table's documentation, for example: "Fact_Sales: one row per order line item."
3. Use surrogate keys. Integer keys join faster than strings and protect you from source-system key changes. They also make SCD Type 2 possible.
4. Build conformed dimensions. Use one Customer, one Product, and one Date dimension shared across all facts, so numbers reconcile across reports.
5. Create a proper Date dimension. Include fiscal periods, week numbers, holidays, and flags like is_weekend. Never rely on auto-generated date hierarchies for serious reporting.
6. Use role-playing dimensions. One Date table can serve as Order Date, Ship Date, and Delivery Date through separate relationships.
7. Keep relationships simple. Use one-to-many, single-direction filters. Avoid many-to-many and bi-directional filtering unless truly needed, because they cause ambiguity and slow performance.
8. Define measures once, in the model. Put calculations like Revenue, Margin %, and YoY Growth in the semantic layer, not in each report. This is how you get a single source of truth.
9. Reduce what you load. Remove unused columns, hide technical keys, use proper data types, and reduce high-cardinality columns. A smaller model is a faster model.
10. Pre-aggregate where it helps. For very large facts, add aggregate tables (for example, daily instead of per-transaction) and let the engine route queries to them.
11. Partition and refresh incrementally. Partition large facts by date and refresh only new data instead of reloading everything.
12. Name things for business users. Use "Customer Name," not cust_nm. Add descriptions, and organize measures into folders.
A Quick Example: Retail Sales
Business question: "What is our monthly profit by product category and region?"
- Process: Sales
- Grain: One row per order line item
-
Fact_Sales:
date_key, product_key, customer_key, store_key, quantity, sales_amount, cost_amount - Dimensions: Dim_Date, Dim_Product (with category), Dim_Customer, Dim_Store (with region)
-
Measure:
Profit = SUM(sales_amount) - SUM(cost_amount)
SELECT d.month_name, p.category, s.region,
SUM(f.sales_amount - f.cost_amount) AS profit
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_store s ON f.store_key = s.store_key
GROUP BY d.month_name, p.category, s.region;
Three simple joins, a clear grain, and a reusable measure.
Common Mistakes to Avoid
- Designing from source tables instead of business questions
- Mixing different grains in one fact table
- Putting calculated logic inside individual reports
- Creating a separate Customer table for each department
- Over-normalizing the reporting layer
- Ignoring history (and then being unable to answer "what was the customer's region at the time?")
- Using many-to-many relationships to avoid fixing the data
- Skipping documentation and testing
Conclusion
A good semantic model is less about picking the "best" schema and more about understanding the business deeply. Start with processes, questions, and definitions. Set the grain. Then choose the structure that fits, which will be a star schema most of the time, a galaxy schema when multiple processes must connect, and a flat table for a narrow, performance-driven case.
Get the domain right and the technical design follows naturally, with faster reports, consistent numbers, and users who trust the data.
Top comments (0)