Star schema optimisation is mostly about reducing the work your database must do on the fact table. Smaller rows, sensible keys, useful indexes, and fast storage usually beat buying a bigger server and hoping for mercy. Hope is not an indexing strategy. It is barely a project plan.
A star schema is designed for reporting, but it can still become slow when the fact table grows, dimensions get messy, or every query has to scan more data than necessary. The aim is simple: read fewer bytes, join fewer expensive values, and let the database find the rows it needs without reenacting Jaws with your CPU.
Start with the fact table because that is where the pain lives
In most star schemas, the fact table is the largest table by a considerable margin. It stores events: orders, sales, payments, stock movements, clicks, invoices, or whatever else your business has decided to measure until the server sighs audibly.
Dimensions describe those events. A product dimension might hold product name, category, brand, supplier, and status. A date dimension might hold day, month, quarter, financial period, and weekday. Microsoft has a useful overview of dimensional modelling if you want the formal version.
Optimising the dimensions helps, but the biggest wins usually come from the fact table. If you reduce every fact row by 20 bytes and the table has 100 million rows, you have removed roughly 2 GB of raw data before overhead, indexes, and pages enter the room wearing expensive shoes.
If a column repeats on almost every fact row and has a limited number of values, it probably belongs in a dimension or as a compact key.
Use compact surrogate keys instead of repeated text
Repeated text in a fact table is convenient during development and costly during reporting. A state code, product code, branch code, or customer type may look harmless. Multiply it by millions of rows and it starts behaving like a small problem with a large appetite.
Take a state code. A Unicode variable-length value such as nvarchar(2) can use more storage than needed. A fixed char(2) is smaller. A tiny integer surrogate key is smaller again, especially when the actual state code and state name live in a dimension table.
| Fact table design | Typical storage idea | Practical effect |
|---|---|---|
nvarchar(2) state code | More bytes plus variable-length overhead | Easy to read, wasteful at scale |
char(2) state code | 2 characters per row | Better, but still repeated text |
tinyint state key | 1 byte per row | Compact and fast to join |
The same idea applies to products, periods, warehouses, sales channels, and other repeated attributes. Do not turn every value into a key just to feel clever. But for repeated low-cardinality fields, the database will thank you in its own quiet way, mostly by not timing out.
Keep fact rows narrow and predictable
A narrow fact table is usually a faster fact table. The database can fit more rows into each data page, read fewer pages from storage, and keep more useful data in memory. That matters when your report asks for monthly totals across several years.
Useful checks include:
- Store measures in suitable numeric types, not oversized defaults.
- Move descriptive text into dimensions.
- Avoid nullable columns that are only used by one report nobody admits owning.
- Use fixed-width types where they make sense.
- Keep raw source notes out of the reporting fact table.
This is not about making the schema pretty. Pretty schemas are nice. Fast schemas are nicer when finance wants the month-end report before lunch.
Index for the queries people actually run
Indexes should match real reporting patterns. If users filter by date, product, and branch, those columns deserve attention. If nobody filters by a column, indexing it because it looks important is database interior decorating.
Start by looking at common queries:
SELECT d.MonthName,
p.Category,
SUM(f.Amount)
AS TotalAmount
FROM FactSales f
JOIN DimDate d
ON f.DateKey = d.DateKey
JOIN DimProduct p
ON f.ProductKey = p.ProductKey
WHERE d.CalendarYear = 2026
GROUP BY d.MonthName,
p.Category;For a query like this, the date key and product key matter. On SQL Server, review the execution plan, actual reads, and index usage before changing anything. Microsoft documents execution plans in detail if you enjoy looking at diagrams that quietly judge your choices.
Good index candidates
- Date keys used in most filters
- Foreign keys used in joins
- Columns used in frequent grouping
- Composite keys matching common reports
Bad index candidates
- Columns nobody filters by
- Highly volatile ETL staging columns
- Wide text fields
- Indexes created because a dashboard was slow once
Partition large fact tables by date when the data supports it
Date partitioning can help when most queries read recent data or a defined reporting period. A monthly sales fact table is a common example. If the query only needs March 2026, the database should not have to rummage through 2014 like it is looking for an old tax receipt.
Partitioning also helps with maintenance. Loading, archiving, and rebuilding can be easier when the table is divided into sensible chunks. But partitioning is not magic. If every query scans all partitions, you have created extra administration with very little benefit.
Use partitioning when the access pattern is clear. Avoid it when the table is small, the date filter is inconsistent, or nobody has time to maintain it properly.
Fast disks still matter more than people like to admit
Hardware is not the first answer, but storage speed still matters. A star schema query often reads a lot of pages. If the disks are slow, everything above them waits politely while nothing useful happens.
We have seen servers with plenty of memory and many processors perform badly because storage was the bottleneck. The processors were not busy. They were waiting. Like developers in a meeting where the decision was already made last Tuesday.
Measure the storage rather than guessing. Tools such as CrystalDiskMark can give a quick read/write benchmark. For production systems, use proper database monitoring as well, because synthetic tests and real workloads are cousins, not twins.
Do not use slow storage and expect indexing to save everything. Indexes reduce work. They do not turn a tired disk array into a racehorse.
Load clean dimension data before loading facts
ETL quality affects star schema performance. If dimension keys are inconsistent, missing, or late, the fact load becomes slower and reporting becomes less reliable. You also end up with mystery rows such as Unknown Customer, which are useful once and suspicious forever after.
A reliable load process should validate source data, load dimensions first, assign keys, then load facts. Loading data into the data warehouse covers the wider ETL flow. Data warehouse performance explains how to estimate query time before the warehouse starts clearing its throat.
If you need to automate the load without writing scripts, Advanced ETL Processor Enterprise can extract, transform, validate, and load the data on a schedule. If you only have one small spreadsheet and one monthly report, you may not need it. Clean the sheet, run the report, and spend the saved time doing something more pleasant than building unnecessary infrastructure.
Test changes with real query plans, not good intentions
Every optimisation should be measured. Capture the query time, logical reads, physical reads, execution plan, and row counts before making changes. Then change one thing at a time. If you change indexes, keys, partitions, and ETL logic together, you will not know which one helped. Or which one broke Tuesday.
A simple test cycle works well:
- Pick the slow report query.
- Record the current runtime and reads.
- Check the execution plan.
- Apply one schema or index change.
- Reload realistic test data.
- Run the same query again.
Small improvements compound. Reducing row width, improving joins, and indexing the correct keys can turn a painful report into a normal one. Normal is underrated. Normal lets people go home.
Star schema optimisation checklist
- Keep the fact table as narrow as practical.
- Use surrogate keys for repeated dimension values.
- Move descriptive text into dimensions.
- Index the keys used by real reports.
- Partition large fact tables only when date filtering supports it.
- Benchmark storage before blaming the database engine.
- Validate dimensions before loading facts.
- Measure every change with the same query and realistic data.
FAQ
What is star schema optimisation?
Star schema optimisation is the process of making a dimensional data warehouse faster by reducing fact table size, improving joins, choosing useful indexes, and matching the schema to real reporting queries.
Should every fact table use surrogate keys?
In most cases, yes. Surrogate keys keep fact rows compact and make joins predictable. Natural keys can still be stored in dimensions where they are easier to manage.
Does RAID 10 improve data warehouse performance?
RAID 10 can improve read and write performance compared with slower storage layouts, but it is not a cure for poor schema design. Measure storage and query behaviour before spending money.
When should I partition a fact table?
Partition a fact table when it is large and queries regularly filter by a partition-friendly column such as date. If reports scan the whole table anyway, partitioning may add work without much benefit.
Can ETL software improve star schema performance?
ETL software helps by validating data, loading dimensions before facts, assigning keys consistently, and automating repeatable loads. It will not fix a badly designed warehouse by magic, but it can stop the nightly load from becoming a manual ceremony.
The short version: make the fact table smaller, make the joins cleaner, measure the storage, and test with real queries. If the schema still misbehaves, download the 30-day fully functional trial before your fact table starts developing a personality.