Exam DP-203 topic 1 question 88 discussion

Product name -type 0 color -type 2 size -type 3

ProductName - type 0, as no changes are done. Color - type 3, as with type 3 we have one column for the current value and one for the previous so only these two are preserved. Size - type 2, as it inserts a new row for every change, so we get all historical values.

Column Requirement SCD Type ProductName Prevent changes (No updates allowed) SCD Type 0 ProductSize Retain only the current and last value SCD Type 3 ProductColor Retain all historical values SCD Type 2

ProductName: Type 0 Color: Type 2 Size: Type3

Type 1: Overwrite the existing data with new data, without maintaining a history. This is suitable when you want to prevent changes to the values stored in a column. Type 2: Keep a history of changes by adding new rows for each change. This is suitable for retaining all the current and previous values in a column. Type 3: Keep a limited history, typically by adding new columns to the dimension table to store both the current and previous values. This allows you to track changes but in a more compact way compared to Type 2. Therefore I believe the answer is 0 3 2

ProductName: To prevent changes to the values stored in ProductName, we should use Type 0. This means that the value remains static and does not change over time. ProductColor: Since we need to retain all the current and previous values in ProductColor, we should use Type 2. This type allows us to track historical changes by creating new rows for each change and maintaining a history of values. ProductSize: To retain only the current and last values in ProductSize, we should use Type 3. This type keeps track of the current value and the previous value in separate columns. Therefore, the correct SCD types for the three columns are as follows: ProductName: Type 0 ProductColor: Type 2 ProductSize: Type 3

Product : Type 0 :No change (This eliminates possibilities of Type 1,2 and other higher dimensions Colour : Type 1 or 2 ;There is a change which can be rowise(type 1) or column wise(Type 2) Size :Type 3 ; All changes means both rowise and column wise

For the given requirements: - **Product Name**: Since changes must be prevented, this would be a Type 0 SCD, as it maintains the original value without any changes. - **Product Size**: To retain only the current and the last values, you would use a Type 3 SCD, which keeps the original value and adds a new column for the current value. - **Product Color**: To retain all the current and previous values, a Type 2 SCD is used, as it tracks historical data by creating a new record for each change. So, you would apply: - Type 0 for ProductName - Type 3 for ProductSize - Type 2 for ProductColor

Name: 0 Fixed Dimension Color: 2 Row Versioning (current + last value) Size: 3 Previous Value column (current value + all previous values in extra column)

SCD0-1-2

Product Name - Type 0 Color - Type 2 Size - Type 3

The answers are 0, 3, 2

correct answer is - type 0, type 3,type 2

type 0 type 3 type 2

Answer is Product name -type 0 color -type 2 size -type 3 Type 0 – Fixed Dimension No changes allowed, dimension never changes A Type 1 SCD always reflects the latest values, and when changes in source data are detected, the dimension table data is overwritten. Type 2 SCD A Type 2 SCD supports versioning of dimension members. Often the source system doesn't store versions, so the data warehouse load process detects and manages changes in a dimension table. Type 3 SCD A Type 3 SCD supports storing two versions of a dimension member as separate columns. The table includes a column for the current value of a member plus either the original or previous value of the member. So Type 3 uses additional columns to track one key instance of history, rather than storing additional rows to track each change like in a Type 2 SCD.

Answer are corrects as reported in the solution: 0, 1, and 2.

• Prevent changes to the values stored in ProductName. => TYPE 0 • Retain only the current and the last values in ProductSize. => TYPE 2 (current and last, menas all the history) • Retain all the current and previous values in ProductColor. => TYPE 3 (it includes a column for the previous value)