To set up a data model, it is important to make well-considered choices. The following structure can be used:
- Normalizing tables
- Defining tables in facts and dimensions
- Establishing relationships
- Defining the architecture of the semantic model
Normalizing tables
Normalization
Normalization is a method for organizing data in a relational database. Let’s say a table has a column with (unique) values, for example, the column product key. Each product key has its descriptive values in corresponding columns, such as color, size, and category. All data in one table can cause confusion and redundant data.
Modifying and/or splitting a table so that it only retains key columns and thereby adding the descriptive values as new tables to the model is called normalization. Redundant data has been minimized and the semantic model will be clearer and easier to understand.
Denormalization
In the same case the new table is denormalized. After all, this is where all descriptive values are placed in a table. Denormalization is the state in which all data, including descriptive values, are in a single table rather than in separate tables. The column below has been denormalized.
In the pursuit of an efficient data model, it is recommended to normalize the data model as much as possible. This reduces the chance of redundant data and Many-to-Many (*:*) relationships.
Defining tables in fact and dimension
In the process of normalization, large tables are cut up into several smaller tables. This creates a split between the dimension table and the fact table.
Dimension table
A dimension table is a table in a star, snowflake, or starflake model that stores descriptive values about the facts. Dimension tables contain data that can be used to search or filter. In a dataset with data on car sales, the color, size, and brand are listed in a dimension table. They often have a key column with unique values and have a relationship with the fact table by means of one-to-many (1:*). In a star model, dimension tables are often denormalized, while in a snowflake model, the tables are often normalized. See also 4 – The architecture.
Fact table
A fact table is a table in a star, snowflake or starflake model that stores data that can be used to calculate or measure. In a dataset on the sale of cars, the turnover and selling price are shown in the fact table. These often do not contain any unique values and are attached to the dimension table by means of a many-to-one (*:1).
In relation to Power BI
In the pursuit of an efficient data model, it is recommended to always include a distinctive value in the name of the table. This can be a d_ for a dimension or a f_ for a fact table. As a result, the developer always has a quick insight into the purpose of the table, which table can be used as lookup or filter values and which table can be used for measurement or calculation.
Establishing relationships
In the process of normalization, large tables are cut up into several smaller tables. This creates a split between the fact table and the dimension table. They communicate with each other through relationships. In order to properly understand relationships, a distinction can be made between two parts: cardinality and filter direction.
Cardinality
Cardinality refers to the number of values in a table that can match the number of values in another table. In Power BI terms, the following cardinalities can be distinguished:
Many-to-one (*:1)
Many-to-one means that a value can appear more than once in the many column (fact table). In the one-column (often the dimension), the value appears only once. This is the most common relationship and will produce the best performance. After all, Power BI only needs to look up and match one value. In principle, this relationship is always advised.
One-to-one (1:1)
One-to-one means that in the many column (fact table) a value appears only once. In the one column (often the dimension), the value only appears once. This relationship is rare, and if it does, you may wonder if normalization is really necessary. After all, adding an extra data table also costs storage.
Many-to-Many (*:*)
Many-to-Many means that in the many column (fact table) values are not distinct. In the one column (often the dimension), the value also appears several times. Although this form of relationship cannot always be prevented, it is recommended to avoid it where possible. In this form of relationship, Power BI will compare everything to everything and multiply the number of queries in the background. This relationship can cause performance issues.
If this relationship can’t be avoided because of requirements, try to create an intermediate table. An intermediate table is a table of unique values that sits between the fact table and the dimension table. The intermediate table has a many-to-one relationship with filter direction – both with the dimension tabel and a many-to-one relationship with the filter direction – single with the fact table.
Filter direction
A relation connects two tables. This relationship allows them to communicate with each other. With filter direction, one can determine whether this communication comes from a table or from both tables. Filter direction can be compared to a door. Based on the direction, it is determined which table is allowed to pass through the door in order to be able to communicate.
Single
With the filter direction ‘single’, the door is open for the one-column (often the dimension). This means that the many column (the fact table) cannot communicate with the one column (often the dimension). In this situation, other dimension tables, which also affect the fact table, do not affect the current dimension table.
Both-relationship
With a both-relationship, both the many column (fact table) and the one column (dimension table) can communicate with each other. It is advised to avoid this form of relationship as much as possible.
In a star, snowflake, or starflake model, a fact table is affected by multiple dimension tables. If the option to have the fact table interact with the dimension table is open, there is a chance that other dimension tables will influence the current dimension table via the fact table. In turn, the current dimension table can also affect the fact table, which greatly increases the likelihood of unclear or erroneous data.
The exception in which a filter direction ‘both’ can be used is if one of the two tables, involved in the relationship, does not have or will not have any other relationships. Or in the case of using an intermediate table where the intermediate table has both a two-direction with the facts table and a single-direction with the dimension. The intermediate table cannot communicate with the dimension table, and thus the influence of the two relationship ends.
Single with filter direction
In the case of a many-to-many relationship, Power BI lacks unique values. In this case, Power BI will automatically choose a both-relationship. The filter direction can then be selected manually. Here you can indicate which table is allowed to communicate with the other table. In the case of many-to-many relationships, it is always recommended to determine the filter direction to avoid both-relationships.
The definition of the architecture of the semantic model in star, snowflake or combination
In the process of normalization, large tables are cut up into several smaller tables. The degree of normalization ultimately determines the architecture of the semantic model.
In relation to Power BI, we recognize three model architectures. The star model, the snowflake model and the starflake model. Each model has its own advantages and disadvantages and is highly dependent on the extent to which standardization is possible.
Star model
In the star model, the fact table is normalized and the dimension tables are denormalized. This fact table has a relationship with different dimension tables, often through the one-to-many relationship. Unlike a snowflake model, the dimension tables are independent of each other. So they have no relationship with each other.
The advantages of a star model are:
Design:
- The star model has a simple and easy-to-understand design.
Query:
- A quick performance because of the many-on-one relationships.
Data:
- Suitable for large amounts of data.
The disadvantages of a star model are:
Redundant data:
- The dimension tables in a star model are often denormalized. This means that the dimension tables in a star model stores its redundant data in its dimension tables. This can be confusing with a large data model.
No Complex Relationships:
- The star schema has a limited ability to represent complex relationships between fact and dimension.
Snowflake model
A snowflake model is almost completely normalized. All redundant data (including the redundant data in dimension tables) is spread across multiple dimension tables. In contrast to a star model, the dimension tables in a snowflake model can have a relationship with each other.
The advantages of a snowflake model are:
Redundant data:
- A normalized data structure reduces the redundant data.
Complex relationships:
- Enables more complex relationships between data.
Searches:
- Good for more structured, predictable searches.
The disadvantages of a snowflake model are:
Design:
- The more complex data structure can be more difficult to understand and use.
Query:
- Multiple relationships between tables can result in slower query performance
Storage:
- Requires more storage due to the larger number of tables.
Starflake model
A starflake model is a combination of a star model and a snowflake model. In a starflake model, only a few dimension tables are denormalized. A starflake model tries to combine the advantages of star model and snowflake.
Finally
In order to arrive at a good and efficient data model, it is important to understand the proces of normalization in combination with different options of relationships. In essence, this means dividing large summary tables (facts) into smaller lookup tables (dimension). The degree of normalization often determines the design of the data model. A star model often has a normalized fact table and denormalized dimension tables. In a snowflake model, the dimension tables are often normalized as well. The aim is to normalize a data model as much as possible, but the possibility of doing so is highly dependent on the input (data).
A right or wrong data model is difficult to define. A data model is highly dependent on the quality of the input (data) you put into it. This can vary from company to company and as a result, there is no single hard truth about a correct data model. In order to arrive at a good data model in Power BI, it is very important that the right components are understood and that well-considered choices are made.
