1. Data modeling
Welcome back! Data modeling is one of the key features of Power Pivot, so let's explore what it is.
2. What is data modeling?
Data modeling is the process of structuring and organizing data for analysis. Analysts can define relationships between several datasets and structure the data for a masterpiece report!
3. Fact and dimension tables
Data is usually organized into fact and dimension tables in a structured database.
Fact tables typically contain numeric data representing business transactions, events, or measurements. They generally have many columns and rows; they need to store the facts of whatever they're recording, hence their name as fact tables.
4. Fact and dimension tables
Fact tables can be helped by dimension tables, or "dim tables" for short, which contain descriptive attributes of the data stored in the fact table. Because the goal of the dim table is to describe the data in the fact table, they are smaller with fewer columns and rows. They add another "dimension" to the fact table, which is why they're called dimension tables.
5. Fact and dimension tables
Together, fact and dimension tables are powerful. For example, a typical relationship could be that the fact table holds sales transaction data, and a dimension table could hold product information.
6. Fact and dimension tables
These tables can be linked with a foreign key, which is a column in each table that matches, much like how you'd perform a VLOOKUP in Excel.
With this data linked, you could have much more product information on your sales transaction data.
7. Star schema
Schemas describe how data in a relational database or model have been set up. A star schema is a typical example of how a relational data model can be set up. It's a fact table with many dimension tables, and its shape looks like a star, giving it its name. Its simplicity makes it popular.
8. Cardinality
When creating links between data tables, matches can be made differently, depending on how the data is structured. Cardinality refers to the number of times one row of data from the first table matches with a row of data from the second table. There are three main types of cardinality.
9. Cardinality
In a one-to-one relationship, each record in Table A corresponds to exactly one in Table B, and vice versa.
10. Cardinality
In a one-to-many relationship, each record in Table A can have multiple related records in Table B. Still, each record in Table B can have only one related record in Table A. This is common in fact and dimension table relationships.
11. Cardinality
In a many-to-many relationship, each record in Table A can have multiple related records in Table B, and each record in Table B can have multiple related records in Table A.
12. Hierarchies
A hierarchy is a way of organizing data into levels of increasing granularity.
Imagine hierarchies as a set of Russian nesting dolls, where each doll fits inside a larger one. Just like the dolls are organized in layers, hierarchies represent data organized in nested levels of detail.
A company org chart is an example of a hierarchy. Each level provides more detailed information about the organization's structure, with higher levels encompassing broader categories, like company and division, and lower levels providing more granular details, like individual teams and employees.
13. Let's practice!
Alright, let's take some time to practice.