Advanced Data Modeling in Power BI | Star Schema, Relationships, Optimization & DAX Applications

Right. So, now we can see we have this particular two tables which are available. And so, first we will try to do a data model. And if you can if as I already told you, we have in our data if I show you over here, we have two location IDs. This is a pickup location ID that is the customer is being picked up from which location and this is a drop off location ID, right? So, these are the two locations which are important for us. And based on these two locations, we have to create the relationship, right? So, these both should be connected to locations table, right? But it depends upon us which is the active relationship and which is an inactive relationship, right? So, first we will do an you know, relationship. So, for that, I will just go in manage relationship, right? And from here, we have to create a new relationship. So, I will just click on new relationship and first table. I will just select the first table that is trip details and second, I have to connect it to locations. So, first I will just create it on based on location ID. The you just have to select this and I will connect it to this location ID. So, this is a pickup location ID and I have joined it to location ID. And here it is automatically showing us that it is a many-to-one relationship. That is fine. We want the same way. I'll just click on save. And as soon as you do, you can see a relationship has been created that is on trip details table, there is a many and on locations, it is one. That is as I told you, the location ID which is available over here. If I show you, the location ID, each and every value or each and every ID on this particular field is unique and different. So, this is a primary key for us. So, obviously, it should have a one value because there is one and here is a star because there are many on PO location ID. If you see the values are been repeating, you can see here 186 137 and 137. So, values are repeating, so there are many values of same digits or same ID available on different rows for PO location. So, it is one to many. And I will just close this and here you can see now this particular I can say relationship has been created. All right? And if you take a closer look on this particular relation, so when I take a hover over my mouse, you can see it highlights those particular fields on which it has been joined. And here you can see one to many I told you one to star means what? Star is many, one means one, right? So, I told you the why it is one to many and all those. And it should be one to many only. This is your fact table, this is your dimension table. So, there is always a relationship of one to many between dimension table and the fact table. All right? And then you can see a direction in this way, right? So, means what? This is the direction how it flows. So, filters whenever you are applying the filters on this particular data model, it is always recommended that filter should come from this particular table, right? So, this table from this table the filters should come and so that you will have and correct results. So, the filter direction is in this way. So, whenever you select any member from location or city, it will be easily filtering out the trip details data, right? So, this is a filter direction. So, whichever table have a primary key, so from that table the filter direction always flows, right? Towards the many table, right? So, you can see star. Now, we have to create one more relationship. Why? Because we just connected the locations table to pick up location. And this will just give us the information of which is location for pick up. But we also want the information of table that is of drop off location, right? You can see this is a drop off location. So, we should connect pick up location as well as drop off location to locations table. We already connected pick up. We will connect now drop off. So, I will just go to manage relationship and I will create one more relationship. Create. I want it from trip details to locations table. And now I will join the drop off location. Select this and I will connect to location ID. Same it will be again many to one relationship and cross direction should be single and I'll just click on save. And you can see a new relationship has been created. And one in one important thing to note over here is you can see the this is the this was the first relation which we created. I've selected the check mark that is on. Pick up location ID to location ID and right now it sits active, right? You can

see this is active right now. And this is the other you know, this is the second relationship we just created which is on drop off location to location right on the locations table which is inactive, right? You cannot create uh both active relationships uh for a single data model from in between uh same two tables, right? You can either you can just create one active relationship. So, we will keep this active because pick up on pick up locations we are going to do more and more work. But we are also going to do some work on drop off locations as well. So, this we will keep inactive, but this will be more actively used and more charts will be created on pick up location ID. So, we will keep this active, but from here you can make it active also, right? Or switch to inactive or switch to active. You can do it both from here. It doesn't say it doesn't matter that you have to create this first and this first. Doesn't matter. You can change the active status from here. That is status to active and inactive. For now, we will just keep same. Close it. And now here you can see a new relationship. When I hover over my mouse, you can see it has been created between drop-off location ID and location ID. So, here it is in a dotted format. So, why? Because it is an inactive relationship. Right? So, you can make this particular relation active uh when you create any visual and write a DAX for it so that you can make it an active. So, through DAX functions, you have to make that particular relationship active. And we are going to see DAX functions so that you can make this active and then actually you can draw some results based on drop-off location. Right? So, any table in between these two tables, you can create as many as relationship you want. But only one can be active. Right? One only can be active and multiple relationships can be inactive between these two tables. It doesn't matter. Many can be inactive, but only one can be active actively used in between any two tables. So, this can be an interview question which can be asked for you. And they can also ask you how can you make an relationship active in between two tables if it is inactive? Then you can make it through DAX functions. I will tell you which DAX functions we are going to use to make it active. And then we will see how what are the results you are getting when you make it active. Right? So, which is very important. Here also you can see in the data you can see here one cross is there, which means that it is inactive. And here you can see a proper relationship is there, which is giving us an information that it is an active relationship. All right? So, very important thing to note over here, active and inactive relationships. Now, we have to create one more table into this particular data model, which is nothing but a calendar table, or we also call it as a date table. Now, you will say why we need that information or why we need to create it? Because if you see in our problem statement in our first dashboard, we have to create this particular chart, that is by trip type. Or if not for this also, we have we will have to create it for total booking by day. And for this, we have to bring an information from date table. If you are performing anything on the dates, so it is always recommended by Microsoft Power BI that you should create a date table. And the date table we will be always creating here in this particular project on PU location. That is on pickup time, right? So, you can see on pickup time, this is the date field and time field available for us. So, based on this field only we will create our what I can say calendar table. All right? So, first I will go in table view, and here we will create a new table. But before creating a new table, what we have to do over here is this pickup time right now, you can see this field is a date and time field. So, I have to extract this field that I have to split this particular two field in two different fields. That is it should only take a pure date over here, and second, it should only take the times over here. That it should only show us the time. So, first I will just do this for first over here. So, for that I will right-click in trip details, and I will create a new column. And I will name it as pickup time. So, this will be my pickup time. Oops. Is equal to and I will use a date function for that. So, function. So, I have to extract the first argument that is year. So, I'll take year and year from where I have to take it from pickup time. Okay, so it will take year from pickup time. Close the bracket for year. Second argument that is month. So, again month from pickup time.

Close the bracket for month and day from pickup time. Okay, close the bracket for day and the close the bracket for date and hit enter. So, you can see pickup time is already a field been used. So, I'll just close this and I will name it as pickup date. I will name this as pickup date. Hit enter. Now, you can see this particular field have been created over here. So, date has been exactly been brought over here. But, you can see there is one information of time is also been brought because pickup time already have a time component over here, but values are 00 for it. And we don't want that to be represented. So, what I will do I will just select this particular column, go to column tools over here and you can see the data type. So, I'll just change the data type from here to just date. Okay, so now you can see you can have or you just have a pure date over here. And by making use of this particular date only we will try to create now calendar table. So, now I will just go in table tools. Go in table tools and I will just create a new table. table and I will name it as calendar table. Okay, so I will just use a calendar functions for it. So, you have a calendar function. So, it is asking us what should be the start date. So, start date you can either hardcode it or you can just bring it from the field from already tables or already present table fields. Any field from the already present table you can bring it from there also. Or you can just hard code it in double inverted commas, whichever you want. But it is always recommended to bring it from the other field. So, I will just bring I will take the minimum of pick up date. Okay, so this is the pick up date field which we already created in calculations few minutes ago. So, this will be the minimum of pick up date, so it will create or it will take the minimum date from this pick up date and I want one more, that is maximum date from pick up date. Okay, so this is maximum close the both brackets. Now, what will happen? It will identify the pick up date, minimum date, and maximum of pick up date. So, in between these dates, so the minimum date and the maximum dates, in between these two dates, whatever dates are there, right? So, it will give us all the dates row by row and it will be different on each and every row. So, as soon as I hit enter, so you can see I get dates from 1st of June to 30th of June. So, minimum date which was captured was 1st June and the maximum date was 30th June because we have the data for only 1 month. And in between all the dates are being brought and you can see each and every date on each and every row, it is different, right? So, each and every date is different on each and every row, right? So, this is important for us when you can see now this time component is also present over here. We will change this, so just go in data type and we will just change it to date. So, make sure it is only date. And now, I will go to model now. So, other columns in this particular calendar date we will create later as we move ahead in our problem statement and we start designing. So, at that particular moment I will tell you why we are creating those and how we will create those. So, I'll just go to model view now and you can see a new table has been created over here, that is calendar table. And as I told you, it is based and created on pick up date. So, what I'll do, I'll just you can see the pickup date is there. So, I'll just connect this date to this date. So, I'll just create a date and I'll just drag it over here. So, this is how you create a relationship. So, this is also one way of creating relationship instead of going over it, manage relationship and create. You can create in this way also. So, you can see date from here and pickup I'll just click on okay. So, as soon as you do it, you can see one more relationship has been created over here. And when I hover over my mouse it, you can see it is between pickup date and date. And you can see here for this particular table, it is one and from here it is many relationship. Why? Because pickup date in this particular table is been repetitive. Here it is uh it is one date on each and every row. So, here it becomes automatically a primary key. But if you see in trip details here, the pickup date you can see 1st June, it is repeating again and again, right? So, that's the reason here many dates are there on different rows. So, it is many over here. That is star. So, in this way we have created our data model. And this particular data model can be considered as a star uh schema, right? So, this is a star schema data model where we have one fact table and there are two dimensions table. One is a locations table and another is a date table or a calendar table which are two dimensions table. And at center there is one fact table. So, this is a type of a star schema for us. So, in this way you create uh data modeling. And always remember 60 to 70% of time is

spent on uh data and data modeling itself, right?