A dimension is a structure, usually composed of one or more hierarchies that categorizes data. If a dimension hasn't got hierarchies and levels it is called a flat dimension or list. The primary keys of each of the dimension tables are part of the composite primary key of the fact table. Dimensional attributes help to describe the dimensional value. They are normally descriptive, textual values. Dimension tables are generally smaller in size than fact table.
For example, "Customer", "Date", and "Product" are all dimensions that could be applied meaningfully to a sales receipt.
Types of dimension :
Slowly changing dimension
Fast changing dimension
Role playing dimension
Conformed dimension
Garbage dimension/Junk Dimension
Slowly changing dimension :
A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse.
It is considered and implemented as one of the most critical ETL tasks in tracking the history of dimension records.
There are three types of SCD's namely
I.Type 1 SCD’s - Overwriting
II.Type 2 SCD’s - Creating another dimension record
III.Type 3 SCD’s - Creating a current value field
Type 1 SCD’s :
In a Type 1 SCD the new data overwrites the existing data. Thus the existing data is lost as it is not stored anywhere else.
This is the default type of dimension you create.
Here is an example of a database table that keeps supplier information.
Now imagine that this supplier moves their headquarters to California. The updated table would simply overwrite this record:
Type 2 SCD’s :
A Type 2 SCD retains the full history of values.
When the value of a chosen attribute changes, the current record is closed. A new record is created with the changed data values and this new record becomes the current record.
We have version, date and flag to identify new records.
Lets consider type 1 SCD’s example using version, date and flag respectively.
Type 3 SCD’s :
A Type 3 SCD stores two versions of values for certain selected level attributes.
Each record stores the previous value and the current value of the selected attribute.
Fast changing dimensions :
A fast-changing dimension is a dimension whose attribute or attributes for a record (row) change rapidly over time.
Example: Age of associates, Income, Daily balance etc.
Technique to handle fast changing dimension: Create band tables
Role playing dimensions:
A single dimension which is expressed differently in a fact table using views is called a Role-playing dimension. This can be achieved by creating views on dimension table.
Ex: A "Date" dimension can be used for “Orderdate", as well as “Shipmentdate". This is often referred to as a "role-playing dimension".
Confirmed dimensions :
Conformed Dimension is the dimension which has the same meaning and content when being referred from different fact tables.
A conformed dimension can refer to multiple tables in multiple data marts within the same organization.
Junk dimensions :
A junk dimension is grouping of low carnality flags and indicators. This junk dimension helps in avoiding cluttered design of data warehouse.