Role-Playing Dimension: A role-playing dimension is a dimension that is used in different ways in the same fact table. For example, a date dimension can be used to analyze sales by order date, ship date, or delivery date. Role-playing dimensions are useful when the same dimension table is used in different contexts with different meanings. By reusing the same dimension table, the data model can be simplified, and the data can be more easily analyzed and understood. However, it’s important to ensure that the meaning of each use of the dimension table is clearly defined to avoid confusion and errors in data analysis.

•     Slowly Changing Dimension (SCD): A slowly changing dimension is a dimension that changes slowly over time. SCDs are classified into six types:

•     Type 1 SCD: In a Type 1 SCD, the old values are simply overwritten with new values when a change occurs. This approach is suitable for dimensions where historical information is not required.

Suppose you have a price master table that contains information about products such as name, price, and details. If the price of a product changes, you might simply update the price in the price master table without keeping track of the historical price.

•     Type 2 SCD: In a Type 2 SCD, a new row is added to the dimension table when a change occurs, and the old row is marked as inactive. This approach is suitable for dimensions where historical information is required.

Continuing with the price master table example, if you want to keep track of the historical price of each product, you might create a new row for each price change. For example, you might add a new row with a new product version number and a new price whenever the price changes. This way, you can keep track of the historical prices of each product.

•     Type 3 SCD: In a Type 3 SCD, a limited amount of historical information is maintained by adding columns to the dimension table to store previous values. This approach is suitable

for dimensions where only a limited amount of historical information is required.

Suppose you have an employee table that contains information about employees such as name, address, and salary. If an employee gets a promotion, you might add a new column to the table to store the new job title. You would only store the most recent job title, and not keep track of historical job titles.

•   Type 4 SCD: Create a separate table to store historical data. This type of SCD is useful when historical data needs to be stored separately for performance reasons.

Suppose you have a customer table that contains information about customers such as name, address, and phone number. If you want to keep track of historical addresses, you might create a new table to store the historical addresses. The new table would contain the customer ID, the old address, and the date the address was changed.

•     Type 5 SCD: Combine SCD Types 1 and 2 by adding an additional column to track the current and historical values. This type of SCD can be useful when there are a large number of historical changes, but only the current value is needed for most queries.

Continuing with the customer table example, if you want to keep track of the current and historical phone numbers for each customer, you might create a new column in the customer table to store the current phone number, and then create a new row in a separate phone number table for each phone number change. The phone number table would contain the customer ID, the phone number, the start date, and the end date.

•     Type 6 SCD: Combine SCD Types 2 and 3 by adding an additional column to track the current and previous values. This type of SCD is useful when historical data is important, but only the current and previous values are needed for most queries.

Suppose you have a product table that contains information about products such as name, price, and description. If the price of a product changes, you might create a new row for the new product version and store the new price in that row. You might also add a new column to the product table to store the previous price. This way, you can easily access the current and previous prices of each product.

•     Time Dimension: A time dimension is a special type of dimension that is used to track time-related data. It provides a way to group and filter data based on time periods such as hours, days, weeks, months, and years.

•     Hierarchical Dimension: A hierarchical dimension is a dimension that has a parent–child relationship between its attributes. For example, a product dimension can have a hierarchy that includes product category, sub-category, and product.

•     Virtual Dimension: A virtual dimension is a dimension that is created on the fly during query execution. It is not stored in the data warehouse and is only used for a specific analysis or report.