A00-260 practice question 5 of 15

When using the SCD Type 2 load method in SAS Data Integration Studio, which columns can be selected to check for changed data?

Full set: 70 questions
Answer options
A: business key columns
B: Type 1 columns
C: generated columns
D: non-key columns Correct answer
Correct Answer:
D. non-key columns
Explanation

In the SAS Data Integration Studio SCD Type 2 Loader transformation, the process detects changes by comparing values in the source data with the current records in the target dimension table. The columns that can be selected for this comparison are the descriptive attributes of the business entity. These are, by definition, the non-key columns. The transformation's properties allow the user to specify which of these non-key columns, when changed, should trigger the creation of a new historical record. By default, all columns that are not designated as business keys are checked for changes.

Why Incorrect

A. business key columns: Business keys are used to uniquely identify and match records between the source and target. They are not used to detect attribute changes for an existing entity.
B. Type 1 columns: While Type 1 columns are non-key columns, changes to them cause an overwrite, not the creation of a new historical record characteristic of the Type 2 process.
C. generated columns: Generated columns (e.g., surrogate keys, effective dates) are created by the loader in the target table; they do not exist in the source data to be checked for changes.

References

1. SAS® 9.4 Data Integration Studio 4.9: User's Guide, Second Edition.

Section: Chapter 22, "Working with Slowly Changing Dimensions" -> "SCD Type 2 Loader Transformation" -> "Step 6: Set the Column Properties for the SCD Type 2 Loader".

Content: The documentation states, "You can specify which columns to check for changed data. By default, all columns that are not business keys are checked." This confirms that the pool of columns for change detection consists of non-key columns.

2. Christie, P. B. (2012). A Template for Slowly Changing Dimensions. SAS Global Forum 2012 Proceedings.

Section: "The SCD Type 2 Loader", Page 4.

Content: The paper explains the loader's function: "The transformation compares the non-key column values in the source data set with the non-key column values in the current rows of the dimension table... If any of the non-key values are different, the transformation adds a new row to the dimension table...". This explicitly identifies non-key columns as the basis for change detection.

Scroll to Top