Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 161:

  You develop and deploy a SQL Server Integration Services (SSIS) package.

  The package is stored in the file system.

  You need to execute the package without importing it to the SSIS server.

  What should you use to execute the package? (Each correct answer presents a complete solution. Choose all that apply.)

  A. dtexec
  B. catalog.start_package
  C. SQL Server Agent
  D. SQL Server Management Studio
  A. dtexec
  C. SQL Server Agent

  ---

  References: http://technet.microsoft.com/en-us/library/ms141708(v=sql.110).aspx

• Question 162:

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

  The package must run a parameterized query against a Windows Azure SQL Database database.

  You need to use the least amount of development effort to meet the package requirement.

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

  Hot Area:

  

  

• Question 163:

  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.
  C. Change the granularity of the fact table to month.

• Question 164:

  You are the data steward for a Business Intelligence project.

  You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.

  You need to produce the CSV file with the least amount of development effort.

  What should you do?

  A. Create an Integration Services package and use a Data Profiling transform.
  B. Create a custom .NET application based on the Knowledgebase class.
  C. Create a data quality project.
  D. Create a CLR stored procedure based on the Knowledgebase class.
  E. Create a Master Data Services (MDS) business rule.
  C. Create a data quality project.

  ---

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

• Question 165:

  You administer a large and complex SQL Server Integration Services (SSIS) solution in the SSIS catalog.

  You are instructed to execute a package by using PowerShell. You need to create the correct PowerShell command.

  How should you place the four code segments in sequence? (To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 166:

  You are creating a SQL Server Master Data Services (MDS) model. This model is used to store a master list of products.

  An attribute must be added to the Product entity to define the sales manager responsible for each product.

  You need to create an attribute in the Product entity that prevents users from entering invalid sales manager values.

  Which type of attribute should you create?

  A. Recursive
  B. Explicit
  C. Domain-based
  D. User-defined
  E. Derived
  F. Parent
  C. Domain-based

  ---

  References: http://msdn.microsoft.com/en-us/library/bb190163.aspx http://msdn.microsoft.com/en-us/library/ee633737.aspx http://msdn.microsoft.com/en-us/library/ee633759.aspx http://msdn.microsoft.com/en-us/library/ee633745.aspx http://msdn.microsoft.com/en-us/library/ee633724.aspx http://msdn.microsoft.com/en-us/library/ee633733.aspx http://msdn.microsoft.com/en-us/library/ff487058.aspx

• Question 167:

  You are designing an extract, transform and load (ETL) solution that loads data into dimension tables. The ETL process involves many transformation steps.

  You need to ensure that the design can provide:

  

  Auditing information for compliance and business user acceptance

  

  Tracking and unique identification of records for troubleshooting and error correction

  What should you do?

  A. Create a SQL Common Language Runtime (SQLCLR) component that records all transformation steps in a Microsoft SharePoint list.
  B. Create a version control repository for the transformation steps in Team Foundation Server (TFS).
  C. Develop a custom data lineage solution.
  D. Develop a Data Quality Services (DQS) solution.
  C. Develop a custom data lineage solution.

• Question 168:

  You administer a Microsoft SQL Server database. You want to import data from a text file to the database. You need to ensure that the following requirements are met:

  Data import is performed by using a stored procedure.

  Data is loaded as a unit and is minimally logged.

  Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Each data import command or recovery model may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)

  Select and Place:

  

  

• Question 169:

  You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.

  All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed.

  You need to develop this project with the least amount of development effort.

  What should you do? (Each answer presents a part of the solution. Choose all that apply.)

  A. Create a package parameter named ConnectionName in each package.
  B. Edit each package Connection Manager. Set the ConnectionName property to @[$Project::ParamConnection].
  C. Edit the project Connection Manager in Solution Explorer. Set the ConnectionName property to @ [$Project::ParamConnection].
  D. Set the Sensitive property of the parameter to True.
  E. Create a project parameter named ConnectionName.
  F. Set the Required property of the parameter to True.
  C. Edit the project Connection Manager in Solution Explorer. Set the ConnectionName property to @ [$Project::ParamConnection].
  E. Create a project parameter named ConnectionName.
  F. Set the Required property of the parameter to True.

  ---

  C: From question: " The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed."

  E: SSIS 2012 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources acrossprojects, connection managers being one of those resources.

  F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.

  Note:

  * Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.

• Question 170:

  You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode.

  Some of the data to be loaded will reference products that need special processing logic in the data flow.

  You need to enable separate processing streams for a subset of rows based on the source product code.

  Which data flow transformation should you use?

  A. Source Assistant
  B. Destination Assistant
  C. Audit
  D. Script Component
  D. Script Component

  ---

  Script Component Transformation:

  The transformation that uses script to extract, transform, or load data.

  Note:

  * SQL Server Integration Services provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.