Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 61:

  You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure SQL Database database. The package consists of several data flow tasks.

  The package has the following auditing requirements:

  

  If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.

  

  The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.

  You need to ensure that auditing is configured to meet these requirements.

  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.
  C. Use an event handler for OnTaskFailed for the package.

• Question 62:

  You work as a database administrator 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. ABC.com has a dimensional database with conformed dimensions and surrogate keys. Which of the following is TRUE with regards to surrogate keys?

  A. It prevents a dimension from being used in multiple data marts.
  B. It is used for internal relationships between tables.
  C. It allows for a dimension to be used in multiple data marts.
  D. It serves as the identifiers to entities from the business itself.
  B. It is used for internal relationships between tables.

• Question 63:

  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.
  A. Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created.

• Question 64:

  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. Change the level of granularity in both fact tables to be the same.
  B. Merge the fact tables.
  C. Partition the fact tables by day.
  D. Create a surrogate key for the time dimension.
  A. Change the level of granularity in both fact tables to be the same.

  ---

  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 65:

  You have a data warehouse that contains all of the sales data for your company. The data warehouse contains several SQL Server Integration Services (SSIS) packages.

  You need to create a custom report that contains the total number of rows processed in the package and the time required for each package to execute.

  Which view should you include in the report?

  A. catalog.executable_statistics
  B. catalog.execution_data_taps
  C. catalog.event_messages
  D. catalog.execution_data_statistics
  D. catalog.execution_data_statistics

  ---

  The catalog.execution_data_statistics view displays a row each time a data flow component sends data to a downstream component, for a given package execution. The information in this view can be used to compute the data throughput for

  a component.

  Fields in this view include:

  created_time The time when the values were obtained.

  rows_sent The number of rows sent from the source component.

  References: https://docs.microsoft.com/en-us/sql/integration-services/system-views/catalog-execution-data-statistics

• Question 66:

  You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data.

  The data source will be polled once per hour. The data is updated with multiple important status changes per minute.

  For each captured data change, the before and after values must be included.

  You need to configure the CDC Source component.

  Which CDC processing mode should you select? (To answer, configure the appropriate option in the dialog box in the answer area.)

  Hot Area:

  

  

• Question 67:

  You are building a fact table in a data warehouse.

  The table must have a columnstore index. The table cannot be partitioned.

  You need to design the fact table and load it with data.

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

  Select and Place:

  

  

• Question 68:

  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) data flow. The data flow must be configured to make data from various external data sources available to the other components in the data flow.

  Which of the following actions should you take?

  A. You should consider making use of the expressions data flow component.
  B. You should consider making use of the source data flow component.
  C. You should consider making use of the transformations data flow component.
  D. You should consider making use of the destination data flow component.
  B. You should consider making use of the source data flow component.

• Question 69:

  You are designing a SQL Server Integration Services (SSIS) package that uploads a file to a table named Orders in a SQL Azure database.

  The company's auditing policies have the following requirements:

  

  An entry must be written to a dedicated SQL Server log table named OrderLog.

  

  The entry must be written as soon as the file upload task completes.

  You need to meet the company's policy requirements.

  Which event handler should you use?

  A. OnPostExecute
  B. OnProgress
  C. OnInformation
  D. OnComplete
  A. OnPostExecute

  ---

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

• Question 70:

  You work as a senior database administrator 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 hosting a training exercise for Microsoft SQL Server 2012 administrators. You are currently discussing the Fuzzy Lookup transformation.

  Which of the following is TRUE with regards to the Fuzzy Lookup similarity threshold setting? (Choose all that apply.)

  A. Increasing the threshold can improve the speed of matching.
  B. Increasing the threshold can deteriorate the speed of matching.
  C. The nearer the value is to 1, the nearer the resemblance of the lookup value to the source value must be to qualify as a match.
  D. The nearer the value is to 1, the further the resemblance of the lookup value to the source value must be to qualify as a match.
  A. Increasing the threshold can improve the speed of matching.
  C. The nearer the value is to 1, the nearer the resemblance of the lookup value to the source value must be to qualify as a match.