Get Graded and Practiced Data Modeling and Architecture Quiz Answers
Table of Contents
Data Modeling and Architecture Module 01 Quiz Answers
Knowledge check: Cardinality Quiz Answers
Q1. What is a key requirement in order to establish a one-to-one relationship between two tables in a Power BI data model?
Correct Answer:
- Both tables must have a column with unique values for each row.
Explanation: In a one-to-one relationship, each row in the first table must correspond to one and only one row in the second table, requiring unique values in both tables.
Q2. True or False: The relationship between a Customer table, including the unique customers, and a Sales table, including all sales for each customer, should be many-to-many.
Correct Answer:
- False
Explanation: The correct relationship is one-to-many, where each customer has multiple sales, but each sale is linked to one specific customer.
Q3. In which cases would a many-to-many relationship be appropriate in a Power BI data model?
Correct Answers:
- One table of products and one table of orders where each order can contain multiple products, and each product can appear in multiple orders.
- One table of students and one table of courses where students can enroll in multiple courses, and each course can have multiple students.
Explanation: A many-to-many relationship is appropriate when there are multiple correspondences between rows in both tables, such as products appearing in multiple orders or students enrolling in multiple courses.
Q4. In which scenario is a high level of granularity most needed in a data model?
Correct Answer:
- To analyze individual customer transactions and their purchase details
Explanation: High granularity is required to capture detailed, individual-level data, such as transaction-level insights for customers.
Q5. In which scenario would a one-to-many relationship be most appropriate in a data model?
Correct Answer:
- To link a table of unique products to a table of categories where each product belongs to one category
Explanation: A one-to-many relationship is appropriate when one record in the first table (unique products) corresponds to multiple records in the second table (categories), but each category can only be associated with a single product.
Knowledge check: Introduction to data schemas Quiz Answers
Q1. What schema is used in a data model where all information fits into a single table with no relationship to dimension tables?
Correct Answer:
- Flat schema
Explanation: A flat schema stores all data in a single table without the need for relationships between dimension and fact tables.
Q2. True or False: In the Data model view, the arrows on connector lines indicate the cardinality of the relationship between the two tables.
Correct Answer:
- True
Explanation: In Power BI’s Data model view, the arrows on the connector lines indicate the direction of the relationship and the cardinality (e.g., one-to-many, many-to-one).
Q3. Adventure Works has a data model that includes the following tables: Calendar, which stores date information; Orders, where order transactions are tracked; and Customer, which holds information about customers. Which of these is the fact table?
Correct Answer:
- Orders
Explanation: The Orders table is the fact table because it contains transactional data (e.g., sales, order details), which is typically the central focus of analysis.
Q4. In a star schema, how are fact and dimension tables typically connected?
Correct Answer:
- All dimension tables are connected to a central fact table.
Explanation: In a star schema, the fact table (e.g., sales) is surrounded by dimension tables (e.g., customers, products), which are all directly connected to the fact table.
Q5. Which of the following is a key benefit of normalization in a data model? Select all that apply.
Correct Answers:
- It improves the readability and maintainability of the data model.
- It eliminates redundant data, ensuring data integrity.
Explanation: Normalization removes redundancy, ensuring data consistency, and also makes the model easier to manage and maintain. It doesn’t necessarily reduce the number of relationships or combine data into fewer tables, as those are typically aspects of denormalization.
Knowledge check: Advanced data schemas Quiz Answers
Q1. Which of the following best describes a snowflake schema in Power BI?
Correct Answer:
- A schema where a table is fully normalized and related via multiple dimension tables
Explanation: A snowflake schema organizes the data in a normalized manner, with multiple related dimension tables connected to the fact table. This reduces redundancy compared to a star schema.
Q2. Adventure Works has normalized the Customer table and is creating a new Customer Region table. The new tables contain Region ID, Continent, and Country columns and are connected to the original table via the Region ID column. Which of the following columns can be safely removed from the Customer table? Select all that apply.
Correct Answers:
- Country
- Continent
- Region ID
Explanation: In the new schema, the Region ID links to the Customer Region table, which contains the Continent and Country information. Therefore, these columns can be removed from the Customer table to avoid redundancy.
Q3. Which of the following types of table (fact or dimension) are typically connected with each other in a snowflake schema? Select all that apply.
Correct Answers:
- Fact table with dimension table
- Dimension tables
Explanation: In a snowflake schema, the fact table is typically connected to normalized dimension tables. These dimension tables are often further divided into additional related tables.
Q4. True or False: In a snowflake schema, data redundancy is increased because tables are normalized.
Correct Answer:
- False
Explanation: In a snowflake schema, normalization reduces data redundancy by organizing data into multiple related tables, which helps avoid duplication.
Q5. Which DAX function is commonly used to create new tables during the normalization process in a snowflake schema?
Correct Answer:
- GROUPBY
Explanation: The GROUPBY function in DAX is often used to create new tables by aggregating data, which is helpful in the normalization process of a snowflake schema.
Data Modeling and Architecture Module 02 Quiz Answers
Knowledge check: Introduction to Data Analysis Expressions Quiz Answers
Q1. Which of the following are aggregation functions in DAX? Select all that apply.
Correct Answers:
- AVERAGE
- MIN
Explanation: DAX has aggregation functions such as AVERAGE, MIN, MAX, SUM, etc. The other options, MONTH and CONCATENATE, are not aggregation functions.
Q2. True or False: DAX calculated columns differ from DAX measures as row context only exists on calculated columns.
Correct Answer:
- True
Explanation: DAX calculated columns are calculated row by row, which means they have row context. Measures, on the other hand, are evaluated in filter context, not row context.
Q3. What is the application of the && operator in DAX?
Correct Answer:
- Logical AND
Explanation: The && operator in DAX is used to perform a logical AND operation between two conditions, returning true if both conditions are true.
Q4. You are a data analyst working with a large sales dataset of Adventure Works. You are using Microsoft Power BI as an analytical tool. In which of the following scenarios would it be beneficial to use DAX functions? Select all that apply.
Correct Answers:
- Calculating the total orders per customer
- Creating a column that flags high-performing products
Explanation: DAX functions are ideal for creating calculated columns, measures, and calculations that need to be dynamic based on filters and context, such as calculating totals or creating flags for specific conditions. Importing data or creating visualizations would not typically involve DAX directly.
Q5. What does filter context in DAX do?
Correct Answer:
- It defines the surrounding data used for evaluating a DAX expression based on applied filters.
Explanation: Filter context in DAX refers to the set of filters that affect the calculation of a measure or expression. This context can be influenced by slicers, columns, rows, or any other applied filters.
Knowledge check: DAX measures Quiz Answers
Q1. Which type of measure in Power BI calculates totals across all rows in a dataset?
Correct Answer:
- Additive
Explanation: Additive measures, such as SUM, calculate totals across all rows in a dataset. They add values together to provide a total.
Q2. Which DAX function would you use to count unique values in a dataset?
Correct Answer:
- DISTINCTCOUNT
Explanation: The DISTINCTCOUNT function counts the number of unique values in a column, unlike COUNT, which counts all values, or COUNTX, which counts rows based on an expression.
Q3. The Adventure Works sales team wants to understand the typical sales amount across all transactions to gain insights into central tendencies without the skew of extremely high or low sales. Which function would best help them find this typical “middle” sales value?
Correct Answer:
- MEDIAN
Explanation: The MEDIAN function helps find the middle value in a dataset, which is not skewed by extreme values (such as extremely high or low sales). This makes it a better choice for understanding the “typical” sales amount compared to the mean.
Q4. When creating a DAX measure, which feature would allow you to set up a measure based on a specific filter context?
Correct Answer:
- CALCULATE
Explanation: The CALCULATE function allows you to modify the filter context for a DAX expression, enabling you to apply specific filters and conditions when calculating a measure.
Q5. In Power BI, which of the following syntax correctly uses the CALCULATE function to return the total value from a specific column while applying additional filters?
Correct Answer:
- CALCULATE(SUM(TableName[ColumnName]), TableName[OtherColumn] = <expression>)
Explanation: This is the correct syntax for using CALCULATE in DAX, where you apply a filter (TableName[OtherColumn] = <expression>) to the SUM calculation.
Knowledge check: Cross-filter and filter context Quiz Answers
Q1. Which of the following functions can be used in the measures to modify the filter context in DAX? Select all that apply.
Correct Answers:
- KEEPFILTERS
- CROSSFILTER
- REMOVEFILTERS
Explanation:
- KEEPFILTERS: Modifies the existing filter context by keeping additional filters applied.
- CROSSFILTER: Adjusts the direction of cross-filters in table relationships.
- REMOVEFILTERS: Removes filters from specific columns or tables.
COUNTROWSis not used to modify the filter context.
Q2. What is a key difference between CALCULATE and CROSSFILTER functions?
Correct Answer:
- CALCULATE alters the filter context for specific measures, while CROSSFILTER changes the direction of cross-filters between tables.
Explanation:
- CALCULATE: Modifies the filter context to perform conditional calculations.
- CROSSFILTER: Adjusts the cross-filter direction between related tables to affect filtering behavior.
Q3. True or False: The CALCULATE function allows you to alter the filter context in Power BI to perform calculations based on specific conditions.
Correct Answer:
- True
Explanation:
- CALCULATE is designed to modify the filter context, enabling calculations under specific conditions or filters.
Q4. Which statement about the CROSSFILTER function is correct?
Correct Answer:
- It can be used to change the cross-filter direction between tables in a relationship.
Explanation:
- CROSSFILTER allows changes to the direction of cross-filters between related tables, making it useful for controlling how relationships impact filtering.
Q5. Which function can you use within CALCULATE to prevent the removal of an existing filter on a column?
Correct Answer:
- KEEPFILTERS
Explanation:
- KEEPFILTERS ensures that existing filters on a column are preserved when additional filters are applied in a calculation
Knowledge check: DAX in table relationships Quiz Answers
Q1. What is the primary purpose of role-playing dimensions in Power BI?
Correct Answer:
- To analyze the same dimension from multiple perspectives
Explanation:
Role-playing dimensions (e.g., a Date table used for both Order Date and Ship Date) enable analysis of the same dimension from different perspectives without duplicating tables.
Q2. Which function is required in DAX to activate an inactive relationship between two tables?
Correct Answer:
- USERELATIONSHIP
Explanation: USERELATIONSHIP temporarily activates an inactive relationship for calculations in DAX.
Q3. True or false: You can use the USERELATIONSHIP function to create a new relationship between tables that have no existing relationship.
Correct Answer:
- False
Explanation: USERELATIONSHIP only activates inactive relationships; it cannot establish new relationships.
Q4. What type of relationship does Power BI default to using for calculations?
Correct Answer:
- Active relationship
Explanation: By default, Power BI uses active relationships for calculations. Inactive relationships are ignored unless explicitly activated using functions like USERELATIONSHIP.
Q5. When should you use the USERELATIONSHIP function?
Correct Answer:
- To activate an inactive relationship for a specific calculation
Explanation: USERELATIONSHIP is used in DAX to temporarily activate inactive relationships for calculations, such as when you have multiple relationships between tables and need to specify which one to use.
Data Modeling and Architecture Module 03 Quiz Answers
Knowledge check: Data privacy and ethics Quiz Answers
Q1. Which of the following is a primary purpose of the General Data Protection Regulation (GDPR)?
Correct Answer:
- Protecting the privacy and data rights of individuals within the European Union
Explanation: The GDPR aims to protect individuals’ personal data and privacy within the EU and to give them more control over their information.
Q2. True or False: Businesses must comply with data privacy laws like GDPR, HIPAA, and CCPA to avoid potential legal and financial consequences.
Correct Answer:
- True
Explanation: Non-compliance with data privacy laws can lead to significant penalties, reputational damage, and financial losses.
Q3. Adventure Works recently conducted a customer survey to gather feedback on their new line of bicycles. Which of the following best defines the principle of consent in ethical data use?
Correct Answer:
- Ensuring that individuals give explicit permission for their data to be collected and used
Explanation: Consent ensures that individuals willingly agree to the collection and use of their data after being informed of its purpose.
Q4. Which of the following are key ethical principles in data analysis?
Correct Answer:
- Anonymization
- Transparency
- Fairness and avoiding bias
Explanation: Ethical data analysis ensures that personal data is anonymized, the process is transparent to stakeholders, and results are free from bias to ensure fairness.
Q5. Which of the following best describes data masking?
Correct Answer:
- Hiding or obscuring sensitive information in a dataset to prevent unauthorized access
Explanation: Data masking is a security technique used to protect sensitive information by substituting it with fictional but realistic values or obscuring it.
Knowledge check: Data governance Quiz Answers
Q1. Which of the following are the goals and benefits of data governance? Select all that apply.
Correct Answer:
- Ensuring data quality and consistency
- Improving operational efficiency
- Meeting compliance and security standards
Explanation: Data governance focuses on maintaining data quality, improving operational efficiency by streamlining data management, and ensuring compliance with legal and security standards.
Q2. Who oversees the accuracy, consistency, and compliance of customer data in marketing at Adventure Works?
Correct Answer:
- Data steward
Explanation: A data steward ensures that data is accurate, consistent, and compliant, particularly within specific business areas such as marketing.
Q3. Which of the following actions are recommended for maintaining data quality in Power BI? Select all that apply.
Correct Answer:
- Set up data validation rules in Power Query.
- Use Power BI’s dynamic alerts for automated data quality monitoring.
- Conduct regular data audits in Power Query or ETL tools.
Explanation: Data quality should be enforced through validation, monitoring, and audits. Cleaning data in the reporting layer (e.g., Power BI reports) is not recommended as it introduces inconsistencies.
Q4. True or False: Power BI’s role in data lifecycle management includes organizing data within workspaces to control, archive, and retrieve information as needed.
Correct Answer:
- True
Explanation: Power BI supports data lifecycle management by enabling data organization, access control, and archival within workspaces.
Q5. You are a data analyst preparing a sales report in Power BI. Upon reviewing the dataset, you notice multiple entries for the same customer order, which may skew the sales figures. Which process in Power Query would help ensure your data is ready for accurate analysis?
Correct Answer:
- Removing duplicates
Explanation: Removing duplicate records ensures that data analysis is accurate and prevents inflation of metrics such as total sales.
Get All Courses Quiz Answers of Microsoft Data Visualization Professional Certificate >>
Data Visualization Fundamentals Quiz Answers
Data Preparation and Management Quiz Answers
Data Modeling and Architecture Quiz Answers
Visualization for Data Analysis with Power BI Quiz Answers
