Model Data-1 Flashcards
What are the different cardinality options in Power BI?
One-to-One
One-to-Many
Many-to-One
Many-to-Many
What is a Many-to-one relationship (*.1)
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
What is a One-to-one (1:1) relationship?
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
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
What storage modes are Many-to-many relationship (:) used in and what requirement is removed?
Used with composite models
It removes the requirements for unique values in tables
What does cross filter direction determine?
Cross filter determines which way your filters flow in the relationships created
What are the 2 cross filter directions?
Single or Both (bidirectional)
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.
What happens when both cross filter is selected?
When both cross filter is selected, filters from both tables will flow in both directions.
How are tables treated when “both” cross filter is selected & what setting does it work best in?
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.
What does “single” for the cross filter direction mean?
Single is the most common, default direction, which means filtering choices in connected tables work on the table where values are being aggregated.
What are the 3 most common schemas?
- Flat (fully denormalized) schema
- Star schema
- Snowflake schema
What is a flat schema?
All attributes are fully demoralized into a single table
What are the downsides of flat schemas?
- 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
What are the two table types Star Schemas use?
Fact Table and Dimension Table
What is a fact table & what type of data does it contain?
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
What data do dimension tables contain?
Dimension tables contain descriptive attributes about data in the fact tables.
It has a unique identifier - a key column & descriptive columns.
What is a snowflake table and how is it different from the star schema?
The snowflake schema is similar to the star schema except it can have dimension tables that “snowflake” from other dimensions.
What are the different table properties?
- 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
What are the column properties?
- 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.
Define quick measures
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
What are Parent-Child hierarchies used for?
For employees charts of accounts, and organizations
What are the 2 nodes parent-child hierarchies are defined by?
Node key and Parent Node Key
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)
PATH Function
What parameters are used in the LOOKUPVALUE DAX Function
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
What parameters are used in the PATH DAX Function
ID_ColumnName (Column containing the IDs for each row)
Parent_ColumnName
How many relationships between two tables can be active at one time?
One
What does the USERRELATIONSHIP function specify?
The USERRELATIONSHIP specifies the relationship to be used in a specific calculation as the one that exists between ColumnName1 and ColumnName2
What are the parameters of the USERRELATIONSHIP function?
ColumnName1 - the name of an existing column (usually represents the many side)
ColumnName2 - the name of an existing column (usually represents the one side)
What function can you use for an inactive relationship?
USERRELATIONSHIP
What are the two scenarios where you will see Many-to-Many relationships?
- Many-to-Many relationships between dimensions
Ex: 1 client may have multiple accounts & 1 account can belong to multiple clients - 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.
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.
What are the 2 types of bridge tables?
- 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)
- 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
What are the benefits of creating your own date table?
- You can use a calendar other than Gregorian
- You can have weeks in the calendar
- you can filter multiple fact tables by a single date dimension table
What are the ways you can create a date table?
- Import a date table (created in Excel)
- CALENDARAUTO – A simple, fully automated DAX expression.
- CALENDAR – A DAX expression where you define a start and end date.
- M Code – In Power Query Editor, you can paste in a common template to generate
dates.
What does the CALENDAR function require to create a date table?
The CALENDAR function requires a start and end date.
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
What does calculated columns do as it relates to relationships between tables?
Calculated columns allow you to establish a relationship between tables when no unique fields exist.
What is a hierarchy?
A set of fields categorized in a hierarchical way that one level is the parent of another level.
What is needed to create a hierarchy & where can it be created?
Using existing columns in the same table
