Design a semantic model in Power BI

Question 1

What is the primary benefit of creating a good semantic model in Power BI?

Answer 1

It helps make data easier to understand and builds valuable Power BI reports more efficiently.

Question 2

What are the benefits of a well-designed semantic model?

Answer 2

Faster data exploration, simpler aggregations, more accurate reports, quicker report writing, and easier future maintenance.

Question 3

Why is it generally better to have a smaller semantic model?

Answer 3

Smaller models perform faster and are simpler for users to navigate and understand.

Question 4

What is a primary key in a database table?

Answer 4

A unique, non-null column or set of columns that uniquely identifies each row in the table.

Question 5

How are relationships between tables defined in Power BI?

Answer 5

Through primary and foreign keys that link rows in different tables.

Question 6

What is a star schema in Power BI?

Answer 6

A schema where each table is defined as a dimension or fact table, optimizing for performance and usability.

Question 7

What is the difference between fact tables and dimension tables?

Answer 7

Fact tables contain measurable data (e.g., sales orders), while dimension tables contain details about the data in fact tables (e.g., products, locations).

Question 8

How does Power BI handle relationships from different data sources?

Answer 8

Power BI allows relationships to be built between tables from different data sources, treating them as a unified semantic model.

Question 9

Why is it important to simplify the table structure in Power BI?

Answer 9

To ensure that the semantic model is user-friendly, easier to navigate, and maintains good-quality relationships between tables.

Question 10

How can you manage relationships between tables in Power BI?

Answer 10

By using the Manage Relationships feature to create, edit, delete, and autodetect relationships.

Question 11

What options can you configure in the column properties in Power BI Model view?

Answer 11

Name and description, synonyms, folders, visibility, data type, formatting, sorting, data category, and summarization.

Question 12

What is the benefit of using Ctrl+click or Shift+click in the Model view?

Answer 12

To update properties for multiple tables and fields simultaneously, improving efficiency.

Question 13

How can formatting changes, such as date formats, be applied in Power BI?

Answer 13

By selecting the appropriate format from the drop-down menu in the Formatting tab of the column properties.

Question 14

Why is it important to create a common date table in Power BI?

Answer 14

To ensure consistent and accurate time-based calculations across multiple tables in your data model.

Question 15

What are three methods to create a date table in Power BI?

Answer 15

Using source data, DAX functions, or Power Query.

Question 16

How can you create a date table using DAX in Power BI?

Answer 16

By using the CALENDAR or CALENDARAUTO functions to generate a range of dates and then adding additional columns for year, month, week, and day.

Question 17

What is the DAX formula to create a date table from May 31, 2011, to December 31, 2022?

Answer 17

Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))

Question 18

How do you create a date table using Power Query in Power BI?

Answer 18

By defining a list of dates using an M formula and then converting the list into a table, adding columns for year, month, week, and day.

Question 19

What must be done after creating a date table to ensure it functions correctly in Power BI?

Answer 19

Establish relationships between the date table and other tables, and mark it as the official date table.

Question 20

What are dimension tables in a star schema?

Answer 20

Tables that store details about business entities, such as products or time, and are connected to fact tables through relationships.

Question 21

How are hierarchies useful in Power BI?

Answer 21

They allow users to drill down into specifics of data, like breaking down dates into years, months, weeks, and days.

Question 22

How can you create a hierarchy in Power BI?

Answer 22

Right-click the desired column in the Fields pane, select “New hierarchy,” and then add sub-levels by dragging and dropping other columns into this hierarchy.

Question 23

What is a parent-child hierarchy?

Answer 23

A hierarchy where a parent (e.g., manager) has multiple children (e.g., employees), used to show levels of relationships within data.

Question 24

What is the PATH() function in DAX used for?

Answer 24

To create a text version of the hierarchical path between parent and child in a table.

Question 25

What are role-playing dimensions?

Answer 25

Dimensions that have multiple valid relationships with fact tables, allowing the same dimension to filter multiple columns or tables of data.

Question 26

What is data granularity?

Answer 26

The level of detail represented within your data, affecting performance and usability of reports.

Question 27

Why might you adjust the granularity of data?

Answer 27

To make the semantic model more usable and improve performance by reducing the number of records.

Question 28

What is an example scenario for adjusting data granularity?

Answer 28

Importing daily averages instead of minute-by-minute records for truck temperatures to reduce data volume.

Question 29

How can data granularity impact relationships between tables?

Answer 29

Different granularities need to be reconciled to build relationships, such as matching monthly budget data with daily sales data.

Question 30

How do you create a relationship between tables with different granularities?

Answer 30

By transforming columns to match formats, such as concatenating Year and Month columns to create a common date format.

Question 31

What is a many-to-one (or one-to-many) relationship in Power BI?

Answer 31

A relationship where many instances in one column relate to a single instance in another column, typically between fact and dimension tables.

Question 32

What defines a one-to-one relationship?

Answer 32

A relationship where only one instance of a value is common between two tables, requiring unique values in both tables.

Question 33

What is a many-to-many relationship in Power BI?

Answer 33

A relationship where many values are common between two tables without requiring unique values in either table.

Question 34

What does single cross-filter direction mean?

Answer 34

Only one table in the relationship can filter data, with filtering occurring on the "many" side.

Question 35

What is bi-directional cross-filtering?

Answer 35

A feature where both tables in a relationship can filter each other, though it can affect performance and introduce ambiguity.

Question 36

When should you use caution with bi-directional cross-filtering?

Answer 36

When dealing with many-to-many relationships due to the potential for ambiguity and performance issues.

Question 37

What is a common pitfall in creating relationships within a semantic model in Power BI?

Answer 37

Circular relationships, which create a web of connections that are difficult to manage.

Question 38

What are dependencies in the context of Power BI relationships?

Answer 38

Relationships where changes in one table or column cause changes in another due to their interconnected nature.

Question 39

What is an example of a column dependency in Power BI?

Answer 39

A calculated column TotalCost in the Sales table depends on the Quantity and Price columns.

Question 40

How can you identify circular relationships in your Power BI model?

Answer 40

By analyzing the dependencies between tables and ensuring there are no circular dependencies.

Question 41

Why is it important to avoid circular relationships in Power BI?

Answer 41

They can make it unclear what relationships exist, complicating the building and understanding of visuals.