Designing the gold layer from the BI estate you already have
Your dashboards are the requirements document. Read them before you model.
Key points
- Dashboards already encode what the business measures, how it slices it, and where the data comes from.
- Extract measures as facts and grouping fields as dimensions across the whole estate, not one dashboard at a time.
- Conform dimensions and set the grain per business process before choosing tables.
- What dashboards cannot tell you — history, slowly changing attributes, source keys — needs the source systems.
- Keep the model small: a conformed core that serves the long tail, not a table per report.
The estate is a specification
When a team migrates BI tools, the data model is often an afterthought: the new dashboards are pointed at whatever the old ones read. That carries every workaround in the old estate into the new platform.
There is a better starting point. Thousands of dashboards, used for years, already say what the business measures, how it slices those measures, and which source tables it trusts. Read them together and you have the specification for a gold layer.
Extract facts and dimensions across the estate
Measures in chart expressions — sums, counts, averages and ratios — point at the facts. The fields charts group and filter by point at the dimensions. Metrics and attributes in MicroStrategy's semantic layer say the same thing more explicitly.
Do this for the whole estate at once. A single dashboard gives you a fragment; the estate shows which measures and fields recur, which are local variations, and which are genuinely shared.
Conform, then set the grain
The same Customer, Product, Branch or Month appears across hundreds of dashboards under slightly different names. Conform them into single dimensions with agreed keys and attributes before designing any fact.
Then set the grain of each fact per business process — a loan balance per month, a claim payment per transaction, a trade per day. Mixing processes into one fact is the most common mistake in estate-driven models, because every dashboard seems to need a bit of everything.
What the dashboards cannot tell you
Dashboards describe consumption. Some design decisions need the sources:
- History: whether attributes change over time and whether reports need the value as it was
- Keys: the natural and surrogate keys the source systems actually guarantee
- Completeness: records the dashboards filter out but other consumers need
- Quality: whether the fields the dashboards rely on are populated and consistent — profile them
Keep the model small
A gold layer that serves thousands of dashboards should still be a small, conformed core — typically a handful of facts and a dozen or so dimensions — with the long tail served by those tables and by calculated fields in the BI tool. A table per dashboard recreates the estate's sprawl in the warehouse.
Favour query-friendly structures for the BI tool: star schemas over deep snowflaking, and aggregates only where a measurable performance need exists.
Review before you build
Treat the first model as a proposal. Check each fact's grain, each dimension's key, and each dashboard measure against the model: can it be answered, and how? Only then generate the physical DDL for the target warehouse.
Data Foundations generates that proposal from the parsed estate — measures, dimensions and upstream tables — and puts it in a modelling workbench with the ERD, DDL and a review loop, so the conversation with your architects starts from the estate rather than from a blank page.
Talk it through for your estate
Thirty minutes with the team that wrote this, on a sample estate and then on yours.