MBN News | The Independent Business Blogging Community

MBN News: Business Intelligence | Databases | BigData

MBN International: English Flag Bulgarian Flag

29 Members | ~1,500 Unique Visitors with ~3,500 Visits Last Month | Last Update: 07 Mar 2021 | Google PageRank 5 (PR5)

MBN Most Popular

MBN Latest

Now Blogging » Business Intelligence (BI)

Business Intelligence Dictionary: Slowly Changing Dimensions-SCD explained | NSBI

<a href='http://morningbiznews.com/en/search/dimension'>dimension</a>, <a href='http://morningbiznews.com/en/search/slowly+changing+dimension'>slowly changing dimension</a>, <a href='http://morningbiznews.com/en/search/SCD'>SCD</a>, <a href='http://morningbiznews.com/en/search/historization'>historization</a>, <a href='http://morningbiznews.com/en/search/nsbi-dict'>nsbi-dict</a>

Things change. Dimensions are no exclusion. When a data designer plans a data warehouse architecture they have to plan ahead with a slowly changing dimensions strategy.

Slowly Changing Dimensions-SCD | Definition

Slowly Changing Dimension (SCD) is a term first introduced by Kimball Group among strategies for implementation of a data warehouse. Ralph Kimball states that dimensions normally change overtime and offers several strategies how a data designer may handle this process.

NSBI Tutorials aim to make Business Intelligence (BI) and Data Warehousing (DWH) attractive to non-technical people as well as to those who are now entering the field and are excited by the numerous ways data is changing our world. NSBI Tutorials are written and delivered by Nick Shopov, (BI Software Developer & DWH Consultant).

SCD Type 0 | Definition

SCD 0 is the simplest form of strategy. SCD 0 says: "Do not update the old row even if a change is detected. Preserve the initial state."

SCD Type 1 | Definition & Example

SCD 1 imposes a destructive update where old values are overwritten by the new state of a dimension.

Slowly Changing Dimension Type 1

SCD Type 2 | Definition & Example

SCD 2 is the most commonly used SCD type. It is flexible and optimized for most business scenarios. SCD 2 imposes "expiration" of a record that is handled by adding 2 new columns to the architecture - start date and end date, i.e. a period within which a dimension is active. End date for active dimensions are hard-coded to future date like 9999-12-31.

Slowly Changing Dimension Type 2

SCD Type 3 | Definition & Example

SCD 3 is rarely used due to data redundancy. SCD 3 requires that a column is appended every time a dimension changes and this new column saves the previous state. SCD 3 also has a slightly optimized version that requires only 2 columns - Old State and New State but the obvious shortage is that this way it can only save 2 conditions.

Slowly Changing Dimension Type 3

SCD Type 4 | Definition & Example

SCD 4 states that one table must include all dimensions in their latest state and yet another History Table would be holding the previous states.

Slowly Changing Dimension Type 4

SCD Type 6 | Definition & Example

SCD 6 is a hybrid of SCD 1, SCD 2 and SCD 3 where it gets its name from (1+2+3). It imposes (SCD 1) a new row for each change, (SCD 2) Start Date/End Date columns and (SCD 3) a new flag column Y/N.

Slowly Changing Dimension Type 6

Note: You may also want to add a separate column, known as "Delete Indicator". It is used whenever a dimension does not have an active state anymore but you don't want to delete it in order to preserve the historical records for it.