Facts vs Dimensions

Quick reference

• 00:04 Before we jump into getting data for our Power Pivot Model,
• 00:08 we need to have a quick discussion over what Facts and Dimensions are.
• 00:12 And the reason being is, this is going to dictate some of the way that I actually
• 00:17 shape the tables that we bring in.
• 00:19 A challenge that we have is that, the Fact versus Dimension discussion is something
• 00:24 that a lot of pivot table users never have, they got no idea what these are.
• 00:27 So this is why we gotta go through them, we just have to talk about it.
• 00:30 So what is a fact anyway?
• 00:32 Well, essentially, it's something that's been aggregated.
• 00:36 Examples of this sum of amount, it's the amount column that's been summed up
• 00:40 into a single value, or customer could have been counted.
• 00:46 That's something that's very common obviously when you drag a text field into
• 00:48 the bottom right-hand corner of a pivot table.
• 00:51 These things are actually really easy to identify.
• 00:54 They live in the bottom right hand corner of the pivot table,
• 00:56 if it's there it's a fact.
• 00:59 It's pretty much that simple.
• 01:01 So what's a Dimension?
• 01:03 Well, the Dimension is how we slice our facts.
• 01:06 Examples of these, they might include things like account group, customer class,
• 01:11 year, month and day, or location.
• 01:13 None of these things are ever actually aggregated.
• 01:16 They're also very easy to identify in the pivot table structure.
• 01:20 They're things like row, column, or filter fields.
• 01:24 They're things like slicers or timelines.
• 01:26 Each of these is a Dimension and the fundamental rule that we want to try and
• 01:30 follow here is, if it's not aggregated then it's gonna be a dimension.
• 01:35 So basically, if you're not gonna count it or sum it in some way,
• 01:38 then it's a Dimension.
• 01:40 Why do we care?
• 01:42 Because facts and Dimensions, as a matter of best practice,
• 01:45 should always live in completely separate tables.
• 01:48 If you do this, trust me, it's gonna save you a lot of headaches and a lot of hair.
• 01:55 So how do we design good fact tables?
• 01:57 Well, a fact tables should only really have two types of columns.
• 02:02 Columns which need to be aggregated, which can be either numeric or text space, so
• 02:06 we could still need to count something that would make it a fact.
• 02:10 The other type of column that would exist inside a fact table is a column
• 02:15 that we use to link to Dimension Tables.
• 02:18 Okay, so this is our lookup column that we actually use.
• 02:20 It's actually called a foreign key if you really want the technical term for it.
• 02:26 Key thing that I want you to remember here is that,
• 02:28 no column from a fact table should ever get used in a Dimension field.
• 02:32 And basically what that means is, if you have a table, unlike a regular pivot table
• 02:38 inside Power Pivot, if you've got a table and you're gonna drag value into that
• 02:41 bottom right-hand corner of the pivot table, any of the other
• 02:44 columns in that table should never be dragged anywhere else on the pivot table.
• 02:48 You should only be putting fields into the bottom right-hand corner of the pivot
• 02:51 table from that table.
• 02:53 And that's kind of a hard thing to wrap your head around.
• 02:55 You're gonna see how this actually plays out as we go through and
• 02:57 we work through the rest of this course.
• 03:00 So what do you do about that?
• 03:01 Well, if you need a Dimension field that's why we use Power Query to spin off all
• 03:04 Dimensions so that we can actually do the filtering that we need.
• 03:07 So that's how we are gonna actually leverage Power Query with Power Pivot to
• 03:10 make this work.
• 03:11 And we got a whole section to talk about this kind of stuff and
• 03:13 how often that happens.
• 03:15 What about designing Dimension Tables?
• 03:17 Well, Dimension Tables also should only really have two types of columns.
• 03:21 They have a Primary Key column.
• 03:24 This is a column that has a unique entry for every row in the table.
• 03:28 This is the column that we use to link back to the fact table.
• 03:32 So the fact table has two things, it's got the columns that are being aggregated and
• 03:36 it has the column to link to other tables,
• 03:38 it links to the primary key column n the Dimension Table.
• 03:41 We also have the columns that we use in dimensional fields.
• 03:45 These can have many repeating values.
• 03:47 They're generally text-based, but don't have to be,
• 03:50 but these are the guys we drag into the row or column headers of report, or
• 03:54 onto slicers in order to actually split our facts by different ways.
• 03:58 Key thing that I want you to remember here,
• 04:00 no column from a Dimension Table should ever be dragged into the bottom of that
• 04:04 right-hand corner of that pivot table.
• 04:06 If you need to do something like count or
• 04:09 sum something from a Dimension Table, We have a tool for that.
• 04:12 It's called Power Query, and
• 04:13 you can actually merge this data back to create a proper Fact Table.
• 04:17 If you do that, you're gonna find that your life is gonna be a lot easier, and
• 04:20 you won't get into cross-filtering problems, and things that just don't work.
• 04:25 And we're gonna apply these kind of techniques as we go through and
• 04:27 we start building up the data for our model.

Lesson notes are only available for subscribers.