The attribute value in Dimension table are going to change slowly over time and they are not going to be static.
For example if we have employees dimension table the location of the employee may change, the contact number can change.
In a product table a product can become obsolete and a new product can supersede it.
For a student dimension table the department may change and their may be a need to track the old and new department.
To handle such scenarios you must define the Slow Changing Dimension type for your dimension table during your requirement sessions with your business users.
Below are the types of Slowly Changing Dimension type.
Type 0 : Retain original
Type 1 : Overwrite
Type 2 : Add new row
Type 3 : Add new attribute
Type 4 : Add Mini-Dimension
Type 5 : Mini-Dimension and Type 1 outrigger
Type 6 : Add type 1 attributes to type 2 Dimension
Type 7 : Dual Type 1 and Type 2 Dimension.
3,948 total views, 2 views today
Changing Database compatibility level will be required when you upgrade your database from one…
To know what is compatibility Level check this post link. You can view the compatibility…
SQL Server Compatibility level will be used to decide how SQL will use it features.…
A view is a virtual table. View will not store any data physically. The data…
Below is the error which I was getting today while deploying the SSIS pacakge for…
Query store is a new powerful feature introduced in SQL 2016 which stores the query…
View Comments
Transactions that reference a particular surrogate key (Supplier_Key) are then permanently bound to the time slices defined by that row of the slowly changing dimension table. An aggregate table summarizing facts by state continues to reflect the historical state, i.e. the state the supplier was in at the time of the transaction; no update is needed. To reference the entity via the natural key, it is necessary to remove the unique constraint making Referential integrity by DBMS impossible. If there are retroactive changes made to the contents of the dimension, or if new attributes are added to the dimension (for example a Sales_Rep column) which have different effective dates from those already defined, then this can result in the existing transactions needing to be updated to reflect the new situation. This can be an expensive database operation, so Type 2 SCDs are not a good choice if the dimensional model is subject to change.