You design a Business Intelligence (BI) solution by using SQL Server 2008. The solution includes a data warehouse that has an online transaction processing (OLTP) database as the data source.
The tables in the OLTP database do not include date or time information. On each execution, the SQL Server 2008 Integration Services (SSIS) package that copies the data must reload and process the entire dataset.
You plan to improve the process of loading the data warehouse. You need to ensure that the following requirements are met:
-Only new and modified data is processed.
-All modifications of the rows caused due to insert, update, and delete activities are processed.
-The impact of the loading process on the source system is minimal.
Which action should you perform on the tables that are involved in the load process?
A.
Set up the Change Tracking feature.
B.
Set up the Change Data Capture (CDC) feature.
C.
Create timestamp columns.
D.
Create Data Manipulation Language (DML) triggers.
Explanation:
Tip: "Only new and modified data is processed" = "Change Data Capture"Change Data Capture
Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.