Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 181:

  You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL Server Data Tools.

  A package Connection Manager has been parameterized with project scope.

  You need to display the parameters that have been generated for the Connection Manager.

  What should you use? To answer, select the appropriate setting or settings in the answer area.

  Hot Area:

  

  

• Question 182:

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

  The package contains several tasks that must repeat until an expression evaluates to FALSE.

  You need to add and configure a container to enable this design.

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

  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] FROMQuotes 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 columnstore 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]
  C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].

  ---

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

• Question 184:

  You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).

  You need to complete the post-installation configuration.

  What should you do?

  A. Run the DQSInstaller.exe command.
  B. Install the data providers that are used for data refresh.
  C. Install ADOMD.NET.
  D. Run the dbimpexp.exe command.
  A. Run the DQSInstaller.exe command.

  ---

  Note: DQS stands for Data Quality Server.

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

• Question 185:

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

  

  The database administrator is concerned about the growth of the database. Users report 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. Partition the table by product type.
  B. Create a view over the fact table to aggregate sales by month.
  C. Change the granularity of the fact table to month.
  D. Create an indexed view over the fact table to aggregate sales by month.
  C. Change the granularity of the fact table to month.

• Question 186:

  You are designing a data warehouse for a software distribution business that stores sales by software title. It stores sales targets by software category. Software titles are classified into subcategories and categories. Each software title is

  included in only a single software subcategory, and each subcategory is included in only a single category. The data warehouse will be a data source for an Analysis Services cube.

  The data warehouse contains two fact tables:

  

  factSales, used to record daily sales by software title

  

  factTarget,

  used to record the monthly sales targets by software category

  Reports must be developed against the warehouse that reports sales by software title, category and subcategory, and sales targets.

  You need to design the software title dimension. The solution should use as few tables as possible while supporting all the requirements.

  What should you do?

  A. Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory and a fourth bridge table that joins software titles to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes.
  B. Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory. Connect factSales to all three tables and connect factTarget to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
  C. Create one table, dimSoftware, which contains Software Detail, Category, and Subcategory columns. Connect factSales to dimSoftware with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factTarget.
  D. Create two tables, dimSoftware and dimSoftwareCategory. Connect factSales to dimSoftware and factTarget to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
  C. Create one table, dimSoftware, which contains Software Detail, Category, and Subcategory columns. Connect factSales to dimSoftware with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factTarget.

• Question 187:

  You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.

  The reference data to be used in the transformation changes with every package execution.

  Common language runtime (CLR) integration cannot be enabled on the SQL Server database where the reference table is located.

  You need to configure the Fuzzy Lookup transformation in the most efficient manner.

  What should you do?

  A. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
  B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
  C. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
  D. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
  B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.

• Question 188:

  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.

  You have received reports regarding a current existing SQL Server Integration Services (SSIS) package not completing its execution, and not transferring data. You have decided to make use of the Package Deployment Model.

  Which of the following is TRUE with regards to using the Package Deployment Model?

  A. CLR integration is NOT required on the database engine.
  B. Environment-specific parameter values are stored in environment variables.
  C. Events that are created by the package are captured automatically and saved to the catalog during execution.
  D. Events that are created by the package are NOT captured automatically during execution.
  A. CLR integration is NOT required on the database engine.
  D. Events that are created by the package are NOT captured automatically during execution.

• Question 189:

  In your ETL process, there are three external processes that need to be executed in sequence, but you do not want to stop execution if any of them fails. Can this be achieved by using precedence constraints? If so, which precedence constraints can be used?

  A. No, this cannot be achieved just by using precedence constraints.
  B. Yes, this can be achieved by using completion precedence constraints between the first and the second and between the second and the third Execute Process tasks, and by using a success precedence constraint between the third Execute Process task and the following task.
  C. Yes, this can be achieved by using completion precedence constraints between the first and the second, between the second and the third, and between the third Execute Process task and the following task.
  D. Yes, this can be achieved by using failure precedence constraints between the first and the second, and between the second and the third Execute Process tasks, and by using a completion precedence constraint between the third Execute Process task and the following task.
  B. Yes, this can be achieved by using completion precedence constraints between the first and the second and between the second and the third Execute Process tasks, and by using a success precedence constraint between the third Execute Process task and the following task.

  ---

  References: https://msdn.microsoft.com/en-us/library/cc505848.aspx

• Question 190:

  You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on Microsoft Azure SQL database. The package uses a Foreach container to process text files found in a folder. The

  package must be deployed to a single server by using the Project Deployment model.

  Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule. Each job passes a different folder path to the package.

  You need to configure the package to accept the folder path from each job.

  Which package configuration should you use?

  A. .dtsConfig file
  B. Registry Entry
  C. Environment Variable
  D. Parent Package Variable
  E. XML Configuration File
  C. Environment Variable

  ---

  References: http://msdn.microsoft.com/en-us/library/ms345179.aspx http://msdn.microsoft.com/en-us/library/ms141708.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://msdn.microsoft.com/en-us/library/hh213296.aspx http://msdn.microsoft.com/en-us/library/hh213293.aspx