What is Dimension?
Dimension table contains the data about the business. The primary keys of the dimension tables are used in Fact tables with Foreign key relationship. And the remaining columns in the dimension is normal data which is the information about the Objects related to the business.Eg: Product,Customer,Orders,Company,Date etc.
- Slowly Changing Dimensions (SCD) :
Slowly changing dimensions refer to how data in your data warehouse changes over time. Slowly changing dimensions have the same natural key but other data columns that may or may not change over time depending on the type of dimensions that it is.
Slowly changing dimensions are important in data analytics to track how a record is changing over time. The way the database is designed directly reflects whether historical attributes can be tracked or not, determining different metrics available for the business to use.
For example, if data is constantly being overwritten for one natural key, the business will never be able to see how changes in that row’s attributes affect key performance indicators. If a company continually iterates on a product and its different features, but doesn’t track how those features have changed, it will have no idea how customer retention, revenue, customer acquisition cost, or other marketing analytics were directly impacted by those changes.
Different Types of SCD:
| SCD Type | Summary |
| Type 0 | Ignore any changes and audit the changes. |
| Type 1 | Overwrite the changes |
| Type 2 | History will be added as a new row. |
| Type 3 | History will be added as a new column. |
| Type 4 | A new dimension will be added |
I will post detailed article on SCD types separately.
2. Rapidly Changing Dimensions:
A dimension is a fast changing or rapidly changing dimension if one or more of its attributes in the table changes very fast and in many rows. Handling rapidly changing dimension in data warehouse is very difficult because of many performance implications.
Example: Patient table with Age, BMI and Weight
3. Conformed Dimension
A conformed dimension is the dimension that is shared across multiple data mart or subject area. Company may use the same dimension table across different projects without making any changes to the dimension tables.
Conformed dimension example would be Customer dimension, i.e. both marketing and sales department can use Customer dimension for their reporting purpose.
4. Junk Dimension
A junk dimension is a grouping of typically low cardinality attributes, so you can remove them from main dimension.
For example, attributes such as flags, weights, BMI (body mass index) etc
5. Degenerated Dimension
A degenerated dimension is a dimension that is derived from fact table and does not have its own dimension table.
For example, receipt number does not have dimension table associated with it. Such details are just for information purpose.
5. Role Playing Dimension
Dimensions which are often used for multiple purposes within the same database are called role-playing dimensions.
For example, you can use a date dimension for “date of sale”, as well as “date of delivery”, or “date of hire”.
SQL Server tips and tricks By Satya Katari 