An Enterprise Semantic Model Serving 500+ Business Users
Designed and governed a multi-domain semantic model framework for a global manufacturing organization, supporting commercial analytics, operational reporting, and self-service consumption by 500+ users across 14 countries.
Single semantic layer serving 500+ business users with sub-second query response on fact tables in the 200M+ row range
Sourced commercial data from Salesforce ERP and operational data from ServiceNow into a unified analytical model
Reduced quarterly business review preparation time from weeks to days through executive-ready dashboards built on the shared model
Mentored a team of 12+ developers on semantic modeling and DAX best practices, several of whom went on to senior architect roles
The brief
The client was a global manufacturing organization with operations across 14 countries and a long-standing investment in Microsoft BI tooling. The analytics requirement was unusual in its scale and breadth: commercial analytics drawn from Salesforce, operational analytics from ServiceNow and various plant-level systems, and a self-service population of roughly 500 business users who needed to build their own reports against governed data.
I joined the BI architecture group as a Technology Architect with a mandate that started narrow — design the next-generation semantic layer — and expanded over five years into broader stewardship of the analytics platform.
The architecture
This engagement predated Microsoft Fabric. The platform was Azure Analysis Services and Power BI shared datasets fed by a Snowflake warehouse, with SSIS and Azure Data Factory handling ingestion. The architectural challenge was not the tooling but the scale.
Domain-aligned model boundaries. The first decision was where to draw model boundaries. We landed on a domain-driven split — Commercial, Operations, Service, Plant — with explicit conformed dimensions (Customer, Product, Geography, Calendar) shared across models. This let business users compose cross-domain analyses where appropriate without forcing every domain into a single, unwieldy enterprise model.
Aggregation strategy from day one. With fact tables in the 200M+ row range and 500 concurrent users, query performance was a first-class architectural concern, not a tuning afterthought. We designed aggregation tables explicitly into the model topology — pre-aggregated at common analytical grains (month-customer, quarter-region) — with the engine routing queries to the appropriate level. Sub-second response on the most common analytical patterns became the design floor, not the ceiling.
Self-service governance via certified datasets. The 500 self-service users were the population the architecture had to serve, and the population most likely to break it. We invested in a certified-dataset model: a small number of governed semantic models, certified for self-service consumption, with documentation, change-control, and a clear escalation path. Self-service report-building happened against these. Direct database queries and ad-hoc datasets were discouraged via a combination of training, governance, and the simple expedient of making the certified path the easiest path.
Tabular Editor as the source of truth. The semantic models lived in Git, edited primarily through Tabular Editor, with deployment to Azure Analysis Services through automated pipelines. This was unusual at the time and was probably the single most consequential operational decision — it made the model definition reviewable, diff-able, and version-controlled, which is what made governance over years sustainable rather than aspirational.
What worked
The architecture survived a five-year horizon largely intact, which is the test I care about. Models built in 2018 were still serving production workloads in 2023, with documented evolution but no fundamental redesign.
The aggregation strategy was the performance lever that made the user population sustainable. Without it, the platform would have hit a scaling wall around 100 concurrent users. With it, we operated comfortably at 500+ concurrent during business hours.
The Salesforce-and-ServiceNow integration patterns — bringing commercial and operational data into the same analytical surface — became reusable templates that other teams in the organization adopted. By the time I rolled off, similar patterns were running for at least three other business units.
What didn’t, the first time
We over-invested early in dynamic security via DAX-based RLS, on the assumption that hierarchical access requirements would be intricate. They turned out to be simpler than projected — most access patterns were country-or-region based, with a small number of cross-cutting roles. Half the RLS complexity we built in year one was simplified out of the model in year two. The lesson, repeated: do not pre-build security infrastructure for hypothetical requirements.
The first cut of the certified-dataset governance model was too rigid — every change required a formal review. Power users routed around it. We adjusted to a tiered model with lighter governance for measure additions and heavier governance for dimension or relationship changes, which struck a more sustainable balance between control and velocity.
What this engagement taught me
This was the engagement where I formed most of my opinions about how to do this work at scale. The discipline of treating the semantic model as the product, the importance of self-service governance that doesn’t fight the users, the value of operational tooling like Tabular Editor and Git-backed model definitions — all of it crystallized here.
The platform is, last I heard, still in production. Some of the developers I mentored on the team have gone on to senior architect roles at other organizations. That is the longer-term outcome of this kind of work, and the one I think about most often.
Technology Stack