3.13 Discuss data model cleanup - Video Tutorials & Practice Problems

<v ->So in this last sub-lesson</v> on Building a Data Model to support self-service reporting we're gonna go through and just do some basic data model cleanup. We're gonna go through and make sure our model has good naming conventions on our columns. We've hidden away columns that we don't want to see, at the report view. We do some more formatting of the measures we created, so we didn't do some of that stuff as we went through the chapter here. So we're just gonna go through and make sure I had models generally ready for end user self-service consumption. So what I'm gonna do here, like normal, is just go ahead and open up the desktop file that supports this. I'm gonna go to 03_14, double click on it. And we'll go ahead and wait a moment here for the file to open up. Okay. So the file is opened up. So let's go ahead and start doing some of our cleaning activities here. We're not gonna get everything cleaned up the way it should be, if I wanted to do this to 100%. But at least to show you some of the things we might consider. So I can take a look at my total sales, year to date, and my prior sales, and they all show four decimal places here. So let's go in and actually get those measures cleaned up. So I open up my sales table here. I am going to go into my prior year, total sales here. And what I wanna do here, is I wanna go over into my currency and I'm gonna change this to English-United States. So we'll go ahead and do that. And what we'll see now, under our prior total sales is now we're actually looking at things with two decimal places. I'm going to do the same thing with my quarter to date, click on it, go over here, choose English-United States. We're not gonna actually see that one 'cause that's not on the visual plan anymore. Let's do the total sales, go ahead and make that one English-United States as well. So now that has been reformatted, go ahead and do the total sales, your year percentage, choose that one there. And just for completeness sake, let's go ahead and do the year to date sales here as well. Just so we have everything all nice and cleaned up from the data models perspective here. In fact, I'm gonna take a look at my model here I can actually see that I made a mistake in it here, the total sales, year over year percentage, this one shouldn't be format as occurrence. So we wanna actually format this as a percentage. So let's go ahead and go like that and we'll leave it at two decimal places. So this one was actually fine. So I just miss, I made a mistake on that one there. Okay. So that is going through and naming some of our measures. So let's go through here. I'm just gonna go over to the data model now. And what we want to do here is there's several more columns that we actually wanna hide away from the report view. So on the model itself, let's go ahead and rehired the order date, so we had only brought that through to support the quick measure we're creating earlier. So there we go, we have that there. I'm just gonna make this fit to the screen a little better. And now we're gonna go through the sales table. I'm just gonna expand this a little bit here. So a lot of the activities that I do here to come through on this particular table here, so let's go like that. And what I'm gonna do here is I'm gonna go through and let's go ahead and hide these sales territory regions. And I'm just gonna highlight on that in the table, click the eyeball there. So it's not gonna show in report view and keep in mind, this is gonna keep people from accidentally choosing it and expecting the filter from sales to flow over to sales territories. So we're gonna go through and do that to all of the columns in here that are being used to join over to the dimensions 'cause we actually want people to use the dimension columns to do the filtering. So I'm gonna turn the product key off and whereas the other one would turn off. We wanna go to the order date. We'll go ahead and turn that one off as well. And the only columns that we're actually gonna expose in this table here, are the ones that we're gonna sum, average, min, max, and count. So we'll leave our calculator columns alone. We'll leave the unit price. Plus all the measures that we went and created. If we wanna take this one step further, what I would do, is actually build everything into a measure and expose none of the calculated columns and none of the default summarizations, and would want to control everything through measures. But for now this is good enough. Okay. So we can go ahead and take a look at our model here now. And things are actually looking pretty good. So we've hidden away things from the report view that we don't want people selecting on. We went and did some reformatting of our columns. Things are actually looking pretty good at this point. If I just go through my data model and just quickly eyeball everything, there's actually only one column in here that doesn't adhere to the standards of having spaces between the names. And that is, the is weekend column that came from power query that we develop a while back. So we'll just go ahead and put a space in there, just so that every column in our model actually has a space between the names. It's just gonna make things a lot more friendly from an end user's perspective. So now we have a model that actually looks pretty good. So let's go and show you what this looks like at the report view. So I'm gonna go back over here and now let's take a look at our sales table. We'll notice that the only columns they're even being exposed here, are the columns that we'd wanna average, sum, min, max, and count. So the aggregate double columns, the ones that were allowing us to join over or build relationships to the dimension tables, those are no longer available here. So we're at no risk of somebody choosing one of those columns and inadvertently trying to apply filters to it and not having those filters actually work their way back into the parts of the data model. Okay. So that was it. That's just some basic model cleanup that we wanna do. This is always one of the last things we should do before we get ready to start pushing the model over into production, so that people can start building more reports on top of it. It's just really make sure that data models is set the way you want. You've got all the naming conventions in place. If you've got default formatting, you basically built everything in the data model to make end user self-service as efficient as possible.