Original subtitles

All right so to kick off this entire section about creating data models I figured it would only be appropriate

to start with a simple question.

What exactly is a data model.

Now consider relationships view that looks like this got three different tables here a product lookup

table and two data tables sales and returns.

This alone is not a data model.

What we're looking at here is a collection of independent tables which share no connections or relationships.

So think of each of these tables as its own little island with no idea that the other two even exist

in the model.

And what that means is that if you were to try to do something like this breaking down of fields from

the sales data like order quantity or IFIELD from the returns table like return quantity and break those

quantities down by field from the product lookup table you're going to get the same grand total in every

single row of the table.

And again that's because both the sales and the returns data tables have no idea that there even is

a product lookup table that exists in the universe let alone how to break down those quantity values

by individual product names.

So if this is not a data model then what is well let's consider that same structure that same relationships

view.

Except now we have relationships connecting the tables together.

This is a data model and again it's a data model because the tables are now connected via relationships

based on a common field or key which in this case is the product key column and without diving into

the specifics just yet what this means is that if we revisit that same analysis looking at order quantity

and return quantity by product names now we see the correct values because now the sales data table

knows that the product lookup table exists.

In fact it's connected to that product table based on the fact that they share a common product key

and the same story holds for the returns data and its relationship to the product lookup.

So as a result we're able to take any field from the product lookup table and filter and segment values

from those two data tables.

Now you'll see that same familiar Grand Total row at the bottom but now we get accurate numbers for

each individual product.

So this is obviously a relatively simple example but it's this core concept of joining and blending

our data together using table relationships and common keys that will be the focal point of this entire

section of the course.

And it's this core concept that's going to enable us to do some really interesting really sophisticated

analyses.

So let's shift gears open up our venture works file.

I want to show you what this looks like in the power be-I environment.

All right.

So once you opened up the Adventure Works report file you're going to want to make sure that you're

using the latest version after the homework assignment from the last section.

So you should see a product category and a product subcategory table.

We're going to go into the report for you.

Now for the first time there is going to be an entire deep dive section covering all of these visualization

and reporting tools.

But for now we're going to use one type of visual in particular called The Matrix which looks like this

and The Matrix acts kind of like a pivot table.

You can analyze values from a numerical field and break those values down by dimensions placed in rows

or columns.

So in this case our values are going to come from or A.W. sales table pacifically the order quantity

field.

So let's click and drag that order quantity to values and you'll see the total quantity from that column

populate here in the matrix.

Eighty four thousand one hundred seventy four.

Now we want to break down this quantity or analyze it in a more detailed way.

Our only option is to use fields that exist within this same sales table.

So product keys for instance can be placed on the rows and now we see those Order quantities broken

down at the product key level.

So that's all well and good but we're extremely limited by the scope of that table and to show you what

I mean.

Let's go ahead and remove that product key Let's go into the product table itself which contains all

sorts of interesting information about those products the price the name the description the color and

let's say we want to break down those quantities by product name when we drag that field into rows.

We get that same grand total over and over and over and over and over again which is the exact same

issue that we saw earlier on the slide.

The sales table has no idea that a product lookup table even exists as a result.

It has no way to break down those quantity values based on any field in the product table.

So this limitation is exactly why we need to build out a data model and exactly why we're going to spend

so much time learning how to do that.

In this section of the course.

So stay tuned.

More to come.