Microsoft 70-463 Free Practice Questions

Q1. 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. 

View Answer

Q2. 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. Knowledge Discovery 

C. SQL Profiler 

D. Matching Rule Definition 

View Answer

Q3. You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day. 

The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table. 

You need to reduce the report processing time and minimize the growth of the database. 

What should you do? 

A. Create an indexed view over the fact table to sum orderTotal by month. 

B. Create a view over the fact table to sum orderTotal by month. 

C. Change the granularity of the fact table to month. 

D. Partition the fact table by productKey. 

View Answer

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.) 

View Answer

Q5. 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 time mapping table. 

B. Partition the fact tables by day. 

C. Create a time dimension that can join to both fact tables at their respective granularity. 

D. Join the two fact tables. 

View Answer

Q6. You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables. 

You have the following requirements: 

The deployment model must support changing the content of connection strings by 

using parameters at execution time. 

The deployment model must automatically log events to the SSISOB database. 

Maximize performance at execution time. 

You need to design a solution that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Use a project deployment model. Modify connection manager properties to use project parameters. 

B. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments. 

C. Use a package deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package. 

D. Use a project deployment model. Modify connection manager properties to use package parameters. 

View Answer

Q7. You are using the Master Data Services (MDS) Add-in for Excel to configure the entities in a model- The model consists of two entities: one named Customer and one named State. You opened the Customer entity. 

Currently, data stewards can enter any text value in the Customer entity's State attribute. You must restrict the entry of values in the State attribute to members defined in the State entity. 

You need to configure the State attribute of the Customer entity. 

Which command should you use? (To answer, select the appropriate command in the answer area.) 

View Answer

Q8. A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse. 

The SSIS catalog includes two Environments: 

. Test 

. Production 

Each Environment defines a single Environment Variable named ConnectionString of type 

string. The value of each variable consists of the connection string to the test or production data warehouses. 

You need to execute deployed packages by using either of the defined Environments. 

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.) 

View Answer

Q9. 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. 

What should you use? 

A. script component 

B. custom component 

C. XML source 

D. script task 

View Answer

Q10. You are developing a SQL Server Integration Services (SSIS) package that is ready for deployment to a production server. The package contains sensitive information secured by using the EncryptSensitiveWithUserKey package protection level. 

You are preparing the package for deployment by the production operations team. 

You need to ensure that the production operations team can open and execute the package without re-entering the sensitive information. 

Which three steps 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.) 

View Answer

Q11. You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature. 

You have added a CDC Source component to the data flow to read changed data from the source system. 

You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations. 

Which data flow transformation should you use? 

A. DQS Cleansing 

B. Merge Join 

C. Pivot 

D. Conditional Split 

View Answer

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

The project will be deployed to an SSIS catalog folder where Environments have already been created. You need to deploy the project. 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.validate_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 OnError event text to the table. 

R. Create a table to store error information. Create an error output on each data flow 

destination that writes OnTaskFailed event text to the table. 

View Answer

Q13. You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables. 

You have the following requirements: 

. Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time. 

. Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database. 

. Maximize performance at execution time. 

. Minimize development effort. 

You need to design a solution that meets the requirements. 

What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created. 

B. Use a project deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package. 

C. Use a package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime. 

D. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments. 

View Answer

Q14. You are developing a SQL Server Integration Services (SSIS) package. The package contains a user-defined variable named .Queue which has an initial value of 10. 

The package control flow contains many tasks that must repeat execution until the .Queue variable equals 0. 

You need to enable the tasks to be grouped together for repeat execution. 

Which item should you add to the package? (To answer, select the appropriate item in the answer area.) 

View Answer

Q15. You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named Quotes. The table has no indexes and consists of seven columns: 

 [ID] 

[QuoteDate] 

[Open] 

[Close] 

[High] 

[Low] 

[Volume] 

Each of the following queries must be able to use a columnstore index: 

SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] 

BETWEEN '20100101' AND '20101231'. 

SELECT AVG([High] - [Low]) AS [AverageRange] FROM Quotes WHERE 

[QuoteDate] BETWEEN '20100101' AND '20101231'. 

SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] 

BETWEEN '20100101' AND '20101231'. 

You need to ensure that the indexing strategy meets the requirements. The strategy must also minimize the number and size of the indexes. 

What should you do? 

A. Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate]. 

B. Create three coiumnstore indexes: 

One containing [QuoteDate] and [Close] 

One containing [QuoteDate], [High], and [Low] 

One containing [QuoteDate] and [Volume] 

C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and 

[Volume]. 

D. Create two columnstore indexes: 

One containing [ID], [QuoteDate], [Volume], and [Close] 

One containing [ID], [QuoteDate], [High], and [Low] 

View Answer

Q16. Topic 2) 

You are the administrator for a Data Quality Server. You are adding a user who must have permission to: 

Edit and execute a project 

View the activity monitoring data 

This user must not be able to: 

Perform any kind of knowledge management 

Create or change a knowledge base 

Terminate an activity or perform administrative duties 

You need to develop a Transact-SQL (T-SQL) script to meet these requirements. 

What should you do? (To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area.) 

View Answer