Model Data - 2 Flashcards
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).
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.
What storage modes can you define roles for?
Import & DirectQuery
When using Analysis Services data model, where is RLS configured at?
RLS is configured in the source
Where are you able to view user roles?
In Power BI service & Desktop
What does dynamic row-level security do?
Dynamic Row Level security allows you to display different data to users within the same role.
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.
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
What will the DAX function USERNAME return?
DOMAIN\User
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
What is Object-level security?
Object Level Security hides tables, columns, and measures but also secures them
What 3rd party application is used to implement object-level security?
Tabular Editor
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
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)
What is an implicit measure?
Measures automatically created by Power BI such as average, sum, count, etc…
What is an explicit measure?
A measure you create using DAX.
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.
What is the purpose of a variable?
A variable stores the result of an expression, which is passed as an argument to other expressions
What does the USERRELATIONSHIP function with CALCULATE activate?
USERRELATIONSHIP with CALCULATE activates an inactive relationship
What does the CROSSFILTER function with CALCULATE change?
the CROSSFILTER function with CALCULATE changes the filter direction
What DAX functions can you use as CALCULATE modifiers to ignore all filters?
ALL
ALLEXCEPT
ALLSELECTED
What can the function ALL remove filters from?
- One or more columns from the same table
- An entire table
- The whole data model (when ALL is used with no parameters)
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.
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
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.
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.
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
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.
What are iterators?
Functions that take a table and go row by row, evaluating an expression for each row
What is row context?
the current row
What is filter context?
all applied filters
What table is required to use Time Intelligence functions?
A date table that has a data type column with unique values
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.
What does the DATESYTD function return?
Returns a set of dates in the year up to the last date visible in the filter context.
What does the TOTALYTD function return?
This function calculates the running total for each year
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
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)
What are the basic statistical functions used to enhance data?
SUM AVERAGE MEDIAN COUNT DISTINCTCOUNT MIN MAX
What are the 3 types of measures?
Additive, Semi-Additive, & Non-Additive
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.
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
What is an additive measure?
Measures that are aggregated by using the SUM function
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.
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.
What feature can you use to trace slow-performing visuals & see the DAX queries behind them?
Performance Analyzer
How are calculated tables defined?
Calculated tables are defined by using DAX
When is it useful to create a calculated table?
- Cloning Tables
- Creating tables that are based on data from different data sources
- Precalculating measures to improve report performance
What are sensitivity labels?
Sensitivity labels are information icons that users can apply in Power BI desktop or the Power BI Service.
Where can sensitivity labels be viewed?
Sensitivity labels can be viewed on reports or dashboards
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)
Where are classifications for sensitivity labels created and defined?
Classifications for sensitivity labels are created & defined in Microsoft 365 Compliance Centered
What is the correct order to use the Performance Analyzer?
- Add a blank report page
- Restart Power BI Desktop to clear the cache
- StartrecordinginPerformanceAnalyzer
- Interact with the visuals
- Stop recording and review the results
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
What is Data cardinality?
the uniqueness of the data.
What is Relationship cardinality
The joins between columns, e.g. one-many, many- many
In what ways can you reduce data 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
What feature is available for data summarization?
GroupBy
What are the steps required to manage aggregations?
- Create a new table name ‘Sales Agg’ containing columns to hold aggregate data
- Right-click on the ‘Sales’ table and select Manage aggregations
- Select the ‘Sales Agg’ table as the aggregation table
- Select the Summarization type
- Select the table and column from the ‘Sales’ table
What does High cardinality mean?
fewer duplicates (many unique values)
What does low cardinality mean?
low cardinality indicates more duplicates
What are Two examples of reducing data 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
What are The steps required to manage aggregations?
- Create a new table name ‘Sales Agg’ containing columns to hold aggregate data
- Right-click on the ‘Sales’ table and select Manage aggregations
- Select the ‘Sales Agg’ table as the aggregation table
- Select the Summarization type
- Select the table and column from the ‘Sales’ table
What is the advantage of aggregated tables?
Allows for much faster processing.
Where to use parameters?
Step argument: You can use a parameter as the argument of multiple transformations driven from the user interface (UI).
Custom Function argument: You can create a new function from a query and reference parameters as the arguments of your custom function.
What is a query parameter?
Query Parameters allow users to easily make parts of the reports & data models depending on one or more parameters.
