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
