3.6 Create hierarchies - Video Tutorials & Practice Problems

<v Instructor>In this sublesson,</v> we're going to talk about what hierarchies are, demonstrate common hierarchies, and then we're going to create two hierarchies. So the two hierarchies we're going to create are the date hierarchy and a product hierarchy so let's start on the date one first. Date is a hierarchy that most people are familiar with and that is where you want to take individual days and rolled into months and then take those months and roll them into years and vice versa, starting at the top at year, going into months, and then down to days. So depending on how you want to start your analysis and sometimes this is dictated by your level in the organization and how you're doing your analysis, you can use hierarchies to enable yourself to drill up and drill down information as you see fit. One of the things we encourage inside Power BI models is to bring data at the lowest level of detail possible because Power BI can handle lots of data and then that allows your users tremendous flexibility on the report side to decide how they want to view their information and how they want to go about aggregating it. And one other thing that we can say about the date hierarchy here is this is what is often referred to as a natural hierarchy 'cause most people aren't going to argue that January 1st does not roll up into January and January 1st of 2021 does not roll up into years. So this is a natural hierarchy. In this case here, in the way I'm potentially describing it is based on calendar. You do have the option of creating date hierarchies that are based on fiscal which are a little bit beyond the scope of this class here so let's just focus in on the simple date hierarchy which allows us to roll from days to months to years and often this is referred to as a natural hierarchy. Contrasting that with the product hierarchy, this is one that is defined by the organization itself. So we as an organization get to choose how we want to aggregate our product data and there is no universal way of going ahead and doing this in most cases. So in this case here, we have individual products which we want to roll into subcategories and those subcategories we want to roll into categories. So once again, this is completely arbitrary and we get to choose as an organization how we want to do this and hopefully we can build hierarchies like this and promote consistency across our organization which makes for ease of comparisons in analytics. So let's go ahead and open up our Power BI desktop file that's going to support this sublesson and get into the demo on creating hierarchies. Okay, so I'm going to go to my Power BI desktop file for lesson three, sublesson six. We're just going to go ahead and double-click on that to open it and wait a moment. Okay, so we now have our desktop file open. What we're going to do here is go into the model view. Let's go ahead and do that. We're going to get ourselves to our star schema here and here's where we're going to go ahead and start creating our hierarchies. So the first hierarchy that we're going to create is the one on order date. So what I'm going to do here first is I'm going to click on calendar year like so and we're going to rename this column to order date year. So I've gone ahead and done that in the name property here on the general card. And the next column that we want to look to rename is the one here called month year. So I'm going to click on month year and we are going to go ahead and call this one order date month so let's put my cursor inside the properties pane here under the general card and choose name and let's go ahead and call that order date month. Do that like so and now we've got the name taken out. So we've gone ahead and done the two column renames that we want to do and I just want to point out to you that even though we did this renaming here at the data model level, if I go into my transform data command at the top of the screen here, I'll go back into Power Query here and we're going to see that the order date name changes have actually been pushed back into the order date query so if I click on order date query here and go to the very last step that says rename columns one and open this up, I can actually see here are the columns that have been renamed so the naming was pushed from the data model level back into Power Query. So I just wanted to go ahead and show you that here and it's going to close the Power Query editor out and get us back over to our data model. Okay, so what we're going to do here is we're going to start building our hierarchy. So what I'm going to do here is go to the top level of the hierarchy which is going to be out order date year and I'm going to right-click on order date year and say create hierarchy. So it's going to take a moment here and go ahead and build this hierarchy out and what I want to call this is just order date hierarchy so I'm just going to go ahead and get rid of the year out of there and that's the name that I want to go ahead and choose for this hierarchy. I'm just going to call order date hierarchy like so, hit enter, and we can see now that we have a hierarchy here with one level and it's just the year. So what I can do is continue to have the order date hierarchy highlighted. We can see here in the general card, it's what has focus and if I go down a little bit lower in the general card, I can see this property setting here called hierarchy and what I'm going to do here is click on this, select a column to add a level, and what I'm going to do here is go to order date month and then I'm going to add one more level called order date. So if I scroll down a little bit here, as you can see, I've got my hierarchy going from year to month to date. The last thing I want to do now is click this apply level changes. I'm going to go ahead and do that and if we give a moment here now, we can see here on the right-hand side of the screen, we've got our hierarchy built that starts with year, month, and date. So what we did off the bat is we renamed these columns here so that it's descriptive as to what type of year, month, and date we're actually using when people go to do reporting. In this data model here, we only have order date but we may have ship dates and due dates and this really helps clarify at report time which column values people are using. Okay, so the last thing I'm going to do here is I'm just going to go over to my order date table here on the diagram and just open this up a little bit so I can see my entire hierarchy. The last thing I want to do here is I'm actually going to go out and hide the columns here from report view that are outside the hierarchy. So I have an order date right here, I have order date inside the hierarchy. I'm going to click this little eyeball next to order date and that's going to keep it from being visible in report view. I'm going to do the same thing on order date month and the same thing on order date year. So now when anybody wants to use the order date year, month, or date, they have to do it through the hierarchy that is built into the data model itself. Okay, so that brings us to the end of the order date hierarchy. Next what we're going to do is create the hierarchy on top of product. Okay, we're going to go ahead and use the exact same technique that we used for the order date. I'm just going to move my sales territory table down a little bit and open up the product query a little bit more here so eventually we can see the hierarchy that's going to get created. What I'm going to do now is go to my product category and click on it and from here, I'm just going to right-click on product category in the table here and say create hierarchy so once again, I'm going to get the exact same thing going here and now I have the name product category hierarchy over in the name property of the general card and I'm just going to go ahead and call this product hierarchy like so, hit enter, and now we have a new hierarchy called product hierarchy with one level in it and that's the product hierarchy. Once again, we're going to go down below in the general card. We're going to choose columns to add to our levels. We're going to go one level down to product subcategory, go down here and say product name so that we have all the necessary levels of our hierarchy. I'm going to click on apply change. We'll see in my table here that I've got the hierarchy all built out and then I want to do the exact same thing I did in the order date table and go in here and hide the columns from the table that we don't want people to see at report time 'cause we'd like them to use the columns through the hierarchy so there's product category. I'm going to turn it off like that or excuse me, that was product subcategory. I'm going to turn product name off in the report view and I'm going to go to product category and do the exact same thing. So just go ahead and do this, click on that eyeball there, and now we can see that we have two hierarchies created, one for order date, one for product, and the fields that are inside the hierarchy we've turned off at the report view so that nobody can see them. They still exist in the data model but you can't use them for a report. Now if you wanted to, you could go ahead and create a hierarchy around sales territory 'cause there's a hierarchy there. We're not going to go ahead and do that. We'll let you do that one on your own. So we're going to show you some other techniques in the reporting section in lesson number four on how you can actually do some different things to build a hierarchy around sales territories on the fly.