Data Model Wizardry - Final Steps for Mind-Blowing Reports - video Dailymotion

expertiseexcelerate

Data Model Wizardry: Final Steps for Mind-Blowing Reports  Welcome to our comprehensive Power BI training series! This playlist is your essential resource for mastering advanced Power BI techniques, starting with our first course, "Data Model Wizardry - Final Steps for Mind-Blowing Reports." Whether you're a data analyst, a business intelligence professional, or a software enthusiast, this playlist is designed to provide you with the latest strategies and best practices for creating exceptional data models and reports.  What You'll Learn: • Advanced techniques for perfecting your data models • Tips and tricks to finalize and optimize your reports • Strategies for creating visually stunning and highly informative reports • Practical examples and hands-on exercises for real-world application  Why This Playlist? • Comprehensive Content: Each course is meticulously crafted to cover all advanced data modeling aspects and Power BI report creation. • Up-to-Date Training: Stay ahead with training that includes the latest features and updates in Power BI. • Expert Guidance: Learn from seasoned professionals with extensive industry experience. • Interactive Learning: Engage with practical examples and follow along with hands-on exercises.  Subscribe now and join our community of learners who are elevating their data skills with Power BI. Don't forget to hit the bell icon to get notified about new videos and updates. Let's explore the final steps to creating mind-blowing reports together!  #BusinessIntelligence #PowerBI #PowerQuery #DataModeling #ReportingTools #BusinessAnalytics #PowerBITricks #DataVisualization #PowerBITutorial #DataAnalysis #DataTransformation #DataInsights #DataReporting #Analytics #PowerBITips #DAX #DataModel #PowerBIDashboards #AdvancedPowerBI #BeginnerPowerBI #DataScience #HealthcareDataAnalyst #DataAnalyticsinHealthcareindustry #businessintelligenceanalyst #DataStorytelling #MicrosoftPowerBI

Transcript

00:00Hey friends, welcome back to the final video of our data modeling challenge.

00:10Now in the last couple of videos, we started with a complete messy Excel file with hotels

00:17and we have created a facts table and also some clean dimension tables.

00:21And we also enriched our data set with information from the internet.

00:26And you can see that I closed Power BI and I reopened it and you can also see that this

00:30message which we had in the last video is gone.

00:33So I'm still not sure why this message appears, but as you can see, just close it.

00:39Just take the report, close it, reopen it, and you can see that now it's gone.

00:43Now the most important thing after cleaning the data set is to set up the model.

00:50And this can be done here in the model view.

00:53So we can go to the model view now and let's just check whether everything is in place.

00:58So let me just zoom out a little bit.

01:00And I can see that I got my facts table here, hotels, that's fine.

01:04And regarding the connections, there are already two connections set up, which is from the

01:08hotel info.

01:09Let's hover over the connection.

01:10It is based on hotel ID, that is fine.

01:14And for the location, I can zoom in again like that, for the location, it is set up

01:18on location ID, which is also correct.

01:21Now manager TBL is currently not mapped.

01:24So here we have an index, okay.

01:26And then Power BI is not able to figure that out because here it's called index and here

01:30it's called manager ID.

01:32So we could have named this properly, then probably the relation would also be automatically

01:36detected, but that was not the case.

01:39So we need to do it manually, just drag it and drop it here.

01:43And then we also could go with this relationship.

01:46So one, two star, which is the perfect setup for a Power BI data model.

01:52Now the last one is the dates table, which we have here.

01:55And this should also be connected from the date here to, in this case, our date here.

02:00So we can connect this one to this one, and it should also give us a one, two star, which

02:05is true.

02:06So now we have set up our data model.

02:08And on the top, we have our relation, our dimension tables, at the bottom, we have the

02:12fact table.

02:14Some people also like to place the fact table in the middle or in the center here, and then

02:18put the dimension tables around.

02:20So like that.

02:21Of course, maybe you want at the bottom here and here, and then one on top, a little bit

02:27too big like that, and maybe one here.

02:29So you can also set up the model like that, right?

02:31This is only for structuring and for yourself, but it doesn't really change the data model.

02:36The crucial part is that you set up the relationships here correctly, make sure that the right columns

02:41are connected, also that you have a one to star, so one to many relationship, and also

02:47that the data types of those columns are the same.

02:50That is also very important, because I also have seen models in the past where this was

02:54not the case.

02:55And then we have some issues in the report creation.

02:58So also always think about the data type that this is the same.

03:03Now after we set up the model like that, we could now go to the report in here, and we

03:09could start actually creating our reports.

03:12So let's just check whether that works after setting up.

03:16In the location table, for instance, we have a country.

03:19So we could go with the country, or let's actually use the city, or that.

03:24And let's just check.

03:25And I can see here, city is currently only one.

03:28Now there we've all, okay, took a while.

03:30Now here is our location regarding the location table.

03:35Now if we use data from the hotels TBL, so from the facts table, I would expect that

03:41this is mapped correctly to this one.

03:43And we can try that.

03:44Let's use the converted value, which we used, so US dollar amount, and put this on a bubble

03:50size.

03:51And we see that there are differences.

03:52And we can see that for various, in this case, cities, we have different kinds of values.

03:58And I can also see that I probably need to format those values a little bit.

04:02So if I go in here, I would probably have here, set this to two decimal numbers, and

04:08also two US dollars.

04:10So that would be actually the correct, let's go here again, two decimal numbers, that would

04:16be the correct value.

04:17If I hover over this now, I should see a better value like that.

04:20That is true, right?

04:21And of course, I would also be able to filter this further.

04:24So if I go to my dates table here, and I choose, for instance, the date here as a slicer, or

04:30I'm choosing the year, actually, as a slicer, go in here, choose year, use this year as

04:36a slicer.

04:37Let's just check that where is the slicer icon, there it is.

04:40And then I also want to have this under slicer settings, I go to the view pane here, and

04:45make sure that under the formatting option, I'm choosing here, the slicer settings.

04:50And I say I would like to see this as a drop down.

04:54And then for instance, I choose one of the years, as you can see here, we have all the

04:57years, just choose the latest year, for instance.

04:59And now I would expect, if I make this smaller, and also the data here changes, and I can

05:05see that this is also less, the value is smaller, right?

05:09So this works, which gives us also an indication that also our, in this case, go back in here,

05:16our dates table is able to filter our facts table hotels, because we have set up the relation

05:21in the model before like that, right?

05:23So we can use this table here to filter our hotels table.

05:26So that also works exactly as it should.

05:31So besides this, of course, we could play around this further, we can go in here and

05:34say, also add maybe for styling here, the category labels, if I take this on, I can

05:39also see here, the various cities and their names, and so on, right?

05:46One last thing I also like to show you is a P&L item, because there might be a little

05:51bug which we need to fix.

05:54So if I go in my hotels, TBL, I have the items.

05:57And if I go to the item here, and take this option, and I also would like to see here

06:02the values.

06:03So if I go to value, convert it here, this one, I can see here, the values for the various

06:10items now across currently all the cities, because I did not slice my data here.

06:15But let me make this bigger, so you can see that better.

06:19Go to the value section here and choose maybe 14.

06:23Okay, so hopefully you can see that on screen.

06:25Now, we can see that this works, but if you are familiar with a P&L structure, and also

06:31with the structure which we have seen in the model here, originally in the Excel file,

06:38you can see that actually we started with various kinds of revenues, like a good accommodation

06:42sales and so on.

06:44And this looks a little bit different from this one here.

06:47Because here we can see that this is a little bit, well, the order is not correct, right?

06:52Accommodation sales, for instance, should also be next to accommodation sales on top.

06:56And at the bottom, actually, we would like to expect rather things like gross profit

06:59and so on, or the other kinds of things.

07:02So obviously, this order is not correct.

07:05And what could we do?

07:07Well, if you go to the three dots, you could see that we can sort by P&L item.

07:13But this only allows us to sort this ascending or descending.

07:16This is not what we need.

07:18We need a proper sorting here.

07:21And that's why we need to go back one more time in our, under home, in our query editor.

07:27Let's click this option here.

07:29And then let's just do the following.

07:31Let's actually create another mapping table, which allows us to sort the, in this case,

07:37the P&L items correctly.

07:40So for that, I'm going to the hotel CBL, this one here.

07:44And what I will do is I will simply right click here, and I'm creating here a reference.

07:51This would be one option.

07:53The other option would be to something I want to show you here, something new, which is

07:58right click on this case on the specific column.

08:02And instead of doing the thing which we've done before, like referencing and then actually

08:06merging, for instance, this back to hotels, after we remove the duplicates, we can go

08:11in here and just right click on a column and say, add as new query.

08:16If you take this option, we get now a new query, which contains a list and contains

08:22all the various items inside the P&L.

08:27So now we first convert this back to a table.

08:31And this can be done by simply going here to table.

08:35So let's do the different data type and we need as a table.

08:37So that's why we did do this at first.

08:40So these are default settings.

08:42There is no delimiter.

08:43Click OK.

08:44Now we get our table.

08:47And now we right click here and say we want to remove the duplicates.

08:50So on here, remove duplicates.

08:53Now we've got a unique list of our items and also in the correct sort order here.

08:59And now we do the same trick.

09:00We go to add column.

09:02We add an index column.

09:03So if I say index column and say from one, in my example, okay, now we got.

09:11That's also our mapping in the correct order.

09:13And now what we do here called simply P&L, it's called rename it and let's say P&L mapping.

09:19Okay.

09:20Mapping is also a helper column or in this case a helper table.

09:24So that's why I drag this outside of my transformations.

09:28And here for this one, now I want to merge this to the hotels.

09:32So I go inside my hotels table.

09:34I go here and say, go to home and then merge queries.

09:40And this time I merge via the P&L item, go in here and I merge my P&L item mapping this

09:46one.

09:47So like that, you can see here, and then I need to select here the P&L item.

09:54And then we should see exactly.

09:56We match everything.

09:57We can click OK.

09:59And now for the table, we expand this and just choose the index because we need the

10:03index for the sorting.

10:05That's our helper column.

10:07Just remove this, use original column name as prefix, then click OK.

10:13And now we have here our index, which is the mapping for the P&L item.

10:17So actually it's the sorting column.

10:20So let's say P&L sort.

10:22That's what I named this column.

10:24And then we should be good to go.

10:26So let's right click on the P&L mapping and also make sure that we do not enable load

10:32because you don't need it in the model.

10:33It was just to create our helper column for sorting.

10:38And now we can go back to the report by clicking close and apply.

10:44Now this gets applied and just a second, we get our hotels data here.

10:50And now we could do the following.

10:52Just watch what happens.

10:53We have here currently the not corrected one.

10:55It's not sorted correctly.

10:57But now if we go inside our data view here and select here the specific facts table,

11:04so hotels TBL in here, and now we can choose the P&L item.

11:09And we can say under the column tools, sort by column, and now we choose our helper column,

11:14which is the P&L sort.

11:16Pick this option here.

11:18And now if we go back to the report, report new, you now see that now this is sorted correctly.

11:24So starting with all the sales measures here, so the sales P&L items, the total sales then

11:28derived from that, cost of sales, gross profit, and so on.

11:32And at the end, we have the APDA, including these additional informations, next rooms

11:37available and rooms open.

11:39So that is it as a little bonus trick here for sorting or creating a sort, a helper column,

11:46because that's required in Power BI if the, well, the alphabetically sort doesn't work

11:51for you.

11:52And of course, now we could still continue creating our reports.

11:56We can play around with different themes and so on, different kinds of visualizations,

12:00and well, creating our final report, which we can deliver then to the customer, including

12:05our insights.

12:06So this is something I leave up for you.

12:09The main point of this complete part was actually figuring out how we can start with messy data,

12:15clean it, convert it into a proper star schema into a model, which Power BI can work with.

12:21And then of course, also a few hints and tips and tricks along the way.

12:25So that's it for this video.

12:26Hopefully you enjoyed the whole part and this was helpful and you can apply what you've

12:31learned in your own projects in the future.

12:33So thanks so much for watching.

Data Model Wizardry - Final Steps for Mind-Blowing Reports

Category

Transcript

Recommended