Here we will discuss about different types of dimension in data warehouse.
Types Of Dimension :
- Slowly Changing Dimension
- Conformed Dimension
- Junk Dimension
- Degenerate Dimension
- Role Playing Dimension
- Rapidly Changing Dimension
1. Slowly Changing Dimension :
Slowly Changing Dimension are dimensions that change slowly over time, rather than changing on regular schedule , regular time interval. In order to report historical data in data warehouse, there is a need to track changes in dimension attributes. For instance, address of a individual may change over time, name of person can change.Slowly changing dimension is categorized into mainly three types – Type 1, Type 2 and Type 3.
2. Conformed Dimension :
Conformed dimensions mean the exact same thing with every possible fact table to which they are joined. A conformed dimension is a set of data attributes that have been physically implemented in multiple database tables using the same structure, attributes, domain values, definitions and concepts in each implementation. For two dimension tables to be considered as conformed, they must either be identical or one must be a subset of another. There cannot be any other type of difference between the two tables. For example, two dimension tables that are exactly the same except for the primary key are not considered conformed dimensions.
3. Junk Dimension :
Junk dimensions are dimensions that contain miscellaneous data such as flags and indicators. In data warehouse design, often situation arises where there are yes/no indicator fields in the source system. Junk dimension is solution to this situation. Junk dimension table holds all the unique combinations of those indicator fields into a single dimension and assigns a unique key. This key is what is stored in the fact table.The junk dimension is simply a structure that provides a convenient place to store the junk attributes.
4. Degenerate Dimension :
A degenerate dimension is a dimension which is derived from the fact table and doesn’t have its own dimension table.
for example : Invoice number can be stored in the fact table and then used as separate dimensions for the drill through purpose to find out what invoices are part of total buying cost in report.
5. Role Playing Dimension :
As the name suggests, these dimensions have multiple role in same database.Dimensions which are often used for multiple purposes within the same database are called role-playing dimensions. For example, a date dimension can be used for “date of sale”, as well as “date of delivery”, or “date of hire”
6. Rapidly Changing Dimension :
A dimensions is said to be rapidly changing dimensions if one or more of its attributes changes very fast and in many rows.
e.g. Consider a Employee dimensions where there are 1000 rows in it.
On an average each employee changes its 10 attribute in a year. Now if use type 2 approach to manage change it will be 1000 * 10 = 10000 rows still manageable right. Now if we have 1 million rows it becomes un-manageable using type two approach it becomes huge.
I have described major types of dimension above but there are other dimensions also. Some of them are shrunken dimension, static dimension etc. I will try to explain them also in future post.