Model Data Flashcards
Describe the star schema
The star schema is often used and it is composed of a FACT table connected via relationships to one or more DIM tables. The visual representation of the schema resembles a star. The fact table contains much more rows than the DIM tables. The FACT table usually contains repeated values in the form of foreign keys while the DIM tables contain only unique values.
How are the DIMENSION and FACT tables related to each other in a star schema?
These two types of tables are related by means of a relationship using primary and foreign keys.
How does the number of tables related to the size of the data model?
The greater the number of tables, the bigger the size of the model, and usually the less intuitive is for other users to understand the model.
What is the DIMENSION table in a star schema?
The DIM table contains information about a particular “trait” of the rows contained on the FACT table. The DIM tables allow one to group and filter the rows on the FACT table.
What is the FACT table in a star schema?
The FACT table records all information about the “fact” we are interested in, for example, sales, orders, etc.
What is a primary key in a table?
It is a unique, non-null value in a given table that allows one to unequivocally identify a particular row on the table.
What is a foreign key in a table?
It is a column on table A that provides a link to another table B, effectively creating a relationship between A and B This foreign key on A refers to primary key on B.
Does adding a new calculated column impacts the size of the .pbix file?
Calculated columns are materialized in the .pbix Power BI file extension, meaning that each time you add a calculated column, you are increasing the size of the overall file. Having too many calculated columns will slow performance and will cause you to reach the maximum Power BI file size sooner.
What are three possible ways to create a calculated column?
- Create the column in the source query when you get the data, for instance, by adding the calculation to a view in a relational database.
- Create a custom column in Power Query.
- Create a calculated column by using DAX in Power BI desktop.
What is usually the preferred approach to create a calculated column?
Generally, the earlier you can create a column, the better. It is not considered an optimal practice to use DAX for calculations if you can use a different mechanism. The DAX calculated column does not compress as well as the other methods. The other column types do get compressed, which makes the .pbix file smaller and the performance is usually faster.
What is the fundamental difference between a calculated column and a measure?
The fundamental difference between a calculated column and a measure is that a calculated column creates a value for each row in a table. For example, if the table has 1,000 rows, it will have 1,000 values in the calculated column. Calculated column values are stored in the Power BI .pbix file. Each calculated column will increase the space that is used in that file and potentially increase the refresh time.
Measures are calculated on demand. Power BI calculates the correct value when the user requests it. Measures do not add to the overall disk space of the Power BI .pbix file.
How to configure the right way to aggregate a given column in a table?
Is it possible to change the aggregation behaviour for a measure?
It’s not possible to modify the aggregation behavior of a measure.
