Dimension tables are containing all attributes of the corresponding fact table, that description of the objects in a fact table, in data warehousing a dimension is a collection of reference information about the measurable events.
Types of Dimension?
Type of Dimension Table.
1.Slowly Changing Dimension
2.Rapidly Changing Dimension
3.Junk Dimension
4.Inferred Dimension
5.Confirmed Dimension
6.Shrunken Dimension
7.Degenderated Dimension
8.Role Playing Dimension
What is SCD?
1. What is SCD?
SCD Attributes are changes over time whenever the business requirement what to update the attributes in the Dimension, this is called Slowly changing Dimension
We have three types of SCD available.
1. SCD Type – 1: Override the attributes without having History.
2. SCD Type – 2: History will be maintained with a new row.
3. SCD Type – 3: History will be maintained with a new column, new will be updated in the target column
2. Rapidly Changing Dimension
Rapidly Changing Dimensions are frequently the attributes are changing as per the business requirement, the dimension will keep on grow rapidly if you have designed the dimension to capture the changes as Type 2 dimension, this type of dimension highly impact the performance and maintenance as the dimension grows.
To resolve this issue, we have introduced the mini-dimension to capture the frequently changing attributes.
2. Junk Dimension
Junk Dimension is a special structure that stores the junk attributes. in general transaction data, we have certain Yes/No or Color ( Red, Green, Blue… ) or flags that are unrelated to the corresponding dimension. We have many types of junk dimension values available in Business, we can see those types of redundant data. Example (Product Code, Product Close, Product Model etc..)
4. Inferred Dimension
In general terms, we can say whenever the data early arrival at the FACT table before loading to the Dimension table, the same data early arrival at the Dimension table before loading to FACT table.
Such of record we can maintain separate columns in the Dimension table as an Inferred column with flag values is 0 and 1.
5. Confirmed Dimension
The confirmed Dimension is a dimension its used one or more data marts. Instead of duplicating the dimension table in a single DB instance. we can create one common dimension table and we can use it across datamart.
Example: DimDate Dimension table is that we can use globally in all datamarts instead of creating each dimension table.
We will discuss detailly in a separate article.
6. Shrunken Dimension
When the Dimension table does not directly maintain the relationship with the Fact table that’s called Shrunken Dimension Table.
Example: You have a Sale Fact table contains Product Id. Product id Foreign key of Product Dimension table and Product Dimension table holds the Product Category id. Here Product Category id is a foreign key of Dim Product Category Table.
Hence Product Category Dimension table is the Shrunken Dimension for the Sales Fact Table.
7. Degenderated Dimension
Degenerate dimension is a dimension key in the fact table that does not have its own dimension table, attributes exist in the fact table as a part of the primary key but do not have the corresponding dimensions.
The common example we can say is the invoice. Generally, for each order we will place one or more products, so for a single order number we have multiple purchased orders stored, and for each product, we have a purchase order Number, have look at the below table.
#Dimension #DimensionTable #SQL
Leave a Reply