Locked lesson.

Upgrade

About this lesson

A review of the process used to develop and update modern business intelligence solutions in Excel.

Exercise files

There are no related exercise files for this lesson.

Quick reference

Overview of the BI Process

An overview of building a Business Intelligence solution.

When to use

This module helps explain what stages and tools go into building a dashboard, report or other data model.

Instructions

The main stages of the BI process are:

  • Get & Transform Data
  • Relate Tables
  • Scope & Develop Metrics
  • Develop Visuals & Reports
  • Publish, Explore & Share

This process is not linear – you may not go through the stages in a set order, and you may go forward and backward between them several times until your solution is built and shared.

Hints & tips

  • This course only touches briefly on other parts of the BI system
  • GoSkills has other courses that cover each of the other topic areas in depth including:
    • Microsoft Excel – PivotTables
    • Microsoft Excel – Power Pivot
    • Microsoft Excel – Dashboards
    • Power BI
Login to download
  • 00:04 So now that we know what Power Query is,
  • 00:07 how does it fit into the overall business intelligence or BI development process?
  • 00:13 Well, every BI solution goes through the same phases.
  • 00:18 We start with getting and transforming data.
  • 00:21 The next thing we do once we've got our tables all set up is we relate
  • 00:25 them together.
  • 00:26 And then we scope and develop the metrics that we wanna use.
  • 00:29 Is there a key performance indicators that show how are we actually aggregating or
  • 00:33 accounting or slicing that data?
  • 00:35 Once we have that, we can develop the visuals and
  • 00:38 reports that we want to actually show what we're trying to actually convey.
  • 00:42 And we'll publish it and share it so that people can explore the data and
  • 00:46 see what's actually in there.
  • 00:48 Now we'd like to believe of course,
  • 00:50 that this is a nice clean cycle that just goes around in a nice little wheel here.
  • 00:54 And, every time we share something, at that point then we'll probably
  • 00:58 get some more questions, we need to go and get, and transform some more data.
  • 01:02 The reality is it's far from that truth.
  • 01:04 This is an iterative process and we can jump back and
  • 01:07 forth in this process anytime.
  • 01:09 You might be scooping the nice little metric for a key performance indicator and
  • 01:12 realize hey, I'm missing some data, or you might be developing a report and
  • 01:16 realize that you need to go and transform your data differently.
  • 01:19 So this can go round and around in a cycle always jumping backwards and
  • 01:23 forwards as needed.
  • 01:26 So where does Power Query fit in?
  • 01:27 Power Query is all about getting and transforming data, and
  • 01:31 that's what the focus of this course is going to be about.
  • 01:34 Now we are gonna do a very quick review of pivot tables.
  • 01:38 Where do pivot tables fit in?
  • 01:40 They're both about developing the metrics and also building our reports.
  • 01:45 We're only going to do cursory review on this and the main reason is, often times
  • 01:49 when we drop our data down out of the transformation, we get a table and it's
  • 01:53 too big to see it quickly as to whether or not it has pulled in everything we want.
  • 01:58 So we'll create some quick pivot tables just to prove that all the data is
  • 02:01 actually there because it's as I say, too big sometimes to see.
  • 02:07 If you haven't worked with pivot tables much,
  • 02:08 we highly recommend taking the GoSkills pivot table course.
  • 02:11 That will give you all the tools to make you a total pivot table ninja, so that you
  • 02:15 can actually leverage this nice clean data and build some amazing things out of it.
  • 02:20 There's also a couple of other wheels in this area around relating tables and
  • 02:24 scoping developing metrics, both of these use a technique called Power Pivots.
  • 02:30 Power Pivot is another add in you can download for excel 2010,
  • 02:34 it's built in to Excel 2013 and higher.
  • 02:37 And is the best way to go and
  • 02:38 build super amazing pivot tables that really do some amazing stuff with data.
  • 02:43 The last thing that we do once we've actually got all of our information built
  • 02:47 up and we've built some beautiful reports and we've got some dashboards, we can go
  • 02:51 through at that point and we can share them with people, the question is how?
  • 02:56 We can use classic methods.
  • 02:58 Classic methods to include printing to PDF or printing to paper,
  • 03:02 sending a file by email.
  • 03:03 But we also have a new layer available to us now which is called PowerBI.
  • 03:09 It won't surprise you to find out that we also have a course on on PowerBI,
  • 03:12 we've got courses of every piece of this entire development cycle.
  • 03:15 The nice thing with PowerBI is that it uses Power Query to go and
  • 03:19 get transform the data, it uses the power pivot modeling engine to model the data.
  • 03:25 And it has a really cool canvas for actually building some nice,
  • 03:28 interactive visuals, as well as a sharing mechanism built right in.
  • 03:32 And you can even publish excel workbooks in the PowerBI as well.
  • 03:36 So all of these components make up this complete cycle.
  • 03:39 You'll notice we have a course on every single one.
  • 03:42 Each one of them is big enough to warrant an entire course on its own.
  • 03:45 And that why this specific course focusing strictly on the get and
  • 03:49 transformation experience of Power Query.

Lesson notes are only available for subscribers.

Gift this course