One published Tableau Data Source · 800+ restaurants · Four audiences · One set of metric definitions
A fast-growing quick-service restaurant chain expanding from ~250 to 850+ locations needed one authoritative source of truth for daily operations reporting. Legacy dashboards computed the same metrics in three or four different places; same-restaurant-sales numbers drifted across reports, fiscal-week boundaries disagreed, and new-restaurant openings were treated inconsistently. Leaders spent time reconciling numbers rather than acting on them.
I designed and built the Operations Recap Data Mart - a single conformed-grain operational fact table paired with a published Tableau Data Source that functioned as the semantic and metrics layer for the entire restaurant base. One schema. One set of metric definitions. Four very different audiences served without building a separate model for each.
The key architectural decision was deliberately variable grain, controlled by a single History classifier on the reporting date dimension. Each business date was tagged at load time: Today, L90, FY1, FY2, FY3, Future, and every measure-group SELECT filtered and coarsened accordingly. Detail where decisions depended on it; rollup where detail had no consumer.
| History Window | Sales | Drive-Thru Speed | Forecasts & Labor |
|---|---|---|---|
| Today & L90 | Hourly by item, order type, serving period | By serving period | Forward & actuals |
| FY1 (current year) | Daily, coarsened dimensions | By serving period | Actuals only |
| FY2 (prior year) | Weekly rollup | Rolled to "All" | Not loaded |
| FY3 (2 years back) | Weekly rollup | Not loaded | Not loaded |
| Future | Not loaded | Not loaded | Forecasts & targets |
After staging each measure-group into temp tables, a stored procedure unioned them into a single conformed schema where every measure column is present in every row, zero-filled where not applicable, and a dummy-row pass guaranteed every restaurant appeared on every date in the window, ensuring downstream Tableau joins never silently dropped rows for closed days or pre-opening restaurants.
Several operational dimensions were pre-computed at load time and embedded in every row, so downstream consumers could filter without re-implementing the rules:
The operational fact table was published to Tableau Cloud as a single Data Source - the semantic layer where business metric definitions lived. Every workbook, every mobile dashboard, and every executive recap inherited these definitions. Representative calculations:
These were not metrics in the SQL; they were definitions in the Data Source, with the SQL producing atomic facts and conformed dimensions. This is the distinction that matters: the semantic layer defined the business meaning; the warehouse held the facts. A new metric required one column in the SQL, one calculation in the Data Source, and every consumer inherited it automatically.
Screen capture of the Tableau mobile dashboard: the Operations Recap as seen by restaurant managers and area directors on a tablet or phone.
Video coming soon
See migration guide for
video upload instructions
Single source of truth replaced multiple conflicting legacy operational reports; same-restaurant-sales numbers stopped drifting across reports
Distinct audiences: field operators, regional leadership, finance, and executives, served from one model without separate builds per audience
Restaurant footprint supported over four years without rebuilding the analytics architecture
When a metric definition changed, the change propagated automatically to every consumer. When a new audience needed a dashboard, they inherited the same calculations through a different layout; that is the test a semantic layer either passes or fails.
The multi-grain History classifier and the presentation-time KPI library are the same design discipline expressed in modern platforms as Snowflake Semantic Views, Databricks Metric Views, and dbt MetricFlow. The metric definitions that lived in the Tableau Data Source map directly to the Open Semantic Interchange specification. The work is portable. The discipline is the same: define metrics where every consumer can reach them, not inside the consumer.