Data Modelling in Power BI

expertiseexcelerate

Data Modelling in Power BI  Master the art of data modeling with our comprehensive guide in Power BI. This playlist is perfect for professionals, data enthusiasts, and Power BI users eager to build efficient and accurate data models for powerful analytics. In this course, we cover the essential techniques for structuring your data, creating relationships, and optimizing performance, all while ensuring your reports are scalable and insightful.  Whether you’re just starting or looking to refine your skills, this course will equip you with the knowledge to design high-performing data models that drive better decision-making.  What You’ll Learn: • Fundamentals of data modeling in Power BI • Best practices for structuring data and creating relationships • How to optimize performance for large datasets • Tips for ensuring scalability and accuracy in your models  Ideal For: • Data Analysts and Business Intelligence Professionals • Power BI users looking to improve their data modeling skills • Anyone interested in designing efficient, high-performance data models  Subscribe to stay updated with the latest software tutorials and unlock the full potential of data modeling in Power BI.  Power BI data modeling techniques Advanced Power BI data models Data visualization in Power BI DAX formulas for data modeling Power BI best practices for data models Creating relationships in Power BI Power Query for data transformation Data model optimization in Power BI Power BI data modeling case studies Common data modeling mistakes in Power BI  #PowerBI #DataModelling #BusinessIntelligence #DataScience #PowerBIDashboard #DataWarehousing #DataAnalytics #DataManagement #BusinessAnalytics #PowerBITutorial #DataAnalysis #DataVisualization #DataInsights #MasterDataModelling #PowerBIforBeginners #DAX#MasteringPowerBI #PowerBITechniques #PowerBITips #PowerBITraining #PowerQuery #DataAnalyst #DataModellingInterviewQuestions #PoerBIInterviewQuestionsandanswers #LearnPowerBI #Data

Transcript

00:00Alright, welcome back to the next video.

00:08Now one thing I'd like to mention at the beginning is maybe you have seen that in the last video

00:12I had a little yellow notice there, apply steps, do I want to apply the steps.

00:18So what I did is I simply saved my file, I closed it and then I reopened it again and

00:24now you can see that the message is gone.

00:26I'm not sure whether this is a bug but sometimes it appears and I clicked several times on

00:31applied and it still showed up the message the whole time so I simply saved my file and

00:36reopened it and now the message is gone.

00:38So just in case you have the same issue then please give this a try.

00:42Okay, so now let's continue.

00:45In the last video we actually enriched our data with public data from the web and now

00:51we need to do something very very important.

00:53We need to make sure that our data model is correct because without our proper data

00:59model nothing can be done in Power BI.

01:02So all the reporting, all the great visualizations you might have in mind and you will do in

01:06the future depend on the data model.

01:09So let's ensure first that this is set up correctly.

01:13To do this we need to go inside the model view.

01:16So after importing our data which we have done and also cleaning the data we have done

01:20in the query editor, we now need to go to the third option here, the model view, click

01:25on it and then you see a model something like that.

01:28So let me just zoom out maybe a little bit.

01:31So now it's a little bit more space and we don't need to scroll.

01:35You can either zoom out with your mouse wheel or also at the bottom here you can see there's

01:39a plus minus here where you can zoom in and out inside the model.

01:43Now what this actually shows us is currently all the tables we have in our data set.

01:49And you can also see that beside the tables this time we have more than just one.

01:54Remember in our original first getting started so the easy way we only had one table but

02:00this time we have several tables and all of them are appearing here in the model view.

02:05Now by default if you do not set this off, this is a feature in Power BI, Power BI will

02:10try to detect relationships between tables.

02:14And this has already been done for some of the tables and that's why they have this connection

02:18between them.

02:19For instance, this one here, if I hover over it, you can see that there's a connection

02:23between the product table and the product ID and the sales table and the product ID

02:28there.

02:29So what happens if you do not specify that this feature should not be enabled, so the

02:35auto detect of relationships, then Power BI is trying to figure out the relationships

02:39and normally it does it by the names of the columns, the names of the columns in the tables.

02:46So you can see that we got product ID here, we got product ID there and that's why it

02:51simply thought that the right connection is here from the product ID to the product ID.

02:57Before we dive deeper into that, let me just restructure the table.

03:01You can see you can drag and drop them around and I personally like either this view that

03:05I have my sales table which is also called the facts table with all the transactions

03:09in the middle and my dimension tables which are actually the tables I'm going to use to

03:14slice and dice my data inside the sales.

03:16I want to have them outside, so either like that or I also prefer often that I drag the

03:22sales table, so my facts table to the bottom and put all my dimension tables to the top

03:28like that.

03:29So it's up to you.

03:30It's not required.

03:31It does not change the model at all, but I personally like to have it in a structured

03:35way like this.

03:38So what we could also see is that, for instance, for the dates table here, we currently do

03:42not have a relationship to the sales table and we need to set this up.

03:47Otherwise, Power BI will not be able to, for instance, choose a specific date and then

03:52show us only the sales for this specific date.

03:55That is not possible as long as we do not have a proper relationship here.

04:00Now in order to create this relationship, we can easily do this by dragging and dropping

04:04fields.

04:05What I mean by that is I can take this column here, date column, and I can drag it and drop

04:12it on the order date because this is the relationship I want to create.

04:16And that's probably also the case why Power BI did not pick this up automatically because

04:21it's called date in our dates table and order date in a sales table.

04:25So this is not, um, well not the same, it's not equal and that's why Power BI did not

04:31detect this relationship.

04:32But we can easily do this simply again, click on the field here.

04:37So on the column, drag it and drop it on top of the specific column you want to create

04:42a relationship with.

04:43So in this case, the order date, I drop it here and now you can see that now the relation

04:48is established.

04:49And if I hover over it, you can see that it's the date and order date.

04:54In case the relation was set up incorrectly or you want to remove a relationship, you

04:58can simply right click here and you can delete it.

05:03Or you could also double click on a relationship and then the edit relationship window opens

05:08and it shows you exactly what the connection is.

05:12So in this case, it's picked up the sales table as the first table, but it doesn't matter.

05:15The point is from the sales table, the order column is here selected and in the dates table

05:21here, the date column is selected and you can easily change this.

05:25For instance, I could simply, if I want like to connect just an example, the order date

05:30to for instance here, the year column, I can simply then click this year column here, right?

05:36And now the relationship would be between those two columns.

05:40Of course, you can also see this relationship has cardinality many to many and so on and

05:44also get a little warning I can hear, but that's simply the case also because this year

05:49of course is not the correct relation.

05:52I just wanted to mention this.

05:53If you double click on a relationship, you can easily adjust from which column to which

05:58column the relationship should be connected simply by selecting from the table.

06:03So first select the table you want to use and then simply choose the column and then

06:07choose the column from the other table and you can see that this relationship is fine.

06:11Then you can specify the cardinality and then also the cross filter direction.

06:16Now the cardinality refers to the fact of or how the columns are actually defined, meaning

06:22in my order date, that's why it's called many to one in my order date, I have several rows

06:28where the transaction has been happened on the same day, meaning for instance here I

06:32have the, this is German date, but it means simply the 11th of January, 2023.

06:38So this means that for instance on this specific date, I might have several transactions.

06:43So that means that the date is appearing several times in various rows on the date.

06:49However, each day is unique.

06:52That's the specific feature of our dates table, which it also, this is a feature it has to

06:57have in order to be a proper dates table.

07:00So this means that many to one means we have, might, might have many entries for the same

07:04day in the sales table, but only one entry for this specific day in the dates table.

07:09That's why it's called many to one or also star to one or end to one.

07:14These are also common names, which you hear for the cross with the direction.

07:19This is currently set to single.

07:20That means that always, and that's best practice in Power BI is that the one side filters the

07:27many or the star side.

07:30Let's just click.

07:31Okay.

07:32For now, let me show this to you.

07:33You see that there's a one, there is a star, so that's the many side and also the little

07:38arrow here.

07:39This indicates the filter direction.

07:42That means that right now we could select a specific date from the dates table in order

07:47to filter the sales table for the specific date.

07:50And this works because we have a relationship and the filter direction is going from the

07:55dimension table, this one, the dates table to the so-called facts table or sales table.

08:03That's why it's single direction and this is best practice.

08:07That also means that if the dates table here filters the sales table, the other way around

08:13does not work.

08:14So that means that the sales table from the many side, because the filter direction is

08:18here from the one to the many side, does on the other hand, not filter the dates table.

08:23So you cannot, can't use, for instance, columns from the sales table in order to filter the

08:28dates table.

08:29That doesn't work because the relationship is one to many and the direction is from the

08:33one side to the many side.

08:36What you could do, and there are specific use cases where you want to do it, but most

08:41often you don't want to do it, but let me still show it to you.

08:44What you could do is you could double click here to open the relationship and you could

08:48say, I want to use this as a both.

08:50So I want to filter in both directions.

08:54If you do this and click okay, you will see that now the error changes here and this means

08:59you could now use inside the report.

09:02So if you go in here and create a virtualization, you could filter from a column from the dates

09:07table.

09:08You could filter the sales table, sorry, you could filter the dates table.

09:13So this is possible if you set the relations like that.

09:15However, as I said before, you most often in let's say 99% of cases, you don't want

09:21to do this.

09:22The best practice is a relation from one side to the many side and it's in a single direction

09:29so that the one side, the dimension table filters the facts table.

09:33Just keep this in mind because if you work like that in 99% of the cases, you're correct.

09:40And that's why in here I don't want this.

09:42So I write here and or let's actually double click here and then I simply specify here

09:47this is single direction.

09:50And the last one, make this relationship active.

09:52This just means that currently the relationship is active.

09:56If I untick this option, then the relationship is inactive.

10:00And you also see this visually if I click okay, because now you can see this is a dotted

10:05line.

10:06It's not a single solid line, it's a dotted line.

10:10And this means that currently this relation, it is in the model, but it is not active.

10:17That means currently the dates table is not filtering the sales table, but we could force

10:21it by creating this inactive relationship and then use Power BI, so specifically DAX,

10:28so the formula language in order in within the calculation, make this relationship active.

10:35So also something we're going to explore later on in a DAX formula.

10:40But for now, just keep in mind, in order to filter the data by default, you need an active

10:44relationship.

10:45So let's actually let me write the key again, and let's make this active again.

10:50So this would be the optimal setup, you have dimension tables, these tables should be used,

10:56you can also call them just mainly filter tables.

10:59These are the filter tables, and the filter tables should filter this transaction.

11:03So the sales table.

11:06And if you have this, then you want to have active relationships.

11:09You want to have one to many relationships.

11:12So the one side, the dimension tables, the many side, the fact table, and you want to

11:16have a filter direction as a single.

11:18That is the optimal case.

11:19And that's what how you should structure your model.

11:22So let me click OK.

11:24And now the relationship is active again.

11:27Also, you might have seen this, if you right click here, of course, you can delete the

11:30relationship completely, right?

11:32So if I delete it, then yes, I want to delete it, then this relationship is gone.

11:37So you can easily create relationships between tables, and make this as free time, your data

11:43about this way.

11:44So let me make this active again by simply dragging and dropping.

11:48Okay, so now we have our tables, and we have them connected.

11:52Also, what you see is, if you click somewhere outside, so just inside this, this model view

11:58somewhere on the gray space here, you see that you have some kind of properties.

12:04So for instance, you could turn this on, then you show the database in the header when

12:08it is applicable.

12:09So if you, for instance, if I hover over this one here, you see that there's a name, a storage

12:13mode.

12:14So how is this table actually stored in Power BI, it's imported, and you also see a refresh

12:19date.

12:20And if you would import data from a database, then you can also get information from the

12:25database by simply make this active.

12:27And then if you hover over table, which comes from a database, you will see additional information.

12:31It's not required, but maybe it's of interest to you.

12:33So then simply take this on.

12:36Show related fields.

12:37When the card is collapsed, it's just you can see that you can collapse a table.

12:41So just click on collapse here, and now you can see that this table only shows this specific

12:46entry.

12:47And this entry is shown because the product ID is currently the column which we use in

12:52order to create this relationship.

12:54If you untick this one here, you see that now also this product ID is gone.

12:59So this collapsing tables here, that might be helpful if you have a really, really big

13:04model with a lot of tables to save a little bit of space in here.

13:07So you don't show all the columns in here or scroll bar with some columns, but it does

13:13not do anything to your model.

13:14It's just simply a way to make this view a little bit more, let's say, less cluttered

13:20if you have a lot of tables, but it does not change anything in the model.

13:23Just there, an option.

13:27Show related fields on top of the cards that could also be done simply by simply adding

13:31the, if I take this option here, you see that now the product ID is always, in this case

13:36for the product table, the first entry.

13:38The same is true here for date or for location ID.

13:40So all the columns we're going to use for our relationship are now on top.

13:44So the first entry in this view, that's all what it does.

13:47So it's nothing, nothing changed the model.

13:50It's just really a visual aspect you can enable or disable.

13:55Under this, you can click on tables like that.

13:57I can select this table and then I get additional properties for a specific table.

14:02So here I also have the option to rename the table.

14:05You've seen this in the Power Query editor, but I can also do it here if I want to do

14:08that.

14:09Simply rename it to a different name.

14:12I can give it a description.

14:13This might be helpful, for instance, for colleagues of us who also explore the model and we can

14:18give him or her a few additional information about the table we have here.

14:22For instance, where's the table coming from?

14:24What kind of information is inside?

14:25Anything like that.

14:28Synonyms could be interesting if you're dealing with Q&A in Power BI, for instance, and you

14:32want to refer to a specific table.

14:34And let's say the table is called products, but most of your viewers or the report consumers

14:40actually have a different name or use a different name, which is common in your organization

14:46for this table.

14:47Then you can also add this as a synonym here and Power BI Q&A will pick up the synonym

14:51name also for this table if you refer to it.

14:55Row label and key column.

14:56We can skip this for now.

14:57That's not that important.

14:58Is hidden.

14:59This is something we can enable or disable.

15:01This could be interesting if you want to hide a report in, or sorry, hide a table in a report

15:08view.

15:09So, if I take this option, is hidden, you see that now the I here, this little I is

15:15also crossed out.

15:17The same can be done, by the way, if I uncheck this box by simply clicking on the I.

15:22If you take this option, then also now this table is hidden.

15:26And what this means is if I go back, remember, this is the product table, and now if I go

15:31back to the report view here, you see that the product table is not shown anymore, right?

15:36You can't see it.

15:37It's still part of the model, but you cannot see it in here and use it, for instance, to

15:41create realizations or calculations.

15:44So if this is something you need, for some reason, you can hide it in here.

15:50So let me uncheck this because I would like to see it and maybe use it later on.

15:55What you also get as an information here is if you check advanced here, click on this

16:00one, there you can see the storage mode.

16:03So normally there currently are these three storage modes.

16:06In the future, there might be another storage mode, but for now you can see it's importing

16:10data.

16:11It could be direct query or dual mode, which is a combination of both of them.

16:17Just to mention this here, even though it's not that important now for our training, but

16:22the difference is here importing the data means we import data directly into Power BI

16:26and save it there, right?

16:28This means we get the best kind of performance.

16:30However, this also means that the data is not real time, which means that in order to

16:34see the latest data, if the underlying data has changed, we need to click on refresh in

16:39order to, well, collect the latest data from the underlying data source.

16:44On the other hand, if we use direct query, then we have a really alive connection to

16:49the underlying data source, meaning if something changes there, then this change is immediately

16:53reflected in our report.

16:56But the problem with the direct query is that it is slow.

17:00So compared, especially compared to import, the query is slow and this means the report

17:04is often slow.

17:05So most often import mode is the better option, especially if you do not need real time data

17:11and you actually need to refresh the data, maybe a few times a day, maybe every two hours

17:16or maybe once a day or once a week or even once a month, then always use import mode.

17:22Okay, so this is it for the storage, which is also available here and we can see it.

17:29And by the way, one additional thing is if you import the data and you try to convert

17:33it to direct query, this will not work.

17:36So as soon as you have imported the data, you cannot go back to direct query.

17:41On the other hand, if you first connected with direct query, you could import it instead.

17:46So you could go for direct query to import, but after importing it, you cannot go back.

17:53That's just one additional thing I'd like to mention here, just in case this is something

17:56you experience maybe in the future.

18:01So besides these options, the property pane here for our tables, we also can click on

18:06the three dots.

18:07You might have seen them already.

18:08There are also more options, which give us additional options here inside.

18:13So for instance, creating DAX calculations in here or adding new columns to the table,

18:17but there's something we're going to explore in the next video or next couple of videos.

18:23You can refresh data.

18:25You can also refresh data up here, but the difference here that's important is if you

18:29click refresh here up there in the ribbon bar, this would mean that you refresh all

18:34the tables in your model.

18:36Now if you have a lot of tables in your model, that might not be what you want because it

18:39takes a little bit of time.

18:41And also maybe only one or a few specific tables have been updated.

18:46And that's where this option can be used.

18:48So you can select the table, for instance, the product table and click on refresh data.

18:53And if you do this, if I click here, then you can see that now only the product table

18:57gets refreshed.

18:59On the other hand, if I would click refresh here, all the tables would get refreshed.

19:04So that's the difference.

19:07So next to that, we have the option to edit the query.

19:10If you go to edit query, this is the same as simply going to transform data and open

19:14the Power Query editor again, if you need to do additional transformations.

19:19The managed relationships, you can take this option.

19:21This will then open a window and there you can see all the relationships which are currently

19:25in place.

19:26For instance, sales from the customer ID to the customer table, location ID to the location

19:31ID from the location table, and so on.

19:34And you can also, of course, select one of them.

19:36You can delete them.

19:38You can edit them.

19:39If I click this and you see exactly the same window as you get, if you double click on

19:43a relationship, you can also do it this way.

19:46As we already know, there's various ways in Power BI to get the same result.

19:52So just let me close this.

19:53I just want to show you, you can also adjust your relationships.

19:57And incremental refresh could be interesting one day.

20:00This is just referring to the fact that maybe you have five years of data and you want to

20:04make sure that you only update the last year, for instance, and not the whole table, then

20:10this could be interesting.

20:11But also, it's not a focus of what we currently do here, what we are trying to achieve.

20:16But I would like to mention that just in case you hear what is incremental refresh, it just

20:20means that you do not update a complete table, but maybe only regarding the time frame.

20:26If you have five years of data or 10 years of data, you only update the latest year,

20:29for instance.

20:30That would be an incremental refresh.

20:31It could also be done in Power BI.

20:35Aggregation tables.

20:36This just means that you might have a table which is quite large, and maybe you want to

20:41speed up the process.

20:43This is something I would refer you to maybe a database administrator or database and admins

20:50because those guys, those IT guys can help you actually setting up aggregation tables,

20:55and they probably know what I'm talking about, which helps us sometimes to speed up the process.

21:00So that means instead of using a real giant big table, which takes a lot of time to do

21:05queries with, we can actually speed up the process by using aggregated data.

21:10So for instance, instead of showing for each or the sales for each day, maybe we only need

21:16monthly data in our report, and then we simply aggregate the data on a monthly level and

21:20use this aggregation table, which then can save time and speed up the process and make

21:26our report faster.

21:27That's what this is about.

21:28Delete from model.

21:29I mean, we can just delete tables from the model if you want to do that.

21:33Of course, I don't want to do this, but this can also be done in here.

21:37And hide in report view is the same as simply clicking on the eye.

21:40If you do not want to show it, you've seen what this does and how that works.

21:46Instead of collapsing or expanding all, it's just if you click on this, you can see that

21:48now the table collapses again or expanding is just the same as clicking here.

21:54So that's actually what you can set up in here.

21:59And then beside this, besides clicking on a table, you also have the option to select

22:03a specific column.

22:05So for instance, if I go to my sales table here, let me just expand it.

22:09And then I could say I select the quantity conferences.

22:12If you do this, you see that now the property window changes again.

22:16So that just gives you the option here to, again, adjust the name for this specific column.

22:22You can also add a description, not only for the complete table, if you select the table,

22:26but also if you select a column, you can do that for a column.

22:30You can also add synonyms here, and this again is for the Q&A feature in Power BI.

22:35You can display it in a folder.

22:37What is meant by that?

22:38You can see that currently all those columns here are part of the sales table, but I could

22:43say this is, for instance, here I could use a folder.

22:46I could say financials, financial, for instance, financials, to spell it correctly, financials,

22:53like that.

22:54And if I press enter, you now see that inside the sales table, we have a folder which is

22:59called financials.

23:00And in this folder, we have the quantity.

23:02So again, this is just for structuring.

23:04It does not change the table itself.

23:06It's just an easier way, maybe, if you have a lot of columns in a specific table and you

23:10want to structure it, right?

23:12So it's easier for you to spot and to find the specific columns you have in here.

23:17And if you want to remove it, just go in here, remove it completely.

23:20So let me just click on it, let me select it, remove it, press enter, and then you see

23:24that now the folder is gone.

23:26It's kind of similar to what we've seen in the Power Query editor, right?

23:29But this time, just for the table itself.

23:33And beside this, you can also check this is hidden.

23:37This just means if I take this option, you will see that now only this specific column

23:41is now hidden in the report.

23:43So if I go in here, inside my sales, I would not see quantity in here.

23:47It's not visible.

23:48It's a part of the table, but it's not visible.

23:51So it's the same as hiding a complete table, but this time it's just one column of this

23:55table.

23:58And beside this, under formatting, you can also specify the data type.

24:03In this case, it's whole number.

24:04And you can also specify here the format.

24:07So there are a lot of formats in here for numerical data, for instance.

24:11And if you really want to customize this format, you can also go in here, go to custom.

24:16And this allows you to specify a custom format, right?

24:20It's the same as in Excel.

24:22You might be familiar with it, that you could, for instance, specify that positive numbers

24:26are written normally and negative numbers are, for instance, in parenthesis, anything

24:30to that.

24:31This can be specified here.

24:33And if you want to learn what kind of formatting options you can apply, simply click this learn

24:37more, because this gives you then or gets you directly to the Power BI documentation

24:41and there you can find it.

24:42But to give you a sneak preview, it's kind of similar to what you can do in Excel, where

24:47you also have the option to format specific data entries the way you want it.

24:53So let me go back to whole number, because I would like to keep it the way it is, I just

24:57wanted to mention this.

24:58If it's a percentage number, you can check this box, then it's a percentage.

25:02And if you want to add 1000 separators to it, also check this box.

25:06And then this is enabled by default.

25:07It's just formatting actually for the specific value in here.

25:13So that's that.

25:14And then summarize by.

25:16This is the default summarization, which we have seen, I remember that I showed you in

25:21the first report that you can, for instance, set the aggregation we used for the price,

25:26we did then an aggregation of average, but the default was sum.

25:30And if you want to change the default aggregation for numerical columns, you can do that here.

25:34Instead of using sum, you could, for instance, use average.

25:37And then whenever you drag the quantity in the report view on a visual, then by default,

25:42the first aggregation would be average and not sum.

25:45You can still change it, as we've seen already, but you can set here a default aggregation

25:50if you want to do that.

25:51And also you can set the default to none.

25:53Then the quantity by default would not be aggregated at all.

25:56Also can be specified here.

26:00And yeah, that's it.

26:01Again, a very long video, I know, but this is really crucial.

26:05It's crucial to understand that after you have prepared and worked so hard on shaping

26:10your data, on cleaning your data, then before you can really create your powerful report,

26:16which gives insights to your organization and helps a lot of people in your organization,

26:20before you can do that, you need to make sure that your data model is set up correctly.

26:24So that you have all the tables, they are in the right structure, and especially that

26:28the relationships are set up correctly.

26:31And only then, if you're done with this, then you can go inside the report view in here,

26:36and now you can get started with creating your awesome report, okay?

26:41So that's it for this video.

26:43Hopefully that was helpful.

26:44Thanks a lot for watching and also for following along, and I can't wait to see you in the

26:48next video.

26:49Until then, best guys.

Category

Transcript

Recommended