In our last lesson, we visualize the data structure that links our information together by building
out a database diagram in this lesson, we will learn how to add data to our data model and then impose
our diagram and its structure onto that data.
Typically, the first step to working with data inside the data model is to load it to the data model.
To do this, we need to visit the power pivot tab on our ribbon.
If you do not have the power pivot tab on your reben, go to the data tab and click on Manage the model.
Once you'll ask you if you want to install the and simply say yes and you'll be ready to move on to
added data table to the data model.
You need to have a cell selected in the table of your choice.
I'm starting with the labor table and I'm going to click on the add to data model button.
And this table will be added to the data model and the data model management window will open for now,
simply close out of the management window.
Each time it opens, we're going to stick to excel this chapter and introduce the data model interface
in the next chapter.
Do this for each of the tables in the workbook that you want to include for this workbook that involves
the sales purchase orders, stores and calendar tables, once uploaded each of them to the data model.
I can begin using them.
So let's see what our data model can build right now by creating a pivotal, just like I covered in
the review lesson.
This can be done by going to insert choosing pivot table and then specifying a location in this case.
And in all cases in this course, we will use the workbooks data model as the source.
If you think back to the introductory chapter, we were able to create a pivot table with total split
by store and math.
For now, let's try calculating the total sales by store to see what we get.
Solvejg store for my stores table to the row section and the sales field from the sales table to my
value section.
And what do we get?
Clearly this pivot table isn't working since it's given us the same value for each store and for the
grand total.
So what's going on in our review lesson also reminded you how to drill down into a pivot table with
a double click.
So let's do that right now.
Double clicking on the Albertsons entry for sales gives us the data that looks like this.
Our problem is immediately apparent while the entry on the pivot table says Albertsons.
And the detail of the top tells us that this data is associated with Albertsons.
The data very clearly includes Kroger, which means that Tom-Tom is probably here, too.
When we did this in the example video.
This just seemed to work.
So what's so different?
Let's go back to our pivot table and look at our pivot table fields again.
There's a little note that says relationships between tables may be needed because we haven't defined
a relationship between the stores table and the sales table.
We have a cross joint relationship between the two.
This means that whenever we choose Kroger, Tom-Tom or Albertsons from that store's table, we will
get the entirety of the data from the sales table.
We need to tell Power Pivot that the store value in the stores table and the store value in the sales
table should match, and that's where our relationships will come into play.
Defining relationships tells Parapet how to relate a field in one table to a field and another table.
The pivot table fields pop out, even has quick links for auto detecting or creating relationships.
So I could click on the create button here, but instead I want to open my relationships manager.
This manager can be found on the data tab under the relationships button.
When I click on this button, a new window will appear with its own commands.
These commands are new detect, edit, activate, deactivate and delete.
Let's start with the detect very briefly.
This button is pretty amazing as it will automatically detect all of the relationships in a workbook.
If the data is structured and named correctly, our next lesson will cover just how to do that.
Next step is the new button.
Clicking on the new button will bring up an interface for defining a relationship from scratch with
some strange terms, notably the phone and primary terms in the column descriptions.
These refer to whether the column key that is specified is a foreign key or a primary key.
Don't worry about that terminology so much.
Instead, understand that the top table should have the bigger data set, while the bottom table should
have the unique set of values.
For us, that means that the top table will be the sales table, since it has many stores and the bottom
table will have the store tables and sets the listing of unique stores.
As I press, OK, watch our pivot table update as soon as this relationship is created, our pivot table
shows the different values for each Rollerball, while our grand total doesn't change.
There's a lot going on here to unpack.
So let's leave the relationships window for a moment.
The first thing I'll do is drill into Albertson's data again with the double click, this time the data
table only has Albertsons as the store name.
There's no Kroger or Tom Thumb.
The relationship is appropriately connected the store table to our sales table so it now understands
how to filter and segregate the data pieces appropriately.
As importantly, all of the computation and filtering of this data happens live in the background as
you work.
So there's not a little calculating time or in Excel that you will wait on.
Those big changes can take a fair amount of time to process.
Finally, and quite curiously, the grand total did not change, the grand total is disconnected from
the individual cells in a sense, and it won't necessarily reflect the total of the pivot table so much
as the total for the underlying data set.
I'll look into this technicality with you later on in the course.
For now, let's go back to our relationships manager and learn about the rest of our commands.
The edit command is pretty straightforward.
Selecting one of the relationships in the list and pressing edit will bring it up for editing.
This can be useful if relationships have been set up incorrectly at some stage and need to be revised.
The next two commands are activate and deactivate, if you look at the relationship window, there is
a status field that says active.
There are certain rules about what types of relationships can exist at a given time with a circular
series of relationships being against those rules.
However, it can be beneficial to have them all defined and then deactivate or activate specific links
in the chain at certain times for certain reports.
Clicking on deactivate will simply turn the relationship off temporarily.
I can do this and you can watch the pivot table update to reflect the Agnon relationship.
If I turn it on again just as easily by clicking the activate button, you can see it correct itself.
The delete button is similar to deactivate, except it's more permanent.
It completely removes the relationship from the model, as you can see here.
Since we have a database diagram and we have most of our tables defined, I can go through and easily
create the same relationships from the demonstration video, a few videos back.
Once I do this and press OK, I can add the month value from the calendar table to the pivot table for
a nice quick report.
As you can see, the hardest part about understanding relationships was covered in the previous chapter
when we define the data structure in the first place.
Would it surprise you to know that this particular lesson covers the hard way of creating relationships?
In our next lesson, I'll start from scratch with our data diagram and we'll learn about an easier way
to have Excel creates relationships for you with just a little bit of tweaking from our end.