Types of Dimensions

Dimension: A dimension table has two types of columns, primary keys and descriptive data. For example, Time and Customer.

Types of Dimensions

1. Slowly Changing Dimensions
2. Rapidly Changing Dimensions
3. Junk Dimensions
4. Stacked dimensions
5. Inferred Dimensions
6. Conformed Dimensions
7. Degenerate Dimensions
8. Role-Playing Dimensions
9. Shrunken Dimensions
10. Static Dimensions

Slowly Changing Dimensions

It depends on the business requirement, where any particular feature history of changes in the data warehouse is preserved. It is called a slowly changing feature, and a quality dimension is called a slowly changing dimension.

Rapidly Changing Dimensions

A dimension attribute change is a rapidly changing feature. If we do not need to track changes, rapid quality is not a problem. If you need to follow the changes, then using the standard slowly changing amplitude technique can cause massive amplitude size inflation. The solution moves the attribute to its dimension, with a different foreign key. The new dimension is called a rapidly changing size.

Junk Dimensions

A junk dimension fact table is a single table with the combination of attributes to avoid multiple foreign keys. Junk dimensions are created to manage foreign dimensions, that are created by rapidly changing dimensions.

Note that the junk dimension is always of type 0 (constant). The site's name has the word junk, usually after dim instead of the end. The data type is consistent, that is, for the Y / N column, it is either bit or CHAR (1), but neither INT nor VARCHAR (N) - my preference is CHAR (1). A junk dimension has not a business key.

Stacked dimension

A stacked dimension is a used where two or more dimensions are combined with an aspect:

A stacked dimension has one or two attributes and is always SCD type 0 (no update).

We see many types and status columns: Product Type, Customer Status, Store Type, Security Type, Security Class, Broker Type, etc. All the columns store to their respective dimensions because they are the properties of the dimension.

Deferred Dimension

When loading a fact record, a dimension record may not be ready yet. It is technically called an inferior member but is often called as sensible dimension.

Distorted Dimension

A dimension that is used in many places is called a distorted dimension. A conformal dimension can be used in a single database or multiple fact tables in multiple data warts or data warehouses.

Degenerate Dimension

A degenerate dimension happens when the dimension attribute is stored as part of a fact table, but not in a separate dimension table. These dimensions are keys for which there is no other attribute. In a data warehouse, they are often used as the result of a query to analyse the source of numbers that are collected in a report. We use these values to detect transactions in an OLTP system.

Role-playing Dimension

A role-playing dimension is one where the same dimension key includes more than one foreign key in the fact table. For example, a fact table contains foreign keys for both the ship date and delivery date. But the same dimension data attributes are applied to every foreign key. So, we can join the same dimension table for both foreign keys. Here, the date dimension is taking many roles to map the ship date and the delivery date.

Shrunken Dimension

A shrunken dimension is a subset of another aspect. For example, the order fact table includes a foreign key for the product. The target fact table can add a foreign key to the product category, which is in the product table but less granular-creating a small dimension table with a product category because its primary key is a way to deal with this situation of many grains. If the product dimension is a snowflake, then there is a separate table for the product category, a shrunken dimension.

Fixed Dimension

Static dimensions are not extracted from the real data source but created in the data warehouse context.