3.3 Create relationships and manage settings - Video Tutorials & Practice Problems

<v instructor>In the sub lesson,</v> we're going to explore the model view we'll create and manage relationships. We're going to discuss the concept of cardinality and we're going to discuss relationship direction. The definition of cardinality is as follows. So cardinality is official non-database dictionary definition is mathematical, and it is the number of values in a set. But when applied to databases the meaning is a bit different. It's the number of distinct values in a table column relative to the number of rows in the table repeated values and the columns do not count, right ,so there's a definition of what carnality is when we move into the exercises in the sublesson. So we're going to actually start physically showing you what cardinality looks like. Plus we have some animations here a little bit later on to explain the concepts as well. So let's go ahead and leave it at the academic definitions at the moment here. Right ,so relationship direction here's another concept that's very important to power BI and what relationship direction means is the direction of the relationship and that determines how power BI filters the data. So once again, we're going to go in and show some examples of what this means as we move through these sub lessons. But right now we're going to leave it at these academic definitions. Okay. So we're going to go over to the files from Github and get the power BI desktop file open up. That is called L_03_03 start. So let's go ahead and get that file opened up and we'll wait a moment. Okay. So our desktop files open and what we're going to do here now is go down to the model view. So we have the model view opened up for this file here now I'm just going to close these panes off here just to give us a little bit more real estate. And we can see here that we have a number of tables brought into our model view and some relationships have already been created for us by default here. And the reason these relationships were created for us were because we're property over here in the file menu. So I'm just going to go ahead and go to the file menu go to options and settings under options. If I go down to the options in the current file here, under data load I can see that auto detect new relationships after data load is turned on here. So power BI is going to try and discover relationships based on the call names and data types and create them for us automatically if it can, I'm actually going to turn that off because I'm not a big fan of the auto-detect being turned on because this is something I think is very important for you as a developer to go through on your own and confirm that the way that the columns are related is in fact the way that they should be related. So I'm just going to go ahead and click Ok. I still have these relationships that have been created by default for me. So I'm just going to go ahead and leave these alone and explain them to you just to go through and verify how they're created and why and show you that they're actually in fact, correct. Okay. And as we learned in our last sub lesson this is a dimensional model and what we're building here is a star schema. So I'm going to go ahead and move the tables around a little bit. Once again, moving these tables around, isn't going to affect anything when it comes to the data model itself or a reporting. I just like to get things into the format which we're going to be explaining over the course of the rest of the sub lessons here in lesson number two. So I'm just moving these tables around to get them into the format of a star schema. So our fact table being in the middle and dimension tables being around the edges, going one level deep keeping the mind that in the star schema here in power crew we actually collapsed the product level table or merged it directly into the product table. Okay. So now that I have all that set I'm just gonna open my properties window back up and down in the bottom right hand corner of the screen. We can see there's this little icon down here that's called fit to screen. I'm just going to click on that to actually make this a little bit larger here so I can see these tables just a little bit better here. All right. So the first thing we want to do here is go in and verify the relationships so we can see that two relationships were created already. And if I just hover over top of the lines between the tables, so between sales and product I can see that the two product category keys are related. And if I go down to the sales territory and hover over this line here, we can see that power query and the power BI data model have picked up on the fact that sales territory region as related between the two tables here. Okay So, but what we're missing the relationship between the order date and the sales table. So the way that we're going to go ahead and create this is at the very top of the screen under the home tab, under relationship groups there is a command here called manage relationships. So I'm gonna go ahead and click that. And this is going to give us a dialog box that has all the relationships that have been set up so far and the ability for us to go and add brand new relationships. So we can see that the tables here are the from table being sales, the product keys the column is being related to the product table on product key sales on the sales territory region column is related to sales territories on the sales territory region column as well. The additional one that we want to create and we're going to do that by clicking new is by going to the sales table because that's what we want to start with first. And we want to relate it to the order of the table. So if I go ahead and choose those two tables like that, I can see that I actually have two columns here called order date. And these are the columns that I actually want to go ahead and build the relationship on. We can see down below here that a few things have happened in power BI. Number one is the cardinality has been detected. So we defined cardinality earlier on in the PowerPoint presentations. And in this instance here, the cardinality is many to one. So what that means here is many rows over in the sales table can relate to one only one row over in the ordinate table. So explain that with some diagrams in animation shortly and the cross filter direction is set to single. So what that means is filters will flow from the order date table down to the sales table but not the other direction. So if somebody filters on sales of value in this one it will not move up to order date. And once again, we've got some examples that are going to show you those two concepts in action. So I'm going to go ahead and click OK. And we can see the third relationship has been created in here. I can now click close and we'll give it a moment here. And now we've got all three relationships built up in the actual data model view. Okay So all of our relationship immigrated here and let's focus in on one of these relationships line here and break down what came out of those configuration boxes that we did just a few moments ago. So we're going to do here is I'm just going to put my mouse over top of the line between sales and product right here. And I'm just going to zoom in a little bit so that we can see that the relationship between products and sales is on the product key. So that's, what's relating the two tables together. The cardinality is being indicated by this one in this star right here, which stands for many. So what this is saying is one row in the product table will relate to many rows over in the sales table. And many rows over in the sales table relate to one row over in the product table. The arrow down here on the middle of the line is showing the relationship direction. So which we filters are actually going to flow. So anytime we use a filter out of the product table it will flow from here down to the sales table. But if I ever chose a filter in the sales table it will not go the other direction. So it will be blocked by this error right here because filters can only flow one direction in this data model. And as you're building out some of your initial models on power BI it's a best practice when you're new to power BI to stick to the one to many relationships and filters flowing a single direction. In fact, I would even argue as you get into much larger, more complex models this is the ideal setup for any relationships one to many and filters flowing in a single direction. Anytime you need to veer beyond these things you really need to understand what you're doing with the data model and understand some docs techniques. So it does become a little bit more intermediate to advanced So to zoom back out, And if you would have recalled back in power query this product query here what we did was we had to deduplicate those rows first. So had we have not gone through that step back in the power creator we would have actually ended up in a many to many relationships scenario in the data model view here. So we don't want that. And that's why we went and deduplicated the dimension table being the product table back in the actual power query editor. Okay so once again, I'm just going to go into the relationship I'm going to actually double click on the line this time to get our edit relationship dialogue back up and just to show you the other types of cardinality they're available we can have a many to one, which is the current carnality. We can have a one to one relationship one too many and a many to many. So there's four different cardinality types that are available here in power BI from the cross filter direction perspective. Either it can be single. So filters flow in one direction or we can allow filters to flow in both directions. So with those two concepts in mind we're going to go out to an animation example to further solidify what these concepts actually mean. So it's going to go ahead and click OK. And we're going to flip ourselves back over into our PowerPoint presentation. Okay so let's first start off with the many to many carnality so often the most difficult one to understand. So let's just start here. So on the left hand side if we have a table that represents our product dimension we can see that we've got road bike ,mountain bike Jersey, road bike, road bike, Jersey mountain bike. So we've got multiple instance of road bike multiple instances of mountain bike and multiple instances of Jersey. So on that side, we have many of each one of these rows on the sales transaction side we have the exact same thing as well. We have many instances of road bike being sold many instance of Mount bike being sold and many instances of Jersey. So what does this look like in action? If this was a many to many relationship the way that we have things all set up here, we would end up with something like this, where we have road bikes on the product side relating to the sales transactions like so, so we have many too many the mountain bikes are doing the exact same thing. And if we take a look at the one to many relationship we can actually see in this case here that the rode bikes fight as backup one step here have been deduplicated. So we only have one instance of road bike one instance of mountain bike, and one instance of Jersey and what that allows us to do here. If I just go forward again, is that row bikes will be one on the product side and many on the sales transaction site mountain bikes, one on the product dimension side and many over on the mountain bike side. And once again to finish off Jersey is one on the product dimension side and many on the sales transaction side. So pretty straightforward. The last cardinality direction that we want to talk about is the one to one. So in this case here, one row on the product dimension table will relate to one and only one row over in the sales transaction table. So once again, if we did this all in aggregate we're going to end up something like this and the other relationship that we could talk about. Cause I did mention there as four is there is one to many and many to one, and they're both the same. It's just which direction are we actually taking a look at the relationship arrows? That's the only real difference. Okay. So there's our explanation of cardinality. Next we're going to go in and explain filter direction. Okay so let's go and open our filter direction demo from our Github repository. So we can see we've got less than 03 Sub lessons 03 filter directions. So let's go ahead and open up that power BI desktop file. Okay so I've got my filter direction demo open before we get in the demo actually want to show you the tables that we're going to use in this demo to demonstrate filter direction. So go to the model first we've got a table called product sales that has two columns in it, one called product, and one called amount a another table called product with just a single column called product. We can see here that is a one to many relationship based on the data that I've set up here. And the filters flow from the product table down to the product sales table. So let's take a look and see what the data looks like in each one of these tables first. So I'm going to go over to the product table and let's go ahead and just zoom in here on this data to show you what we've got. So we have got two columns, product and amount which has one row for bike which is 200 helmet which is 100 and bike which is 200. So we've got three rows of data in this table here. Let's go over and show you what's in the product table now. So in the product table, once again, if I go ahead and zoom myself in, I can see here that I brought three rows. I've got bike helmet and water bottle. Now for a call water bottles. There are no water bottles sold over in the product sales yet. So let's zoom back out. So keep in mind that is our data here. So I'm going to go out to the visual here and demonstrate what filter direction looks like. So to further explain this demonstration here and you guys can take the files and pick it apart in room if you want this dropdown box right here or slicer is from the product table. So I use the product from the product table. This slicer over here is using the product from the product sales table. So keep in mind if I use this filter here the way that the filter directions set up it should not go up and filter the product table. This one will because the filters will flow from product to product sales. Here are the two tables that we have information from. So we've got our product dimensions here and we've got the product amount. So right now it is being summarized. So we've got the two-bike sales, which is adding up to $400. The one helmet sale which is 100 and our total, which is five. So let's first go and demonstrate doing the filtering from the product side. And if I go in here and choose bike I would expect that both these visuals are going to accept that filter like so, and we can see that the product table is filter down to bike as was the product amount. Okay so that's all well and good. So let's go ahead and erase the filters there and come over to the filter as if we were doing it from the product side. So I'm going to go over here and do the exact same and choose bikes. I'm going to choose bike and notice here that the product amount tables filter down to bike but that filter did not work its way back up to the product so that the products could be filtered as well. Okay so let's go here and actually set the filter direction of both. So I've got a filter over here on bikes still. I'm going to leave it on the product table. We can see that it had the filter, but this one was blocked. What we're going to do here now is go over to the data model on this relationship line here just double click it and down, across filter direction. We're going to set this to both. So we go ahead and do that click OK. The model's going to do some recalculations here. And when I go back out to my report visualization we're going to see now that the filter from the product sales actually did work its way over the product table now it has been filtered down to bike. So that's just a really simple demonstration of filter direction in practice. What you're ultimately going to do in your data models is you're going to keep this one at a cross filter direction of single like soap and the way that you will avoid people ever filtering out of the product sales table by accident is by hiding this particular column called product in the report view. So there is a mode here called hide and report view which will keep the field in actual data model itself. But when it comes out to the report writing side it won't actually be available. So you never run the risk of somebody trying to filter on a field that will not actually allow the filters to go the other direction. So let's just have general modeling best practice. Okay so that brings us to the end of this sub lesson on cardinality filter direction and building relationships.