Data Warehousing (10 Blogs) Become a Certified Professional

Types Of Dimension Tables

Last updated on Jan 29,2021 118.8K Views


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

Types Of Dimension Table

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.

SCD-type-3 - Dimension Tables - EdurekaConformed 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.

Types of Dimension tables

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. 

edureka-logoRelated Posts:
Get started with Data Warehousing

Comments
1 Comment
  • The latest study shows that on average 75% people are involved into internet activities. Web world-wide has become bigger and better and making a lot of opportunities. Working at home on line jobs are becoming poplar and developing individual’s lives. Precisely why it is actually in demand? Mainly because it allows you to do the job from anywhere and any time. You receive much more time to invest with people you care about and can plan out journeys for holidays. People are generating nice income of $14000 each week by utilizing the effective and smart methods. Performing right work in a right direction will always lead us in the direction of success. You can start to get paid from the first day once you have a look at our website. >>>>> CONTACT US TODAY

Join the discussion

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.

image not found!
image not found!

Types Of Dimension Tables

edureka.co