Q1. You are designing an extract, transform, and load (ETL) solution that loads data into dimension tables. The ETL process involves many transformation steps.
You need to ensure that the design can provide:
Auditing information for compliance and business user acceptance Tracking and unique identification of records for troubleshooting and error correction
What should you do?
A. Create a SQL Common Language Runtime (SQLCLR) component that records all
transformation steps in a Microsoft SharePoint list.
B. Create a version control repository for the transformation steps in Team Foundation Server (TFS).
C. Develop a custom data lineage solution.
D. Develop a Data Quality Services (DQS) solution.
Answer: C
Q2. You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.
The reference data to be used in the transformation changes with every package execution.
Common language runtime (CLR) integration cannot be enabled on the SQL Server database where the reference table is located.
You need to configure the Fuzzy Lookup transformation in the most efficient manner.
What should you do?
A. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
C. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
D. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
Answer: B
Q3. You are developing a SQL Server Integration Services (SSIS) package.
The package contains several tasks that must repeat until an expression evaluates to
FALSE.
You need to add and configure a container to enable this design.
Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order,)
Answer:
Q4. You are designing a data warehouse that uses SQL Server 2012. You are preparing to update the contents of a fact table that contains a non-clustered columnstore index.
You need to run an update statement against the table.
What should you do first?
A. Pause the columnstore index.
B. Change the recovery model of the database to Bulk-logged.
C. Change the non-clustered columnstore index to be a clustered columnstore index.
D. Drop the columnstore index.
Answer: A
Q5. You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a data warehouse that is hosted on Windows Azure SQL Database. The package contains a data flow task that has 10 source-to-destination execution trees.
Only five of the source-to-destination execution trees are running in parallel.
You need to ensure that all the execution trees run in parallel.
What should you do?
A. Add OnError and OnWarning event handlers.
B. Create a new project and add the package to the project.
C. Query the ExecutionLog table.
D. Add an Execute SQL task to the event handlers.
Answer: A
Q6. You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure SQL Database database. The package consists of several data flow tasks.
The package has the following auditing requirements:
. If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.
. The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.
You need to ensure that auditing is configured to meet these requirements.
What should you do?
A. Use an event handler for OnError for the package.
B. Use an event handler for OnError for each data flow task.
C. Use an event handler for OnTaskFailed for the package.
D. View the job history for the SQL Server Agent job.
E. View the All Messages subsection of the All Executions report for the package.
F. Store the System::SourceID variable in the custom log table.
G. Store the System::ServerExecutionID variable in the custom log table.
H. Store the System::ExecutionInstanceGUID variable in the custom log table.
I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
K. Deploy the project by using dtutil.exe with the /COPY DTS option.
L. Deploy the project by using dtutil.exe with the /COPY SQL option.
M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
N. Create a SQL Server Agent job to execute the SSISDB.catalog.va!idate_project stored procedure.
O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
P. Create a SQL Server Agent job to execute the
SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.
Q. Create a table to store error information. Create an error output on each data flow
destination that writes OnTaskFailed event text to the table.
Answer: C
Q7. You are designing a data warehouse for a software distribution business that stores sales by software title. It stores sales targets by software category. Software titles are classified into subcategories and categories. Each software title is included in only a single software subcategory, and each subcategory is included in only a single category. The data warehouse will be a data source for an Analysis Services cube.
The data warehouse contains two fact tables:
. factSales, used to record daily sales by software title
. factTarget, used to record the monthly sales targets by software category
Reports must be developed against the warehouse that reports sales by software title, category and subcategory, and sales targets.
You need to design the software title dimension. The solution should use as few tables as possible while supporting all the requirements.
What should you do?
A. Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory and a fourth bridge table that joins software titles to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes.
B. Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory. Connect factSales to all three tables and connect factTarget to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
C. Create one table, dimSoftware, which contains Software Detail, Category, and Subcategory columns. Connect factSales to dimSoftware with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factTarget.
D. Create two tables, dimSoftware and dimSoftwareCategory. Connect factSales to dimSoftware and factTarget to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
Answer: C
Q8. You are adding a new capability to several dozen SQL Server Integration Services (SSIS) packages.
The new capability is not available as an SSIS task. Each package must be extended with the same new capability.
You need to add the new capability to all the packages without copying the code between packages.
What should you do?
A. Use the Expression task.
B. Use the Script task.
C. Develop a custom task.
D. Use the Script component,
E. Develop a custom component.
Answer: C
Explanation: References:
http://msdn.microsoft.com/en-us/library/ms135965.aspx http://msdn.microsoft.com/en-us/library/ms345161.aspx
Q9. You administer a Microsoft SQL Server 2012 database. The database contains a table that has the following definition:
You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
The first row of the file contains the first row of data.
Each record is of the same length.
The date follows the U.S. date format.
The file supports international characters.
What should you do? (To answer, simply select the option or options in the answer area that you would configure.)
Answer:
Q10. You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night.
The SQL Azure data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching string from a reference table of allowed values. The number of rows in the reference table is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.
The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.
What should you do?
A. Decrease the maximum number of matches per lookup.
B. Change the similarity threshold to 0.55.
C. Change the Exhaustive property to True.
D. Increase the maximum number of matches per lookup.
Answer: B
Reference: http://msdn.microsoft.com/en-us/library/ms137786.aspx