As you can see, we're back to Excel and away from PowerPoint for these last two lessons in this video,
I'm going to show you how to take your dimensional data and create custom hierarchies and then also
how to edit your date hierarchy.
Going way back to the beginning of the course, I introduced our grocery data set and we created our
data relationships, but we skipped our department's table since they appeared to be incongruent.
Today, we will not only bring them into alignment, but will also create a series of groupings that
will function similarly to how our dates work.
We're going to update our date hierarchy after that so that it includes our quarter field in between
our year and month drill downs to start with us.
Let's go and brainstorm what our department dimensional table should look like.
My approach to this for a new data set is to align the unique values up from each of the tables and
compare them excels, remove duplicates.
Function is very useful for this.
Once I've done this for all three of our tables, we can make some observations.
Departments with specialized labor seem to have specific labor line items, while the labor costs for
the other departments appear to be shared within a general department.
With this in mind, we can begin structuring the data.
The departments from sales and purchase orders line up nicely and at a detailed level.
So I'm going to define a new table with a field call department that contains all of those.
Next, I'm going to create a field called Section.
I don't have a better name for it and I'm going to populate it with butcher and bakery for the butcher
and bakery sections.
And then I'm going to specify the rest as being the general section.
In addition to creating this whole general section, I'm going to add a new RO that uses the general
for both the department detail and our section.
What we're doing here is creating this hierarchy of values and we need to use some specialized measures
to allocate it to the lowest level.
But I'm going to back burner that issue for now.
Before I add this to the data model, let's create one more field that I'll call top.
I'm going to group the butcher and bakery into a specialized group and then the rest into the general
group.
Yet again.
This will give us a three tier hierarchy just for fun.
And now I can add this table to our data model and start working with it in our data model.
I'll quickly create the relationships between each of our supporting tables and this department's table.
The reason we needed to add General to the department's detailed Section two is because we needed to
ensure that there was a one to many relationship between this table and the other tables.
Otherwise we would have tried to create a many to many relationship since there would have been many
general values in this table to connect to.
With these relationships created, we can go and use our department's table just like any other in our
pivot table, but I don't want users throwing department in and seeing the arresting results that we
expect.
So let's move to our diagram for you in the diagram.
You.
There are some buttons that I skipped over early in the course, hidden in each of our tables.
They appear at the top, right?
When you mouse over a table, the left button is create hierarchy while the right buttons maximize window.
Clicking on the maximize window button is pretty straightforward.
It makes the window big and we can click on it again to shrink it back down.
What we're interested in now is the create hierarchy button and we to our department's table and click
on the Create Hierarchy button for it.
It's going to create a new entry in our table with a little folder hierarchy icon and it'll ask us to
input a name.
I'll call this department hierarchy.
Once I've done this, there's a little drag field's indicator showing to the right.
I can create this custom hierarchy by doing exactly as it says and dragging fields onto this.
I'll start with the topmost level and drag it onto the department hierarchy item and it'll get added
that I'll drag section down below it and you can see where the black line shows up to indicate where
it's going to drop.
Finally, I'll try the department field below the section area and now we have a three tiered hierarchy.
Once I've done this, I'm going to right click on each of the fields that are outside of the hierarchy
and choose to hide them from the client tools.
This will prevent our users from using them in the pivot table, thereby forcing them to use the department
hierarchy.
I'll close the Excel data model and we go to the pivot table and start to see our hierarchy in action.
I'm going to shrink our data down and then remove store from the pivot table to limit the number of
moving pieces.
Once I find the department's table and the listing, I can select the department hierarchy and add it.
Since it's the only thing available anyways, I'll drag it to the rows.
And now we have a split between the general and specialized high level department sections.
I'll start by drilling into specialized and we can see the butcher and bakery values.
Once I in the general, we see one more general level, but drilling into that again will give us the
detail department components.
As we see it, this detailed granularity and as we expected, our profit calculations don't seem to
make any sense, since the wages are only not zero for the general field and it's zero for all others.
So we'll aggregate this back to the general level for now.
With that, we created a brand new hierarchy for our departments.
But what about the date hierarchy?
We wanted to edit it.
We have the quarter field that we created and we've used it manually.
So let's go to the data model, go back to our diagram for you and look at the calendar table.
This is as easy as finding quarter in this table and dragging it all the way down into the data hierarchy
area and dropping it between year and month, once I've done this, I can close power of it yet again,
go to our years and expand one to see that we have a quarter split now rather than the monthly split
from before hierarchy's like these are exceptionally good tools for creating very useful aggregations
and splits like the one we used for our departments and also works well for geography's.
Imagine having state, city and address levels of detail if you're working with data spread across the
globe.
And obviously it works well for tiered structures like dates and times.
Further, as you've been able to see, the hardest part about setting up a hierarchy might actually
be naming the hierarchy fields themselves, since all the rest is drag and drop once you've created
your relationships and your proper dimensional table.
The next lesson will close out our educational portion of this course with a short lesson on how to
define key performance indicators, after which you'll be given a data set and a full assignment to
apply everything from this course to followed, of course, by a guided walk through of how you could
have done it.