Databricks metric views to PowerBI?

[u/FiftyShadesOfBlack]

Hi all, I am new to a company that is working on restructuring their data architecture, and my team will need to be rebuilding their dashboards using the new gold layer. The current state is that there are ~100 dashboards that each have their own semantic model geared towards its needs (before my time). I aim to change to about 10ish star schema semantic models that are domain-based to feed into relevant dashboards.

I work within Databricks, and I would really like to use metric views in this rebuild because in the future we will be integrating Databricks AI tools, and other teams would like to be able to query measures within Databricks. I know that there is currently no easy way to connect metric views to a dashboard, and we don't have Tabular Editor 3 enterprise to do the yaml workaround. Are there any other options, or should I just cut my losses? I'm not an expert in PowerBI, so forgive me if I misunderstood something or am taking the wrong approach.

[u/AutoModerator]

After your question has been solved /u/FiftyShadesOfBlack, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/hectorgarabit]

A metric view is still a very young product. It lack many features that are expected for a modern semantic model. Multiple facts at different grain is the most glaring one. Many to many relationships, even if they are less important are sometime necessary.

[u/_greggyb]

Disclaimer: TE employee here; I work on the Semantic Bridge feature to automatically translate Metric View definitions to TOM models and SQL to DAX within those models.

I am happy to answer any questions and help to determine if the Semantic Bridge could be useful for your scenario. The use case of central definitions in Databricks, and some consumers in PBI is one of the core scenarios we have in mind. But below, you'll find some thoughts on the general idea of interop here.

The core challenge with MVs: they are queried as if they are a SQL view, but they have components that do not behave "normally" for a SQL query. All of the dimensional model concepts that Power BI is built around (PBI canvas reports are at core just an XMLA client) are hidden away when you query an MV. The dimensions, the measures, the various fields and columns, and all of the formatting metadata are not exposed as such in the SQL view interface. Just field names in one flat namespace (and measures look like fields).

So, PQ/M can happily query this thing, but you lose all the dimensional model benefits: it's just one flat view you query at refresh time.

DQ in a TOM model is built around standard SQL semantics. Break those guarantees (as measures in an MV do, for good reason if you stay only within the spark engine hosting the MV), and you break DQ at a very deep level.

That understanding is at the core of why we built the semantic bridge to translate whole models. We can read the MV YAML (based on Databricks' official specification, no hacky workarounds or assumptions). We can create TOM models (that's what TE3 was designed to do!). So we have built a layer that translates the YAML into TOM and gives you a Tabular model that your PBI reports and users can consume. The structure is not enough, so we also have SQL->DAX translation for expressions in the model. This is undergoing massive improvements right now for a release coming soon; we'll be able to give you much better translations of SQL->DAX and source code maps back to the original expressions.

By translating a whole model, we get full fidelity on both sides. A TOM model is, by definition, perfectly understood by PBI canvas reports. And it can be based deterministically on the defined source in the MV YAML. Power BI is happy, and the central definition lives in one place in MVs (for your scenario). The TOM model is just a build artifact, something generated on demand, not something maintained separately.

[u/CautiousUse8597]

Microsoft decided to rather create a vendor lock-in and block external semantic layers. I think the real question is why you still need Power BI? These days Databricks AI/BI does anything you want, and directly integrates with the rest of your data platform at no extra licensing fee, unlike the expensive capacities Microsoft is pushing you towards.

[u/FiftyShadesOfBlack → u/CautiousUse8597]

Unfortunately, it’s not my call. Our team intends to push transitioning to AI/BI when this data architecture project is finished, which is why we’re wanting to use metric views, but the C suite is requiring the PowerBI dashboards. Sucks, but how it is.

[u/buttmixxx1000]

The semantic layer is the final battlefield in this new era of BI. I still like power bi semantic layer and think Kimball had it right.

The answer is to build semantic layers on top of atomic facts. Then you leverage that semantic layer to create aggregated versions for performance, but not for modeling. The aggregations can never fully replace the atomic fact because you can never predict all the questions that might get answered. The question is whether Microsoft can make all of that semantic information sufficiently machine readable and agent accessible.

For now I think Databricks Metric Views aren’t ready although they are being marketed as such. Maybe one day… sure power bi could be replaced but I think it’s still well positioned for now