PL-300: Microsoft Power BI Data Analyst
You build a report to analyze customer transactions from a database that contains the tables shown in the following table.
You import the tables.
Which relationship should you use to link the tables?
many-to-many between Customer and Transaction
one-to-many from Transaction to Customer
one-to-many from Customer to Transaction
one-to-one between Customer and Transaction
Answer is one-to-many from Customer to Transaction
Each customer can have many transactions.
For each transaction there is exactly one customer.
You have two tables named Customers and Invoice in a Power BI model. The Customers table contains the following fields:
- CustomerID
- Customer City
- Customer State
- Customer Name
- Customer Address 1
- Customer Address 2
- Customer Postal Code
The Invoice table contains the following fields:
- Order ID
- Invoice ID
- Invoice Date
- Customer ID
- Total Amount
- Total Item Count
The Customers table is related to the Invoice table through the Customer ID columns. A customer can have many invoices within one month.
The Power BI model must provide the following information:
- The number of customers invoiced in each state last month
- The average invoice amount per customer in each postal code
You need to define the relationship from the Customers table to the Invoice table. The solution must optimize query performance.
What should you configure?
Box 1: One-to-many
A customer can have many invoices within one month.
Box 2: Single
For One-to-many relationships, the cross filter direction is always from the "one" side, and optionally from the "many" side (bi-directional). For Single cross filter direction means "single direction", and Both means "both directions". A relationship that filters in both directions is commonly described as bi- directional.
Reference:
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand
You are creating a Microsoft Power BI data model that has the tables shown in the following table.
The Products table is related to the ProductCategory table through the ProductCategoryID column.
You need to ensure that you can analyze sales by product category.
How should you configure the relationships from Products to ProductCategory?
Box 1: One-to-many
Box 2: Single
For One-to-many relationships, the cross filter direction is always from the "one" side, and optionally from the "many" side (bi-directional).
Reference:
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand
What turns a collection of independent tables into a data model?
Connecting the tables via relationships, based on their common fields
Connecting to the tables in a single Power BI file
Merging the tables into a single "master" table
Giving the tables related names
Answer is Connecting the tables via relationships, based on their common fields
In a data model, tables are connected via relationships, based on their common fields
Which of these is NOT a data model best practice?
Use a star schema with many-to-many relationships
Contain relationships with one-way filters (vs. bidirectional)
Contain tables that each serve a specific purpose, including data (fact) tables and lookup (dim) tables
Only include the data you need for analysis (no redundant or unnecessary records or fields)
Answer is Use a star schema with many-to-many relationships
A well designed model is critical and ideally should use a star schema with one-to-many (1:*) relationships, NOT many-to-many
Which of the following fields would typically find in a data table?
Product name
Retail price
Quantity Sold
Product Brand
Answer is Quantity Sold
Data tables contain measurable metrics about the business, like quantity sold, while lookup tables provide descriptive attributes about each dimension in your model, like product name, product brand, and retail price.
What is the name of a column or field used to uniquely identify each row of a table?
Primary key
Foreign key
Native key
Unique key
Answer is Primary key
Primary keys uniquely identify each row of a table, and match the foreign keys in related data tables.
Which of the following statements is NOT true regarding the use of merged tables instead of data models?
Merging creates redundant data
Merging uses more memory
Merging uses more processing power
Merging keeps metrics and dimensions in separate tables
Answer is Merging keeps metrics and dimensions in separate tables
Merging data by pulling all the attributes into the fact table itself creates redundant data and utilizes significantly more memory and processing power than creating relationships between multiple small tables.
In a one-to-many relationship cardinality, what is the "many" attached to?
Primary key
Foreign key
Index column
Lookup table
Answer is Foreign key
Cardinality refers to the uniqueness of values in a column, and whenever there is only one instance of each value, then it is a primary key, if there are many instances then it is a foreign key.
Which of the following statements is true regarding filter flow?
By default, the filter direction will point from the "one" side of the relationship to the "many" side
When you filter a table, the filter context is passed along to all related "downstream" tables.
Filters cannot flow "upstream"
All of the above
Answer is All of the above
The filter directions (arrows) in each relationship will point from the “one” side of the relationship (lookups) to the “many” side (data) by default. When you filter a table, that filter context is passed along to all related “downstream” tables (following the direction of the arrow). Filters cannot flow “upstream” (against the direction of the arrow).