Lessons Learned from Third-Party Integration Efforts in Power BI

Take a percentage metric (a margin, an allocation rate, a year-over-year variance) defined in a third-party metrics store and try to feed it into an Import mode Power BI model. The detail rows will look right. The subtotals will not, because a ratio has to be calculated after the base data is aggregated, and pre-computed values cannot be summed back into a correct total. That single technical fact carries most of the explanation for a position Microsoft has now stated openly: Power BI's front end will not be made to work properly with third-party semantic models, and there is no plan for that to change.

The statement comes from Chris Webb on Microsoft's Fabric CAT team, who writes that the Power BI team has spoken to all major vendors of third-party semantic models about integration over the last year, and that the obstacles are purely technical rather than competitive strategy. Whether or not you take that framing at face value, the technical detail is the valuable part: the post reads as a catalogue of integration approaches that were tried, and why each one failed. For finance teams who have sat through a “define your metrics once, use them everywhere” pitch, those failure modes are worth understanding before anything gets signed.

Three integration routes, and what broke

The vast majority of Power BI solutions use an Import mode semantic model. Data is copied into the model, which also holds the relationships between tables, the measures written in DAX, and the rules about which columns are visible; reports then query the model in DAX. Any third-party semantic model has to fit into that architecture somewhere, and according to the post, vendors have tried three places.

Pretending to be Analysis Services. Power BI reports connect to semantic models over the XMLA protocol, which is also how they reach older Azure Analysis Services and SQL Server Analysis Services models. Some vendors implemented XMLA support and told customers to connect through the SQL Server Analysis Services connector. Webb's verdict: it works up to a point, but pointing that connector at something that is not Analysis Services is unsupported and not wholly reliable.

Importing the metrics store's output. This is the opening example. When Power BI imports already-calculated metrics such as percentage shares or time intelligence calculations, it cannot aggregate them and get correct results, because most metrics need to be calculated after the base data has been aggregated.

DirectQuery against the metrics store. In DirectQuery mode the Power BI model stores no data and generates SQL on demand. Running that against a third-party semantic model means one semantic model sitting on top of another, which Webb calls out as making no architectural sense, and the symptoms back him up. Power BI assumes a star schema and generates SQL that joins dimension tables to fact tables; not all third-party models support even that. Power BI also assumes it is the place where metrics get calculated, which pushed some vendors into workarounds like making the SQL SUM() function not actually sum values. The consequence is that you can never be sure a metric defined in the third-party model returns correct subtotals or grand totals. DirectQuery is also usually slower and less cost-effective than Import or Direct Lake. None of this is unique to Power BI, the post notes: no semantic model would work well with another semantic model as its source.

(Direct Lake, the third storage mode, only works with data in OneLake or reachable through a shortcut, so it does not enter the discussion.)

Why the aggregation problem matters to finance in particular

Finance metrics are disproportionately non-additive. Margin percentages, allocation rates, variance-to-budget ratios, anything involving time intelligence: these only mean something when computed over the exact slice the user is currently looking at.

When I was building activity-based expense allocation at Morgan Stanley (a Power BI and DAX solution distributing settlement expenses to cost centers based on trading activity, across regions and business units), nearly every number that mattered was a rate evaluated at query time. The allocation share of a cost center within one business unit is not the sum, the average, or any other simple rollup of the shares beneath it. Materialize those rates as stored rows and every subtotal on every management report becomes quietly wrong, which is considerably worse than loudly wrong.

This is why the question of where metric definitions live is not a matter of tooling taste. It decides whether your grand totals can be trusted.

The supported route

One integration path does get Webb's blessing: synchronizing the calculations defined in a third-party semantic model into a Power BI semantic model, reproduced in DAX. That is possible and supported, partners such as Tabular Editor have already started down this path, and Microsoft has recently invested in making programmatic changes to semantic models as easy as possible. The follow-on argument is hard to dismiss: DAX is a better fit for defining metrics than SQL, some of the metrics you need will be definable only in DAX, and once part of your metric catalogue has to live in the Power BI model anyway, splitting definitions across two systems is self-inflicted complexity.

The consumption side is also more open than the “walled garden” framing suggests. Per the post, Power BI semantic models can be used from Excel and Fabric paginated reports, from Tableau and other non-Microsoft front ends, queried in SQL within Fabric, and pulled into a Pandas DataFrame through the Semantic Link library.

The options, side by side

The organizational lesson in the closing paragraph

Webb's closing diagnosis is the part I would put in front of a CFO. Organizations that already use Power BI extensively consider third-party semantic models, he argues, largely because people from a data engineering or database background, often not the Power BI users themselves, think of Power BI as just a visualization tool and do not realize it carries the most mature and most widely used semantic model on the market, designed for self-service and enterprise BI alike. The scale figures he cites: 35 million users view Power BI reports every month, querying 20 million semantic models.

That matches what I see in practice. At Syngenta my work treats semantic models as governed, CI/CD-deployed assets on a Fabric lakehouse, with row-level security and data quality standards applied to them like any other production artifact. Once a team works that way, the division of labor settles on its own: lakehouse and warehouse layers own data preparation (a layer where, as the post points out, Microsoft works closely with Databricks, Snowflake and dbt), and the semantic model owns business meaning. The pull toward an external metrics store tends to come from teams that have not yet granted the semantic model that engineering status.

Where to read the original

The full piece, Power BI and support for third-party semantic models, is unusually direct for something written from inside Microsoft; Webb has worked with Microsoft BI tools for 28 years and backs every argument with the underlying architecture. If Power BI is how your end users consume data, his conclusion doubles as practical guidance for any finance BI estate: the Power BI semantic model, not an external metrics store, is the right place for your metric definitions and business logic, and the past year of integration experiments is the evidence.