|
1) SCD (slow changing ) |
2) RCD (Rapid changing ) |
|
3) JUNK |
|
|
4) INFERRED . |
5) DEFERRED |
|
6) CONFORMED (multiple facts ) |
|
|
7) DEGENERATE (invoice) |
|
|
8) ROLE PLAY |
|
|
9) STATIC |
10) DYNAMIC |
|
10. SPARSE |
11) DENSE |
|
12) SHRUNKEN |
|
1 Roleplay dimension
Role playing dimensions are dimensions that are used more than once in a fact table, each time with a different meaning or role.
when we want two attributes from the source entity to be connected to 1 entity as destination, that is not possible. In such cases, we can connect both the columns of one table to 2nd table, but with a new role name that is called as role play dimension something like connecting address and customer table with 2 columns bill to address and ship to address
2 Conformed dimension
Conformed dimensions are dimensions that are shared by multiple stars
3 Degenerate dimension
A column of the key column in the fact table that does not have the associated dimension table but used for reporting and analysis, such column is called degenerate dimension
For ex, we have a fact table with customer_id, product_id, branch_id, employee_id, bill_no
4 Junk dimension
This combined dimension table covers all possible set of values which might occur in fact table and is known as junk dimension.5 Shrunken dimension
5 Static dimensions
Static dimension items do not change with time; they are fixed components that are always returned in a freeform table.
The time , date , country code , status codes etc
6. Dynamic dim
which change dynamically based on values and formula
7. Deferred dims
Sometimes the fact table have to be loaded with some data, like customer id. But the dimension of customer is not loaded with that customer id. In such cases, we delay inserting such rows into fact table And later on, once the the dimension of customer is loaded with this customerId, then we add this rows into fact table.
This type of delaying the fact table is called deferred dimensions
8 Inferred Dimensions
When the fact data is available to be loaded in the source but related dimension table does not have that
eg: like the customerId is in sales orders table but not in customer table .. in such case we add an sample entry in customer table and enable 'inferred dimension' flag and later point of time we make a original entry into customer table and disable the inferred dimension
9 A slowly changing dimension (SCD)
scd is a dimension which contains relatively static data which can change slowly but unpredictably, rather than according to a regular schedule
SCD
Type 0 Ignore any changes and audit the changes.
Type 1 Overwrite the changes
Type 2 History will be added as a new row.
Type 3 History will be added as a new column.
Type 4 A new dimension will be added
Type 6 Combination of Type 2 and Type 3
Type 1 Overwrite the changes
Type 2 History will be added as a new row.
Type 3 History will be added as a new column.
Type 4 A new dimension will be added
Type 6 Combination of Type 2 and Type 3
10 Rapidly changing dimensions (RCD)
Rapidly changing dimension (RCD) is a dimension which has attributes where values will be getting changed often.
11) sparse dim
Some of the dimension tables are not loaded with full values
12) dense dim
Sometimes most of the columns of the dimension tables are loaded, fully loaded. So such dimensions
13) shrunken dim
Sometimes we create a replica of the original dimension, but it is with lesser, fewer number of columns than the original table
