Types Of Dimension Tables

Dimension Table

This is a table in a star schema of a data warehouse. Data warehouses are built using dimensional data models which consist of fact and dimension tables. The latter is used to describe dimensions. They contain dimension keys, values and attributes.

You can also watch the below video where our Data Warehousing Training expert is discussing each & every nuance of the technology in detail.

Types of Dimensions

Slowly Changing Dimensions– Dimension attributes that change slowly over a period of time rather than changing regularly is grouped as SCDs.  Attributes like name, address can change but not too often.

These attributes can change over a period of time and that will get combined as a slowly changing dimension. Consider an example where a person is changing from one city to another. Now there are 3 ways to change the address;

Type 1  is to over write the old value, Type 2 is to add a new row and Type 3 is to create a new column.

Type 1

The advantage of type 1 is that it is very easy to follow and it results  in huge space savings and hence cost savings. The disadvantage is that no history is maintained.

Type 2

The advantage of type 2 is that the complete history is maintained. The only disadvantage lies in the huge space allocation because the entire history right from the start has to be maintained.

Type 3

The best approach could be to add a new column where you add two new columns. In this case keeping a tracking of the history becomes very easy.

Conformed Dimension- This is used in multiple locations. It helps in creating consistency so that the same can be maintained across the fact tables. Different tables can use the table across the fact table and it can help in creating different reports.

For example, there are two fact tables. Fact table 1 is to determine the number of products sold by geography. This table will calculate just the number of products by geography and fact table 2 will determine the revenue generated by customer. Both are dependent on the product which contains product Id, name and source.

There is the geography dimension and customer dimension which are being shared by two fact tables. The revenue fact gives the revenue generated by both the geography and the customer, while the product units fact gives number of units sold in the geography to a customer.

Degenerate Dimension– A degenerate dimension is when the dimension attribute is stored as part of the fact table and not in a separate table. Product id comes from product dimension table. Invoice number is a standalone attribute and has no other attributes associated with it. An invoice number can be crucial since the business would want to know the quantity of the products.

Junk Dimension– It is a single table with a combination of different and unrelated attributes to avoid having a large number of foreign keys in the fact table. They are often created to manage the foreign keys created by rapidly changing dimensions.

Role play dimension– It is a table that has multiple valid relationships with a fact table. For example, a fact table may include foreign keys for both ship date and delivery date. But the same attributes apply to each foreign key so the same tables can be joined to the foreign keys.

Got a question for us? Please mention them in the comments section and we will get back to you. 

Related Posts:
Get started with Data Warehousing