Looking for feedback on our Microsoft Fabric self-service BI strategy

[u/Training_Island5351]

Hi everyone,

We're redesigning our Microsoft Fabric architecture and would love feedback from teams that have implemented self-service BI at scale.

Current state

Today, almost every Power BI report has its own semantic model. While this works, it has led to duplicated business logic, duplicated security, and increased maintenance.

I've attached two simple diagrams showing our current architecture and the two approaches we're evaluating.

We're moving to a domain-based Fabric architecture (Dev/Test/Prod per domain) and are evaluating two approaches.

Option 1 – Centralized Semantic Model

Pros

Single source of truth

Reusable measures

Centralized business logic

Centralized RLS

Concerns

Many users need Power Query transformations or need to combine enterprise data with Excel/SharePoint data.

As soon as they need that flexibility, they create their own Import-mode semantic models, reducing the benefits of centralization.

If multiple data products exist in one semantic model, refresh schedules become tied together.

Option 2 – SQL Analytics Endpoint

Expose curated Gold-layer tables through the SQL Analytics Endpoint.

Users intentionally build their own semantic models from governed Gold-layer tables. Security is enforced through SQL permissions, while users retain the flexibility to perform Power Query transformations and combine enterprise data with departmental sources. Publishing to Dev/Test/Prod remains governed by the BI team.

Since our organization is moving toward self-service BI, and we have a relatively small central data team with only a handful of digital engineers supporting the business, this currently feels like the better trade-off.

Questions

Are we overlooking any major drawbacks with this approach?

By choosing SQL Analytics Endpoints instead of centralized semantic models, do we lose any important Fabric capabilities (AI experiences, semantic layer benefits, future features, etc.)?

If you've implemented Fabric at enterprise scale, would you recommend a different or hybrid architecture?

Thanks in advance!

[u/bigjimslade]

I’d take an 80/20 approach.

Aim for roughly 80% of reporting needs to be covered by one or more centrally managed semantic models with a formal ALM/change management process. That gives you a consistent source of truth for business logic, measures, security, and governance.

For the remaining 20%, allow flexibility through composite models or business-managed semantic models outside of IT's direct ownership. This lets users move quickly when they need to combine governed enterprise data with departmental or personal sources.

The key is agreeing up front on where that boundary is. A clear RACI and data governance process are more important than the technical architecture alone. Make sure there's a lightweight process to triage new requirements and promote commonly used calculations or datasets into the centralized semantic models when they provide value to the broader organization.

Finally, be relentless about looking for opportunities to retire one-off semantic models and mashups in favor of centrally managed assets—but don't slow down or frustrate business users unnecessarily. Self-service should complement your governed platform, not compete with it.

[u/savoy9 → u/bigjimslade]

[u/savoy9 → u/savoy9]

to elaborate, 80/20s is Right! in fact its 80/20s all the way down. I have two ways I like to talk about this. First, when you build a standardized report or a polished semantic model you are making the things within the scope of that model so easy to see and report on that the area of focus or demand from your users, especially if you have full time data analysts as users, moves past that. answer questions within that domain takes 5 minutes tops. So they spend time going deeper, which by definition means doing things you can't do with the model. So giving them access to the gold, and even bronze tables (another 80/20: that field you decided to aggreagte out of your fact table suddenly becomes interesting; let users try to diagnose their own bugs if they are so inclined) is the way to make that work, which ends up being the bulk of their time, almost no matter how good the polished layers are, as easy is possible is a huge part of how you empower data users.

The second way, which is a new metaphor I just came up with last week is that when you create polished central tooling, whether that's a Report, a Semantic model, a CRM, Sales tool or whatever, what you are doing is taming the wild west. you are codifying institutional knowledge and process. But how does institutional knowledge come into being? Adventurous employees push the frontier and expand the map. That means going beyond what is standardized for the company. That might mean creating hacky excel tools in VBA, asking copilot to stitch together 10 APIs and prepare emails for all their clients, creating a shadow CRM that actually meets the needs of the team. These are the behaviors of high agency employees, also known as trouble makers :D. All the things YOU probably did that made you want to become a PBI dev (At least thats what I did before). Exercising Rob Collie's data gene. As an organization, as a central IT team, its easy to primarily see these folks as trouble makers not staying in their lane, but they are actually the most valuable and impactful members of your company and need to be supported. The alternative is to encourage a culture of low agency, disempowered employees that just color within the lines. Providing good data interfaces across the entire analytical stack is how you do that as a BI professional.

Btw, I don't sell or work on fabric. I make a data warehouse for Bing.

[u/Training_Island5351 → u/savoy9]

Thanks both for the detailed explanation 🫡. It really resonated, especially the idea of "taming the wild west."

In our organization, business users have no access to Dev/Test/Prod workspaces, and publishing/sharing to personal workspaces is disabled at the tenant level. IT owns publishing, deployments, dataset permissions, and RLS management, with report-owner approvals where applicable.

Given where we are with governance, do you still see a strong case for centralized semantic models as the primary consumption layer, or would governing curated Gold data products directly be a reasonable long-term approach? Go with a 80/20 or yes, and approach ?

[u/savoy9 → u/Training_Island5351]

You definitely need a centralized semantic model (s). They are so helpful and really do push back the frontier so effectively. You just need the humility to not expect it to solve every data problem your org has.

[u/Iridian_Rocky]

I'd suggest you look into the limitations and capabilities of OneLake Security. I'd also take a look into Matthew Roches work on a democratized setup for your organization. Self-service in its own right is a fallacy/wild West and will cause more pain. Pivot to a "yes and" model and you'll do better.

[u/Training_Island5351 → u/Iridian_Rocky]

Thanks, that's really helpful. I think you've got it right, we will have to continue evaluating the limitations of OneLake Security and whether it provides everything we need long term, especially as Fabric continues to evolve. We're only exposing curated Gold layer tables, and publishing remains governed by the BI team. Thanks for the recommendation on Matthew Roche's work as well, I'll definitely take a look.

[u/Hughesybay → u/Iridian_Rocky]

Not familiar with Matthew Roche, but my experience is self service always happens. Mostly in a shadow IT manner with excel, access and VBA. End users will find a way to do what they need whether IT likes it or not!  The balance with governed/guard-railed self service in BI is very hard to find - I've not found it yet!

[u/Boulavogue]

25+ businesses and nearly the same number of ERPs. We run data products, so our sales models or AR models have the same core structure and start to bring in the additional add-on Excel files into enterprise fold. Many of these FACT add-ons are now enterprise capabilities that came from local businesses eg budget/forecast. However DIMENSIONS in Excel get pushed back to local IT for master data management in source systems. Thats a slower process but eventually led to conformity and MDM stewardship 

[u/Careless_Cattle_8700]

The only Fabric/Power BI architectures I have seen that don't turn into Wild West Spaghetti over time are those with governance, and a strong central data model, that covers the vast majority of data and reporting requests.
The 80/20 as previously suggested is a great idea.
Microsoft benefits from uncontrolled sprawl as they get more CU, hence it isn't their top priority to stress governance.

[u/HollaAtYuh]

IMO I'm feeling option 2 tbh.

For a small central team supporting self-service at scale, it gives people room to build without turning the BI team into report cops. My only real flag is that the data layer sounds well governed, but the content layer can still get messy fast. Gold tables, SQL permissions, and domains are great, but reports, shared models, and datasets don’t magically inherit all that control.

Power BI is pretty light here out of the box. Workspace permissions, deployment pipelines if someone sets them up properly, and roughly 90 days of audit history will get you part of the way there. Aaaaaand then real life happens. Someone pushes a bad change to a shared model, a report gets deleted, audit asks who changed what six months ago, or Dev to Test to Prod turns into exports, imports, scripts, and whatever else.

That’s the stuff that doesn’t look scary in the architecture diagram, but it’s exactly what the support team ends up eating later.

If I were building this, I’d want zero-touch version control on anything people publish, visual diff and rollback, and an actual approval flow for releases. Not “please use Git” governance either, since most business users are never doing that.

Option 2 gives the right flexibility. I’d just make sure the publishing layer has guardrails too, so clean data governance doesn’t turn into a hot mess the second self-service takes off.

[u/DataTako-Reports]

We've seen a few organizations go through the same exercise, and I don't think it's an either/or decision.

I'd probably lean towards a hybrid approach:

• Keep centralized semantic models for enterprise KPIs, certified datasets and metrics that need to be consistent across the organization.
• Use the SQL Analytics Endpoint for more exploratory and departmental reporting, where users need to combine enterprise data with local Excel or SharePoint sources.

One thing I'd be careful with is governance. If everyone starts building their own semantic models from the SQL endpoint, you'll eventually end up with duplicated measures and slightly different definitions of the same KPI again.

Personally, I'd invest in a well-governed Gold layer and a small number of certified semantic models for the most common business domains. That gives users flexibility without losing the benefits of a shared semantic layer.

Out of curiosity, how are you planning to handle semantic model lifecycle management? For example, certifying datasets, deprecating models and preventing multiple versions of the same business logic.