Brainscape's Knowledge GenomeTM

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


See full index

Power BI > Power BI - Model The Data > Flashcards

Power BI - Model The Data Flashcards

1
Q

What are the 3 most common schemas?

A
  1. Flat (fully denormalized) schema
  2. Star schema
  3. Snowflake schema
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is a flat schema

A

All attributes are fully demoralized into a single table

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

What are the downsides of flat schemas?

A
  1. A single table can be cumbersome & confusing to navigate
  2. Columns & Data can often be duplicated, leading to comparatively large file size
  3. Mixing facts of different grains results in more complex DAX formulas
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are the two table types Star Schemas use?

A

Fact Table and Dimension Table

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

What is a fact table & what type of data does it contain?

A

A fact table is a Transaction table

  • metrics you want to aggregate.
  • Contain foreign keys that are required to create relationships with dimensions
  • columns that you can aggregate
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What data do dimension tables contain?

A

Dimension tables contain descriptive attributes about data in the fact tables.

It has a unique identifier - a key column & descriptive columns.

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

What is a snowflake table and how is it different from the star schema?

A

The snowflake schema is similar to the star schema except it can have dimension tables that “snowflake” from other dimensions.

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

What are the different table properties?

A
  1. Name – the name of the table
  2. Description – you can add a description of the table which is stored in the model’s metadata
  3. Synonyms – User for the Q&A feature. Synonyms are added so the Q&A feature will that you’re referring to a specific table.
  4. Row Label – Useful for Q&A & featured tables. It allows you to select a column whose values will serve as labels for each row.
  5. Key Column – If your table has a column that has unique values for every row, you can set that column as the key column
  6. Is hidden – you can hide a table so it will disappear from the fields pane list
  7. Is featured table – allows you to make a table featured which will allow it to be used in Excel in certain scenarios
  8. Storage mode – can be set to import, direct query, or dual
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What are the column properties

A
  1. Name – the name of the column
  2. Description – you can add a description of the column which is stored in the model’s metadata
  3. Synonyms – User for the Q&A feature. Synonyms are added so the Q&A feature will that you’re referring to a specific column.
  4. Display folder – You can group columns form the same table into display folders
  5. Is hidden – Hiding a column keeps it in the data model and hides it in the fields pane
  6. Data type – Different from those that are in Power Query (Percentage, Time, Time zone, & duration are not available)
  7. Format – Different data types will show different formatting properties
  8. Sort by Column – You can sort 1 column by another
  9. Data category – default is uncategorized. Depending on the data type, you can select one of the follow:
    a. Address
    b. City
    c. Continent
    d. Country/Region
    e. County
    f. Latitude
    g. Longitude
    h. Place
    i. Postal Code
  10. Summarize By – Determines how the column will be aggregated if you put it in a visual. The options you can choose from depends on the data type. For most the options are:
    a. Count
    b. Distinct Count
    c. Average
    d. Sum
    e. Min & Max
  11. Is Nullable – You can disallow null values for a column. If a null value is a result after a refresh, the refresh will fail.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Define quick measures

A

A measure in Power BI is a dynamic evaluation of a DAX query that will change in response to interactions with other visuals; enabling quick, meaningful exploration of your data

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

What are Parent-Child hierarchies used for?

A

For employees charts of accounts, and organizations

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

What are the 2 nodes parent-child hierarchies are defined by?

A

Node key and Parent Node Key

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

What function can you use for parent-child hierarchies that will return a string that contains a delimited list of IDs (starting with the top/roof of the hierarchy and ending with the specified ID)

A

PATH Function

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

What parameters are used in the LOOKUPVALUE DAX Function

A

Result_CoulmnName (column with desired value)
Search_ColumnName (column that contains search value)
Search_Value (value you want to find in search_column)
Alternate_Value

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

What parameters are used in the PATH DAX Function

A

ID_ColumnName (Column containing the IDs for each row)

Parent_ColumnName

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

How many relationships between two tables can be active at one time?

A

One

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

How do you use an inactive relationship?

A

You can use an inactive relationship by defining a DAX expression that uses the USERRELATIONSHIP function.

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

What does the USERRELATIONSHIP function specify?

A

The USERRELATIONSHIP specifies the relationship to be used in a specific calculation as the one that exists between ColumnName1 and ColumnName2

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

What are the parameters of the USERRELATIONSHIP function?

A

ColumnName1 - the name of an existing column (usually represents the many side)

ColumnName2 - the name of an existing column (usually represents the one side)

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

What function can you use for an inactive relationship?

A

USERRELATIONSHIP

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

What is cardinality in Power BI?

A

Cardinality represents the data characteristics of the “from” and “to” related columns (the relationships)

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

What are the different cardinality options in Power BI?

A

One-to-One
One-to-Many
Many-to-One
Many-to-Many

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

What is a Many-to-one relationship (*.1)

A

It’s the most common relationship and the default type
The column in a given table can have more than one instance of a value and the other related table often known as the lookup table has only one instance of a value

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

What is a One-to-one (1:1) relationship?

A

The column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value

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

What is a One-to-Many (1:*) relationship?

A

The column in one table has only one instance of a particular value and the other related table can have more than one instance of a value

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

What does a Many-to-many relationship (:) do and what is it used with?

A

Used with composite models

It removes the requirements for unique values in tables

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

What does cross filter direction determine?

A

Cross filter determines which way your filters flow in the relationships created

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

What are the 2 cross filter directions?

A

Single or Both (bidirectional)

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

What happens when single cross filter is selected?

A

When single cross filter is selected, the filters from the table on the “one side” will filter through the table on the “many” side.

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

What happens when both cross filter is selected?

A

When both cross filter is selected, filters from both tables will flow in both directions.

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

In addition to defining relationships, what else does the term Cardinality refer to?

A

Cardinality also refers to the number of distinct values in a column

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

What are the two scenarios where you will see Many-to-Many relationships?

A
  1. Many-to-Many relationships between dimensions
    Ex: 1 client may have multiple accounts & 1 account can belong to multiple clients
  2. Relationships between tables at different granularities.
    Ex: you have a sales table at the date level & a targets table at the month level. Both tables are related to a single date table. The relationship between the targets and date tables would be many-to-many.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What is a bridge table in a Many-to-Many relationship?

A

A bridge table is a table that allows you to create one-to-many relationships with each table that is in a many-to-many relationship.

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

What are the 2 types of bridge tables?

A
  1. A one-column table with unique values. The bridge table is on the “one” side in each relationship (typical for relating facts or tables that have different grains)
  2. A two-column table with a unique combination of values. The bridge table is on the many side in each relationship. Common for many-to-many relationships between dimensions
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What are the benefits of creating your own date table?

A
  1. You can use a calendar other than Gregorian
  2. You can have weeks in the calendar
  3. you can filter multiple fact tables by sing a single date dimension table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What are the ways you can create a date table?

A
  1. Import a date table (created in Excel)
  2. CALENDARAUTO – A simple, fully automated DAX expression.
  3. CALENDAR – A DAX expression where you define a start and end date.
  4. M Code – In Power Query Editor, you can paste in a common template to generate
    dates.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

What does the CALENDAR function require to create a date table?

A

The CALENDAR function requires a start and end date.

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

What are the parameters of the CALENDAR function?

A

The parameters of the CALENDAR function are:
StartDate
EndDate

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

What does the CALENDARAUTO function do to create a date table?

A

The CALENDERAUTO function scans your data model for dates and returns an appropriate date range automatically

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

What is Data granularity?

A

Data granularity is the level of data in your data model.
Ex: Data in a targets table provides a target for each month. If you filter for date instead of month, you won’t get meaningful data.

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

What is data model development?

A

Data model development is the enhancements you add to your data model after you’ve loaded your data & created relationships

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

How are calculated tables defined?

A

Calculated tables are defined by using DAX

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

When is it useful to create a calculated table?

A
  1. Cloning Tables
  2. Creating tables that are based on data from different data sources
  3. Precalculating measures to improve report performance
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

What are sensitivity labels?

A

Sensitivity labels are information icons that users can apply in Power BI desktop or the Power BI Service.

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

Where can sensitivity labels be viewed?

A

Sensitivity labels can be viewed on reports or dashboards

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

How to turn on Sensitivity labels?

A

Have to be an admin. In Admin Portal go to settings and go to tenant settings & turn on Information Protection (select enable)

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

Where are classifications for sensitivity labels created and defined?

A

Classifications for sensitivity labels are created & defined in Microsoft 365 Compliance Centered

48
Q

What are role-playing dimension tables?

A

Role-playing dimension tables are tables that have multiple relationships with fact tables.

For example, a date (in the dimension table) is used to create a relationship with an order date, ship date, and due date (in the fact table).

49
Q

How are cloned tables defined?

A

To clone a table, you use DAX.

Ex: InvoiceDate = Date

50
Q

What Power BI Feature is “UNION” similar to?

A

UNION acts similarly to appending tables in Power BI.

51
Q

How does appending tables differ from using UNION to combine tables?

A

UNION ignores column names and combines table columns based on their positions. The # of columns between the tables must match.

Append combines tables based on column names and it’s possible to combine tables that have a different # of columns

52
Q

What other functions require tables to have the same # of columns?

A

EXCEPT & INTERSECT

53
Q

What is row-level security (RLS) with Power BI?

A

RLS is used to restrict data access for given users. Filters restrict data at the row level. RLS DOES NOT restrict data access.

54
Q

How are filters defined for row-level security?

A

Filters are defined within roles for RLS.

55
Q

What are hierarchies?

A

A hierarchy is a set of fields categorized in a hierarchical way that one level is the parent of another level.

56
Q

What tab is used to create hierarchies?

A

The model tab

57
Q

What is used to create hierarchies?

A

Hierarchies can be created using existing columns of the same table only.

58
Q

How are calculated columns created?

A

Calculated columns are created using DAX.

59
Q

What data can be used to create calculated columns?

A

Data already loaded into the model or new data generated by DAX

60
Q

What’s the downside of calculated columns?

A

Calculated columns widens your tables, so having multiple calculated columns can contribute to slow performance

61
Q

What are the are two ways to edit a relationship?

A

go to the Home Ribbon and then select ‘Manage relationships’, then select edit.

The second way is to double click on the line connecting the two tables.

62
Q

What are the two ways to edit a relationship?

A

go to the Home Ribbon and then select ‘Manage relationships’, then select edit.

The second way is to double click on the line connecting the two tables.

63
Q

What storage modes can you define roles for?

A

Import & DirectQuery

64
Q

When using Analysis Services data model, where is RLS configured at?

A

RLS is configured in the source

65
Q

Where are you able to view user roles?

A

In Power BI service & Desktop

66
Q

What does dynamic row-level security do?

A

Dynamic Row Level security allows you to display different data to users within the same role.

67
Q

What must the data model contain to utilize dynamic row-level security?

A

Your data model must contain the usernames of people who should have access to the relevant rows of data.

68
Q

What two functions does Power BI have that allow you to get the user name of the current user for Dynamic Row-Level security?

A

USERNAME

USERPRINCIPALNAME

69
Q

What will the DAX function USERNAME return?

A

DOMAIN\User

70
Q

What will the DAX function USERPRINCIPALNAME return?

A

UPN (like email) - when you have a column in your data that contains the email address you can filter using USERPRINCIPALNAME

71
Q

What is Object-level security?

A

Object Level Security hides tables, columns, and measures but also secures them

72
Q

What 3rd party application is used to implement object-level security?

A

Tabular Editor

73
Q

What view of Power BI should you be in to implement the Q&A visual?

A

You must be in Report View to start using Q&A

74
Q

Where can you add other words your organization uses to describe the data fields for Q&A?

A

In the “Field synonyms” option (under Modeling, Q&A setup)

75
Q

What is an implicit measure?

A

Measures automatically created by Power BI such as average, sum, count, etc…

76
Q

What is an explicit measure?

A

A measure you create using DAX.

77
Q

What’s the difference between measures and calculated columns?

A

You can see the results of calculated columns immediately but have to add measures to visuals to see the results.

Calculated column formulas apply to each row whereas measures apply to columns and tables, not specific rows.

78
Q

What is the purpose of a variable?

A

A variable stores the result of an expression, which is passed as an argument to other expressions

79
Q

What does the USERRELATIONSHIP function with CALCULATE activate?

A

USERRELATIONSHIP with CALCULATE activates an inactive relationship

80
Q

What does the CROSSFILTER function with CALCULATE change?

A

the CROSSFILTER function with CALCULATE changes the filter direction

81
Q

What DAX functions can you use as CALCULATE modifiers to ignore all filters?

A

ALL
ALLEXCEPT
ALLSELECTED

82
Q

What can the function ALL remove filters from?

A
  1. One or more columns from the same table
  2. An entire table
  3. The whole data model (when ALL is used with no parameters)
83
Q

If you want to remove filters from a column that’s sorted by another column, what you should you do to prevent getting unexpected results?

A

You should remove filters from both columns to prevent unexpected results.

84
Q

What are the parameters of the ALL function?

A

TableNameOrColumnName (The name of an existing table or column.) - Optional

ColumnName (A column in the same base table. The column can be specified in optional parameters only when a column is used in the first argument, too.) - Optional Repeatable

85
Q

How does the ALL function work?

A

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

86
Q

How does the ALLEXCEPT function work?

A

Returns all the rows in a table except for those rows that are affected by the specified column filters.

87
Q

What are the parameters of the ALLEXCEPT function?

A

TableName (The name of an existing table.)

ColumnName (A column or a table whose filtering is to be retained when ALLEXCEPT is used as a CALCULATE modifier. The column/table must be part of the expanded table specified in the first parameter.) - Repeatable

88
Q

How does the ALLSELECTED function work?

A

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied inside the query, but keeping filters that come from outside.

89
Q

What are iterators?

A

Functions that take a table and go row by row, evaluating an expression for each row

90
Q

What is row context?

A

the current row

91
Q

What is filter context?

A

all applied filters

92
Q

What table is required to use Time Intelligence functions?

A

A date table that has a data type column with unique values

93
Q

What are the parameters of DATESYTD

A

Dates (The name of a column containing dates or a one-column table containing dates.)

YearEndDate (Optional) End of year date.

94
Q

What does the DATESYTD function return?

A

Returns a set of dates in the year up to the last date visible in the filter context.

95
Q

What does the TOTALYTD function return?

A

This function calculates the running total of sales for each year

96
Q

Why is the calculate function so powerful?

A

It allows you to perform an expression while defining a custom filter that overrides the filter used by a visual

97
Q

What are the parameters of the CALCULATE function?

A

Expression - The expression to be evaluated.

Filter - A boolean (True/False) expression or a table expression that defines a filter. (Optional & Repeatable)

98
Q

What are the basic statistical functions used to enhance data?

A 
SUM
AVERAGE
MEDIAN
COUNT
DISTINCTCOUNT
MIN
MAX
99
Q

What are the 3 types of measures

A

Additive, Semi-Additive, & Non-Additive

100
Q

What is an additive measure?

A

Measures that are aggregated by using the SUM function

101
Q

What is a Semi-Additive measure?

A

Measures that can be added across some but not all dimensions.
Ex: count of inventory; can be added across different product categories and cities but not dates.

102
Q

What is a non-additive measure?

A

Measures that can’t be added across any dimensions.

Ex: you cant add up the average price across any dimension because the result would not make any practical sense

103
Q

What does the OPENINGBALANCEMONTH function return?

A

Is the last day of the previous month.

The function evaluates the specified expression for the date corresponding to the end of the previous month after applying specified filters.

104
Q

What does the CLOSINGBALANCEMONTH function return?

A

Will show data from the last day of the previous month.

Evaluates the specified expression for the date corresponding to the end of the current month after applying specified filters.

105
Q

What feature can you use to trace slow-performing visuals & see the DAX queries behind them?

A

Performance Analyzer

106
Q

How does the Performance Analyzer work?

A

It works by recording traces and then shows you how long each visual took to render

107
Q

What are two ways you can reduce the cardinality of columns?

A
  1. By changing data types
  2. for columns that have decimals, you can split the decimal number columns into pairs of whole numbers and decimals numbers
108
Q

What is the correct order to use the Performance Analyzer?

A
  1. Add a blank report page
  2. Restart Power BI Desktop to clear the cache
  3. StartrecordinginPerformanceAnalyzer
  4. Interact with the visuals
  5. Stop recording and review the results
109
Q

What is the simplest way to have a smaller-sized data model?

A

 Delete unnecessary columns and rows
 Avoid repeating values by using clean dimension tables
 Replace calculated columns with measures where possible
 Summarize the data to the appropriate granularity

110
Q

What is Data cardinality?

A

the uniqueness of the data.

111
Q

What is Relationship cardinality

A

The joins between columns, e.g. one-many, many- many

112
Q

In what ways can you reduce cardinality?

A

 Split a Date Time column into a separate date column and a separate time column. This creates fewer distinct values
 Convert a string or decimal number to a whole number (integer). Again, this will reduce the number of distinct values

113
Q

What feature is available for data summarization?

A

GroupBy

114
Q

What are the steps required to manage aggregations?

A
  1. Create a new table name ‘Sales Agg’ containing columns to hold aggregate data
  2. Right-click on the ‘Sales’ table and select Manage aggregations
  3. Select the ‘Sales Agg’ table as the aggregation table
  4. Select the Summarization type
  5. Select the table and column from the ‘Sales’ table
115
Q

What does High cardinality mean?

A

fewer duplicates (many unique values)

116
Q

What does Low cardinality mean?

A

indicates more duplicates

Power BI (4 decks)

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