Our pivot is all about creating mini databases with an excel and then letting the database do the heavy
lifting for you, you don't need to know any database languages or install any crazy systems to make
this work.
However, you will need to be able to structure your data in certain ways in traditional data work.
What I'm going to discuss in this video is a type of data normalization.
When you normalize the data set, you look for unique identifiers that describe how pieces of data relate
to each other.
In many ways, normalizing a data set is about defining the types of lookups that you might want to
be able to perform with a given data set.
Let's start by looking at our sales data, our sales data as a single data table so we're not working
within a database yet.
What are some ways that we can describe and explain this table to someone?
As I describe this, I'm going to create an entity relationship diagram or database diagram that's related
to this table.
The simplest thing we can say is that it has four fields store, date, department and sales.
That's a start, but we need to be more explicit in describing what our table looks like conceptually
to define this in our diagram, I'll create a little box for a table called sales and add these four
fields along the left side.
I can go a step further and say that the sales column is a number the department and store fields are
both text and then the date field contains date values.
Our place in identifier for each of these next to the field name.
This box defines the contents of our table fairly succinctly, but it's still very flat.
There's no context about how the fields within the table relate to each other, aside from the simple
fact that we know they do.
Conceptually, we know that the starfield has a certain set of store names, if we define that unique
set elsewhere, then we specified a unique store dimension table that's related to our sales table.
So I'm going to create a little table called stores here to the side.
This table looks dumb and pointless, but it's incredibly important.
This is our first dimensional table.
It defines all of the options that a store can be and it relates directly to the store column on our
sales table.
Visually, I can represent this with an arrow drawn from the store name field in the store's table to
the store name field and our sales table, the arrow points into the sales table intentionally because
for one entry in the store's table, like, say, Kroger, there are many copies of that name in the
sales table.
This is called a one to many relationship and will be a major component of this course, let's do the
same thing with the department field this time just faster.
We get our unique department values table and our data base diagram and we create a connector arrow.
Note that the department and store both appear to be independent of one another.
What this means is that each store should have every department and every department should be in every
store.
This type of a cross relationship can be called a cross joint or cross product, since we have three
stores and seven departments.
This means we have twenty one combinations.
The two, the product of three and seven.
We want to treat the date field the same way.
We'll pull the unique dates into its own table and call this the calendar.
When we build more robust reports will probably want a calendar table that extends outside of strictly
our existing data range.
However, the general concept is the same.
I'll add this to our diagram with its fields and then I'll give it a relationship out to date, and
now we have a spider web diagram that describes our sales table.
With this diagram laid out, we fully described our singular sales table.
We need to do this for each of our other tables to instead of walking through the creation of each one,
let's go ahead and just fade to each of them.
Here's the purchase orders table.
It's very similar.
Now, here's our labor expenses again, very similar.
What commonalities do we see across all three of our data tables, they all had relationships to a calendar
table and they all had relationships to a store table.
Now, can you imagine what our data base diagram might look like if we put all of our tables in one
place?
Well, if we put them all in one diagram, we can begin to see how each different data set relates to
the others through the different dimensional tables.
One interesting notation to make is that our labor expenses has labor departments that are different
from the other departments will address this detail in later lessons.
At this stage, we've drawn out the structure for a database based upon our three tables going through
the effort of drawing out every data structure you use is a bit tedious and not always strictly necessary.
However, you should draw out a few to get used to breaking your data down into its components and tying
them together.
In just a few lessons, we'll have power pivot building and showing this diagram to us with a little
bit less work.
Before that, we will take this data structure and build it with our relationships tool in the next
lesson.