3: Building a Data Model to support self-service reporting
3.4 Modify column properties
3: Building a Data Model to support self-service reporting
3.4 Modify column properties - Video Tutorials & Practice Problems
Video duration:
5m
Play a video:
<v Instructor>In this sub lesson, we're gonna explain</v> what are data categorizations, explain the benefits of data categorizations, discuss the three groups of categorizations, and explain default summarization options. Okay, so once again, let's get our file open from GitHub. So we're gonna go to Lesson 03, so Lesson 04 on start. Go ahead and double click that and wait a moment. Okay, so we've got our desktop file open now, I'm gonna move myself over to the data view here. So I'm just gonna go on the left-hand side and click on data view. And now I am taking a look at the different tables we have here. What I want to do first is move myself down into the sales territory table. So I'm just going to go ahead and open it up and take a look at the three different columns and sales territories. So I've got continent, country, and sales territory region. So what I want to do here first is I'm going to go into the column properties for the country and continent and set data categories. Okay, so what we're going to do here is we are going to focus in on the grouping of commands right here called Properties. So that's the subject of this particular sub-lesson, Is taking like a data category and the summarization. Okay, So let's start off by clicking on the country column here. So with the country column highlighted, we can actually look at this with our eyes and see that those actually represent countries. But to the storage engine, this is just a column that happens to store text data. And it doesn't really know what type of data this is. So what we can do with this data category property is go in and actually categorize that column with an extra piece of metadata called a data category. So, what we're going to do in this one here is given I have country highlighted, is I'm gonna scroll down the list, and make sure I go to the one below postal code here and choose country. If we give it a minute here, now the data model is going to put this little globe right next to the country column right here. And that's signifying to the engine that this is not just a column that contains text information, but it contains geographical information in the form of country. And I can go ahead and do the exact same thing with continent here. So I'm going to click on continent, go up to data category, go down below country, choose continent. And now that one has been given a categorization as well. So what happens here now is whenever these columns are used in the reporting engine, the fact that they are default category of geographical information, by default, a map will be drawn out for you. Okay, so let's just quickly review the other types of data categories. So, we've got everything here from this first gray bar down to the second has to do with geographical information. The next two here have to do with URL. So if you have a text string in your model that represents a URL, you can choose that to be a web URL or an image URL. So that when you click on it, it goes to the appropriate location. Cause once again, the engine just sees that and sees a bunch of text, but you're actually giving it some extra significance. And the third one down here is called barcode. So three different data categories that we can give to our data in the actual data model. Okay, so next, let's focus on a default summarization. So I'm going to close my sales territory table up and go over to my sales table. And we can see here that if we go ahead and zoom in on our sales table here, so I'm just gonna go ahead and click the Order Quantity column, and zoom in. That we've got a number of columns here in this particular table, that have the Sigma symbol next to them. And what that means is these ones have a summarization applied to them by default. So if we just zoom back out here, we can actually go through each one of these columns. Maybe we'll start at the top here, we can click on Line Total Sales PQ, keeping in mind this is the line total sales we brought in from power query. And if we take a look up in the column properties, the summarization is set to sum. And that would seem to make sense. when we're gonna take a look at analyzing total sales we like would want to, by default, sum that up. Go down to order quantity, same thing. This one's default summarization is sum, go down to the unit price. This one is set to sum, which may or may not make sense. What I'm going to do here in this model here is I'm just going to actually change it to do not summarize. Because I don't necessarily want to sum it up nor do I want to average or do any other types of aggregations on it. So I'm just going to go ahead and say do not summarize on the unit price. So it's a good idea to go through each one of the columns inside of each of the tables and check out the default summarizations that have been assigned to them. Just to make sure that they make sense. Because as mentioned, as you go into each one of the tables in the data model, any of the columns that are set to numerics, will go ahead and be given a default summarization. And just to go through and cover the rest of the default summarizations, if I go ahead and open this up, there is sum, average, min, max, and count. So those are the summarizations you hear me talking about all the time when I talk about fact tables. So fact tables contain the things we're trying to sum, average, min-max, and count because those are the values we're trying to aggregate. Alrighty, so that brings us to the end of the sub-lesson on properties.