Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 261:

  You have a SQL Server Integration Services (SSIS) package. The package contains a script task that has the following comment.

  // Update DataLoadBeginDate variable to the beginning of yesterday

  The script has the following code.

  Dts.variables["User::DataLoadBeginDate''].Value = DataTime.Today.AddDays(-1);

  The script task is configured as shown in the exhibit. (Click the Exhibit button.)

  

  When you attempt to execute the package, the package fails and returns the following error message: `'Error: Exception has been thrown by the target of an invocation.''

  You need to execute the package successfully.

  What should you do?

  A. Add the dataLoadBeginDate variable to the ReadOnlyVariables property.
  B. Add the DataLoadBeginDate variable to the ReadWriteVariables property.
  C. Modify the entry point of the script.
  D. Change the scope of the DataLoadBeginDate variable to Package.
  B. Add the DataLoadBeginDate variable to the ReadWriteVariables property.

  ---

  You add existing variables to the ReadOnlyVariables and ReadWriteVariables lists in the Script Task Editor to make them available to the custom script. Within the script, you access variables of both types through the Variables property of the Dts object.

  References: https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/using-variables-in-the-script-task?view=sql-server-2017

• Question 262:

  You are editing a SQL Server Integration Services (SSIS) package that contains a task with a sensitive property.

  You need to create a project parameter and configure it so that its value is encrypted when it is deployed to the SSIS catalog.

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

  Select and Place:

  

  

• Question 263:

  You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog. One of the steps in each package accesses an FTP site to download sales transaction

  data.

  You create project parameters to store the username and password that are used to access the FTP site.

  You need to ensure that the username and password values are encrypted when they are deployed.

  What should you do?

  A. Set the Sensitive property of the parameters to True.
  B. Set the Protection Level property of the package to EncryptSensitiveWithUserKey.
  C. Change the parameters to package parameters.
  D. Change the project to the Legacy Deployment model.
  A. Set the Sensitive property of the parameters to True.

  ---

  Sensitive. If the value of this property is True, parameter values are encrypted in the catalog. References: https://www.microsoftpressstore.com/articles/article.aspx?p=2224037andseqNum=2

• Question 264:

  You are editing a SQL Server Integration Services (SSIS) package.

  You need to edit the Script task that has an expression defined.

  Which task should you select? To answer, select the appropriate setting or settings in the answer area.

  Hot Area:

  

  

• Question 265:

  You have a database named Sales and a data warehouse named DataW.

  From Sales, you plan to bulk insert to a table in DataW that is more than 2 TB. The process will insert a minimum of 11 million rows simultaneously.

  You need to identify which data storage strategy must be used to minimize load times.

  Which data storage should you identify?

  A. a nonclustered columnstore
  B. a clustered index
  C. a durable In-Memory OLTP
  D. a clustered columnstore
  D. a clustered columnstore

  ---

  To achieve the fastest loading speed for moving data into a data warehouse table, load data into a staging table. Define the staging table as a heap and use round-robin for the distribution option. No such option is available here, however.

  Loading into a clustered columnstore is also a known practice for loading data into a data warehouse.

  Incorrect Answers:

  C: In-Memory OLTP can significantly improve the performance of transaction processing, data ingestion and data load, and transient data scenarios. It does not fit a data warehouse scenario. References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/guidance-for-loading-data

• Question 266:

  You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL Azure database. The project uses the Package Deployment Model. This project is deployed to SQL Server on a test server.

  You need to ensure that the project is deployed to the SSIS catalog on the production server.

  What should you do?

  A. Open a command prompt and run the dtexec /dumperror /conn command.
  B. Create a reusable custom logging component and use it inthe SSIS project.
  C. Open a command prompt and run the gacutil command.
  D. Add an OnError event handler to the SSIS project.
  E. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
  F. Open a command prompt and run the dtexec /rep /conn command.
  G. Open a command prompt and run the dtutil /copy command.
  H. Use an msi file to deploy the package on the server.
  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.
  I. Configure the SSIS solution to use the Project Deployment Model.

  ---

  References: http://msdn.microsoft.com/en-us/library/hh231102.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx

• Question 267:

  Your company has several line-of-business applications. The applications use a server that has SQL Server installed and contains several databases.

  You need to ensure that the business applications can access curated and validated data from the databases.

  Which features should you deploy to the server?

  A. SQL Server Integration Services (SSIS)
  B. Master Data Services (MDS) subscription
  C. Data Quality Services (DQS)
  D. SQL Server Analysis Services (SSAS) dimension hierarchies
  C. Data Quality Services (DQS)

• Question 268:

  You are designing a fact table in a SQL Server database.

  The fact table must meet the following requirements:

  

  Include a columnstore index.

  

  Allow users to choose up to 10 dimension tables and up to five facts at one time.

  

  Maximize performance of queries that aggregate measures by using any of the 10 dimensions.

  

  Support billions of rows.

  

  Use the most efficient design strategy.

  You need to design the fact table to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

  A. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the dimensional key columns.
  B. Design a fact table with 5 dimensional key columns and 10 measurecolumns. Place the columnstore index on the measure columns.
  C. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on the dimensional key columns and the measure columns.
  D. Design a fact table with 10dimensional key columns and 5 measure columns. Place the columnstore index on only the measure columns.
  C. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on the dimensional key columns and the measure columns.

• Question 269:

  You are developing a SQL Server Integration Service (SSIS) package. The package loads a customer dimension table by using a data flow task.

  Changes to the customer attributes must be tracked over time.

  You need to produce a checksum value to identify the rows that have changed since the last Extract, Transform and Load (ETL) process execution. You need to use the least amount of development effort to achieve this goal.

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

  Hot Area:

  

  

• Question 270:

  Reporting from a Star schema is simpler than reporting from a normalized online transactional processing (OLTP) schema. What are the reasons for wanting simpler reporting? (Choose all that apply.)

  A. A Star schema typically has fewer tables than a normalized schema. Therefore, queries are simpler because they require fewer joins.
  B. A Star schema has better support for numeric data types than a normalized relational schema; therefore, it is easier to create aggregates.
  C. There are specific Transact-SQL expressions that deal with Star schemas.
  D. A Star schema is standardized and narrative; you can find the information you need for a report quickly.
  A. A Star schema typically has fewer tables than a normalized schema. Therefore, queries are simpler because they require fewer joins.
  D. A Star schema is standardized and narrative; you can find the information you need for a report quickly.

  ---

  References: http://www.vertabelo.com/blog/technical-articles/data-warehouse-modeling-star-schema-vs-snowflake-schema