Chapter 4: Show 1 Data Warehouse: Giải quyết thay đổi dữ liệu lịch sử: Sử dụng Slowly 03/22/21 2 LOAD DW LOAD 03/22/21 3 Giúp quản lý việc thay đổi dữ liệu của chiều Có 3 loại: sử tạo ra một dịng mới với cùng khố tự nhiên nhưng khác hiệu lực sử dụng đồng thời với các giá trị mới tạo thêm các cột 4 Type 1 DW 5 Type 2 6 Type 3 7 Khóa đại diện (Surrogate Keys) Surrogate Keys: Không cho biết thông tin về record 8 Lợi ích: 9 2 Thời điểm thực hiện: 10 Initial Load Ví dụ: natural key surrogate key natural key Subsequent Load (Refresh) Mỗi giá trị khóa tự nhiên được so sánh với khóa tự nhiên đang tồn tại 12 Subsequent Load: LOOK UP Tables LOOK UP tables: Ánh xạ giữa NK & SK Production key Surrogate Key Prod1 1 Prod2 2 Prod3 3 Prod4 4 Prod5 5 Thực tế, dữ liệu được thay đổi liên tục Khó khăn đối với hệ thống ETL truyền thống: Tạo nên Giải pháp: 14 SCD có nghĩa là gì? Trên đây là một trong những ý nghĩa của SCD. Bạn có thể tải xuống hình ảnh dưới đây để in hoặc chia sẻ nó với bạn bè của bạn thông qua Twitter, Facebook, Google hoặc Pinterest. Nếu bạn là một quản trị viên web hoặc blogger, vui lòng đăng hình ảnh trên trang web của bạn. SCD có thể có các định nghĩa khác. Vui lòng cuộn xuống để xem định nghĩa của nó bằng tiếng Anh và năm nghĩa khác trong ngôn ngữ của bạn. Ý nghĩa của SCDHình ảnh sau đây trình bày một trong những định nghĩa về SCD trong ngôn ngữ tiếng Anh.Bạn có thể tải xuống tệp hình ảnh ở định dạng PNG để sử dụng ngoại tuyến hoặc gửi hình ảnh định nghĩa SCD cho bạn bè của bạn qua email. Ý nghĩa khác của SCDNhư đã đề cập ở trên, SCD có ý nghĩa khác. Xin biết rằng năm ý nghĩa khác được liệt kê dưới đây.Bạn có thể nhấp vào liên kết ở bên trái để xem thông tin chi tiết của từng định nghĩa, bao gồm các định nghĩa bằng tiếng Anh và ngôn ngữ địa phương của bạn.Định nghĩa bằng tiếng Anh: Slowly Changing DimensionSCD định nghĩa:
‹ Sống Daylights Sự công nhận của học tập trước › [box type=”note” align=”” class=”” width=””]Below given post is an excerpt from a book by Rahul Malewar titled Learning Informatica PowerCenter 10.x. The book is a quick guide to explore Informatica PowerCenter and its features such as working on sources, targets, transformations, performance optimization, and managing your data at speed. [/box] Our article explores what Slowly Changing Dimensions (SCD) are and how to implement them in Informatica PowerCenter. As the name suggests, SCD allows maintaining changes in the Dimension table in the data warehouse. These are dimensions that gradually change with time, rather than changing on a regular basis. When you implement SCDs, you actually decide how you wish to maintain historical data with the current data. Dimensions present within data warehousing and in data management include static data about certain entities such as customers, geographical locations, products, and so on. Here we talk about general SCDs: SCD1, SCD2, and SCD3. Apart from these, there are also Hybrid SCDs that you might come across. A Hybrid SCD is nothing but a combination of multiple SCDs to serve your complex business requirements. Types of SCDThe various types of SCD are described as follows: Type 1 dimension mapping (SCD1): This keeps only current data and does not maintain historical data. Note : Use SCD1 mapping when you do not want history of previous data.Type 2 dimension/version number mapping (SCD2): This keeps current as well as historical data in the table. It allows you to insert new records and changed records using a new column (PM_VERSION_NUMBER) by maintaining the version number in the table to track the changes. We use a new column PM_PRIMARYKEY to maintain the history. Note : Use SCD2 mapping when you want to keep a full history of dimension data, and track the progression of changes using a version number.Consider there is a column LOCATION in the EMPLOYEE table and you wish to track the changes in the location on employees. Consider a record for Employee ID 1001 present in your EMPLOYEE dimension table. Steve was initially working in India and then shifted to USA. We are willing to maintain history on the LOCATION field. Type 2 dimension/flag mapping: This keeps current as well as historical data in the table. It allows you to insert new records and changed records using a new column (PM_CURRENT_FLAG) by maintaining the flag in the table to track the changes. We use a new column PRIMARY_KEY to maintain the history. Note : Use SCD2 mapping when you want to keep a full history of dimension data, and track the progression of changes using a flag.Let’s take an example to understand different SCDs. Type 2 dimension/effective date range mapping: This keeps current as well as historical data in the table. SCD2 allows you to insert new records and changed records using two new columns (PM_BEGIN_DATE and PM_END_DATE) by maintaining the date range in the table to track the changes. We use a new column PRIMARY_KEY to maintain the history. Note : Use SCD2 mapping when you want to keep a full history of dimension data, and track the progression of changes using start date and end date.Type 3 Dimension mapping: This keeps current as well as historical data in the table. We maintain only partial history by adding a new column PM_PREV_COLUMN_NAME, that is, we do not maintain full history. Note: Use SCD3 mapping when you wish to maintain only partial history.EMPLOYEE_IDNAMELOCATION1001STEVEINDIAYour data warehouse table should reflect the current status of Steve. To implement this, we have different types of SCDs. SCD1 As you can see in the following table, INDIA will be replaced with USA, so we end up having only current data, and we lose historical data: PM_PRIMARY_KEYEMPLOYEE_IDNAMELOCATION1001001STEVEUSANow if Steve is again shifted to JAPAN, the LOCATION data will be replaced from USA to JAPAN: PM_PRIMARY_KEYEMPLOYEE_IDNAMELOCATION1001001STEVEJAPANThe advantage of SCD1 is that we do not consume a lot of space in maintaining the data. The disadvantage is that we don’t have historical data. SCD2 – Version numberAs you can see in the following table, we are maintaining the full history by adding a new record to maintain the history of the previous records: PM_PRIMARYKEYEMPLOYEE_IDNAMELOCATIONPM_VERSION_NUMBER1001001STEVEINDIA01011001STEVEUSA11021001STEVEJAPAN22001002MIKEUK0We add two new columns in the table: PM_PRIMARYKEY to handle the issues of duplicate records in the primary key in the EMPLOYEE_ID (supposed to be the primary key) column, and PM_VERSION_NUMBER to understand current and history records. SCD2 – FLAGAs you can see in the following table, we are maintaining the full history by adding new records to maintain the history of the previous records: PM_PRIMARYKEYEMPLOYEE_IDNAMELOCATIONPM_CURRENT_FLAG1001001STEVEINDIA01011001STEVEUSA1 We add two new columns in the table: PM_PRIMARYKEY to handle the issues of duplicate records in the primary key in the EMPLOYEE_ID column, and PM_CURRENT_FLAG to understand current and history records. Again, if Steve is shifted, the data looks like this: PM_PRIMARYKEYEMPLOYEE_IDNAMELOCATIONPM_CURRENT_FLAG1001001STEVEINDIA01011001STEVEUSA01021001STEVEJAPAN1SCD2 – Date rangeAs you can see in the following table, we are maintaining the full history by adding new records to maintain the history of the previous records: PM_PRIMARYKEYEMPLOYEE_IDNAMELOCATIONPM_BEGIN_DATEPM_END_DATE1001001STEVEINDIA01-01-1431-05-141011001STEVEUSA01-06-1499-99-9999We add three new columns in the table: PM_PRIMARYKEY to handle the issues of duplicate records in the primary key in the EMPLOYEE_ID column, and PM_BEGIN_DATE and PM_END_DATE to understand the versions in the data. The advantage of SCD2 is that you have complete history of the data, which is a must for data warehouse. The disadvantage of SCD2 is that it consumes a lot of space. SCD3As you can see in the following table, we are maintaining the history by adding new columns: PM_PRIMARYKEYEMPLOYEE_IDNAMELOCATIONPM_PREV_LOCATION1001001STEVEUSAINDIAAn optional column PM_PRIMARYKEY can be added to maintain the primary key constraints. We add a new column PM_PREV_LOCATION in the table to store the changes in the data. As you can see, we added a new column to store data as against SCD2,where we added rows to maintain history. If Steve is now shifted to JAPAN, the data changes to this: PM_PRIMARYKEYEMPLOYEE_IDNAMELOCATIONPM_PREV_LOCATION1001001STEVEJAPANUSAAs you can notice, we lost INDIA from the data warehouse, that is why we say we are maintaining partial history. Note : To implement SCD3, decide how many versions of a particular column you wish to maintain. Based on this, the columns will be added in the table.SCD3 is best when you are not interested in maintaining the complete but only partial history. The drawback of SCD3 is that it doesn’t store the full history. At this point, you should be very clear about the different types of SCDs. We need to implement these concepts practically in Informatica PowerCenter. Informatica PowerCenter provides a utility called wizard to implement SCD. Using the wizard, you can easily implement any SCD. In the next topics, you will learn how to use the wizard to implement SCD1, SCD2, and SCD3. Before you proceed to the next section, please make sure you have a proper understanding of the transformations in Informatica PowerCenter. You should be clear about the source qualifier, expression, filter, router, lookup, update strategy, and sequence generator transformations. Wizard creates a mapping using all these transformations to implement the SCD functionality. When we implement SCD, there will be some new records that need to be loaded into the target table, and there will be some existing records for which we need to maintain the history. Note : The record that comes for the first time in the table will be referred to as the NEW record, and the record for which we need to maintain history will be referred to as the CHANGED record. Based on the comparison of the source data with the target data, we will decide which one is the NEW record and which is the CHANGED record.To start with, we will use a sample file as our source and the Oracle table as the target to implement SCDs. Before we implement SCDs, let’s talk about the logic that will serve our purpose, and then we will fine-tune the logic for each type of SCD.
In this article we concentrated on a very important table feature called slowly changing dimensions. We also discussed different types of SCDs, i.e., SCD1, SCD2, and SCD3. If you are looking to explore more in Informatica Powercentre, go ahead and check out the book Learning Informatica Powercentre 10.x.
Share Savia Lobo A Data science fanatic. Loves to be updated with the tech happenings around the globe. Loves singing and composing songs. Believes in putting the art in smart. |
