You are developing a SQL Server Integration Services (SSIS) package.
The package uses a data flow task to source data from a SQL Server database for loading
into a dimension table in a data warehouse.
You need to create a separate data flow path for data that has been modified since it was
last processed.
Which data flow components should you use to identify modified data? (Each correct answer
presents a complete solution. Choose all that apply.)
A.
Multicast
B.
Data Conversion
C.
Lookup
D.
Slowly Changing Dimension
E.
Aggregate
Explanation:
A: The transformation that distributes data sets to multiple outputs.
The transformation that distributes data sets to multiple outputs.
C: Lookup Transformation
The transformation that looks up values in a reference table using an exact match.
Note:* SQL Server Integration Services provides three different types of data flow components:
sources, transformations, and destinations. Sources extract data from data stores such as
tables and views in relational databases, files, and Analysis Services databases.
Transformations modify, summarize, and clean data. Destinations load data into data stores
or create in-memory datasets.
Incorrect:
Not B: Data Conversion Transformation
The transformation that converts the data type of a column to a different data type.
Not D: Slowly Changing Dimension Transformation
The transformation that configures the updating of a slowly changing dimension.
Not E: The Aggregate transformation applies aggregate functions, such as Average, to
column values and copies the results to the transformation output. Besides aggregate
functions, the transformation provides the GROUP BY clause, which you can use to specify
groups to aggregate across.
A Multicast transformation directs every row to every output.
Not ‘A’
0
0
the fact that Multicast directs a row to every output doesn’t mean that Multicast is not able to do some logical reasoning, so i think that A is still a valid answer
0
0
Thats not true! Can you give an example?
0
0
…(Each correct answer presents a complete solution. Choose all that apply.)
Without some kind of extra manipulations, Multicast cannot (and will not), create a separate data flow path for data that has been modified since it was loaded. Please, go to MSDN and read up on what Multicast does again. Better yet, try using it in a data flow in SSDT and see what output you get.
Answers: SCD (Changing/Changed Dimension) & Lookup.
1
0
Why not SCD with Changing attributes?
0
0
the question is a bit crap.. for me A doesn t help you identify modified data.. if so give an example ?.. Look up ok why not .. but SCD is actually the first choice you should tick , it s been creating for this very precise purpose , if not then tell me why ?
0
0
The only correct answers would med Lookup and SCD task. It’s not illegal to think use SSIS to test it.
0
0
The scenario described in the question requires an SCD (rows modified since the last processing… any of insert/update/delete). Apart from an SCD, a Lookup transform could also do a row-to-row or column-to-column comparison with a target table to see if data change has occurred.
Correct options are C & D. Multicast is never an option here.
0
0
agree it makes sense to use a lookup and a SCD
0
0
A and C
A(first part of solution) – to …. create a separate data flow path ….
C(second part of solution) – …. to identify modified data ….
0
0
Have you ever tried to use an SCD in a project and see what it does?! It actually splits input data into multiple output as described in the question scenario above… I would have uploaded a screen-shot of a project if this textbox would have allowed!! Multicast is completely off-the-mark.
0
0
I think the correct answer should be A & D
The question says “each answer present a complete solution” only A and D can satisfy that requirement in my opinion.
checkout these links;
https://msdn.microsoft.com/en-us/library/ms137701(v=sql.110).aspx
https://msdn.microsoft.com/en-us/library/ms141715(v=sql.110).aspx
0
0
Anyone who think A is a correct answer, do not try this exam…
0
0
Hahahhaha!!
0
0
lol. so true!
0
0
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/slowly-changing-dimension-transformation
When you read the explanation in the link above, the question is answered for you.
It states (for a Slowly Changing Dimension) the following…
“The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables.”
The question states…
“The package uses a data flow task to source data from a SQL Server database for loading into a dimension table in a data warehouse.”
Seems clear that this answers the question on Multicast or SCD.
0
0