Define Business Metrics Once, Use Everywhere with UC Metrics

0:05Hey folks, today I have the pleasure to host Matthew Lamirez, product specialist at datab bricks for the second time. The first time we talked about SQL execution API and if I'm not wrong, it was maybe one year and a half ago. Welcome back, Matthew. Thank you. You got to have me more often here. Yeah, I guess so. We have more things to present and so

0:27stay stay tuned. We're not going to spoil anything. Just make sure to subscribe. This is the first time I'm asking you to do it. So do it. Lots of stuff for you. But today got a full pack. Exactly. We have packed agenda. So you're going to be talking first, I think, about the um semantic layer. We're going to try to explain what's the what's a semantic layer, what's the

0:49purpose of using a semantic layer. Then we have a surprise for our viewers. Yeah, let's let's that's that's an interesting concept. Semantic layer. Um it's concept that talked often in the context of data visualization and BI but I think it's an important maybe just go back a little bit of what the concept is and I think it's the difference between data and information and that's that's at the heart of the discussion because

1:12we have lots of data especially coming in from Databricks and structured in number different tables and different ways but being able to understand what the data means and how it can be interpreted in a business point of view is often a little bit difficult. Um and maybe we can just start with a just a very concrete example. Here at Databricks we often look at revenue using the concept of a r. What the hell does

1:34that mean? It simply means annual recurring revenue. But the question is how do we calculate that? How do we go from our table where we have all of our billing which is essentially our revenue um and we calculate our AR which AR essent essentially is the amount of revenue that we have on a monthly basis. That's usually calculated on a monthly basis. um and then um pushed on an

1:57annual basis. So so times 12 pretty simple but one you have to know what that definition is. It would be wouldn't it be really interesting if it was already calculated for you so you don't have to redo it every time and that's that's the importance and that's that what we mean by the semantic layer. taking the data transforming transforming it into something that is usable by the business

2:20essentially by integrating business concepts through either the usually the notion of measures so that they can be readily interpretable. Yeah. So if understand well to make sure is to unify the layer where we're going to have the same like using the same rules everywhere rather than having someone defining a r I don't know as taking the revenue divided by 12 for example and

2:44someone else using the same naming but with another definition. The goal is to unify and have to make sure that you are getting the same KPI as everyone and you're not using something else. Yeah and I think I think that's a very important one. It's the notion of governance and that's why I think the notion of semantic layer gets more and more important. Having a shared governance around these definitions is something that's important. So it's not

3:06reinvented five times because well I could calculate AR and just put it just as a column but if no one knows where that data is and how it's being calculated that's that's not very useful. I need to be able to find it. And that's why here at Databricks the notion of semantic layer is something that we're integrating into our unity catalog through the notion of metrics with the idea of to be able to centralize those definitions and I think

3:28that's an important concept is a notion of centralization of definition and its governance so that it can be readily found easily understood um maybe so and also understand which are the elements which are reliable now so that's why we have also notions of certification so that people can understand it. Um, and there's also notion of performance to be able to make

3:50sure that that uh whenever we query it, it's it's used um it's it's used in in with performance. So what you're saying that databris is introducing its own semantic layer and tightly integrated with the with unity catalog introducing metrics man. Okay. So that's what you call UC metrics. Exactly. So that's a new product, not a product, but at least

4:14a new functionality that we're introducing within Unity catalog which is called metrics. And essentially it's that capability of being able to define those business concepts within the catalog that can be used. So essentially we're reting in some of these notions of that we find often times in visualization tools but we're integrating into that catalog. So what is a metric? Essentially metric is a

4:38definition of measure. What is a measure? measure is just a calculation that is being done on data and that is evaluated versus dimensions. So very much what we're finding is the notion of cubing concepts. So people who are used to old tools such as analytic services or used to working with uh with tools such as PowerBI are very familiar with these these different

5:00things. Essentially you have a calculation and then we'd want to be able to project it or calculate versus a notion of dimension. For example, AR that's how I calculated it. So it's my monthly revenue times 12. But sometimes I want to evaluate versus a number of different things. I want to evaluate versus notion of time. I want to potentially want to evaluate versus a notion of geography or where maybe another third dimension I want to look

5:22at is maybe in terms of organization. So the idea is to be able to structure all of these things so they can be easily used. And so a metric in in UC in in Databricks UC metrics will be structured around two different things. On one hand you'll have your dimensions. saying where I want to be able to project these calculations and the calculations themselves the measures and this way you don't have to reinvent them it's already there to calculate to be

5:45able to have these definitions for you and we can also have a notion of certification so that it's been validated and people can I easily find these information so I can reuse it can you show me how I can define those metrics does it require like a very deep knowledge on SQL or just some basic functionalities that I can implement that I No it's pretty easy to do essentially we're using YAML files to be

6:08able to do that and I'll do a quick quick presentation but I think it's what important thing is to show where we want to where we want to use it. So metrics are often defined usually in in the context of dashboards and we have that integrated in within Databricks. So metric can use in dashboards it can be used in genius it can be used in queries um and and and I think that's that's an important because it's important for the entire data life cycle

6:30when we're trying to start and transform the information we need to have common definitions how we're going to do it. uh people often see it just on the side of the data consumption, but it's I think it's also very relevant on the ETL side being able to have the standard way of being able to calculate AR or any other type of concept so that it can be prepared. Another place where we're seeing this to be very very important is

6:53for example for machine learning especially for the notion of feature. Yeah. Understanding well bring these concepts into future preparation is something that's that's pretty important. But to your question on how to prepare that that's actually pretty it's pretty simple to do. So maybe maybe I can share do it quick. That's what that's what I was about to ask. Can you

7:15show us a demo how I can define my dimensions and uh metrics and then maybe how to use them within I don't know dashboards or Jenny. Absolutely. I will show you exactly how how it's how it's done here. The way an interesting one is as I as we have discussed metrics are a UC object. So they are governed centrally and

7:39identified within the concept of the central catalog. So how are they defined? So it's essentially it's a YAML file that we can see here where we look at a source. Optionally we can put different filters on it globally. Maybe we need to join it here. So in my case here, I'm looking at a standard the

8:03TPCH data set where I've got a number of different orders, but I want to have on top of the orders I want to be able to join it with one of the dimensions with the customers so that I understand and have extra information not for example the notion of segments so that I can do my joints on top of that. So I need to bring that in so I can join with different other different sources. And then here comes that definition part. So

8:27here I've got my dimensions. So in my case here I've got market segments. So I want to understand the versus different different segments I have versus different customers the notion of a notion of time and notion of status and priority. So all these different dimensions are going to be the different elements on which I will want to evaluate my different

8:51calculations. And so that brings me to the notion of measures. So this is where I want to evaluate them and here is what I want to evaluate and here I'll bring the number of calculations. That's where the concepts are being defined. So here I've got a number of different things. I can do just simple counts and in fulfilled order. So here it's basically it's a count with a filter. I can do distinct. I can do sum. So essentially anything any type of

9:15aggregation functions can be used with with metrics. And an interesting thing also is you can also use something I find really those window functions because those are usually often hard to understand and use in in SQL. It's it's not that it's complex but it it's it is not something that that can be used where it's usually used by

9:39your your average business user. This makes it a lot easier to use. I can define those here. And once I have that and I've defined this, this makes it very easy. So I've got all of those definitions which are ready to be used directly in Databricks. Now I can use those in different ways. So for example, let me just share this for example. I

10:02can use them directly in SQL. So this is what for example here this is the same matrix I had. So it makes it a lot easier to use as I can see I can and to read also because you no longer need to do aggregate by this, aggregate by this. You just bring in those measures and that's it. So this the SQL sentence is much easier to read also. It's a lot easier to use. It makes it a lot more legible. Here I've got I've got my

10:24dimensions. I want to look at by month, segment, and status. And then here I've got all my different measures. And because I've given them a pretty easily understandable name, that's pretty easy to use. And then I just here I'm just doing a little a little a little filter. But that that that's basically it. Now let's take a look at how we would look if I were to do it just in SQL looking

10:46at that same source. It's a lot more complicated. So I was doing in here a number of different a little bit of transformation on my on on my months and u my so I need to understand the underlying data. So that makes a little bit more complicated. I need to understand how I want to transform these things. these three things those are my dimensions and then underneath here here are some of my different u measures I

11:09have but I don't that have them all because for example if I want to do the to mode for previous months that requires a window function I probably have to do a CTE plus my function beneath it makes it a lot harder to use a lot more complex whereas here pretty easy I say I want to analyze this by month segment status these are the dimensions and here's what I want to calculate and bam you're ready to go makes it a lot easier way easier and

11:31sharable Oh, cuz everybody says, "Well, total revenue, hey, this is how you calculate it. It's already there for you." So, it's going to be it's it's a trusted asset because you know, you're 100% sure of the rule that has been used behind this measure. So, you know, if another someone is using the same metrics, you're 100% sure that you're sharing the same view and you're 100%

11:53sure. I think this is also something very important because I I noticed in the past I was working with some teams they were leveraging the same data the same name of metrics but not the same way of calculating this and it was creating some frictions like which one is the right one which one's the right one which one's the way to use they both have the same name how does it mean and then all of a sudden then the worst part

12:15is when it gets start to get used into dashboards and two people have using two different dashboards have different numbers and they're like I'm right no I'm right no I'm right And it just creates a lot of confusion. And I guess like with all those like metrics, they can also easily be used to build for example charts or like to build dashboards like I don't know dashboards can highlight the total revenue by

12:37product or by segment or maybe by month and so for dashboards I see it will be very easy but what what about maybe Jenny? So essentially the idea is for it to be once I have it so I can query in SQL but the most interesting thing is to use it in other places. So like you said it's integrated into AIBI dashboards. So

13:00for example here this quick little dashboard that I created here. As you can see I just the only thing I put here is my order metrics. So I've got all the different things and then I can create a whole bunch of different visualization directly on top of that. Now, as you can see, AIBI knows how to use these knows a proper syntax to be able to query this with the notion of that

13:22that keyword measure. So, it's it's pretty easy to use. But what I find the coolest thing is not necessarily the dashboards. Um, essentially it's it's the use within with Genie because one of the things that sometimes we find with Genie is that it's sometimes hard to describe to Genie how to calculate business concepts and

13:45since Genie is inherently a probabilistic engine and sometimes it will calculate things slightly differently. when we use metrics with Genie essentially tell it to look up how things should be calculated and just makes it a lot easier and so for example I can I can just give you an example so

14:07here I've I've created my my genie room just on top of my metrics so I can see it here and that's the only thing I've put in now of course as with all genie rooms it's really important to put descriptions because that's how Genie knows how to use what so what measure for what dimensions because it's going to look up that metadata. But other than that, I don't really have much more to

14:30do because it will it will be able to look up those different dimensions, those different measures based on that. Now, another really interesting cool thing to do is that when we use metrics, Genie automatically calculates dictionaries. So, it understands the data and the values that are within the dimensions to make it a lot easier. So that's a function that was added a few a

14:53few months back and it's already integrated when we when we do that with with the metrics. You don't have to add it's already there. It it just just does it for you. So just for information for example if we are saying aggregate the product sold in California for example it knows that rather than filtering by where state equal to California it can be just used

15:17state equal to CA because it did it did like let's say infer the values you have there to know how to filter the data while in the past you had to like add those uh reference as instructions. and saying if I refer to California then it's CA if it's EMA if it's like Europe it's going to be EA and so on. So now

15:39it's much easier which means you no longer need to do a lot of manual tuning. Well so here's an example of a of a gen room that I've uh using it to be able to get these these measures as you can see you can use it just as anything else. So for example to be able to configure this I just put my metrics here my metric view and um it will integrate all the data in

16:03here. That's what's interesting to hear here is actually to see here as I've put everything I've only put one object which is my orders metric but in the background it's actually going to look up a number of different tables. Remember how I put those joints in there. Um and as you can see here I've got my my different measures and my dimensions. As always, when using Genie rooms, it's important to have all the different

16:25descriptions set up because that's the metadata that's going to be used by Genie to understand which metrics to use. So once I've done that, that's pretty easy to use. I can just use it just as with any Genie in the room and it will be able to select the right measures. As you can see, what's really cool about it is that it gives Genie the ability to

16:47systematically always calculate the same in the same way. For example, total revenue. It's always going to come back to that total revenue measure and that the calculation behind there is handled by the metrics view and not by Genie. So that makes it a lot more reliable. Um and then we can just use it in the same way. So here I' I've just one dimension, one measure. If I I can go a little bit.

17:10I want to add the number of customers and number of orders. If I you see here adds a number of different measures. Um the distinct customer is an interesting one because sometimes something that um genie has a little bit of difficulty to do but it has to do with the way that things are interpreted um and usually requires specific instructions. Whereas

17:32here when we talk about customers, we're looking at distinct customers because it's been defined within a measure. It's always going to be calculated the same way. And last but not least, we were talking about that notion of dictionary that was added so that it understands what values exist. And you can see here that's exactly what it's being using here. Were all orders for

17:55October 1995 fulfilled? What the hell does fulfilled mean? Well, that's that's very much a term a business term that's nothing to do with SQL but here it understands it because it knows how to use the right measure and the right value for that measure same thing here if I add it to for example I add it how about uh for

18:17June 1985 and give me a breakdown it allows to be able to have understand all that information and it uses those those measures I think so it just makes it a lot more reliable so that's an very interesting element I think enhances also the power of Genie to make it a lot more it brings more

18:39reliability and gives patience. Yeah, just makes it a lot easier to use. I didn't have to use here I've got three tables here. I didn't have to just specify the joints because the metric view does it for me. It calculates the dictionaries automatically and it makes the way Genie uses or calculates things a lot more predictive. So overall much better user

19:03experience. Perfect. I think it's crystal clear. Thank you so much Machu for this uh getting you started let's say demo with UC metrics and I guess see you for our next session. We won't be sparing them about what, but stay tuned. I'll be I'll be back very soon. Bye. Take care everyone.