In the first chapter, we use the relationship tools with an Excel plus some Excel formula magic to
build our first Power Pivot report.
In this chapter, we'll get into the meat of power, pivot and rely mostly, at least on power pivots,
internal tools to rebuild and improve that report.
To that end, I've reverted my data file to one without relationships or data model settings for us
to start from.
My first step will be to add data to the data model, the easiest way to do this is with the add to
data model button on the power pivot ribbon.
Selecting any cell within each table and pressing that button will add the table directly to the data
model for use.
Each time I do this, the power pivot window will open and I can simply close it.
Since I don't need it yet.
I'll do this for all three of my data tables plus my stores dimensional table quite quickly.
Note that the stores table is manually defined using the remove duplicates command, the best approach
for this would have been to use power query and have it load to the data model.
But that's for a more advanced course.
My next step is to create the date table in the first version.
I did this manually inside Excel with formulas in this.
I'm going to flip through my tables really quickly to see what date range I need in order to handle
my data.
Though this isn't strictly necessary, the labor table tells me that I need one one two thousand nineteen
through eight thirty one two thousand nineteen.
Now I only need a few mouse clicks to create this table on the desired tab.
I can go to the date table dropdown and click on New Immediately.
I have a table with most of the date information I need clicking on that date dropdown again, I can
check the range to see that it has chosen one one twenty nineteen through twelve thirty one twenty nineteen.
So at this point I'm ready to create my relationships.
I'll use the create relationship button while on my calendar table to create these relationships.
What I click on this, a new window shows up with a preview of my calendar table, the top and a dropdown
about halfway down the screen.
This dropdown will let me pick the other table that I want the relationship created for so I can click
on Labor to create the relationship to it.
When creating this relationship, I want to pick the column along the top and the column along the bottom
tables that match.
In this case, that's the date field.
Since they're named appropriately, the selection is made automatically.
Once I'm ready to create the relationship, I can press OK, I need to create the state relationship
two more times for sales and purchase orders.
And as you can see, doing so takes only a few seconds.
I'll also need to do the same thing for my stores table and the process will be identical.
I'll select the stores table along the bottom of my data model window and then I'll go to the create
relationship and create a relationship between the stores table and each of my three data tables, just
as I did with the dates.
Once I've finished with this again, after only a few seconds, I go to my diagram view via the home
tab of my ribbon to see what my data model looks like.
Much, much easier than creating a complicated service function.
Right from here, I can go to the home tab and choose the pivot table dropdown and create a pivot table
immediately on a new worksheet.
I drag some fields here, I apply some formatting there and suddenly we're looking at an almost completed
report.
I'm still missing the profit computation, but we'll save that for one of our next lessons.
I've recreated the basic structure here, so let's pop into our power pivot window and briefly review
the setup.
The diagram shows how the tables connect together at a very high level, much like the entity relationship
diagram we created on the design tab.
I can access the managed relationships button to pull up the list of relationships like an Excel.
But in this window I have much more detail about the type of relationship, the table names and the
field names.
All the clients editing any single one will bring up the same relationship creation window that we used
before with the table previews and the selection of the appropriate column in each section for specifying
the relationship.
Let's go look at our calendar table again, I could modify the range of this calendar table by going
to the design tab, going to the date table, dropdown and choosing the update range option here.
There's a little calendar pop up next to the field that you could use to select the specific date I
could navigate to and select.
January twenty twenty two.
Just because to give us a little bit of future proof, other future updates could be made just as easily,
making the state table far, far easier to manage and use than the Excel based one we created earlier.
As an additional bonus, it has another beneficial side effect.
So let's pop back out to our table and learn some more about the special date hierarchy field.
Let's remove the month field from the Rose section and take the date hierarchy field from the calendar
and move it there instead, this date hierarchy is basically a fully formed set of date dependencies.
Imagine if you had dragged year, month and day all into your Rollerball section.
It'd be huge, but you'd have all of your information at hand.
Date hierarchy does that with a foldable container.
So by adding it, I can expand out here and then I can expand out back to see individual day results.
With that, we've recreated one portion of our original report, we've abused one of its cells, pivot
table mechanics, to get around using any DACS to create measures by creating what are called implicit
measures using the values field of our pivot table.
In the next lesson, I will introduce you to two different concepts the concept of a measure and the
concept of a calculated column from which we'll begin to learn some basic tax.