Brainscape's Knowledge GenomeTM

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


See full index

PL-300 > Model Data-1 > Flashcards

Model Data-1 Flashcards

1
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
2
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
3
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
4
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
5
Q

What storage modes are Many-to-many relationship (:) used in and what requirement is removed?

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
6
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
7
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
8
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
9
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
10
Q

How are tables treated when “both” cross filter is selected & what setting does it work best in?

A

When “both” is selected, both tables are treated as if they’re a single table. The “both” setting works well with a single table that has many lookup tables that surround it.

Ex: A sales actuals table with a look-up table for its department.

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

What does “single” for the cross filter direction mean?

A

Single is the most common, default direction, which means filtering choices in connected tables work on the table where values are being aggregated.

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

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

19
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
20
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.
21
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

Measures predefined DAX templates for the most common calculations

22
Q

What are Parent-Child hierarchies used for?

A

For employees charts of accounts, and organizations

23
Q

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

A

Node key and Parent Node Key

24
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

25
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

26
Q

What parameters are used in the PATH DAX Function

A

ID_ColumnName (Column containing the IDs for each row)

Parent_ColumnName

27
Q

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

A

One

28
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

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

30
Q

What function can you use for an inactive relationship?

A

USERRELATIONSHIP

31
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.
32
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.

33
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
34
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 a single date dimension table
35
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.
36
Q

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

A

The CALENDAR function requires a start and end date.

37
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

38
Q

What does calculated columns do as it relates to relationships between tables?

A

Calculated columns allow you to establish a relationship between tables when no unique fields exist.

39
Q

What is a hierarchy?

A

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

40
Q

What is needed to create a hierarchy & where can it be created?

A

Using existing columns in the same table

41
Q
A

PL-300 (7 decks)

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