Synchronising Power BI to Metric Views with Tabular Editor's Semantic Bridge

Hello, Spark fans. Welcome back to Advancing Spark, brought to you by Advancing Analytics, your favorite data and AI people. I am so excited to be

making this video. Finally, this has been months in the making. So, a little while ago, early this year, uh, Unity catalog metric views were announced. So,

we had this whole brand new idea of a semantic model living inside of datab bricks. But when we looked at it and we

bricks. But when we looked at it and we were like, well, okay, so I can I can do that, but then PowerBI can't read from a metric view. Those two will like not

metric view. Those two will like not talk to each other. And then you're presented with a problem of going, well, if I want to bring my semantic definitions into data bricks and use it

in Genie, use it in agent bricks and have all my richness of my data platform able to use my semantic measures. Well,

that means I can't use PowerBI or I got to keep the two in sync and that's going to be really painful. So, I had a wacky master plan. I spoke to my friends at

master plan. I spoke to my friends at Tabular Editor and went, can we build something? Can we get it working so that

something? Can we get it working so that you can suck all the good details from UD catalog and then push that automatically to a PowerBI semantic model and keep the two in sync? That is

my master plan. Now, they've been busy working away in secret and now have this whole idea ready to go. So, they now have it. We have a preview. Uh, I had a

have it. We have a preview. Uh, I had a chat with Greg from Tabular Editor just earlier this morning and yeah, I want to share a bit of that chat, share a bit of a demo about how it actually works. Tell

you what you can look forward to from January. You'll be able to actually link

January. You'll be able to actually link these two things up for the first time ever. And that is so cool. So, yeah. As

ever. And that is so cool. So, yeah. As

always, if you're new around here, don't forget to like and subscribe and then let's go and have a look.

Righty, Greg, welcome to the show.

Thanks for joining us today.

>> Introduce yourself a bit. Could like

tell us a bit about yourself?

>> Sure. I'm Greg Baldini. I'm one of the core developers with Tabular Editor and I've been working on language support as well as several new features. Uh, one of which I'm very excited to talk about with you today.

>> I mean, I've been waiting to make this video for so long. So this is this is very very exciting. Uh let me let me kind of just explain I guess the the problem how this started and then we'll get into the the crazy stuff you've been building.

>> Sure.

>> Okay. Cool. So we've got Unity catalog metric views this whole YAML defined semantic model that is now coming out in data bricks. So if you've been doing

data bricks. So if you've been doing things where you wanted some kind of contextsensitive measure so you can cut and slice all the things that we've been doing in a semantic model in things like PowerBI for a long time you can. Now

they do that in data bricks. So you can use Genie on top of it. You can plug it into agents. You can build agent bricks.

into agents. You can build agent bricks.

It's just all the rich stuff inside data bricks. You can plug into that. But that

bricks. You can plug into that. But that

is a problem because there's barely any company that doesn't already have some kind of a semantic model living somewhere else. If you're sitting there

somewhere else. If you're sitting there in PowerBI and you've got your your semantic model sat there with a rich pile of DAXs and calculations and things and then you go well actually I could

pull this upstream and use it in a wider variety of things but then I need to either migrate it make a choice and get rid of all the stuff I'm using that I love in PowerBI which I don't want to do

or I need to maintain two copies of all my KPIs and definitions and they're gonna get out of sync. I mean the whole reason why we do BI and warehousing is because of the problem of people coming with different definitions of the same

measure and it being organizational chaos >> and we don't want to do that. So how do we keep multiple semantic models in sync specifically how do we go from UC metric

views into a PowerBI model was the problem that I was like I know some guys that can help >> maybe a couple of them.

So I guess from that starting point from going I've got a UT metric view. How do

I how do I sync that up with my PowerBI semantic model kind of that was the problem that over some beers I poised the tabular editor crew and said fix this for me [laughter]

>> and uh yeah I mean that's basically where we started from the the question is you know we've got the the idea of the semantic model and the idea of the semantic model is the same. It's this

one place where we have definitions of business metrics and where we can go to to do our reporting. But the actual implementation of that semantic model is is very different. And so the the

challenge was with metric views, you've got this YAML definition that you're defining in the UC catalog. Uh and then with a PowerBI, you've got the semantic model that you're defining with PowerBI

desktop or using a tool like tabular editor and it's using this BIM or timal format. So the the formats are very very

format. So the the formats are very very different but the ideas are the same of facts, dimensions and then measures which are aggregations of those things.

And so that's where we kind of started off from. And we quickly realized that

off from. And we quickly realized that it wasn't just a onetoone mapping and it wasn't just a simple translation but there were actually a lot of reusable components and ideas in there. And so we

we started with this idea of metric views to tabular.

And as we were talking about it and we talked about workflows that we might want to do or the idea of going back the other direction, you really can't just do these onetoone mappings. And so what we started building and working on is

what we call the semantic bridge. And

this is more of a framework for doing these translations between semantic models. It's the the idea of the

models. It's the the idea of the semantic model and then specific implementation platforms like data bricks, like tabular. Uh and so that's what I've been working on uh since that

conversation over beers is uh this generic platform for migrations. And

what we've built specifically as the the first implementation of it is the uh metric view to tabular like you've just been talking about.

>> It's just uh nuts in terms of the power that that could actually be you So what that that kind of my original wacky idea of going can you just like translate these two things automatically

>> growing and turning into uh I I I coined the cheesy term of a BI babelish. this

kind of thing that can just pull from any semantic model, put it into a generic and then translate it to another one is is so powerful in terms of allowing people to break out of essentially there like vendor lock and

all these different semantic definitions of going you're going to use my language definitions, you're going to use my one and going well actually you should be able to use anything that you want and take it with you and it's the

portability of these rich metrics and knowledge and it's meaning business semantics aren't tied to a tool which is insanely powerful.

Yeah. And I mean I I know you know this, but uh I've spent most of my career in BI consulting and I've never met a customer that has one data platform.

Never.

>> It's always at least two, sometimes a dozen uh reporting tools, data warehouse, data lake layers, and even if you're trying to centralize on one, any sort of migration period, you still have

multiple during the course of a multi-year migration. So it's it's never

multi-year migration. So it's it's never a question of which one, and it's never a one-time migration. And it's always business users that want to deal with this data in the tool of their choice.

That tool might be PowerBI. It might be an Excel pivot table. Let's be honest, it's usually an Excel pivot table. Uh

but it might be anything in the data bricks world, but also Snowflake, uh Google, Amazon. All of these providers

Google, Amazon. All of these providers have visualization and data layers. And

the idea that business metrics can now be portable across them, like you were saying, that's huge.

best and all those historic migration projects like you're essentially playing a BI tool whack-a-ole. It's a while you're doing that migration like because everyone's frustrated there's a big migration they'll just grab another tool

and start using again. It's like it's a neverending journey of because the business shouldn't have to wait on large technical projects. They should just be

technical projects. They should just be able to react and fix the problems they need to which >> yeah it's it's an endless problem.

[laughter] >> All right. Very cool. So semantic bridge itself. So kind of that's the the name

itself. So kind of that's the the name of this uh feature coming into tabular editor that's going to allow people to do this translation starting off with EC metric views and kind of PowerBI models

and allowing them to talk to each other a bit more seamlessly.

>> Yes. And the idea is the structure is one of the most important parts. So

again the idea of facts dimensions measures being able to port that structure is one big part of it. The

other part of it is the business logic itself. uh the idea of these

itself. uh the idea of these calculations and aggregations uh and they're they're both hard problems. The structure is uh I'd say a little bit more approachable, but we're tackling

both.

>> Yeah.

>> I was going to say maybe it'd be useful to kind of talk about it as we look at it.

>> Yeah. Yeah. Yeah. We if we can see it, if it's not just a thing like this doesn't really exist. This sounds too good to exist. I think we need to take a look. [laughter]

look. [laughter] >> Yeah.

>> Let me let me go. forgot I have control of the stage. Let me bring it up.

[laughter] >> Sure. So, uh what we're looking at here

>> Sure. So, uh what we're looking at here is a metric view. Like I said, this is a YAML definition. I know you've seen this

YAML definition. I know you've seen this before. Uh but what it defines is these

before. Uh but what it defines is these structural components of a model. We've

got things like joins here. A join has a name. It has a source table and it has

name. It has a source table and it has uh an on condition saying how these two tables are going to be related to each other. You've got the idea of

other. You've got the idea of dimensions. Each of these is a field

dimensions. Each of these is a field coming from one of the tables. uh and

it's got an expression that can define how you actually determine what the value of that field will be. And you've

got measures. Uh measures are these business aggregations of interest that again things with names and expressions defining exactly what that business logic is. And so like I said, the

logic is. And so like I said, the structure is what these things are and what their names are. But the

translation is being able to say things like here's a SQL snippet of a join or here's a SQL snippet of an aggregation and being able to move these across platforms as well.

So this is the the metric view, but what we want to be able to do like we've talked about is getting from a metric view into a tabular model and then therefore being able to use it with PowerBI. So I'm jumping here to tabular

PowerBI. So I'm jumping here to tabular editor. And I've got this loaded up with

editor. And I've got this loaded up with an empty model. You can see there's nothing actually in this model on the right. It's just an empty skeleton. And

right. It's just an empty skeleton. And

then as we're looking here, we've got a little snippet of C# that I've written.

And we're actually going to be building this into a file menu. So you don't even need to deal with this uh snippet of C#.

But what this is doing is it's calling into our semantic bridge. And the

specific implementation that we want to use is a metric view. And we want to import to the tabular model from a file.

And so what I need to do is I need to say I want to use this file path to that YAML that we were just looking at to import it into this model that I've currently got open. And then what we

need is a little bit of detail about the data bricks source. So uh what your URL and what the path to the database is on the data brick side and then we also emit diagnostics. Uh but because this is

emit diagnostics. Uh but because this is a demo we won't see any negative diagnostics in this. Uh but all this is is a little bit of metadata about where the file lives and where the database lives and how we're importing. And what

we can do is just click run script right here. And that will read that YAML file.

here. And that will read that YAML file.

It parses all of those objects out of it. And you'll see that in just a moment

it. And you'll see that in just a moment it will create tables and relationships based on that. So we saw a join and we see the relationship. We saw that there was a customer dimension and a fact

table and within each of these we see the fields created. We define properties like that. This is a key field. You can

like that. This is a key field. You can

see that because it's bolded and it's part of the relationship. We define the measures and if I double click on one of these measures, we'll see the expression. We actually are translating

expression. We actually are translating from that SQL snippet to a DAX snippet.

And I I want to be upfront here. We

can't translate all SQL to all DAXs or the other way around. But we can identify common patterns and give you the ability to automatically do that because this is not what it looked like in SQL. If we look in SQL, it doesn't

in SQL. If we look in SQL, it doesn't have these single quotes. It doesn't

have these square brackets. But over

here, it's just this count of the key or this sum of the price. And we are able to translate that into the correct DAX syntax.

>> Cool. So there loads and likes of uh basically loads of simple models. This

will just be straightforward. Hit a

button, go through. If there's something real gnarly complexity, people have got their giant page of nested calculated scripts and decks. That's probably not what they're going to be these nice easy

easy migrate candidates.

>> But we just want to go like what's the >> like get the 80% out the way. The models

where people have done the hard bit upstream. They've done it in their data

upstream. They've done it in their data modeling. Most of the complexity is

modeling. Most of the complexity is taken care of by the actual fact dimensional structure.

>> Yeah. then happy days, easy path. Just

hit a button and sync one to the other.

>> And this is also u something that we've talked about is the idea of the structure versus the the snippets of code in there. And

>> really simply like the structure regardless of what gnarly SQL you've written in there or whatn gnarly DAX the fact that the measure exists or that the table exists that's something that we can translate and uh you know right now

this is in MVP so we don't have uh full facilities for that sort of after the migration assistance but the idea is that we could bring all the structure over and we could flag something as here's some gnarly SQL we couldn't

translate and then that's an opportunity for you as the operator as the user to look at this and say I know how to rewrite this and you could just rewrite it in yourself or maybe you could hook

in an LLM or an AI that can take a stab at translating gnarly SQL to gnarly uh to gnarly DAX but the structure is all there and you know that the easy pieces the tables the relationships uh the

basic measures which like you said is probably 80% of the model that's all done no problem and you can focus just on that that uh important business logic without having to do all the drudgery of making every single column making sure

that you rename things with a friendly name in the same way all that's handled for you.

>> Just giving you the the structure and the process and rather than someone having to just assume that two different developers who speak different language is going to build things in the same way, it's a giving you this way of maintaining it, >> you know. So,

>> yes, >> there's lots of like logistics of how you need to plug this in, you know, how if someone makes a change to the metric view, how how do you make sure that they know to open table and do that sync?

There's obviously going to be organizational things to try and actually keep these in sync, but it's just so much more structured and measured and controlled than just assume it's going to happen.

>> Yeah. And it's also, you know, if this is just one line of script or like I said, we're going to put a menu button in there. It's also something that's a

in there. It's also something that's a translatable process much more easily.

If you're working on this, I know Simon, you could sit down with a metric view and with a tabular model and you could make some changes to one and make sure they're reflected in the other. You

could do that, but you couldn't do that for a whole organization every day because eventually you're going to need to take a break or there just going to be too many models. And so the idea that you can have a developer who knows metric views and doesn't need to care as

much about the tabular end, but they just click a button or run one line of script and then they get the the tabular model out the other end. It makes that repeatable and it makes it trainable. I

don't need to teach someone both of these modeling layers. I need to teach someone one and a button. And that's

it's going to happen more and more honestly. Uh so all of the metric views

honestly. Uh so all of the metric views as it stands currently you go into catalog you you have a bunch of tables and you go click a new metric view and

it pre-calculates it suggests a load of metrics. It it's kind of half vibe coded

metrics. It it's kind of half vibe coded itself in terms of how it puts out. So

>> so even the people building the metric views might not know how to build metric views but they're just getting something out that they know how it works and they could check it and go yeah this does what I needed to do. And then being able

to make sure that just isn't then a siloed part that's not part of the rest of the enterprise semantic uh stack.

>> Yeah.

>> Yeah. And how often do you come across that where someone has just made something work and they don't really know all of the ins and outs of what the appropriate platform is or what the enterprise standard is, but they made

something work and it's good. It's

providing business value. Then someone

else sees it and they say, "Oh, I want that, but I want it over here.

And that's the the challenge you see with self-service all the time. And so

especially for for that sort of model that's been developed to meet a specific need, but then all of a sudden you need it in multiple places. Uh that's the sort of workflow and the sort of challenge we're trying to address with this is making sure that you can build

where you're comfortable, but you can still consume from any tool that you need.

like my my data hippie philosophy, right, is kind of if you try and restrict people from doing this stuff, they're just going to do it where you can't see it, you know, whereas actually allowing people to build something

scrappily, get it working, get it to a point where they're like, "This solves the problem that I have." And then giving them an easy route to getting that into a more operationalized state that that allows people to innovate, it

keeps people curious, allows people to do more. But you've built a way to then

do more. But you've built a way to then control it. You can't have it completely

control it. You can't have it completely all controlled, but if you say no, you're not allowed to do it, then they just won't tell you when you they actually do do it.

>> So, yeah, completely believe it.

[laughter] >> Okay. So, if people people are looking

>> Okay. So, if people people are looking at this going, "That is fantastic. I

want this." Like, when can people actually see it and start using it and get their hands on it?

>> Yeah. So, it's going to be in our January release for tabular editor. I

don't have a specific day in January that's going to be coming out, but we're going to be finalizing all of our features and doing our code freeze at the end of the month here and then doing testing and release in January. So,

they'll certainly be able to see it in the new year.

>> Very cool. So, people should be spending the entirety of their December time off building a an elaborate maze of metric views without worry knowing that they can synchronize everything up come January. And then we'll have a nice

January. And then we'll have a nice happy time.

>> [laughter] >> Yeah, it'll be a great new year and I really look forward to seeing what sort of gnarly things people build and turning that into feature requests and new features. You know, like I said,

new features. You know, like I said, this is our MVP. We've tackled uh the basic workflow, but we're looking at how do we extend it? And I can come up with lot of ideas, but I I think we'll

probably hear a lot more useful ones from the community, from users.

>> Very cool. So, you've got kind of got two two future directions. You've got

kind of extending the features, kind of getting more plugged in. we're allowing

it to do more and more, but then also looking at other semantic models and bringing other things into it and just creating it. So, it's not just like

creating it. So, it's not just like two-way between two particular systems, but actually five, six, 20 ways as we go. Anywhere you've got a semantic

go. Anywhere you've got a semantic model, you should be able to plug it into your middle piece and then translate it to anything that you've built around it. Right.

>> Yeah. So I mean if you want to bring me back for I don't know five hours I can go into all the detail you want of the the detailed architecture but what you've described is at a high level exactly correct the the semantic bridge

what we've built is this abstract layer so that we can go from the metric view like we saw and before it goes to tabular it's actually going through this middle layer of an abstract semantic model and so we define all of our

translations as one-way translations either to or from the that abstract layer and what that allows us to do is do multiple targets like you said. So

I've built the code. What we saw uh was going from metric view to the abstract model and then from the abstract model to tabular. If tomorrow we wanted to

to tabular. If tomorrow we wanted to tackle something else, all we need to do is get that one thing to the abstract model and it can go to tabular. And once

we build those translations from the abstract layer to a specific concrete platform, that's where we get the power because once we can go from abstract model to tabular, we only need to get to

the abstract model from any other platform. And when we go from the

platform. And when we go from the abstract model to metric view for example as soon as we add that additional platform once we get it to the single target of the abstract model then we'll be able to target both of

tabular and metric view so that instead of having to do all these combinatorial onetoone translations we get this centralized hub and we can onboard new platforms very rapidly. I'm really

excited about that part of it as well which >> is why I'm calling you guys the new BI babelish. are [laughter]

babelish. are [laughter] >> translating one to everything and that is incredibly powerful.

>> All right, >> just helping the data hitchhikers along their way.

>> But thanks for joining me and super excited for this actually going out and people getting their hands on it and then giving you a giant pile of future features and additional things you want

to do. So yeah, it's very exciting.

to do. So yeah, it's very exciting.

>> Very much so. Thanks for having me on.

>> So there you have it. So, tabular editor has a semantic bridge that allows you to universally translate between firstly

Unity catalog metric views through to panel semantic models, but in the future literally any semantic model, turn it into this generic definition of a

semantic model and then push it out there and break free from any kind of boundaries of whatever language of the BI tool that you're using.

So so cool. Definitely my original idea of like, can you just build me a translator has turned into this mad genius idea of building a BI Babel fish

that can talk to literally anything. Uh

so yeah, interesting stuff. So from

January, I'm expecting we're going to be helping clients build those migrations, build a take my PowerBI model, lift and shift that into a UC metrics view, but

keep the two in sync so you don't have to get rid of your met your semantic model. So many things we can do there

model. So many things we can do there come January. And yeah, we'll we'll be

come January. And yeah, we'll we'll be putting some more content around. We're

going to share some of that information.

We'll share some of the more bits of how it works. You do need tabular editor for

it works. You do need tabular editor for this whole magic to actually fit together. But tabular editor is a great

together. But tabular editor is a great tool for actually building and managing uh semantic models. So that's fine. It's

a great tool that we have anyway. So

yeah, interesting stuff. That is all I want to take you through today. As

always, don't forget to like and subscribe and I'll catch you next time.