Schemas (Data Models)

Question 1

5 steps in the data model design process

Answer 1

1. Connect to Data Sources
2. Prepare and Transform Data
3. Configure Table and Column Properties
4. Create Relationships in model
5. Create Columns and Measures using DAX

Question 2

What is a schema?

Answer 2

1. A structure that defines the organization and relationships of tables within a dataset
2. A logical framework of how the data is organized and connected
3. Defines the rules that govern the relationships

Data Modeling in Power BI
Intro to Data Models Week 1

Question 3

Benefits of an Optimized Data Model

Answer 3

1. Improve performance significantly
2. Easier Aggregation of structured data in a data model due to clear relationships and hierarchies
3. Facilitates complex measures and and predictive analysis
4. Improved Accuracy and Reliability of Reports

Question 4

What are the benefits of creating an appropriately designed schema?

Answer 4

Data
1. Data Structure Definition
2. Faster Data Exploration

Analysis
3. Efficient analysis
4. Assists with visualizations
5. Meaningful Insights
6. Easier aggregation

Reporting
7. Precise reporting
8. Quicker report creation
9. Simpler report maintenance

Data Modeling in Power BI
Intro to Data Models Week 1

Question 5

What are the three types of schemas?

Answer 5

1. Flat Schema
2. Star Schema
3. Snowflake Schema

Data Modeling in Power BI
Intro to Data Models Week 1

Question 6

What is a flat schema?

Answer 6

A flat schema stores all data in a single table

Data Modeling in Power BI
Intro to Data Models Week 1

Question 7

What are three benefits of a flat schema?

Answer 7

1. Easy to retrieve data from
2. Less complex to analyze
3. Simpler way to visualize data

Data Modeling in Power BI
Intro to Data Models Week 1

Question 8

Disadvantages of flat schema

Answer 8

1. Typically has a large dataset
2. More suited to smaller datasets
3. Can be slow to query
4. Can be difficult to maintain
5. Leads to data redundancy and data inconsistency
6. Doesn’t allow for complex datasets that require more flexibility and detail

Data Modeling in Power BI
Intro to Data Models Week 1

Question 9

What is a star schema?

Answer 9

1. Allows for multiple tables to be connected through one central table
2. A central fact table connects to multiple dimension tables

Data Modeling in Power BI
Intro to Data Models Week 1

Question 10

Example of a Star Schema

Answer 10

Question 11

What are the advantages of a star schema?

Answer 11

1. Reduces data redundancy
2. Boosts query performance
3. Easy to understand
4. The tables you have to navigate through to answer questions are minimal

Data Modeling in Power BI
Intro to Data Models Week 1

Question 12

What are the disadvantages of a star schema?

Answer 12

1. Lacks flexibility
   Adding or modifying tables can require extensive changes to the schema
2. Can struggle to manage complex relationships

Data Modeling in Power BI
Intro to Data Models Week 1

Question 13

What is a snowflake schma?

Answer 13

1. An extension of the star schema
2. It breaks down the dimension tables into multiple related tables
3. Can take advantage of an existing star schema and normalize it to create a snowflake schema

Data Modeling in Power BI
Intro to Data Models Week 1

Question 14

Example of extending an existing star schema to make it a snowflake schema

Answer 14

Question 15

What are the advantages of a snowflake schema?

Answer 15

General
- It provides for more efficient storage and retrieval
- It improves data integrity and consistency
- Eliminates data redundancy
- Offers scalability and flexibility by integrating new tables as required
- Less storage requirements

Analysis
- Allows for more granularity
- Improves data governance
- Easier to identify patterns and relationships

Dimension Hierarchies
- More flexibility with dimension hierarchies (think product to sub category to category)
- Facilitates sub dimensions within hierarchies

Data Modeling in Power BI
Intro to Data Models Week 1

Question 16

What are the disadvantages of a snowflake schema?

Answer 16

1. More difficult to analyze because of the extra relationships
2. Can be more challenging to understand and manage
3. Slower queries

Data Modeling in Power BI
Intro to Data Models Week 1

Question 17

How do you validate a schema?

Answer 17

1. Make sure each column has the correct data type
2. Make sure each column has the correct formatting
3. Make sure all columns have clear descriptions
4. Make sure all table and column properties are correctly configured

Data Modeling in Power BI
Intro to Data Models Week 1

Question 18

What is an important thing to do around summarization when setting up columns?

Answer 18

• Set the default summarization for the column. This could be sum, count, avg, etc,
  OR
• You can choose DON’t SUMMARIZE.

Setting the default summarize behavior for the column ensures that when you bring the column into a visualization that automatically aggregates, it will apply the action you want.

Question 19

What settings are important to set up for a column when working on the data model/schema?

Answer 19

1. Data Type
2. Format
3. Summarization (default you want)
4. Data Category
5. Sort by Column
6. Data Groups

Data Modeling in Power BI
Intro to Data Models Week 1

Question 20

What does Sort Order on a column do?

Answer 20

Specifies the default sorting of a columns values such as:
- Ascending
- Descending
- Custom

Data Modeling in Power BI
Intro to Data Models Week 1

Question 21

What is the merge function the same as in database language?

Answer 21

A Join
Merging in PowerBI joins two data sources based on a common key.

Writing a long sentence so it will left justify

Data Modeling in Power BI
Intro to Data Models Week 1

Question 22

How are the columns from the table you merged with represented right after the merge?

Answer 22

• They are all in one column where every value says Table.
• You have to expand the column and choose which columns from the table you merged to that you want to include.

Data Modeling in Power BI
Intro to Data Models Week 1

Question 23

How do you know if an aggregation default has been applied to a column just by looking at the column list?

Answer 23

• There will be a ∑ in front of the column
• If there is no ∑ then the default is DON’t SUMMARIZE for that column

Data Modeling in Power BI
Intro to Data Models Week 1

Question 24

What do fact tables hold?

Answer 24

• Quantifiable, measurable data about a business
• All foreign keys of the dimension tables
• Facts or measures (a property on which calculations can be made) such as quantity sold, amount sold and average sales.

Question 25

What do dimension tables hold?

Answer 25

- **Description information** for all the measurements in the fact table - **Descriptive attributes** related to fact data They offer the context surrounding a business event

Question 26

What is normalization?

Answer 26

- A data model design technique that involves structuring data to **minimize redundancy to ensure data integrity** - It **divides data into multiple related tables**, each with a specific purpose - **Reduces data duplication**

Question 27

Why might you normalize data?

Answer 27

To reduce data redundancy

Question 28

What are some advantages of normalization?

Answer 28

- Remove data redundancy - Improve data integrity - Easily maintain your data model

Question 29

What is denormalization?

Answer 29

- Converting the normalized schema into a schema that has **redundant information.** - Implementing denormalization helps to **avoid expensive queries** between the tables but at the cost of creating redundant data

Question 30

What are the benefits of denormalization?

Answer 30

- Instead of loading multiple tables you load **one large table**, which may be more efficient in performance - With no relationships between tables, **queries to join** the data are **reduced** - **Filters** amongst tables may be less **efficient** than filters applied to a single table - **Establishing a hierarchy** from fields within a table is simpler

Question 31

Does normalization or denormalization optimize memory?

Answer 31

**Normalization optimizes memory use** since duplicate data is eliminated

Question 32

What is an advantage of normalization over denormalization?

Answer 32

- **Data integrity** is maintained during the normalization process - It is harder to maintain with a denormalized model.

Question 33

What factors should you take into account when choosing normalization or denormalization?

Answer 33

What strikes the right balance between: - Data integrity - Query performance - Ease of maintenance. There is no one-size-fits-all solution, and the choice depends on your project's specific business and analytical requirements.

Question 34

What is cardinality?

Answer 34

- Cardinality refers to the nature of the **relationship between two datasets.** - In other words, how tables in your database relate to each other

Question 35

What happens if cardinality is not configured correctly?

Answer 35

This can lead to: - innacurate data analysis - flawed business decisions

Question 36

What are the three types of cardinalities?

Answer 36

1. one-to-one 2. one-to-many 3. many-to-many

Question 37

Why is assessing the granularity of data important?

Answer 37

- It is crucial for establishing the **correct cardinality** - It influences how you **set up** your **cross-filter direction** in Power BI

Question 38

What can happen if you incorrectly assess granularity?

Answer 38

It can lead to: - Misrepresented data - Incorrect business insights

Question 39

What does cross-Filter direction in Power BI determine?

Answer 39

The direction of automatic filtering between tables

Question 40

How are Power BI relationships different than other database management systems?

Answer 40

- They are **directional in nature** - The **direction** significantly **impacts** how **filtering** operates

Question 41

How does a single direction cross-filter work?

Answer 41

- **A filter applied to table B will affect table A, but not vice versa.** - For example, **filtering on a dimension table filters the fact table** it has a single direction cross-filter to. However, **filtering on the fact table will not filter the dimension table**

Question 42

How does a bi-directional cross-filter work?

Answer 42

**Bi-directional cross-filters** - Sales (Fact table) to Product (Dimension table) - Sales (Fact table) to Salesperson (Dimension table) **Number of products sold by each salesperson** 1. You **apply a filter on Salesperson dimension table** 2. This **automatically filters the Sales fact table** since there is a bi-directional cross-filter between Sales fact table and Salesperson dimension table 3. Then this **automatically filters the Product dimension** table since there is a bi-directional cross-filter between Sales fact table and Product dimension table

Question 43

Disadvantages of bi-directional cross-filter direction

Answer 43

- Can negatively **impact performance** - Can result in **ambiguous filter propagation** (spreading throughout the model) paths

Question 44

What are the steps for setting up a star schema in Power BI

Answer 44

1. Disable auto detect 2. Load Fact and Dimension tables 3. Create relationships 4. Set cardinality 5. Set cross-filter direction

Question 45

How can you normalize tables?

Answer 45

Create calculated tables using DAX