Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 111:

  You are installing SQL Server Data Quality Services (DQS).

  You need to give users belonging to a specific Active Directory group access to the Data Quality Server.

  Which SQL Server application should you use?

  A. Data Quality Client with administrative credentials
  B. SQL Server Configuration Manager with local administrative credentials
  C. SQL Server Data Tools with local administrative permissions
  D. SQL Server Management Studio with administrative credentials
  D. SQL Server Management Studio with administrative credentials

• Question 112:

  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. Join the two fact tables.
  B. Merge the fact tables.
  C. Create a time dimension that can join to both fact tables at their respective granularity.
  D. Create a surrogate key for the time dimension.
  C. Create a time dimension that can join to both fact tables at their respective granularity.

  ---

  Microsoft SQL Server Analysis Services, a time dimension is a dimension type whose attributes represent time periods, such as years, semesters, quarters, months, and days. The periods in a time dimension provide time-based levels of granularity for analysis and reporting. The attributes are organized in hierarchies, and the granularity of the time dimension is determined largely by the business and reporting requirements for historical data

  References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/database-dimensions-create-a-date-type-dimension

• Question 113:

  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.

  Hot Area:

  

  

• Question 114:

  You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  You are in the process of creating a SQL Server Integration Services (SSIS) package. The package will be configured to import data into a SQL Azure hosted data warehouse. You have decided to make use of the Environment Variable

  package configuration as part of your solution.

  Which of the following is TRUE with regards to the Environment Variable package configuration?

  A. It stores the property path inside the package and the value outside the package.
  B. It stores both the property path and the value outside the package.
  C. It is used to fetch a value from a variable in a calling package.
  D. It is the most commonly used configuration type.
  A. It stores the property path inside the package and the value outside the package.

• Question 115:

  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
  A. script component

  ---

  Creating a Source with the Script Component

  You use a source component in the data flow of an Integration Services package to load data from a data source to pass on to downstream transformations and destinations.

  References: https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting-data-flow-script-component-types/creating-a-source-with-the-script-component?view=sql-server-2017

• Question 116:

  You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database.

  The current design of the package control flow is shown in the answer area.

  

  The Insert New Orders Data Flow task must meet the following requirements:

  

  Usage of the tempdb database should not be impacted.

  

  Concurrency should be maximized, while only reading committed transactions.

  

  If the task fails, only that task needs to be rolled back.

  You need to configure the Insert New Orders Data Flow task to meet the requirements.

  

  How should you configure the transaction properties?

  IsolationLevel = ReadCommited TransactionOption = Required

• Question 117:

  Note: This question is a part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series.

  Information and details provided in a question apply only to that question.

  You are developing a SQL Server Integration Services (SSIS) project with multiple packages to copy data to a Microsoft Azure SQL database.

  An automated process must validate all related Environment references, parameter data types, package references, and referenced assemblies. The automated process must run on a regular schedule.

  You need to establish the automated validation process by using the least amount of administrative effort.

  What should you do?

  A. Use an event handler for OnError for each data flow task.
  B. Use an event handler for OnTaskFailed for the package.
  C. View the job history for the SQL Server Agent job.
  D. View the All Messages subsection of the All Executions report for the package.
  E. Store the System::SourceID variable in the custom log table
  F. Store the System::ServerExecutionID variable in the custom log table.
  G. Store the System::ExecutionInstanceGUID variable in the custom log table.
  H. Deploy the project by using dtutil.exe with the /COPY DTS option.
  I. Deploy the project by using dtutil.exe with the /COPY SQL option.
  J. Deploy the .ispac file by using the Integration Services Deployment Wizard.
  K

• Question 118:

  You are the data steward at your company.

  Duplicate customers exist in a Microsoft Excel workbook. You create a Data Quality Services (DQS) knowledge base and matching policy to identify these duplicate customers.

  You need to identify the duplicate customers.

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

  Hot Area:

  

  

• Question 119:

  You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for ABC.com's database developers. You are currently discussing the transformations data flow component. You are highlighting the Pivot Transformation type. Which of the following is TRUE with regards to the Pivot Transformation type?

  A. It is used to obtain a randomly selected subset of an input dataset.
  B. It sorts input data in ascending or descending order and copies the sorted data to the transformation output.
  C. It makes a normalized data set into a less normalized but more compact version.
  D. It creates new column values by applying expressions to transformation input columns.
  E. It creates a sample data set by selecting a percentage of the transformation input rows.
  C. It makes a normalized data set into a less normalized but more compact version.

• Question 120:

  You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Microsoft Azure SQL Database database. The package has been deployed to a 64-bit production SQL Server.

  The package fails when executed on the production server.

  You need to ensure that the package can load the 32-bit Excel workbook data without errors.

  What should you do?

  A. Execute the package by using the 32-bit run-time option.
  B. Uninstall the 64-bit version of SSIS and install the 32-bit version instead.
  C. Create a custom SSIS source component that encapsulates the 32-bit driver and compile it in 64-bit mode.
  D. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
  A. Execute the package by using the 32-bit run-time option.

  ---

  If the computer has a 32-bit version of Office, then you have to install the 32-bit version of the components, and you also have to ensure that you run the package in 32-bit mode. References: https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/connect-to-an-excel-workbook