Locked lesson.

Upgrade

About this lesson

As there are many steps to build a great model, it can take some time before you see the results. For that reason we thought it might be a good idea to inspire you with our "end game" and show you the final model that we will build throughout this course.

Exercise files

There are no related exercise files for this lesson.

Quick reference

A Sneak Peek at Your Future

A demonstration of the dashboard we will be building in the course.

When to use

When you want to create an interactive, refreshable Business Intelligence solution.

Instructions

In this course, we will work through the process of creating an interactive dashboard that allows us to slice and filter out data however we like.

  • Power Query is the tool we will use to shape and clean our data
  • Power Pivot is the tool we will use to model our data
  • DAX is the language we will use for creating calculated fields and measures we will be putting on our Pivot Tables
  • Slicers and filters allow us to view and analyze the data in a specific way

Hints & tips

  • When building a data model in real life, you go through the BI creation cycle with regressions between steps. However, in the course we will follow the cycle linearly for ease of understanding.
Login to download
  • 00:04 So let's take a quick look at what's gonna happen in this course.
  • 00:07 There's a lot of things that are gonna go through.
  • 00:09 But the end goal that we're trying to get to is a nice little dashboard that we have
  • 00:13 with something like revenues that is nice and sliceable so that we can go and
  • 00:17 choose a couple of departments that we want to filter into, so
  • 00:21 that we can compare, maybe January and February, or even go and
  • 00:24 compare different years together in order to see what's going on.
  • 00:28 You'll notice in here that when I actually look at January, I've got growth versus
  • 00:32 the previous year, these are all custom formulas that we have to write in order to
  • 00:36 actually build pivot tables that are really extensible and dynamic.
  • 00:40 We have the ability to go to our trends page and take a look now and
  • 00:43 say, show me the sales on January 18th.
  • 00:46 And I've got my month to date and my year to date.
  • 00:48 And when I move to February, I can grab a different range and see what's selected,
  • 00:52 what do I have for month to date here and year to date in this case as well.
  • 00:56 But the challenge is that building pivot tables,
  • 00:58 well, it's easy as long as the data's in a good state and shape to start with.
  • 01:03 The challenge is that the data that we've got
  • 01:05 actually comes from a lot of different places.
  • 01:09 I need to show you how we're gonna work through and
  • 01:10 actually shape the data in order to land it into these
  • 01:13 five individual tables that we're gonna work with.
  • 01:17 Some of this data, our first one, actually comes from a database, but
  • 01:21 we've also got data that lives inside an Excel table.
  • 01:25 And even worst than this simple Excel table, we have budgets that lives in
  • 01:29 a pivoted, a massive pivoted table that we have to somehow figure out
  • 01:35 how do we unpivot in order to build it into a nice stable table to work with.
  • 01:41 If we go and take a look at what actually happens inside the power pivot engine,
  • 01:44 I've gotta show you this.
  • 01:45 We've got tables that we've actually brought in, shaped through power query,
  • 01:49 that's what we saw before, those things on the right-hand side.
  • 01:52 We've got a bunch of measures and formulas that we've built in order to actually go
  • 01:56 and build a couple of cool things that'll slice the way that we want, and we have to
  • 01:59 show you how that we work through the process of building what we call a data
  • 02:03 model with all of these different fields linking all this stuff together.
  • 02:07 So we've got to talk about facts and dimensions and whatnot.
  • 02:11 This is the endgame that we're shooting for.
  • 02:13 And as you can see, there's a lot of complications along the way.
  • 02:17 I'm gonna actually walk you through this process without doing the whole cyclical
  • 02:23 with regressions piece, which is not normally the way you would build a model.
  • 02:27 But in order to make things clearer from a learning perspective,
  • 02:29 that's the way that we're actually gonna focus on this particular module.
  • 02:32 So there's lot of things coming here.
  • 02:35 We're gonna be looking at Power Query for
  • 02:37 shaping our data, we're gonna be looking at Power Pivot for modelling our data,
  • 02:41 we're gonna be looking at DAX formula language for actually creating our
  • 02:44 calculated fields and measures that we're putting on pivot tables.
  • 02:48 And then we're gonna look at the slicing and
  • 02:50 dicing and how we can actually override these measures in order to make some
  • 02:53 really cool stuff that's gonna build you a business intelligence for your future.
  • 02:58 This is your future, this is where we're going.
  • 03:00 We're just gonna walk through the process logically in order to get there
  • 03:04 on this Mulligan's Golf Course financial report that we're gonna build up.
  • 03:09 So let's get started.

Lesson notes are only available for subscribers.

Gift this course