What are dimensions? And their types

Question

Accepted Answer

Dimensions are attributes or characteristics of data that can be used for analysis and reporting.

Dimensions are used in data warehousing and business intelligence to categorize and organize data.
Types of dimensions include time, geography, product, customer, and organization.
Dimensions can be hierarchical, with subcategories and levels of detail.
Dimensions are often used in conjunction with measures, which are the numerical values being analyzed.

Accepted Answer

Dimension Table:

In data warehouse we store the data in two forms 1. Dimension table 2. Fact table.

Dimension Table:

 It is detail descriptive data about the business.

 It contains Primary Key (PK)

 It contains Texture data

Types of Dimension Table

1. SCD (Slowly Changing Dimension)

2. Conformed Dimension Table

3. Junk Dimension Table

4. Degenerative Dimension Table

5. Role-play Dimension Table

1. SCD (Slowly Changing Dimension)

The values or data’s of the table are changes over a long period hence we call as slowly changing dimension.

There three types of SCD

a. SCD1, b. SCD2, c. SCD3

a. SCD type 1: In which we can maintain only current data. This can be achieved by overwrite on old data.

b. SCD type 2: In which we can maintain current and all or complete historical data. This can be achieved by inserting as new row.

c. SCD type 3: In which we can maintain current and partial historical data. This can be achieved by inserting as pair of attribute.

2. Conformed Dimension Table

It is the dimension table that can be shared by multiple fact tables.

3. Junk Dimension Table

These are the unwanted data’s attribute in a fact table. These are nothing but the collection of transactional code flag which are not same as other dimension table.

Ex: Mode of Payment in which

Cash

Cheque

Credit

4. Degenerative Dimension Table

In which there is no physical separate dimension table available, but the key (PK) is available in fact table.

Ex: Purchase Order Number, Policy Number, Order Number

5. Role-play Dimension Table

A dimension table is having unique label for all business aspects in a same fact table. or

A dimension table rapidly used for all attributes with unique label.

Ex: Online Purchase in which the date dimension

In above all attribute the date is unique (No duplicate) so that time dimension table is a role-play dimension table.

What are dimensions? And their types

Top Cognizant ETL Tester interview questions & answers

Popular interview questions of ETL Tester

Top HR questions asked in Cognizant ETL Tester