Snowflake schema

The snowflake schema is similar to the star schema, except it can have some dimensions that “snowflake” from other dimensions. You can see an example in Figure 2-2.

FIGURE 2-2 Snowflake schema with State Province snowflaking from the City table

In the Wide World Importers example, if we loaded the State Province query, the data model could be a snowflake schema. This is because the State Province table is related to the City dimension table, which in turn is related to the Sale fact table.

Snowflake schemas can be beneficial when there are fact tables that have different grains.

Need More Review? Dimensional Modeling

In addition to fact and dimension tables, there are other types of tables such as factless facts, junk, and degenerate dimensions. For more information, see “Understand star schema and the importance for Power BI” at https://docs.microsoft.com/en-us/power-bi/guidance/star-schema.

Configure table and column properties

Both tables and columns have various properties you can configure, and you can do it in the Model view. To see the properties of a column or a table, select an object, and you will see its properties in the Properties pane.

Table properties

For tables, depending on the storage mode, you can configure the following properties:

  • Name Enter the table name.
  • Description This property allows you to add a description of the table that will be stored in the model’s metadata. It can be useful when building reports because you can see the description when you hover over the table in the Fields pane.
  • Synonyms These are useful for the Q&A feature of Power BI, which we review in the next skill section. You can add synonyms so that the Q&A feature can understand that you’re referring to a specific table even if you provide a different name for it.
  • Row label This property is useful for both Q&A and featured tables, and it allows you to select a column whose values will serve as labels for each row. For example, if you ask Q&A to show “sales amount by product” and you select the Product Name column as the Row label of the Product table, then Q&A will show sales amount for each product name.
  • 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 that it disappears from the Fields pane.
  • Is featured table This property allows you to make a table featured, which will allow it to be used in Excel in certain scenarios.
  • Storage mode This property may be set to Import, DirectQuery, or Dual, as we covered in the previous chapter.