70-463 | Accurate 70-463 Free Practice Questions 2021

NEW QUESTION 1
DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a data warehouse hosted on SQL Azure.
You have the following requirements:
• A destination table must contain all of the data in two source tables.
• Duplicate records must be inserted into the destination table.
You need to develop a data flow that imports the data while meeting the requirements.
How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)

Answer:

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141703.aspx http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms141020.aspx http://msdn.microsoft.com/en-us/library/ms140182.aspx

NEW QUESTION 2
You develop a SQL Server Integration Services (SSIS) project by using the Package Deployment Model. A package in the project extracts data from a Windows Azure SQL Database database. The package is deployed to SQL Server.
The package is not producing the desired results.
You need to generate the .mdmp and .tmp debug files in order to troubleshoot the issues. What should you do?

• A. Execute the catalog.add_data_tap stored procedure with the package execution_id.
• B. Execute the catalog.create_execution_dump stored procedure with the package execution_id.
• C. Run the DTEXEC utility with the /DumpOnError option.
• D. Run the DTEXEC utility with the /Reporting V optio

Answer: C

NEW QUESTION 3
You are maintaining a Data Quality Services (DQS) environment. The production server failed and a new server has been set up. The DQS databases are restored to a new server. All the appropriate permissions are granted.
DQS users are experiencing issues connecting to the new Data Quality Server.
You need to enable users to connect to the new server.
Which Surface Area Configuration property should you enable?

• A. AdHocRemoteQueriesEnabled
• B. SoapEndpointsEnabled
• C. ClrIntegrationEnabled
• D. RemoteDacEnabled
• E. OleAutomationEnabled
• F. XpCmdShellEnabled

Answer: C

NEW QUESTION 4
You are designing a SQL Server Integration Services (SSIS) project that uses the Project Deployment Model.
The project will contain 10 packages. All the packages must connect to the same data source and destination.
You need to define and reuse the connection managers in all the packages by using the least development effort.
What should you do?

• A. Implement parent package variables.
• B. Copy and paste connection managers from one package to the others.
• C. Implement project connection managers.
• D. Implement package connection manager

Answer: C

NEW QUESTION 5
You are deploying a new SQL Server Integration Services (SSIS) package to five servers. The package must meet the following requirements:
• .NET Common Language Runtime (CLR) integration in SQL Server must not be enabled.
• The Connection Managers used in the package must be configurable without editing and redeploying the package.
• The deployment procedure must be automated as much as possible.
• Performance must be maximized.
You need to set up a deployment strategy that meets the requirements. What should you do?

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

Answer: D

NEW QUESTION 6
DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that is ready for deployment to a production server. The package contains sensitive information secured by using the EncryptSensitiveWithUserKey package protection level.
You are preparing the package for deployment by the production operations team.
You need to ensure that the production operations team can open and execute the package without re-entering the sensitive information.
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.)

Answer:

Explanation:

NEW QUESTION 7
DRAG DROP
You have a data warehouse that contains all of the sales data for your company.
You plan to create a SQL Server Integration Services (SSIS) package to extract a list of files from a database and to load the files to the data warehouse.
You need to design a control flow for the SSIS package. The control flow must meet the following requirements:
-Load the data in a single transaction.
-Load multiple tables in parallel after extracting the data.
Which types of containers should you add to the design? To answer, drag the appropriate container types to the correct locations. Each container type 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.

Answer:

Explanation:

NEW QUESTION 8
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 column
• B. Place the columnstore index on the dimensional key columns.
• C. Design a fact table with 5 dimensional key columns and 10 measure column
• D. Place the columnstore index on the measure columns.
• E. Design a fact table with 10 dimensional key columns and 5 measure column
• F. Place the columnstore index on the dimensional key columns and the measure columns.
• G. Design a fact table with 10 dimensional key columns and 5 measure column
• H. Place the columnstore index on only the measure columns.

Answer: C

NEW QUESTION 9
You are designing a data warehouse that uses SQL Server 2012.
The data warehouse contains a table named factSales that stores product sales. The table has a clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for sales totals. All key columns use the int data type and the aggregate column uses the money data type.
You need to increase the speed of data retrieval from the factSales table. Which index type should you add to the table?

• A. Clustered
• B. Semantic search
• C. Nonclustered
• D. XML

Answer: C

NEW QUESTION 10
You are designing a data warehouse that contains a product dimension.
The product dimension contains the original product attributes as well as the most recent set of updated attributes. When a product is updated, the original product attributes must be preserved. The product dimension design must:
Support a Type 3 Slowly Changing Dimension (SCD) process Minimize the total storage consumed
You need to design a solution that meets these requirements. What should you do?

• A. Design two table
• B. One table will hold the original product surrogate key and attribute
• C. The other table will hold the most recent product surrogate key and attributes.
• D. Design a table as a Type 2 SCD dimension that holds the surrogate key for each product and its attribute
• E. Limit the number of records per product within the table to two.
• F. Design a table to hold the surrogate key for each product and its attributes as well as additional columns to store the original attributes.
• G. Design a table to hold the surrogate key for each product and its attribute
• H. Add a column to store an indicator that identifies the record as original data or recent data.

Answer: C

NEW QUESTION 11
You are developing a SQL Server Integration Services (SSIS) package.
You need to design a package to change a variable value during package execution by using the least amount of development effort.
What should you use?

• A. Expression task
• B. Script task
• C. Execute SQL task
• D. Execute Process task
• E. Term Extraction transformation

Answer: A

Explanation: Reference:
http://msdn.microsoft.com/en-us/library/hh213137.aspx

NEW QUESTION 12
DRAG DROP
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.)

Answer:

Explanation: Note: Example:
ProjectInfo testProject = isserver.Catalogs["SSISDB"].Folders["TEST"].Projects["Integration Services Project2"];
PackageInfo testPackage = testProject.Packages["Package.dtsx"]; Full example:
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=SSISDB;Integrated Security=SSPI;");
IntegrationServices isserver = new IntegrationServices(conn);
ProjectInfo testProject = isserver.Catalogs["SSISDB"].Folders["TEST"].Projects["Integration
Services Project2"];
PackageInfo testPackage = testProject.Packages["Package.dtsx"]; testProject.Parameters["ProjectTestParam1"].Set(ParameterInfo.ParameterValueType.Referenced, "661");
testPackage.Parameters["PackageTestParam1"].Set(ParameterInfo.ParameterValueType.Referenced, "662");
testPackage.Execute(false, null);
}
}
}

NEW QUESTION 13
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 strategy should you identify?

• A. a nonclustered columnstore
• B. FileStream
• C. a durable In-Memory OLTP
• D. non-durable staging table

Answer: D

NEW QUESTION 14
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. Script Component
• B. Audit
• C. Destination Assistant
• D. Data Conversion

Answer: A

Explanation: The Script component can be used as a source, a transformation, or a destination. This component supports one input and multiple outputs. Depending on how the component is used, it supports either an input or outputs or both. The script is invoked by every row in the input or output.
If used as a source, the Script component supports multiple outputs.
If used as a transformation, the Script component supports one input and multiple outputs. If used as a destination, the Script component supports one input.

NEW QUESTION 15
Your company has a data mart that contains customer review data. The customer reviews are of the product review or service review type. You plan to load each customer review type to a separate fact table.
You create a SQL Server Integration Services (SSIS) package as shown in the exhibit. (Click the Exhibit button.)

You need to modify the package to load the customer review data to the new fact tables. The SSIS package
must contain a single data flow. What should you do?

• A. Set the transformation to Conditional Split.
• B. Set the transformation to CDC Splitter.
• C. Add a second transformation and set the transformation to Merge.
• D. Add a second transformation and set the transformation to Multicas

Answer: A

NEW QUESTION 16
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure.
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. Parent Package Variable
• B. XML Configuration File
• C. Environment Variable
• D. .dtsConfig file
• E. Registry Entry

Answer: C

NEW QUESTION 17
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. Install the Analysis Services OLE DB Provider.
• B. Run the DQSInstaller.exe command.
• C. Run the Configuration component in the Data Quality Client.
• D. Make the data available for DQS operation

Answer: B

NEW QUESTION 18
DRAG DROP
You need to design a data load strategy for a data warehouse fact table. The solution must use an ordered data load.
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.

Answer:

Explanation: