Model Data - 2 Flashcards

1
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).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
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.

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

What storage modes can you define roles for?

A

Import & DirectQuery

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

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

A

RLS is configured in the source

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

Where are you able to view user roles?

A

In Power BI service & Desktop

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
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

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

What will the DAX function USERNAME return?

A

DOMAIN\User

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
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

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

What is Object-level security?

A

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

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

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

A

Tabular Editor

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
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)

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

What is an implicit measure?

A

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

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

What is an explicit measure?

A

A measure you create using DAX.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
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

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

What does the USERRELATIONSHIP function with CALCULATE activate?

A

USERRELATIONSHIP with CALCULATE activates an inactive relationship

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

What does the CROSSFILTER function with CALCULATE change?

A

the CROSSFILTER function with CALCULATE changes the filter direction

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

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

A

ALL
ALLEXCEPT
ALLSELECTED

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
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)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
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

28
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.

29
Q

What are iterators?

A

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

30
Q

What is row context?

A

the current row

31
Q

What is filter context?

A

all applied filters

32
Q

What table is required to use Time Intelligence functions?

A

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

33
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.

34
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.

35
Q

What does the TOTALYTD function return?

A

This function calculates the running total for each year

36
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

37
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)

38
Q

What are the basic statistical functions used to enhance data?

A
SUM
AVERAGE
MEDIAN
COUNT
DISTINCTCOUNT
MIN
MAX
39
Q

What are the 3 types of measures?

A

Additive, Semi-Additive, & Non-Additive

40
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.

41
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

42
Q

What is an additive measure?

A

Measures that are aggregated by using the SUM function

43
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.

44
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.

45
Q

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

A

Performance Analyzer

46
Q

How are calculated tables defined?

A

Calculated tables are defined by using DAX

47
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
48
Q

What are sensitivity labels?

A

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

49
Q

Where can sensitivity labels be viewed?

A

Sensitivity labels can be viewed on reports or dashboards

50
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)

51
Q

Where are classifications for sensitivity labels created and defined?

A

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

52
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
53
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

54
Q

What is Data cardinality?

A

the uniqueness of the data.

55
Q

What is Relationship cardinality

A

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

56
Q

In what ways can you reduce data 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

57
Q

What feature is available for data summarization?

A

GroupBy

58
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
59
Q

What does High cardinality mean?

A

fewer duplicates (many unique values)

60
Q

What does low cardinality mean?

A

low cardinality indicates more duplicates

61
Q

What are Two examples of reducing data 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

62
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
63
Q

What is the advantage of aggregated tables?

A

Allows for much faster processing.

64
Q

Where to use parameters?

A

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.

65
Q

What is a query parameter?

A

Query Parameters allow users to easily make parts of the reports & data models depending on one or more parameters.