Brainscape's Knowledge GenomeTM

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


See full index

PL-300 > Modeling the Data > Flashcards

Modeling the Data Flashcards

1
Q

By using DAX, you can add 3 types of calculations to your data model:

A
  • Calculated tables
  • Calculated columns
  • Measures
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

How can DAX be used to enforce filters over model tables

A

By using RLS

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

The formula can duplicate or transform existing model data or create a series of data to produce a new table and always imported into your model?

A

Calculated tables

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

Cons of Calculated Tables

A

Increases model storage size and can prolong data refresh time

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

Calculated tables are useful in

A
  • Date tables
  • Role playing dimensions
  • What-if analysis
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What table is required to apply special time filters known as time inteligence

A

Date tables

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

When your source data doesnt include a date table, you can create one as calculated tables by using the

A

CALENDAR or CALENDARAUTO function

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

In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2011.
CALENDARAUTO() will return

A

all dates between January 1, 2010 and December 31, 2011.

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

In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2011.
CALENDARAUTO(3) will return

A

CALENDARAUTO(3) will return all dates between April 1, 2010 and March 31, 2012.
*Hint: July(7)-3 = Apr(4)
June(6) -3 = March(3)

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

What-if parameters allow report users to

A

select or filter by values that are stored in the calculated table. For example, awhat-ifparameter could allow the report user to select a hypothetical currency exchange rate, and a measure could divide revenue values (in a local currency) by the selected rate.

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

Formula is evaluated for each table row and it returns a single value

A

Calculated Columns

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

Concerned with achieving summarisation over model data and returns a single value.

A

Measures

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

_____ are evaluated at query time and their results are never stored in the model

A

Measures

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

Compound Measure

A

When a measure references one or more measures, it’s known as compound measure

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

Implicit vs Explicit Measures

A
  • Explicit: model calculations that are written in DAX
  • Implicit measures: columns that can be summarised by visuls in simplistic ways like counts, sum, minimum, maximum, etc. Implicit measures are noted by $\sum$
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

The DIVIDE function

A

TheDIVIDEfunction automatically handles division by zero cases. If an alternate result isn’t passed in, and the denominator is zero or BLANK, the function returns BLANK.

17
Q

You’re developing a data model in Power BI Desktop. You’ve just added a date table by using the CALENDARAUTO function. You’ve extended it with calculated columns, and you’ve related it to other model tables. What else should you do to ensure that DAX time intelligence calculations work correctly?
A. Add time intelligence measures to the date table
B. Mark as a Date table
C. Add a fiscal hierarchy
D. Add a date column

A

Mark as a Date table so that Power BI can correctly filter its dates.

18
Q

Difference between calculated column and a measure

A
  • Calculated columns extend a table with a new column, while measures define how to summarize model data.
  • Calculated columns are evaluated by usingrow contextat data refresh time, while measures are evaluated by usingfilter contextat query time.
  • Calculated columns (in Import storage mode tables) store a value for each row in the table, but a measure never stores values in the model.
  • Calculated columns (like any column) can be used to filter, group, or summarize (as an implicit measure), whereas measures are designed to summarize
19
Q

Row context

A

The formula for a calculated column is evaluated for each table row.

  • Row context doesn’t extend beyond the table. If your formula needs to reference columns in other tables, you have 2 options
    • f the tables are related, directly or indirectly, you can use the[RELATED]or[RELATEDTABLE]DAX function. TheRELATEDfunction retrieves the value at the one-side of the relationship, while theRELATEDTABLEretrieves values on the many-side. TheRELATEDTABLE`function returns a table object.
  • When the tables aren’t related, you can use theLOOKUPVALUE
20
Q

Using DAX Time Intelligence Functions …

A

simplifies the task of modifying date filter context

21
Q

Date Table Requirement:

A
  • It must have a column of data type Date (or date/time), known as thedate column.
  • The date column must contain unique values.
  • The date column must not contain BLANKs.
  • The date column must not have any missing dates.
  • The date column must span full years. A year isn’t necessarily a calendar year (January-December).
  • The date table must be indicated as a date table
22
Q

DATESYTD

A

Returns a single-column table that contains dates for the year-to-date (YTD) in the current filter context.

23
Q

DATESBETWEEN

A

Returns a table that contains a column of dates that begins with a given start date and continues until a given end date.

24
Q

DATESINPERIOD

A

Returns a table that contains a column of dates that begins with a given start date and continues for the specified number of intervals.

25
Q

Define Rules

A

Row context means the expression is evaluated for each row using the column values of that row. When the expression returns TRUE, the user can “see” the row. You can define rules that are either static or dynamic

26
Q

‘Region’[Region] = “Midwest”

A

Static Rules

27
Q

Dynamic Rules

A

uses specific DAX functions that return environmental values

28
Q

USERPRINCIPALNAME

A

USERPRINCIPALNAME function always returns the user in the user principal name format.

29
Q

Object level security (OLS) can restrict…

A

access to specific tables and columns and secure their metadata

30
Q

Restrictions in OLS

A
  • You cannot mix RLS and OLS in the same role
  • If you need to apply RLS and OLS in the same model, create separate roles dedicated to each type.
31
Q

Good Modeling Practices on the number of datasets, number of roles, rules, model design, USERPRINCIPALNAME/USERNAME

A
  • Strive to define fewer datasets (data models) with well-designed roles.
  • Strive to create fewer roles by using dynamic rules. A data-driven solution is easier to maintain because you don’t need to add new roles.
  • When possible, create rules that filter dimension tables instead of fact tables. It will help to deliver faster query performance.
  • Validate that the model design, including its relationships and relationship properties, are correctly set up.
  • Use theUSERPRINCIPALNAMEfunction instead ofUSERNAMEfunction. It provides consistency when validating the roles in Power BI Desktop and the Power BI service.
  • Rigorously validate RLS and OLS by testing all roles.
  • Ensure that the Power BI Desktop data source connection uses the same credentials that will be applied when set up in the Power BI service.
32
Q

Star Schema Design

A
  • presents a user-friendly structure and supports high performance analytic queries
  • it classifies model tables as either fact or dimension.
  • The fact table forms the center of a star and the dimension table represents the points of the star
33
Q

Fact Tables

A
  • store an accumulation of rows that represent observations or event sthat record a specific business activity
  • It is not GCP to relate a fact table directly to another fact table
34
Q

Dimension Tables

A
  • describe your business entities
  • The columns in dimension tables allow filtering and grouping of fact table data
  • Each dimension table must have a unique column which is referred to as its key column
  • Dimension tables can be used to filter multiple fact tables and fact tables can be filtered by multiple dimension tables.
35
Q

Analytic Queries

A

produces a result from a data model. Each power bi visual in the background submits an analytic query to Power BI to query the model

36
Q

An analytic query has 3 phases that are implemented in the following order:

A
  1. Filter
  2. Group: divides query results into groups
  3. Summarise: produces a single value result. Typically numeric columns are summarised by using summarisation methods. These methods are simple summarisation.
37
Q

3 Different Model Resources that are fields to configure visuals which in the background configures an analytic query:

A
  • Columns: Filter, Group and Summarise
  • Hierarchy levels: Filter and Group
  • Measures: Filter and Summarise (cannot be used to group data)
38
Q

Table Storage Mode:

A
  • Import– Queries retrieve data that’s stored, or cached, in the model.
  • DirectQuery– Queries pass through to the data source.
  • Dual– Queries retrieve stored data or pass through to the data source. Power BI determines the most efficient plan, striving to use cached data whenever possible.

PL-300 (4 decks)

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