Microsoft 70-463 Exam Practice Test Instant Access

Question 1

You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model.

The project is deployed to a single SSIS catalog, and transfers data to and from multiple databases hosted on SQL Server.

The project must be configured to be able to export data to and from five different production servers that run SQL Server 2012. Each target server requires different values for connection strings and parameters in the SSIS project.

You need to meet the requirements by using the least amount of administrative effort.

What should you do?

AFor each target server, create separate registry entry configurations. Select the registry entry at package execution time.

BFor each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time.

CCreate one SSIS catalog Environment. Change the values of each Environment variable at package execution time.

DFor each target server, create a separate XML configuration file. Select the XML configuration file at package execution time.

Answer : B

Question 2

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?

AOrganizational

BRecursive

CNon-Mandatory Explicit

DMany-to-Many

Answer : B

Question 3

To support the implementation of new reports, Active Directory data will be downloaded to a SQL Server database by using a SQL Server Integration Services (SSIS) 2012 package.

The following requirements must be met:

All the user information for a given Active Directory group must be downloaded to a SQL Server table.

The download process must traverse the Active Directory hierarchy recursively.

You need to configure the package to meet the requirements by using the least development effort.

Which item should you use?

AScript task

BScript component configured as a transformation

CScript component configured as a source

DScript component configured as a destination

Answer : C

Question 4

You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012. All packages are deployed to the SSIS catalog.

Package developers in your company must have permission to perform the following tasks only on their own projects:

*View projects and package

*View Environments

*Validate packages

*Execute packages

You need to grant rights to the developers without assigning unnecessary privileges.

What should you do? (Each correct answer presents part of a solution. Choose all that apply.)

AAdd developer logins to the db_ssisltduser role in the msdb database.

BAdd developer logins to the db_ssisoperator role in the msdb database.

CGrant Execute permission in the projects for the developer logins.

DGrant Read permission in the SSIS catalog folder, the projects, and the Environments.

EAdd developer logins to the ssis_admin role in the SSISDB database.

FGrant Modify permission in the projects for the developer logins.

Answer : B, D

B: db_ssisoperator

* Read actions

Enumerate all packages.

View all packages.

Execute all packages.

Export all packages.

Execute all packages in SQL Server Agent.

* Write actions

None

D: Need read permissions on the Environments in order to be able to view them.

Incorrect:

Not A: db_ssisltduser

* Read actions

Enumerate own packages.

Enumerate all packages.

View own packages.

Execute own packages.

Export own packages.

* Write Actions

Import packages.

Delete own packages.

Change own package roles.

Not E: db_ssisadmin

Too many permissions (such as delete all packages).

Question 5

You are designing a data warehouse that contains a product dimension.

The product dimension contains the original product attributes as well as the most recent set of updated attributes. When a product is updated, the original product attributes must be preserved.

The product dimension design must:

Support a Type 3 Slowly Changing Dimension (SCD) process

Minimize the total storage consumed

You need to design a solution that meets these requirements.

What should you do?

ADesign two tables. One table will hold the original product surrogate key and attributes. The other table will hold the most recent product surrogate key and attributes.

BDesign a table as a Type 2 SCD dimension that holds the surrogate key for each product and its attributes. Limit the number of records per product within the table to two.

CDesign a table to hold the surrogate key for each product and its attributes as well as additional columns to store the original attributes.

DDesign a table to hold the surrogate key for each product and its attributes. Add a column to store an indicator that identifies the record as original data or recent data.

Answer : C

Question 6

Which of the following statements about simple and complex data movements are true? (Choose all that

apply.)

ASimple data movements only have a single data source and a single data destination.

BComplex data movements require data to be transformed before it can be stored at the destination.

CIn simple data movements, data transformations are limited to data type conversion.

DIn complex data movements, additional programmatic logic is required to merge source data with
destination data.

Answer : B, D

References:

http://colleenmorrow.com/2014/12/09/exam-prep-70-463-introduction-to-ssdt/

Question 7

Answer : J

References:

http://msdn.microsoft.com/en-us/library/ms140246.aspx

http://msdn.microsoft.com/en-us/library/hh231187.aspx

Microsoft Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 70-463 Exam Practice Test