When we are talking about Business Intelligence (BI) we immediately think about business strategy and that is because BI helps any type of company to reach their goals. A dimension is an important part of all this great Business Intelligence´s world.
When we are modeling a multidimensional Data warehouse we have to deal with different things that depend on the specific context and company´s needs. Thus, in this post I am going to expose 3 types of dimension we have when we work with data marts or data warehouse. In the headline of this post I have wrote Data warehouse or data mart that is because of the differences that Ralph Kimball and Bill Inmon have when they defined the word: Data mart (I will write about it later on) , but at the end, we have dimension to work with.
The following part describes the differences between the three types of dimensions:
Type 1: You use it, when you don’t need to have all the history changes of the data. It means that in the future you can’t see the changes that a specific row has had in a period of time. In this case there is not way to find out the old value(s) because it has only the current values of a specific field. Normally, you use UPDATE to manage it. In other words you replace the old value with the new value.
Example: The Row 2 holds specific values in each field. We will suppose the Field 3 changed its value and now it will be:
| New value (Row 2, field 3) |
Table 1 shows us its values before any change is applied.
| Field 1 | Field 2 | Field 3 | Field 4 | Field 5 | |
| Row 1 | |||||
| Row 2 |
Table 1 Dimension before the change
Table 2 shows us the results after we have made the change (Update).
| Field 1 | Field 2 | Field 3 | Field 4 | Field 5 | |
| Row 1 | |||||
| Row 2 |
Table 2 Dimension after the change (Type 1)
Type 2: When is important to have all changes in the dimension this one is the type you should use. In this case you manage it adding a new record if any value has changed. Here you use INSERT instead of update because you need to keep all the information change during a period of time. The old values don’t be replaced because the new row contains the new values and if your intention is compare that information you can easily retrieve them. This kind of dimension grows ’vertically’
Example: The Row 2 holds specific values in each field. We will suppose the Field 3 changed its value and now it will be:
| New value (Row 2, field 3) |
Table 1 shows us its values before any change is applied.
| Field 1 | Field 2 | Field 3 | Field 4 | Field 5 | |
| Row 1 | |||||
| Row 2 |
Table 1 Dimension before the change
Table 2 shows us the results after we have made the change (INSERT). In this case we have added a new record: (Row 3) in the table with the same values in the fields that have not changed (1,2,4,5) and the new value in the field that has changed (3).
| Field 1 | Field 2 | Field 3 | Field 4 | Field 5 | |
| Row 1 | |||||
| Row 2 | |||||
| Row 3 |
Table 2 Dimension after the change (Type 2)
Type 3: In this case the dimension grows ‘horizontally’, it means that you keep the new values adding a new field in the table and so on.
Example: The Row 2 holds specific values in each field. We will suppose the Field 3 changed its value and now it will be:
| New value (Row 2, field 3) |
Table 1 shows us its values before any change is applied.
| Field 1 | Field 2 | Field 3 | Field 4 | Field 5 | |
| Row 1 | |||||
| Row 2 |
Table 1 Dimension before the change
Table 2 shows us the results after we have made the change. In this case we have added a new field: (Field 6) in the table and the new value in the field that has changed.
| Field 1 | Field 2 | Field 3 | Field 4 | Field 5 | Field 6 | |
| Row 1 | ||||||
| Row 2 |
Table 2 Dimension after the change (Type 3)