Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

PL-300 Deck > Designing a Semantic Model In PBI > Flashcards

Designing a Semantic Model In PBI Flashcards

1
Q

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

A

Makes data easier to understand for users, leading to better reports.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are two characteristics of a good semantic model?

A

Simplicity (fewer tables/columns) and comprehensiveness (includes all necessary data).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

How are relationships defined between tables in a semantic model?

A

Using primary and foreign keys.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are the two main types of tables in a star schema?

A

Fact tables (measurable data) and dimension tables (data details).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is the difference between a fact table and a dimension table in a star schema?

A

Fact tables store measurable data for aggregation, while dimension tables contain details used for filtering and grouping fact table data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Why are fact tables typically larger than dimension tables?

A

Fact tables hold numerous event records, while dimension tables have a limited number of filtering/grouping options.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is a benefit of a simple table structure in a Power BI semantic model?

A

Easier navigation due to clear column and table properties.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

How can you manage relationships between tables in Power BI?

A

Go to the “Manage Relationships” option in the “Model” tab.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How does the Autodetect feature help with relationships in Power BI?

A

It automatically suggests relationships between columns with similar names.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What are two ways to access table and column properties for editing in Power BI Desktop?

A

Ctrl+click or Shift+click the items in the “Model” view.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What can you do under the “Formatting” tab for columns in Power BI?

A

Change data type and format

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is the main purpose of a common date table in Power BI?

A

To enable time-based calculations and ensure consistent date formatting across reports.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

Source data (existing table), DAX functions (CALENDAR or CALENDARAUTO), or Power Query (M language).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is the benefit of using a source data table (if available) for the common date table?

A

It’s likely shared with other tools and avoids recreating the table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How can you add a “Year” column to a date table created with DAX?

A

Use the YEAR function (e.g., Year = YEAR(Dates[Date])).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What do you need to do after creating a common date table with Power Query?

A

Mark the table as the official date table to ensure data validity and recognition by Power BI.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

How do you build a relationship between a common date table and another table?

A

Use the “Manage Relationships” option in the “Model” tab and connect them using matching date columns.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is the difference between a dimension table and a fact table in a star schema?

A

Dimension tables store details about entities, while fact tables contain information about events.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is the purpose of a hierarchy in Power BI?

A

To allow drilling down into specific details from high-level data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

How can you create a hierarchy for a product category in Power BI?

A

Right-click the “Product Category” column in the “Fields” pane, select “New hierarchy,” and drag and drop subcategories into the hierarchy.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is the benefit of flattening a parent-child hierarchy in Power BI?

A

To view multiple child levels based on a parent for more data granularity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What DAX functions are used to flatten a parent-child hierarchy in Power BI?

A

PATH() to create a text path showing the managerial chain and PATHITEM() to separate each level into new columns.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is a role-playing dimension in Power BI?

A

A dimension table with multiple valid relationships to fact tables, allowing for filtering data in different ways.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is data granularity in Power BI?

A

The level of detail within your data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Why is data granularity important in Power BI?

A

It impacts both report performance and how usable your reports are for users.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

How can data granularity affect report performance?

A

More data (higher granularity) can lead to slower reports and refresh rates.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

How can data granularity affect user experience?

A

Summarizing data (lower granularity) may prevent users from drilling down to see specific details.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What is a good approach to balancing data granularity and user experience?

A

Negotiate the level of detail with report users to understand their needs.

29
Q

How can data granularity affect relationships between tables?

A

Tables need compatible granularity (e.g., matching date formats) to be properly related.

30
Q

What are some factors to consider when choosing data granularity?

A

User needs for detail, report performance, and the ability to drill down for analysis.

31
Q

What is the difference between a one-to-many and a many-to-one relationship in Power BI?

A

Both describe a relationship where one value in one table relates to multiple values in another table. The terms indicate which table has the single value (one) and which has the many values (many).

32
Q

Why is a one-to-one relationship between separate Product and Product ID tables not recommended?

A

It creates redundant data. Combine these tables into a single table.

33
Q

What is the risk of using a many-to-many relationship in Power BI?

A

It introduces ambiguity because both tables can have the same value, leading to unexpected filtering results.

34
Q

When would you use bi-directional cross-filtering in a relationship?

A

Only use it if you fully understand the potential for ambiguity, performance issues, and unexpected results.

35
Q

How can you determine which table can be used to filter another table in a relationship?

A

Follow the direction of the arrow on the relationship line in the Power BI model.

36
Q

What is the safest type of relationship to use in Power BI?

A

A one-to-many relationship with single direction filtering.

37
Q

What is a circular relationship in Power BI data modeling?

A

A loop of relationships between tables where each table has a relationship with another that ultimately connects back to itself.

38
Q

Why are circular relationships a problem in Power BI?

A

They create unclear data connections, making it difficult to build visuals and potentially leading to unusable data.

39
Q

What are the three main types of calculations you can add to a Power BI model using DAX?

A

Calculated tables, calculated columns, and measures.

40
Q

How do calculated tables differ from calculated columns?

A

Calculated tables create entirely new tables, while calculated columns add new data points to existing tables.

41
Q

What are some benefits of using calculated tables?

A

They can be used to create date tables, handle role-playing dimensions, and support what-if analysis.

42
Q

What is a role-playing dimension in Power BI?

A

A single table acting as multiple dimensions due to having multiple relationships with the same column in another table.

43
Q

What are disconnected tables in Power BI?

A

Another name for calculated tables created for what-if analysis. They are not related to other model tables.

44
Q

How do calculated columns differ from measures?

A

Calculated columns are evaluated at data refresh time and their results are stored in the model, while measures are evaluated at query time and their results are not stored.

45
Q

What is the general structure of a DAX formula?

A

<Calculation> = <DAX>
</DAX></Calculation>

46
Q

How do calculated tables differ from calculated columns and measures in terms of output?

A

Calculated tables return a table, while calculated columns and measures return a single value.

47
Q

List the main components used in DAX formulas.

A

DAX functions, operators, model object references (tables, columns, measures), constant values (optional), and whitespace (optional).

48
Q

What are the two main categories of DAX functions?

A

Functions originating from Excel and functions unique to DAX (data modeling).

49
Q

What does the DAX function DISTINCTCOUNT do?

A

Counts the number of distinct values in a column (useful for unique counts).

50
Q

What does the DAX function DIVIDE do?

A

Performs division with an optional alternate result for a zero denominator (avoids division by zero errors).

51
Q

What is a compound measure

A

A measure that references one or more measures

52
Q

What are three ways calculated columns and measures are similar?

A

Both are calculations added to the semantic model using DAX formulas.
Both are referenced in formulas by enclosing their names in square brackets.

53
Q

How does the purpose of a calculated column differ from a measure?

A

A calculated column extends a table with a new column, while a measure defines how to summarize model data.

54
Q

How does the evaluation of a calculated column differ from a measure?

A

A calculated column is evaluated using row context at data refresh time, while a measure is evaluated using filter context at query time.

55
Q

How does the storage of a calculated column differ from a measure?

A

A calculated column (in Import mode tables) stores a value for each row, while a measure never stores values in the model.

56
Q

How can calculated columns and measures be used differently in visualizations?

A

Calculated columns can be used for filtering, grouping, or summarizing (as an implicit measure), whereas measures are specifically designed for summarization.

57
Q

What are the three primary methods to add columns to a model table

A
  1. Add columns to the underlying database view or table. 2. Add custom columns during PQ. 3. Add calculated columns using DAX.
58
Q

When is it generally recommended to add custom columns in PQ?

A

When possible, use PQ to add custom columns for better performance and model efficiency.

59
Q

In what situations is using a calculated column in DAX preferable?

A

When column depends on summarised data or requires special modeling functions like RELATED, RELATEDTABLE, or parent-child hierarchies.

60
Q

What is the first step in optimizing report performance?

A

Identify the elements causing performance issues (e.g., measures, relationships, visuals).

61
Q

What tool can be used to identify performance bottlenecks in a Power BI report?

A

Performance Analyzer

62
Q

What are some key indicators of performance issues when analyzing a report?

A

Long DAX query duration, complex visuals, large semantic model size, and inefficient relationships.

63
Q

How can you optimize DAX queries for better performance?

A

Use DAX Studio for in-depth analysis, identify performance bottlenecks, and experiment with different DAX functions.

64
Q

How can you optimize visuals for better performance?

A

Reduce the number of visuals and fields, consider alternative ways to present information (e.g., drill-through pages, tooltips).

65
Q

How can you optimize the semantic model for better performance?

A

Remove unnecessary columns, review relationships, analyze metadata, and consider disabling Auto date/time if not needed.

66
Q

What is cardinality?

A

A measure of the uniqueness of values in a column or the direction of a relationship between tables.

67
Q

What is the difference between high and low cardinality?

A

High cardinality means many unique values, while low cardinality means many repeated values.

68
Q

How does cardinality affect model performance?

A

High cardinality can negatively impact performance, while low cardinality improves it.

69
Q

What are some techniques to reduce cardinality in a Power BI model?

A

Summarization, mixed mode modeling, and removing unnecessary columns.

PL-300 Deck (3 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions