All language subtitles for 5. Understanding Table Relationships vs. Merged Tables

All right one last conceptual lecture before we get our hands dirty and actually create some table relationships

and I really just wanna drive home this very important idea of relationships versus merging tables.

Now I know some of you out there are thinking like this guy.

Can I just merge my queries or use familiar functions like look up a related or index match to pull

attributes into the fact table itself so that I have everything in one place.

And the answer is yes technically you can.

You can create a table that looks like this where you've got your original data your metrics your fact

table fields on the left just like the one we've been looking at with a date a product ID and a quantity

field and you could take those key columns and you could stitch in attributes from a calendar lookup

table attributes from our Product table.

And this could go on and on and on until you have hundreds of columns based on a handful of IDS or keys.

And the thing is this is a totally normal habit to be in.

This was a habit that I had as a longtime Excel user.

Where is this instinct to want to always blend and stitch things together and force my tables into one

place.

And there's a good reason for it.

Before these tools came along like power Korean power pivot Dax in Excel there were very few user friendly

intuitive tools that would allow you to do this type of data modeling work.

So as a result you were kind of forced to mash these different sources of data together into these Franken

tables that contain all sorts of information because that was the only way you can analyze it like if

you wanted to use a traditional pivot table for instance you had to point that pivot table to a single

data source or a single table.

And that often required using hundreds of thousands of look up or index match functions.

To tie this information together with brute force.

So bottom line.

Sure you can do this mechanically it's possible but it's just really really inefficient.

Like we talked about in that normalization lecture merging data like this creates a ton of redundant

information.

As a result it utilizes way more memory a lot more processing power than simply creating relationships

between multiple small thin tables.

So next time you feel that instinct to mash everything together to merge it to stitch it manually just

say no.