Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables. What should you do?
A. Create a view on the sales table.
B. Partition the fact tables by day.
C. Create a surrogate key for the time dimension.
D. Change the level of granularity in both fact tables to be the same.
Answer: D
Q2. You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on ServerA. The package includes a data flow and is executed on ServerB. The destination table has its own identity column.
The destination data load has the following requirements:
✑ The identity values from the source table must be used.
✑ Default constraints on the destination table must be ignored.
✑ Batch size must be 100,000 rows.
You need to add a destination and configure it to meet the requirements. Which destination should you use?
A. OLE DB Destination with Fast Load
B. SQL Server Destination
C. ADO NET Destination without Bulk Insert
D. ADO NET Destination with Bulk Insert
E. OLE DB Destination without Fast Load
Answer: A
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms141237.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms139821.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms141095.aspx
Q3. You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The three Execute SQL tasks modify products in staging tables in preparation for a data warehouse load.
The package and all three Execute SQL product tasks have their TransactionOption property set to Supported.
You need to ensure that if any of the three Execute SQL product tasks fail, all three tasks will roll back their changes.
What should you do?
A. Change the TransactionOption property of the package to Required.
B. Change the TransactionOption property of all three Execute SQL product tasks to Required.
C. Move the three Execute SQL product tasks into a Foreach Loop container.
D. Move the three Execute SQL product tasks into a Sequence container.
Answer: A
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms141144.aspx
Q4. You are completing the installation of the Data Quality Server component of SQL Server
Data Quality Services (DQS).
You need to complete the post-installation configuration. What should you do?
A. Run the DQSInstaller.exe command.
B. Install the data providers that are used for data refresh.
C. Install ADOMD.NET.
D. Run the dbimpexp.exe command.
Answer: A
Explanation: References:
http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/gg492277.aspx
Q5. You are creating a SQL Server Master Data Services (MDS) model for a company.
The source data for the company is stored in a single table that contains the manager-to- subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?
A. Organizational
B. Recursive
C. Non-Mandatory Explicit
D. Many-to-Many
Answer: B
Q6. HOTSPOT
You are designing a SQL Server Integration Services (SSIS) package configuration strategy.
The package configuration must meet the following requirements:
✑ Include multiple properties in a configuration.
✑ Force packages to load all settings in the configuration.
✑ Support Encrypting File System (EFS) formats.
You need to select the appropriate configuration. Which configuration type should you use?
To answer, select the appropriate option from the drop-down list in the dialog box.
Answer:
Q7. You install a SQL Server 2021 database engine instance on a production server. A month later, you install SQL Server 2021 Integration Services (SSIS).
You must develop an SSIS project and deploy it to the server by using the Project Deployment model.
Operations log records that are outside the configured retention period must be cleaned automatically.
You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Enable CLR Integration.
B. Enable FILESTREAM with Full Access.
C. Enable the Resource Governor.
D. Change the recovery mode of the msdb database to FULL.
E. Change the Server-wide Default Logging Level in SSISDB to Verbose.
F. Start the SQL Server Browser service.
G. Start the SQL Server Agent service.
Answer: A,G
Q8. DRAG DROP
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:
Explanation:
Box 1: Open the Control Flow designer of the package. Box 2: Add and edit a For Loop container.
Box 3: Configure the EvalExpression property.
Note:
* You create the control flow in a package by using the control flow designer.
Integration Services includes three types of containers that you can use in a control flow.
/ Foreach Loop container
/ For Loop container
/ Sequence container
* For Loop Container
The For Loop container defines a repeating control flow in a package. The loop implementation is similar to the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.
The For Loop container uses the following elements to define the loop:
/ An optional initialization expression that assigns values to the loop counters.
/ An evaluation expression that contains the expression used to test whether the loop
should stop or continue.
/ An optional iteration expression that increments or decrements the loop counter.
Q9. You administer a Microsoft SQL Server 2021 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. DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a data warehouse hosted on SQL Azure.
You have the following requirements:
•A destination table must contain all of the data in two source tables.
•Duplicate records must be inserted into the destination table.
You need to develop a data flow that imports the data while meeting the requirements. How should you develop the data flow? (To answer, drag the appropriate transformation
from the list of transformations to the correct location in the answer area.)
Answer:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms141703.aspx http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms141020.aspx http://msdn.microsoft.com/en-us/library/ms140182.aspx
Q11. You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode.
You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode.
What should you do?
A. Add StartDate and EndDate columns to the customer dimension.
B. Add an IsCurrent column to the customer dimension.
C. Enable Snapshot Isolation on the data warehouse.
D. Add CurrentValue and PreviousValue columns to the customer dimension.
Answer: A
Q12. You develop and deploy a SQL Server Integration Services (SSIS) package. The package is stored in the file system.
You need to execute the package without importing it to the SSIS server.
What should you use to execute the package? (Each correct answer presents a complete solution. Choose all that apply.)
A. catalog.start_package
B. dtexec
C. SQL Server Management Studio
D. SQL Server Agent
Answer: B,D
Explanation:
Ref: http://technet.microsoft.com/en-us/library/ms141708(v=sql.110).aspx
Q13. 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 ($Project::StringVar)
C. INSERT INTO dbo.Table (variablevalue) VALUES (?)
D. INSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar)
Answer: C
Q14. 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
Q15. You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse.
The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis.
You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum.
What should you do?
A. Add an OnError event handler to the SSIS project.
B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
C. Use an msi file to deploy the package on the server.
D. Open a command prompt and run the gacutil command.
E. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
F. Open a command prompt and run the dtutil /copy command.
G. Create a reusable custom logging component and use it in the SSIS project.
H. Configure the SSIS solution to use the Project Deployment Model.
I. Configure the output of a component in the package data flow to use a data tap.
J. Open a command prompt and run the dtexec /rep /conn command.
K. Open a command prompt and run the dtexec /dumperror /conn command.
Answer: I
Explanation:
References:
http://technet.microsoft.com/en-us/library/hh230989.aspx
http://www.rafael-salas.com/2021/01/ssis-2021-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/ms162820.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx http://technet.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx
Q16. To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place.
The methodology has the following requirements:
•The deployment process must be simplified.
•All the logs must be centralized in SQL Server.
•Log data must be available via reports or T-SQL.
•Log archival must be automated.
You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort.
What should you do?
A. Open a command prompt and run the gacutil command.
B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
C. Add an OnError event handler to the SSIS project.
D. Use an msi file to deploy the package on the server.
E. Configure the output of a component in the package data flow to use a data tap.
F. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
G. Open a command prompt and run the dtexec /rep /conn command.
H. Open a command prompt and run the dtutil /copy command.
I. Open a command prompt and run the dtexec /dumperror /conn command.
J. Configure the SSIS solution to use the Project Deployment Model.
K. Create a reusable custom logging component and use it in the SSIS project.
Answer: B
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/ms180378(v=sql.110).aspx
Q17. You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure.
Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart.
You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented.
What should you do?
A. Create a conformed dimension for the geography dimension.
B. Implement change tracking.
C. Create a degenerate dimension for the geography dimension.
D. Create a Type 2 slowly changing dimension for the geography dimension.
Answer: A