Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You are creating a SQL Server Master Data Services (MDS) model. This model is used to store a master list of products.
An attribute must be added to the Product entity to define the sales manager responsible for each product.
You need to create an attribute in the Product entity that prevents users from entering invalid sales manager values.
Which type of attribute should you create?
A. Recursive
B. Explicit
C. Domain-based
D. User-defined
E. Derived
F. Parent
Answer: C
Explanation: References: http://msdn.microsoft.com/en-us/library/bb190163.aspx
http://msdn.microsoft.com/en-us/library/ee633737.aspx http://msdn.microsoft.com/en-us/library/ee633759.aspx http://msdn.microsoft.com/en-us/library/ee633745.aspx
http://msdn.microsoft.com/en-us/library/ee633724.aspx http://msdn.microsoft.com/en-us/library/ee633733.aspx http://msdn.microsoft.com/en-us/library/ff487058.aspx
Q2. You are building a fact table in a data warehouse.
The table must have a columnstore index. The table cannot be partitioned.
You need to design the fact table and load it with data.
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:
Q3. You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort.
What should you do?
A. Create an Integration Services package and use a Fuzzy Lookup transform.
B. Create a Master Data Services (MDS) model.
C. Create a Data Quality Project.
D. Create an Integration Services package and use a DQS Cleansing transform.
E. Create an Integration Services package and use a Fuzzy Grouping transform.
Answer: D
Explanation: The DQS Cleansing transformation uses Data Quality Services (DQS) to correct data from a connected data source, by applying approved rules that were created for the connected data source or a similar data source. To determine whether the data has to be corrected, the DQS Cleansing transformation processes data from an input column when the following conditions are true:
* The column is selected for data correction.
* The column data type is supported for data correction.
* The column is mapped a domain that has a compatible data type.
* The transformation also includes an error output that you configure to handle row-level errors
Q4. You plan to deploy a SQL Server Integration Services (SSIS) project by using the project deployment model.
You need to monitor control flow tasks to determine whether any of them are running longer than usual. Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list ofactions to the answer area and arrange them in the correct order.)
Answer:
Q5. You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).
The package includes custom scripts that must be upgraded.
You need to upgrade the package to SQL Server 2012.
Which tool should you use?
A. SSIS Upgrade Wizard in SQL Server 2008 BIDS
B. SSIS Upgrade Wizard in SQL Server Data Tools
C. SQL Server DTExecUI utility (dtexecui.exe)
D. SQL Server dtexec utility (dtexec.exe)
Answer: B
Q6. A new SQL Server Integration Services (SSIS) project is deployed to the SSIS catalog.
To troubleshoot some data issues, you must output the data streaming through several data flows into text files for further analysis. You have the list of data flow package paths and identification strings of the various task components that must be analyzed.
You need to create these output files with the least amount of administrative and development effort.
Which three stored procedures should you execute 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:
Q7. 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 Windows Azure SQL Database. 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. Multicast
B. Conditional Split
C. Script Task
D. Data Conversion
Answer: C
Q8. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure.
The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model.
Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule. Each job passes a different folder path to the package.
You need to configure the package to accept the folder path from each job.
Which package configuration should you use?
A. .dtsConfig file
B. Registry Entry
C. Environment Variable
D. Parent Package Variable
E. XML Configuration File
Answer: C
Reference: http://msdn.microsoft.com/en-us/library/ms345179.aspx
http://msdn.microsoft.com/en-us/library/ms141708.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://msdn.microsoft.com/en-us/library/hh213296.aspx http://msdn.microsoft.com/en-us/library/hh213293.aspx
Q9. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1?
A. DateHired
B. DepartmentID
C. EmployeeID
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Answer: C
Q10. You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project.
Which stored procedure should you use?
A. catalog.set_environment_reference_type
B. catalog.set_environment_property
C. catalog.create_environment_reference
D. catalog.create_environment
Answer: C
Q11. You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).
You need to add a task or component to the package that allows you to implement the SCD logic.
What should you use?
A. a Script component
B. an SCD component
C. an Aggregate component
D. a Merge component
Answer: D
Q12. You are deploying a new SQL Server Integration Services (SSIS) package to five servers.
The package must meet the following requirements:
. .NET Common Language Runtime (CLR) integration in SQL Server must not be enabled. . The Connection Managers used in the package must be configurable without
editing and redeploying the package.
. The deployment procedure must be automated as much as possible.
. Performance must be maximized.
You need to set up a deployment strategy that meets the requirements.
What should you do?
A. Add an OnError event handler to the SSIS project.
B. Use an msi file to deploy the package on the server.
C. Open a command prompt and run the gacutil command.
D. Open a command prompt and run the dtutil /copy command.
E. Open a command prompt and run the dtexec /rep /conn command.
F. Open a command prompt and run the dtexec /dumperror /conn command.
G. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
H. Create a reusable custom logging component and use it in the SSIS project.
I. Configure the SSIS solution to use the Project Deployment Model.
J. Configure the output of a component in the package data flow to use a data tap.
K. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
Answer: D
Q13. You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data.
You need to use DQS profiling to obtain the required statistics.
Which DQS activity should you use?
A. Cleansing
B. Matching
C. Knowledge Discovery
D. Matching Policy
Answer: A
Q14. You are developing a SQL Server Integration Services (SSIS) package.
You need to design a package to change a variable value during package execution by using the least amount of development effort.
What should you use?
A. Express on task
B. Data Cleansing transformation
C. Fuzzy Lookup transformation
D. Term Lookup transformation
E. Data Profiling task
Answer: A
Q15. You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS). The package includes custom scripts that must be upgraded.
You need to upgrade the package to SQL Server 2012.
Which tool should you use?
A. SQL Server dtexec utility (dtexec.exe)
B. SQL Server DTExecUI utility (dtexecui.exe)
C. SSIS Upgrade Wizard in SQL Server Data Tools
D. SQL Server Integration Services Deployment Wizard
Answer: C
Explanation: Use the SSIS Package Upgrade Wizard to upgrade SQL Server 2005 Integration Services (SSIS) packages and SQL Server 2008 Integration Services (SSIS) packages to the package format for the current (2012) release of SQL Server Integration Services.
Q16. You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours.
Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.
You need to partition the table to maximize the performance of queries. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Partition the fact table by month, and compress each partition.
B. Partition the fact table by week.
C. Partition the fact table by year.
D. Partition the fact table by day, and compress each partition.
Answer: D