Power BI - Model The Data Flashcards
1
Q
What are the 3 most common schemas?
A
- Flat (fully denormalized) schema
- Star schema
- Snowflake schema
2
Q
What is a flat schema
A
All attributes are fully demoralized into a single table
3
Q
What are the downsides of flat schemas?
A
- A single table can be cumbersome & confusing to navigate
- Columns & Data can often be duplicated, leading to comparatively large file size
- Mixing facts of different grains results in more complex DAX formulas
4
Q
What are the two table types Star Schemas use?
A
Fact Table and Dimension Table
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
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.
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.
8
Q
What are the different table properties?
A
- Name – the name of the table
- Description – you can add a description of the table which is stored in the model’s metadata
- Synonyms – User for the Q&A feature. Synonyms are added so the Q&A feature will that you’re referring to a specific table.
- Row Label – Useful for Q&A & featured tables. It allows you to select a column whose values will serve as labels for each row.
- Key Column – If your table has a column that has unique values for every row, you can set that column as the key column
- Is hidden – you can hide a table so it will disappear from the fields pane list
- Is featured table – allows you to make a table featured which will allow it to be used in Excel in certain scenarios
- Storage mode – can be set to import, direct query, or dual
9
Q
What are the column properties
A
- Name – the name of the column
- Description – you can add a description of the column which is stored in the model’s metadata
- Synonyms – User for the Q&A feature. Synonyms are added so the Q&A feature will that you’re referring to a specific column.
- Display folder – You can group columns form the same table into display folders
- Is hidden – Hiding a column keeps it in the data model and hides it in the fields pane
- Data type – Different from those that are in Power Query (Percentage, Time, Time zone, & duration are not available)
- Format – Different data types will show different formatting properties
- Sort by Column – You can sort 1 column by another
- 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 - 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 - Is Nullable – You can disallow null values for a column. If a null value is a result after a refresh, the refresh will fail.
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
11
Q
What are Parent-Child hierarchies used for?
A
For employees charts of accounts, and organizations
12
Q
What are the 2 nodes parent-child hierarchies are defined by?
A
Node key and Parent Node Key
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
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
15
Q
What parameters are used in the PATH DAX Function
A
ID_ColumnName (Column containing the IDs for each row)
Parent_ColumnName
16
Q
How many relationships between two tables can be active at one time?
A
One
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.
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
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)
20
Q
What function can you use for an inactive relationship?
A
USERRELATIONSHIP
21
Q
What is cardinality in Power BI?
A
Cardinality represents the data characteristics of the “from” and “to” related columns (the relationships)
22
Q
What are the different cardinality options in Power BI?
A
One-to-One
One-to-Many
Many-to-One
Many-to-Many
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
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
25
What is a One-to-Many (1:*) relationship?
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
26
What does a Many-to-many relationship (*:*) do and what is it used with?
Used with composite models
| It removes the requirements for unique values in tables
27
What does cross filter direction determine?
Cross filter determines which way your filters flow in the relationships created
28
What are the 2 cross filter directions?
Single or Both (bidirectional)
29
What happens when single cross filter is selected?
When single cross filter is selected, the filters from the table on the "one side" will filter through the table on the "many" side.
30
What happens when both cross filter is selected?
When both cross filter is selected, filters from both tables will flow in both directions.
31
In addition to defining relationships, what else does the term Cardinality refer to?
Cardinality also refers to the number of distinct values in a column
32
What are the two scenarios where you will see Many-to-Many relationships?
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.
33
What is a bridge table in a Many-to-Many relationship?
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.
34
What are the 2 types of bridge tables?
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
35
What are the benefits of creating your own date table?
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
36
What are the ways you can create a date table?
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.
37
What does the CALENDAR function require to create a date table?
The CALENDAR function requires a start and end date.
38
What are the parameters of the CALENDAR function?
The parameters of the CALENDAR function are:
StartDate
EndDate
39
What does the CALENDARAUTO function do to create a date table?
The CALENDERAUTO function scans your data model for dates and returns an appropriate date range automatically
40
What is Data granularity?
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.
41
What is data model development?
Data model development is the enhancements you add to your data model after you've loaded your data & created relationships
42
How are calculated tables defined?
Calculated tables are defined by using DAX
43
When is it useful to create a calculated table?
1. Cloning Tables
2. Creating tables that are based on data from different data sources
3. Precalculating measures to improve report performance
44
What are sensitivity labels?
Sensitivity labels are information icons that users can apply in Power BI desktop or the Power BI Service.
45
Where can sensitivity labels be viewed?
Sensitivity labels can be viewed on reports or dashboards
46
How to turn on Sensitivity labels?
Have to be an admin. In Admin Portal go to settings and go to tenant settings & turn on Information Protection (select enable)
47
Where are classifications for sensitivity labels created and defined?
Classifications for sensitivity labels are created & defined in Microsoft 365 Compliance Centered
48
What are role-playing dimension tables?
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
How are cloned tables defined?
To clone a table, you use DAX.
| Ex: InvoiceDate = Date
50
What Power BI Feature is "UNION" similar to?
UNION acts similarly to appending tables in Power BI.
51
How does appending tables differ from using UNION to combine tables?
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
What other functions require tables to have the same # of columns?
EXCEPT & INTERSECT
53
What is row-level security (RLS) with Power BI?
RLS is used to restrict data access for given users. Filters restrict data at the row level. RLS DOES NOT restrict data access.
54
How are filters defined for row-level security?
Filters are defined within roles for RLS.
55
What are hierarchies?
A hierarchy is a set of fields categorized in a hierarchical way that one level is the parent of another level.
56
What tab is used to create hierarchies?
The model tab
57
What is used to create hierarchies?
Hierarchies can be created using existing columns of the same table only.
58
How are calculated columns created?
Calculated columns are created using DAX.
59
What data can be used to create calculated columns?
Data already loaded into the model or new data generated by DAX
60
What's the downside of calculated columns?
Calculated columns widens your tables, so having multiple calculated columns can contribute to slow performance
61
What are the are two ways to edit a relationship?
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
What are the two ways to edit a relationship?
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
What storage modes can you define roles for?
Import & DirectQuery
64
When using Analysis Services data model, where is RLS configured at?
RLS is configured in the source
65
Where are you able to view user roles?
In Power BI service & Desktop
66
What does dynamic row-level security do?
Dynamic Row Level security allows you to display different data to users within the same role.
67
What must the data model contain to utilize dynamic row-level security?
Your data model must contain the usernames of people who should have access to the relevant rows of data.
68
What two functions does Power BI have that allow you to get the user name of the current user for Dynamic Row-Level security?
USERNAME
| USERPRINCIPALNAME
69
What will the DAX function USERNAME return?
DOMAIN\User
70
What will the DAX function USERPRINCIPALNAME return?
UPN (like email) - when you have a column in your data that contains the email address you can filter using USERPRINCIPALNAME
71
What is Object-level security?
Object Level Security hides tables, columns, and measures but also secures them
72
What 3rd party application is used to implement object-level security?
Tabular Editor
73
What view of Power BI should you be in to implement the Q&A visual?
You must be in Report View to start using Q&A
74
Where can you add other words your organization uses to describe the data fields for Q&A?
In the "Field synonyms" option (under Modeling, Q&A setup)
75
What is an implicit measure?
Measures automatically created by Power BI such as average, sum, count, etc...
76
What is an explicit measure?
A measure you create using DAX.
77
What's the difference between measures and calculated columns?
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
What is the purpose of a variable?
A variable stores the result of an expression, which is passed as an argument to other expressions
79
What does the USERRELATIONSHIP function with CALCULATE activate?
USERRELATIONSHIP with CALCULATE activates an inactive relationship
80
What does the CROSSFILTER function with CALCULATE change?
the CROSSFILTER function with CALCULATE changes the filter direction
81
What DAX functions can you use as CALCULATE modifiers to ignore all filters?
ALL
ALLEXCEPT
ALLSELECTED
82
What can the function ALL remove filters from?
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
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?
You should remove filters from both columns to prevent unexpected results.
84
What are the parameters of the ALL function?
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
How does the ALL function work?
Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
86
How does the ALLEXCEPT function work?
Returns all the rows in a table except for those rows that are affected by the specified column filters.
87
What are the parameters of the ALLEXCEPT function?
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
How does the ALLSELECTED function work?
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
What are iterators?
Functions that take a table and go row by row, evaluating an expression for each row
90
What is row context?
the current row
91
What is filter context?
all applied filters
92
What table is required to use Time Intelligence functions?
A date table that has a data type column with unique values
93
What are the parameters of DATESYTD
Dates (The name of a column containing dates or a one-column table containing dates.)
YearEndDate (Optional) End of year date.
94
What does the DATESYTD function return?
Returns a set of dates in the year up to the last date visible in the filter context.
95
What does the TOTALYTD function return?
This function calculates the running total of sales for each year
96
Why is the calculate function so powerful?
It allows you to perform an expression while defining a custom filter that overrides the filter used by a visual
97
What are the parameters of the CALCULATE function?
Expression - The expression to be evaluated.
Filter - A boolean (True/False) expression or a table expression that defines a filter. (Optional & Repeatable)
98
What are the basic statistical functions used to enhance data?
```
SUM
AVERAGE
MEDIAN
COUNT
DISTINCTCOUNT
MIN
MAX
```
99
What are the 3 types of measures
Additive, Semi-Additive, & Non-Additive
100
What is an additive measure?
Measures that are aggregated by using the SUM function
101
What is a Semi-Additive measure?
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
What is a non-additive measure?
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
What does the OPENINGBALANCEMONTH function return?
**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
What does the CLOSINGBALANCEMONTH function return?
**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
What feature can you use to trace slow-performing visuals & see the DAX queries behind them?
Performance Analyzer
106
How does the Performance Analyzer work?
It works by recording traces and then shows you how long each visual took to render
107
What are two ways you can reduce the cardinality of columns?
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
What is the correct order to use the Performance Analyzer?
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
What is the simplest way to have a smaller-sized data model?
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
What is Data cardinality?
the uniqueness of the data.
111
What is Relationship cardinality
The joins between columns, e.g. one-many, many- many
112
In what ways can you reduce cardinality?
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
What feature is available for data summarization?
GroupBy
114
What are the steps required to manage aggregations?
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
What does High cardinality mean?
fewer duplicates (many unique values)
116
What does Low cardinality mean?
indicates more duplicates
