In business, one of the most important resources you need to manage is time; you have only X number of hours in a day in which you have to complete your tasks.
This is especially true when you are working in a service industry where you have to complete all your routine tasks plus any additional chores that may arise from some customers requiring impromptu services. But time is not just something that affects you; it also affects your data.
When working with your data in a data warehouse, you might assume that most dimensions of your database stay the same. Sometimes, what appears to be a static dimension is actually a slowly changing dimension or SCD.
An SCD is marked by the fact that, unlike sales-related events that might accumulate new entries over time, there can theoretically only be a single value for this dimension.
For example, there can only be one customer address, right? But what happens when he/she moves? How do you track it, now that the value in the 'Address' dimension has changed?
How do you keep the new address linked to all the activities linked to the previous address? The answer is to use one of the three types of SCD update actions.
Capture the changing data within the dimension over time with the help of a dimensional model. Speak to Our Experts to get detailed insights.
OLAP vs OLTP: the Difference
In a data warehouse environment, users perform either Online Transaction Processing (OLTP: insertion, update, deletion) or Online Analytical Processing (OLAP: data retrieval for decision support).
Update performance, query performance, and schema simplicity are essential for the efficient use and maintenance of a data warehouse.
To achieve this, data is organized in a dimensional model called a star schema. In the star schema, the fact table is at the center surrounded by dimension tables.
What is OLAP (Online Analytical Processing)?
OLAP (Online Analytical Processing) is categorized by comparatively small volumes of transactions. Queries are repeatedly very complex and contain aggregations.
For OLAP systems, an answering time is a success measure. Online Analytical Processing (OLAP) applications are extensively utilized by Data Mining methods. An OLAP database contains a collected, historical dataset, warehoused in multi-dimensional schemas, generally a star schema.
What is OLTP (Online Transaction Processing)?
OLTP (Online Transaction Processing) is categorized by a huge number of small online transactions (INSERT, UPDATE, and DELETE).
The key importance of OLTP systems is placed on very fast query processing and retaining data integrity in multi-access environments, and measuring efficiency by the total number of transactions per second.
In OLTP databases, there is comprehensive and present data, and the schema used to accumulate transactional databases and catalogues is the entity model, generally 3NF.
A typical Star Schema Diagram is Shown Below:
While building star schemas in a data warehouse, the dimension tables are joined with the fact table. This ensures the tracking of attributes of the dimension.
An example could be customers and their sales activities. We can track sales information using various attributes of a customer, such as a city, state, zip code, etc. However, what if the dimensions keep changing over time? Say, if the customer moves from Los Angeles to New York, how would you associate this new city with that customer's sales and other activities?
It is precisely to tackle this problem that we have 3 common types of SCD techniques that can be implemented while designing the dimension table in the data warehouse.
Achieve business success with our data analytics consulting services
What are the Types of Slowly Changing Dimensions and Actions?
The most popular approaches to dealing with SCD are as follows:
- Type 0 SCD – The Fixed Method
- Type 1 SCD – Overwriting the old value with new values
- Type 2 SCD – Creating a new additional record by row versioning
- Type 3 SCD – Adding a new column to show the previous value
- Type 4 SCD – Using historical table
- Type 6 SCD – Combine approaches of types 1,2,3 (1+2+3=6) or Hybrid SCD
Type 1 SCD (Overwriting)
The first type of SCD action possible is overwriting. Here, dimension values are overwritten by new values. For example, if our customer Mike moves from Los Angeles to New York, then his city will be updated with the latest value, i.e, New York.
Original Record
Type 2 SCD (Row versioning)
The second type of SCD action is row versioning. In this type 2 SCD, when values for a current record change, the current record is marked as closed, and a new record gets inserted. Then, there will be 2 records associated with Mike in the updated table, but only the latest version will be marked "open".
Original Record
Original RecordType 3 SCD (Adding previous value column)
The third common SCD type is adding a previous value column. Here, the previous and current versions are maintained in a single row. In the customer Mike moving address example, we would copy Mike's 'Current City' record into the 'Previous City' and overwrite the Current City record with the new city.
Original Record
Other Types
In addition to these three SCD types in the data warehouse, there are also Types 4 and 6. In Type 4, the dimension table has the latest value while its history is maintained in a separate table. In Type 6, a combination of Types 1, 2 & 3 is used to track changes in dimension.
Generally, Type 6 is adopted in scenarios where multiple parts of a record are slowly changing dimensions, but using multiple implementations of a single type could lead to issues with rapid inflation of table size.
That is part 1 of the blog post. In part 2, I will be focusing on Type 2 SCD and the issues that are likely to crop up during implementation.
