Slowly changing dimensions
- Sarabjeet Kaur
Applies to: Kyvos Enterprise Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace
Kyvos Azure Marketplace Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)
Slowly Changing Dimension (SCD) refers to the concept where some or most of the data changes at irregular intervals. There are multiple SCD types available, and each has different implementations.
SCD Type 1
Use SCD Type 1 to update data without tracking historical data. For example, you can correct a typographic mistake by marking a dimension as SCD type 1. These changes in semantic model design require a full process of the semantic model. After that, future incremental processes will update the data.
To specify that a dimension is an SCD Type 1, select a dimension and click the Is a Slowly Changing Dimension checkbox in the Dimension Properties pane.
Note
Kyvos does not allow you to run incremental builds on semantic models where changes made are in the SCD Type 1 dimensions prior to the Kyvos 2021.1 release. You need to first run a full process job, and thereafter, you can use incremental processes.
Note
You will see informational messages when using this feature, along with third-party tools for data analysis.
Tableau shows the following message: “The worksheet contains errors. The following will be removed: There is no field named [Dimension_Name].[Hierarchy_Name].[Level_Name]”, and then removes that level from the worksheet.
Excel shows the message: “The organization or content of the OLAP semantic model has changed and, as a result, no data items are available for display in this PivotTable report”, and then removes that level from the worksheet.
SCD Type 2
SCD Type 2 includes historical data along with current data. It includes another dimension record to track the full history of values and uses keys to identify each one. Each change includes an effective and expiration time.
To specify that a dimension is an SCD Type 2, create an associated dimension with fields named startdate and enddate and create a relationship between that and the field used in your primary dimension.
For example, in a semantic model that contains data about employees and the hours they work, there is a field named date. However, employees may change departments or even leave a company and later return to it. To track the duration over time, create an associated dimension record that includes fields named start date and end date. Then, create an equi join (=) between both fields named EmployeeID and create a non-equi join (<>) between startdate, end date, and date fields and set the relationship to Between.
When you view the properties for the relationship used to create the semantic model, you'll see the startdate and enddate fields in the added dimension and that the relationship is set to Between.
For example, date <> start date and end date.
Copyright Kyvos, Inc. All rights reserved.