You design a Business Intelligence (BI) solution by using SQL Server 2008. You plan to transform vehicle survey data from a flat file to a data warehouse. You need to design a solution that performs the following tasks:
-Redirect data of vehicle owners to a table named DimOwner.
-Redirect data of vehicle enthusiasts to a table named DimEnthusiast.
-Log each record’s key along with the data transfer date to an audit table.
What should you do?
A.
Use a Conditional Split component to redirect data to the audit table and to a Multicast component.
Use the Multicast component to redirect the data to the DimOwner and DimEnthusiast tables.
B.
Use a Conditional Split component to redirect data to the audit table and to a second Conditional Split component.
Use the second Conditional Split component to redirect the data to the DimOwner and DimEnthusiast tables.
C.
Use a Multicast component to redirect data to the audit table and to a second Multicast component.
Use the second Multicast component to redirect the data to the DimOwner and DimEnthusiast tables.
D.
Use a Multicast component to redirect data to the audit table and to a Conditional Split component.
Use the Conditional Split component to redirect the data to the DimOwner and DimEnthusiast tables.
Explanation:
Tip: "audit table" = "Multicast / Conditional Split" (in this order)http://msdn.microsoft.com/en-us/library/ms137701(SQL.100).aspx
Multicast Transformation
The Multicast transformation distributes its input to one or more outputs. This transformation is similar to the Conditional Split transformation. Both transformations direct an input to multiple outputs. The difference between the two is that the Multicast transformation directs every row to every output, and the Conditional Split directs a row to a single output.
Using the Multicast transformation, a package can create logical copies of data. This capability is useful when the package needs to apply multiple sets of transformations to the same data. For example, one copy of the data is aggregated and only the summary information is loaded into its destination, while another copy of the data is extended with lookup values and derived columns before it is loaded into its destination.
This transformation has one input and multiple outputs. It does not support an error output.