Q1. You are installing SQL Server Data Quality Services (DQS).
You need to give specific users access to the Data Quality Server.
Which SQL Server application should you use?
A. SQL Server Configuration Manager
B. SQL Server Data Tools
C. SQL Server Management Studio
D. Data Quality Client
Answer: C
Q2. A SQL Server Integration Services (SSIS) package is designed to download data from a financial database hosted in SQL Azure.
The connection string to the financial database is defined as a project parameter named FinConStr. The parameter value must be stored securely and must be set explicitly every time the package is executed.
You need to configure the parameter to meet the requirements.
What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
Answer:
Q3. You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application to modify an existing knowledge base.
In the mapping configuration, two of the three columns are mapped to existing domains in the knowledge base. The third column, named Group, does not yet have a domain.
You need to complete the mapping of the Group column.
What should you do?
A. Map a composite domain to the source column.
B. Create a composite domain that includes the Group column.
C. Add a domain for the Group column.
D. Add a column mapping for the Group column.
Answer: C
Q4. You are designing a data warehouse for a fresh food distribution business that stores sates by individual product. It stores sales targets by product category. Products are classified into subcategories and categories.
Each product is included in only a single product 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 product
. factProductTarget, used to record the monthly sales targets by product category
Reports must be developed against the warehouse that reports product sales by product, category and subcategory, and product sales targets.
You need to design the product dimension. The solution should use as few tables as possible while supporting all the requirements.
What should you do?
A. Create two product tables, dimProduct and dimProductCategory. Connect factSales to dimProduct and factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
B. Create one product table, dimProduct, which contains product detail, category, and subcategory columns. Connect factSales to dimProduct with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factProductTarget.
C. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory, and a fourth bridge table that joins products to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes.
D. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory. Connect factSales to all three product tables and connect factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
Answer: B
Q5. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You add an Execute SQL task to the control flow.
The task must execute a simple INSERT statement. The task has the following requirements:
. The INSERT statement must use the value of a string package variable. The variable name is StringVar. . The Execute SQL task must use an OLE DB Connection Manager.
In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter.
You must configure the SQLStatement property of the Execute SQL task.
Which SQL statement should you use?
A. INSERT INTO dbo.Table (variablevalue) VALUES ($StringVar)
B. INSERT INTO dbo.Table (variablevalue) VALUES (0)
C. INSERT INTO dbo.Table (variablevalue) VALUES (@0)
D. INSERT INTO dbo.Table (variablevalue) VALUES (?)
Answer: D
Explanation: References: http://msdn.microsoft.com/en-us/library/ms141003.aspx
http://msdn.microsoft.com/en-us/library/ms140355.aspx http://msdn.microsoft.com/en-us/library/cc280502.aspx
Q6. 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 seven source-to-destination execution trees.
Only three 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. Set the EngineThreads property of the data flow task to 7.
B. Set the MaxConcurrentExcecutables property of the package to 7.
C. Create seven data flow tasks that contain one source-to-destination execution tree each.
D. Place the data flow task in a For Loop container that is configured to execute seven times.
Answer: A
Q7. You are designing a SQL Server Integration Services (SSIS) package. The package moves order-related data to a staging table named Order. Every night the staging data is truncated and then all the recent orders from the online store database are inserted into the staging table.
Your package must meet the following requirements:
. If the truncate operation fails, the package execution must stop and report an error. . If the Data Flow task that moves the data to the staging table fails, the entire refresh operation must be rolled back. . For auditing purposes, a log entry must be entered in a SQL log table after each execution of the Data Flow task.
The TransactionOption property for the package is set to Required.
You need to design the package to meet the requirements.
How should you design the control flow for the package? (To answer, drag the appropriate setting from the list of settings to the correct location or locations in the answer area.)
Answer:
Q8. You are developing a SQL Server Integration Services (SSIS) package.
The package is stored as the myPackage.dtsx file in the root directory of the C drive of the
SSIS server.
You need to run the package from the command prompt.
Which command should you use?
A. dtexec /sql myPackage
B. dtexec /d "C:\File System\myPackage.dtsx"
C. dtexec /f "C:\myPackage.dtsx"
D. dtexec /com "myPackage.dtsx"
Answer: C
Q9. You are creating a sales data warehouse. When a product exists in the product dimension, you update the product name. When a product does not exist, you insert a new record.
In the current implementation, the DimProduct table must be scanned twice, once for the insert and again for the update. As a result, inserts and updates to the DimProduct table take longer than expected.
You need to create a solution that uses a single command to perform an update and an insert.
How should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag the appropriate answer choice from the list of options to the correct location or locations in the answer area.
You may need to drag the split bar between panes or scroll to view content.)
Answer:
Q10. You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on SQL Azure. One of the columns in the data source is named ProductCode.
Some of the data to be loaded will reference products that need special processing logic in the data flow.
You need to enable separate processing streams for a subset of rows based on the source product code.
Which data flow transformation should you use?
A. Audit
B. Source Assistant
C. Script Task
D. Conditional Split
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms137640.aspx http://msdn.microsoft.com/en-us/library/ms141150.aspx http://msdn.microsoft.com/en-us/library/ff929138.aspx http://msdn.microsoft.com/en-us/library/ff929116.aspx