Slowly Changing Dimensions

Slowly Changing Dimensions (SCD):

Slowly changing dimensions are the dimensions in which the data changes slowly, rather than changing regularly on a time basis. Slowly changing dimensions (SCD) determine how the historical changes in the dimension tables are handled. 

Generally we use following three types of slowly changing dimensions like: SCD type1, SCD type 2, SCD type3.

SCD Types:

a.      SCD type 1

It is used when there is no need to store historical data in the dimension table. This method overwrites the old data in the dimension table with the new data. It is used to correct data errors in the dimension.

E.g. If you entered address for employee is Mumbai and after some time the employee address is changed then you can use SCD Type1.

EmpNo	EmpName	Address
1001	Sagar	Mumbai(India)

If new address is Pune then you can overwrite old one.

EmpNo	EmpName	Address
1001	Sagar	Pune(India)

b.      SCD type 2

SCD type 2 stores the entire history the data in the dimension table. With type 2 we can store unlimited history in the dimension table. In type 2, you can store the data in three different ways.

They are:

ü  Versioning

ü  Flagging

ü  Effective Date

SCD type 2 Versioning: In versioning method, a sequence number is used to represent the change. The latest sequence number always represents the current row and the previous sequence numbers represents the past data.
For the same example we can use SCD Type2

EmpNo	EmpName	Address	Version
1001	Sagar	Mumbai(India)	1

If new address is Pune then you can add new version data as:

EmpNo	EmpName	Address	Version
1001	Sagar	Mumbai(India)	1
1001	Sagar	Pune(India)	2

If we add new location then version no also changes.

SCD type 2 Flagging: In flagging method, a flag column is created in the dimension table. The current record will have the flag value as 1 and the previous records will have the flag as 0.

EmpNo	EmpName	Address	Flag
1001	Sagar	Mumbai(India)	1

If new address Pune is added then the old records will be updated with flag value as 0 and the latest record will have the flag value as 1.

EmpNo	EmpName	Address	Flag
1001	Sagar	Mumbai(India)	0
1001	Sagar	Pune(India)	1

SCD type 2 Effective Date: In Effective Date method, the period of the change is tracked using the start_date and end_date columns in the dimension table.

EmpNo	EmpName	Address	Start_date	End_date
1001	Sagar	Mumbai(India)	01-Mar-2010	20-Feb-2011
1001	Sagar	Pune(India)	21-Feb-2011	NULL

The NULL in the End_Date indicates the current version of the data and the remaining records indicate the past data. 

c.       SCD type 3

Only the current status and previous status of the row is maintained in the table. To track these changes two separate columns are created in the table. It maintains the most recently used record history.

EmpNo	EmpName	New address	Old address
1001	Sagar	Mumbai(India)	NULL

If you add new address Pune then data stored should look like

EmpNo	EmpName	New address	Old address
1001	Sagar	Pune(India)	Mumbai(India)

The type 3 method will have limited history and it depends on the number of columns you create.

Data Warehouse Concepts

Facts Tables:

ü  A fact table typically has two types of columns: foreign keys to dimension tables and measures those that contain numeric facts. A fact table can contain fact's data on detail or aggregated level.

ü  A fact table stores quantitative information for analysis and is often de-normalized.

ü  Fact table is typically numeric data and it is often data that can be easily manipulated, particularly by summing together many thousands of rows.

Types of fact:

1.      Additive:
Additive facts are facts that can be summed up through all of the dimensions in the fact table. A sales fact is a good example for additive fact.

2.      Semi-Additive:
Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
E.g.  Daily balances fact can be summed up through the customers dimension but not through the time dimension.

3.      Non-Additive:
Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
E.g. Facts which have percentages, ratios calculated.

Dimensions:

ü  A dimension is a structure, often composed of one or more hierarchies, that categorizes data. Dimensional attributes help to describe the dimensional value. They are normally descriptive, textual values. Several distinct dimensions, combined with facts, enable you to answer business questions. Commonly used dimensions are customers, products, and time.

ü  Dimension data is typically collected at the lowest level of detail and then aggregated into higher level totals that are more useful for analysis. These natural rollups or aggregations within a dimension table are called hierarchies.

Surrogate key:

ü  Surrogate keys are nothing but integers which do not have any meaning in terms of business and used as primary key in dimension table.

ü  Surrogate keys join the dimension tables to the fact table. Surrogate keys serve as an important means of identifying each instance or entity inside of a dimension table.

Fact less fact:

A fact less fact table is fact table that does not contain fact. They contain only dimensional keys and it captures events that happen only at information level but not included in the calculations level. Just information about an event that happen over a period.

A fact less fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information. Common examples of fact less fact tables include:

ü  Identifying product promotion events

ü  Tracking student attendance or registration events

ü  Tracking insurance-related accident events

ü  Identifying building, facility, and equipment schedules for a hospital or university

ü  Fact less fact tables are used for tracking a process or collecting stats.

E.g. student, time, and class dimensions used to create student attendance fact less fact table. 

Degenerated dimension:

ü  A degenerate dimension is when the dimension attribute is stored as part of fact table, and not in a separate dimension table.

ü  These are essentially dimension keys for which there are no other attributes. In a data warehouse, these are often used as the result of a drill through query to analyze the source of an aggregated number in a report.

ü  You can use these values to trace back to transactions in the OLTP system.

Conformed dimension:

ü  A Dimension that is used in multiple locations is called a conformed dimension.

ü  A conformed dimension may be used with multiple fact tables in a single database, or across multiple data marts or data warehouses.

Schema types:

1.  Star Schema:

ü  In the star schema design, a single object sits in the middle and is radically connected to other surrounding objects (dimension lookup tables) like a star.

ü  Each dimension is represented as a single table.

ü  The primary key in each dimension table is related to a foreign key in the fact table.

2.  Snowflake schema:

ü  The snowflake schema is an extension of the star schema, where each point of the star explodes into more points.

ü  In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy.