COURSE 4 – DATA MODELING IN POWER BI

Module 1: Concepts for Data Modeling

MICROSOFT POWER BI DATA ANALYST PROFESSIONAL CERTIFICATE

Complete Coursera Study Guide

Enroll in Coursera Microsoft Power BI Data Analyst

Last updated:

Landscape Banner Thumbnail Promo 400H

TABLE OF CONTENT

INTRODUCTION – Concepts for Data Modeling

This module delves into the intricate world of data modeling, exploring the various schemas that are employed to construct them. Through a comprehensive examination, learners will gain insights into the fundamental principles and methodologies that underpin data modeling. They will understand how different schemas are utilized to effectively structure, organize, and manage data, facilitating efficient data handling and retrieval. By the end of the module, students will be equipped with the knowledge and skills necessary to create robust data models tailored to meet specific needs and requirements, ensuring optimal data integrity and performance.

Learning Objectives

  • Identify different data schema types.
  • Create and maintain relationships in a data model.
  • Form a model using a Star schema.

SELF-REVIEW: CONFIGURING A FLAT SCHEMA

1. How many tables were displayed in the Model View once the dataset was loaded?

  • A data structure that included multiple related tables.
  • A single table with one-to-many relationships.
  • A single table with multiple columns that included different data. (CORRECT)

Correct! You created a single table with columns that included different data in one table.

2. How many rows were present in the dataset after all duplicate rows were removed from the OrderID column in the Adventure Works dataset?

  • 37
  • 48 (CORRECT)
  • 96

Correct! 48 rows remained after all duplicates were removed.

3. What was the data type of the Product Price column after you loaded the data to Power BI before applying a transformation to the dataset?

  • Date
  • Whole Number (CORRECT)
  • Decimal Number
  • Text 

Correct! The column was formatted as a whole number.

4. Adventure Works have tasked you with creating a data model to analyze sales data and improve the store’s performance. What should your first step in the data modeling process be?    

  • Prepare and transform your data.
  • Configure column and table properties.
  • Connect to your data sources. (CORRECT)

Correct! You must begin building a data model by connecting to the relevant data sources in Power BI.

5. Which of the following are benefits of a schema? Select all that apply.

  • A schema helps to enable efficient data analysis. (CORRECT)
  • A schema helps with generating meaningful insights into your data. (CORRECT)
  • A schema helps with the creation of visualizations. (CORRECT)
  • A schema helps to define the structure of your data. (CORRECT)

Correct. You can use a schema to analyze data more efficiently by working with its entities and relationships.

Correct! You can use a schema to generate insights into your data by managing entity relationships.

Correct! You can use a schema to visualize your data within the Power BI platform.

Correct! A schema plays a crucial role in defining the data structure by providing a visual overview of your data.

6. Which of the following steps are essential to ensure a well-structured and accurate flat table schema? Select all that apply.

  • Validating the schema. (CORRECT)
  • Connecting to data sources. (CORRECT)
  • Merging all tables into a single table.
  • Configuring column properties. (CORRECT)

Well done! Validating the schema is essential to ensure that the relationships between tables are accurate and that the tables and columns are correctly configured. This step minimizes data inconsistencies and improves the overall quality of data analysis.

Correct! Connecting to data sources is the first step in setting up a schema in Power BI. It involves linking the data sources containing relevant information, such as spreadsheets or databases, to Power BI for further analysis.

That’s right! Configuring column properties is a crucial step to ensure accurate data analysis. It involves setting the correct data type, format, sorting order, and description for each table column.

KNOWLEDGE CHECK: INTRODUCTION TO DATA MODELS

1. In Power BI, relationships are established between the tables based on _____________ that match between the tables.

  • Rows
  • Column fields (CORRECT)
  • Table properties

Correct! In Power BI, relationships are developed between tables based on the common column fields or columns. These fields are key to connecting and relating the data across different tables within the data model.

2. What is the primary characteristic differentiating a Snowflake schema from a Star schema?

  • A hierarchical structure.
  • A central Fact table.
  • Denormalized dimension tables.
  • Normalized dimension tables. (CORRECT)

Correct! In a Snowflake schema, dimension tables are normalized, which reduces redundancy but may increase query complexity.

3. What are the limitations of using a Flat schema in Power BI? Select all that apply.

  • A Flat schema cannot be used to perform aggregations.
  • A Flat schema offers a lack of flexibility for organizing data from multiple sources. (CORRECT)
  • A Flat schema offers limited capacity for storing large volumes of data. (CORRECT)

Correct! A Flat schema is unsuitable for handling data from multiple sources or incorporating complex relationships.

Correct! Storage capacity in Power BI is not related to schema structure.

4. True or False: In Power BI, a schema is automatically created when you import data from various sources and establish relationships between tables.

  • True (CORRECT)
  • False

Correct! In Power BI, when you import data from different sources and define relationships between tables, a schema is automatically created. The schema can be further adjusted and optimized within Power BI’s data modeling interface.

5. Which property cannot be adjusted for a table or column in Power BI?

  • Sort order
  • Table relationship  (CORRECT)
  • Data type

Correct! A table relationship is not a property of a table or column. Instead, table relationships are established in the Model view of Power BI desktop.

KNOWLEDGE CHECK: INTRODUCTION TO CARDINALITY AND CROSS-FILTER DIRECTION

1. In the context of Power BI, which of the following descriptions best outlines the main purpose of a Fact table?

  • A Fact table is primarily used for storing descriptive attributes of business dimensions.
  • A Fact table is primarily used for storing detailed, transactional business data.
  • A Fact table is primarily used for storing measured, quantitative data about a business process. (CORRECT)

Correct! Fact tables primarily store measured quantitative data related to a business process. They also typically contain keys that link to associated dimension tables.

2. Which of the following statements are true regarding cardinality and cross-filter direction in Power BI?

Select all that apply:

  • Cardinality defines the number of unique values in one column compared to another.
  • Cardinality and cross-filter direction are two key elements of model relationships in Power BI. (CORRECT)
  • Setting a cross-filter direction to Both allows filters to be applied from either direction in a relationship. (CORRECT)

Correct! Cardinality defines the relationship between tables (Such as one-to-one, or one-to-many), and cross-filter direction determines how filters are applied across the relationship.

Correct! A cross-filter direction of Both means that a filter applied on either table will affect the other in a relationship.

3. True or False: In Power BI, you can create a many-to-many relationship between tables.

  • True (CORRECT)
  • False

Correct! Power BI does allow the creation of many-to-many relationships between tables.

4. In data analysis, __________ refers to the level of detail or summarization of your data.

  • Data cardinality
  • Data granularity (CORRECT)
  • Cross-filter direction

Correct! Data granularity refers to the level of detail at which data is collected, recorded, and represented.

5. What is the role of dimension tables in Power BI?

  • They store the descriptive attributes of a business process. (CORRECT)
  • They store transactional data related to a business process.
  • They store measured, quantitative data about a business process.

Correct! The main purpose of dimension tables is to store descriptive attributes for the data in the fact table.

6. Adventure Works is building a star schema in Power BI. Which of the following tables in the schema can be used to store measurable business data like order and product IDs, quantities, and total cost?

  • A Customer table that holds data on customers.
  • A Product table that contains information on products.
  • A Sales table that contains data on sales transactions. (CORRECT)

Correct. The Sales table is the fact table in this scenario. It contains measurable transaction data. It is the most likely place to store measurable business data.

7. You are working on two tables where each record in a column of Table A corresponds to multiple records in a column of Table B, but not vice versa. What kind of relationship, or cardinality, is this an example of?

  • A many-to-many relationship.
  • A one-to-many relationship. (CORRECT)
  • A one-to-one relationship.

That’s correct. This is an example of a one-to-many relationship.

8. Cross-filter direction refers to the direction in which filtering occurs between two tables in a data model.

  • True (CORRECT)
  • False

That’s correct. Cross-filter direction refers to the direction through which filtering occurs between tables. This direction can be either single or bi-directional.

9. Which of the following scenarios represents a high level of data granularity for Adventure Works?

  • Monitoring sales revenue by product category monthly.
  • Analyzing hourly sales data for individual products. (CORRECT)
  • Tracking overall sales revenue on an annual basis.

Correct! Analyzing data hourly and for individual products represents a high level of data granularity. This provides a more detailed view of the data, allowing for a deeper level of analysis.

SELF-REVIEW: CONFIGURING A STAR SCHEMA

1. True or False: The Sales table was identified as a dimension table in the exercise.

  • True
  • False (CORRECT)

Correct! The Sales table was identified as the fact table, while the Products, Salesperson, and Region tables were identified as dimension tables.

2. Which relationship type was configured between the Fact table and dimension tables in the exercise? 

  • Many-to-many
  • One-to-one
  • Many-to-one (CORRECT)

Correct! Many-to-one relationships were configured between the Fact table (Sales) and each dimension table (Products, Salesperson and Region).

3. True or False: The default cross-filter direction is set to Single, meaning that filters applied to the Products table will also apply to the Sales table, but not vice versa.

  • True (CORRECT)
  • False

Correct! In Power BI, the cross-filter direction is set to Single, meaning filters are applied from the Products table to the Sales table, but not the other way around.

4. True or False: The autodetect function must be disabled before loading multiple tables. This is to prevent Power BI from automatically creating relationships between tables.

  • True (CORRECT)
  • False

That’s correct. Power BI autodetects the relationships between the tables once you load multiple tables. So, you first need to disable the autodetect function before loading your tables.

KNOWLEDGE CHECK: WORKING WITH ADVANCED DATA MODELS

1. Which of the following statements is correct regarding a Star schema Fact table?

  • A Fact table stores an accumulation of business events. (CORRECT)
  • A Fact table must have a unique column
  • A Fact table stores an accumulation of business entities.

Correct! A Fact table stores an accumulation of business events like sales orders.

2. How are dimension tables structured in a Snowflake schema?

  • They are fully denormalized, with all attributes in a single table.
  • They are connected in a hierarchical structure with multiple levels.
  • They are normalized with a separate table for each attribute. (CORRECT)

Correct! Normalization of a dimension table in multiple lookup tables is the characteristic feature of a Snowflake schema.

3. What is the primary benefit of normalizing dimension tables in Power BI?

  • It simplifies data querying and reporting.
  • It reduces storage requirements. (CORRECT)
  • It improves data quality and accuracy.

Correct! Normalization eliminates redundant data, which reduces storage requirements. 

4. Which of the following statements is true about relationships in Power BI?

  • Relationships can only be created between columns that contain the same data type.
  • Relationships can only be created between tables with the same number of rows.
  • Relationships can be created between tables that contain different types of data. (CORRECT)

Correct! A working relationship can be created between tables if there is one common column between them.

5. True or False: A Star schema is more suitable for complex hierarchies and relationships.

  • True
  • False  (CORRECT)

Correct! A Star schema is more suitable for simplified structures. It’s not designed to handle complex hierarchies and relationships.

6. What is the primary advantage of using a Snowflake schema in Power BI over a Star schema?

  • The Snowflake schema reduces query complexity.
  • The Snowflake schema is more suitable for complex data structures. (CORRECT)
  • The Snowflake schema requires less storage space.

Correct. The Snowflake schema is designed for complex data structures by normalizing data into multiple related tables, improving data storage and retrieval efficiency, maintaining data integrity, and reducing redundancy.

7. True or False: A star schema uses a normalized approach.

  • True
  • False (CORRECT)

That’s correct. A Star schema uses a denormalized approach which is more suited to analyzing smaller datasets.

8. True or False: All issues within a data model must be identified before the challenges can be resolved.

  • True (CORRECT)
  • False

That’s correct. By identifying all issues within a data model, you can create a new one that resolves these issues to meet the organization’s requirements.

MODULE QUIZ: CONCEPTS FOR DATA MODELING

1. A health insurance company wants to build a star schema to analyze its data. What is the primary function of the Fact table in the company’s Power BI data model?

  • Storing patient information.
  • Storing medical claims. (CORRECT)
  • Storing diagnosis information.

That’s correct! This is the Fact table. It will record information like claim ID, patient ID, healthcare provider, and billed amount.

2. Which of the following is an example of high-granularity data? 

  • Sales data aggregated by month.
  • Sales data aggregated by region.
  • Sales data aggregated by product category. (CORRECT)

That’s correct! Products are broken down into subcategories and categories. So this aggregation is an example of high-granularity data.

3. You are working on a data model for a supply chain management system. You have a Suppliers table and a Products table in your dataset. Each supplier in the Suppliers table can provide multiple products, but each product in the Products table comes from a single supplier. What type of relationship must you establish between these tables? 

  • Many-to-many 
  • One-to-many  (CORRECT)
  • One-to-one 

That’s correct! Each product comes from a single supplier, so one-to-many is the best option. 

4. In an e-commerce data model, what would be the most suitable primary key for the Fact table?

  • A Sales transaction ID column that lists the unique ID of each transaction. (CORRECT)
  • A Product ID column that lists the unique ID of each product.
  • A Customer ID column that lists the unique ID of each customer.

That’s correct! Each sales transaction has a unique ID associated with it. Therefore, it is used as the primary key to establish a relationship.

5. What are the benefits of establishing relationships between tables? Select all that apply:

  • It allows you to combine data from multiple tables for analysis. (CORRECT)
  • It reduces storage requirements for the data model.
  • It facilitates drill-down analysis from high-level to detailed data. (CORRECT)

That’s correct! Data from multiple tables can be combined and analyzed via working relationships as one coherent dataset.

That’s correct! Once the filter is applied to one table, the filter propagates via relationships to enable drill-down analysis. 

6. You are working as a data modeler for a bank. You have a table called Branches and a table called Accounts. What happens if you select Both cross-filter direction between the Accounts Fact table and the Branches dimension table? 

  • Selecting a value from either table will filter the related table. (CORRECT)
  • Selecting a specific branch will filter the account table for that branch.
  • Selecting an account will filter the branch for that account.

That’s correct! A bidirectional filter enables account data analysis based on a specific branch, and branch data based on accounts.

7. A Power BI model contains two tables called Products and Orders. The Orders table contains information about each customer’s orders. The Products table contains details about the products sold. You need to filter the Products table based on the selected values in the Orders table. Which cross-filter direction should you apply?

  • Both  (CORRECT)
  • Single 
  • None 

That’s correct! By selecting Both cross-filter direction, you can allow filtering to flow in both directions. This enables the Product table to be filtered based on the selected values in the Order table and vice versa.

8. Which of the following is not a component of a Star schema?

  • A dimension table.
  • A denormalized table. (CORRECT)
  • A Fact table. 

That’s correct! A denormalized table is a component of a Snowflake schema.

9. Which of the following tables are examples of dimension tables in a data model? Select all that apply:

  • Sales
  • Employees (CORRECT)
  • Product (CORRECT)
  • Customer (CORRECT)

That’s correct! Information about employees also provides context for the sales data table. 

That’s correct! The Product table stores information about the product, category, subcategory, and product ID of each transaction. All these elements are descriptive attributes. 

That’s correct! The customer information, name, email, and address provide context for the data in the Fact table.

10. Which of the following tools can be used to configure table and column properties in Power BI?

Select all that apply:

  • The Power BI Properties pane in the model view. (CORRECT)
  • The Power Query editor. (CORRECT)
  • The Power BI Visualization pane

That’s correct! The Properties pane in the Model view can also be used to configure table and column properties further. 

That’s correct! You can configure table and column properties in the Power Query editor and other transformations. 

11. Which of the following statements accurately describes a Fact table in Power BI?

  • A table that provides descriptive attributes. 
  • A table used for storing Measures. 
  • A table that contains numerical data. (CORRECT)

That’s correct! A Fact table stores measurable information about the business process. 

12. What does data granularity mean? 

  • The nature of the relationship between two tables. 
  • A filter direction associated with the relationship between two tables. 
  • The level of detail that is represented in the dataset. (CORRECT)

That’s correct! Data granularity refers to the level of detail at which data is captured, stored, and represented. 

13. Adventure Work’s data warehouse includes a Products table that stores data on products, a ProductCategories table that stores product categories. Each product can belong to multiple categories, and each category can have multiple products. Which cardinality type should you set to represent the relationships between these two tables in the model? 

  • One-to-many 
  • Many-to-many  (CORRECT)
  • One-to-one 

That’s correct! Category columns in the Product table and ProductCategories table have multiple values, so many-to-many is the best choice. 

14. You are designing a data model for an e-commerce company. You want to capture customer information like name, email, address, and phone number. Which type of table should you use to store this information?

  • A dimension table. (CORRECT)
  • A Fact table.
  • You can store data in any table within the data model. 

That’s correct! The dimension table stores descriptive attributes such as customer information in a data model.

15. You are analyzing Sales data by customers in Power BI. Your model has a Fact table for Sales data and a dimension table for Customer data. You want to ensure that filtering the sales data only affects the customer information. Which cross-filter direction should you choose?

  • Both 
  • Single  (CORRECT)
  • None 

That’s correct! Filtering the Sales data based on customers requires a single cross-filter for seamless analysis. 

16. Which of the following statements is true regarding Snowflake schemas in Power BI? Select all that apply:

  • A Snowflake schema improves query performance. (CORRECT)
  • A Snowflake schema requires fewer tables compared to a Star schema. 
  • A Snowflake schema reduces data redundancy. (CORRECT)

That’s correct! A Snowflake schema improves query performance through smaller data volume and enhanced data aggregation.

That’s correct! Normalization of dimension tables eliminates redundant data. 

17. True or False: The default state of dimension tables in Power BI is denormalized.

  • True
  • False  (CORRECT)

That’s correct! The default state of dimension tables depends on the source data. 

18. You want to merge two tables in Power BI. Which function can you use in the Power Query editor to combine the two tables?

  • Table tools
  • Column tools
  • Merge queries  (CORRECT)

That’s correct! Merge queries in the power query editor is used to combine two tables based on a common column.

19. Which of the following statements are true about table relationships in Power BI? Select all that apply:

  • Relationships help to create meaningful visualization and reports.  (CORRECT)
  • Table relationships define how tables connect and interact.  (CORRECT)
  • Relationships are only established between dimension tables. 

That’s correct! Relationships between tables in the model help to create insightful visualizations and reports. 

That’s correct! Data from multiple tables can be treated as one coherent dataset via relationships.

20. Which of the following types of data is most suited to a Fact table?

  • Product Categories 
  • Sales Revenue   (CORRECT)
  • Customer Information

That’s correct! A Fact table stores measurable information about the business process.

21. True or False: Fact tables in Power BI are denormalized to optimize query performance.

  • True   (CORRECT)
  • False 

That’s correct! Denormalization involves combining related data from multiple tables into a single table to improve query performance. 

22. When establishing relationships between tables in Power BI, which of the following options can be used as the basis of the relationship? Select all that apply:

  • Unique identifiers.  (CORRECT)
  • Common fields or columns.  (CORRECT)
  • Primary keys and foreign keys.  (CORRECT)

That’s correct! Unique identifiers are pivotal in establishing a relationship between two tables for integrated analysis. 

That’s correct! Each relationship needs a common field or column between the two tables to be connected. 

That’s correct! The primary key from the Fact table and the foreign key from the dimension tables establishes relationships between the Fact table and dimension tables. 

23. Adventure Works data model contains a Sales, a Product, and a Customers table. Which tables will you use to group, filter, and categorize data for your reports? Select all that apply:

  • Products  (CORRECT)
  • Sales
  • Customers  (CORRECT)

That’s correct! You can filter sales data for a specific product and product category using the Products table. 

That’s correct! Like the Products table, the Customers table can also be used to group and categorize data within the reports and visualizations.

24. What is the impact of selecting the Both cross-filter direction in Power BI? 

  • A filter applied to one table affects the other but not vice versa.
  • Filters applied to either table do not affect the other table.
  • Filters applied to either table in the relationship affect the other table.  (CORRECT)

That’s correct! In Both cross-filer direction, filter propagation is bidirectional.

25. A company’s data model contains a Warehouse table and a Products table. Each product can be stored in multiple warehouses, and each warehouse can have multiple products. What type of relationship can be established between these tables?

  • One-to-one 
  • Many-to-many   (CORRECT)
  • One-to-many 

That’s correct! The Products and Warehouse tables contain duplicate values, so many-to-many is the

CONCLUSION – Concepts for Data Modeling

In conclusion, this module provides a thorough understanding of data modeling and the critical schemas used in their creation. By mastering these concepts, learners will be well-prepared to design and implement effective data models that ensure data is well-organized, accessible, and reliable. Equipped with these skills, students can confidently tackle data-related challenges in various contexts, enhancing their ability to contribute to data-driven projects and initiatives with precision and efficiency.

Weekly Breakdown
Next Module

Quiztudy Top Courses

Popular in Coursera

Liking our content? Then, don’t forget to ad us to your BOOKMARKS so you can find us easily!

Expanded Thumbnail Promo1

Mood Zone for Studying & Relaxing