Microsoft 70-463 Exam Questions and Answers 2021

NEW QUESTION 1
You are preparing to install SQL Server 2012 Master Data Services (MDS). You need to ensure that the database requirements are met.
What should you install?

• A. Microsoft SharePoint Foundation 2010 SP1
• B. SQL Server 2012 Enterprise (64-bit) x64 on the database server
• C. SQL Server 2012 Data Center (64-bit) x64 on the database server
• D. SQL Server 2008 Enterprise (64-bit) x64 on the database server

Answer: B

Explanation: * Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012.
* SQL Server 2012 Enterprise features include Master Data Services:

Note:
* Microsoft SQL Server Master Data Services is a Master Data Management (MDM) product from Microsoft, which will ship as a part of the Microsoft SQL Server database.Originally code-named Bulldog, Master Data Services is the rebranding of the Stratature MDM product titled +EDM, which Microsoft acquired in June 2007. Master Data Services is architecturally similar to +EDM, with increased integration with other Microsoft applications as well as some new features. Master Data Services first shipped with Microsoft SQL Server 2008 R2.

NEW QUESTION 2
Which of the following statements are true? (Choose all that apply.)

• A. An SSIS package can contain one or more SSDT solutions, each performing a specific data management operation.
• B. An SSIS project can contain one or more SSIS packages.
• C. An SSIS project can contain exactly one SSIS package.
• D. SSIS packages contain programmatic logic used in data movements and data transformation operations.

Answer: BD

Explanation: References:
https://docs.microsoft.com/en-us/sql/integration-services/integration-services-ssis-projects-andsolutions https://mindmajix.com/ssis/scripting-in-ssis

NEW QUESTION 3
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

Answer: D

Explanation: 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 dat
A. Destinations load data into data stores or create in-memory datasets. Incorrect:
Not A, Not B: No Data Transforms called Source Assistant or Destination Assistant Not C: Not related to auditing

NEW QUESTION 4
HOTSPOT
You are using the Master Data Services (MDS) Add-in for Excel to configure the entities in a model. The model consists of two entities: one named Customer and one named State. You open the Customer entity.
Currently, data stewards can enter any text value in the Customer entity's State attribute. You must restrict the entry of values in the State attribute to members defined in the State entity.
You need to configure the State attribute of the Customer entity.
Which option should you use? (To answer, select the appropriate area in the answer area.)

Answer:

Explanation:

NEW QUESTION 5
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 mode
• B. Modify connection manager properties to use project parameter
• C. Ensure that the SSISDB database is created.
• D. Use a project deployment mode
• E. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
• F. Use a package deployment mode
• G. Use a SQL Server package configuration with a common filte
• H. Change the contents of the SSIS Configurations table at runtime.
• I. Use a package deployment mode
• J. Save each SSIS package to a file share that can be accessed from all environments.

Answer: A

NEW QUESTION 6
You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job.
You make changes to the package that should improve performance.
You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal.
What should you do?

• A. Enable logging to an XML file in the package control flow for the Onlnformation even
• B. After 10 executions, view the XML file.
• C. After 10 executions, view the job history for the SQL Server Agent job.
• D. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.
• E. Enable logging to an XML file in the package control flow for the OnPostExecute even
• F. After 10 executions, view the XML file.

Answer: C

NEW QUESTION 7
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. Create a time mapping table.
• B. Change the level of granularity in both fact tables to be the same.
• C. Merge the fact tables.
• D. Create a view on the sales tabl

Answer: B

NEW QUESTION 8
HOTSPOT
You are a data warehouse developer responsible for developing data cleansing processes. Duplicate employees exist in an employee dimension.
You need to map, discover, and manage domain values based on the employee dimension.
Which Data Quality Services (DQS) option should you use? (To answer, select the appropriate option in the answer area.)

Answer:

Explanation:

NEW QUESTION 9
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 10
You are deploying a project to the SQL Server Integration Services (SSIS) catalog.
You need to ensure that the project is deployed to the production environment with the least amount of administrative effort.
What should you do?

• A. Create an On Error event handler.
• B. Add a data tap on the output of a component in the package data flow.
• C. Use the gacutil command.
• D. Create a reusable custom logging component.
• E. Run the package by using the dtexecui.exe utility and the SQL Log provider.
• F. Run the package by using the dtexec /rep /conn command.
• G. Use the Integration Services Deployment Wizard.
• H. Run the package by using the dtexec /dumperror /conn command.
• I. Use the dtutil /copy command.
• J. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
• K. Deploy the package by using an msi fil

Answer: G

Explanation: Reference:
http://msdn.microsoft.com/en-us/library/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx

NEW QUESTION 11
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You add an Execute SQL task to the control flow.
The task must execute a simple INSERT statement. The task has the following requirements:
• The INSERT statement must use the value of a string package variable. The variable name is StringVar.
• The Execute SQL task must use an OLE DB Connection Manager.
In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter.
You must configure the SQLStatement property of the Execute SQL task. Which SQL statement should you use?

• A. INSERT INTO dbo.Table (variablevalue) VALUES ($StringVar)
• B. INSERT INTO dbo.Table (variablevalue) VALUES (0)
• C. INSERT INTO dbo.Table (variablevalue) VALUES (@0)
• D. INSERT INTO dbo.Table (variablevalue) VALUES (?)

Answer: D

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141003.aspx http://msdn.microsoft.com/en-us/library/ms140355.aspx http://msdn.microsoft.com/en-us/library/cc280502.aspx

NEW QUESTION 12
HOTSPOT
You administer a Microsoft SQL Server 2012 database. The database contains a table that has the following definition:

You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
-The first row of the file contains the first row of data.
-Each record is of the same length.
-The date follows the U.S. date format.
-The file supports international characters.
What should you do? (To answer, simply select the option or options in the answer areathat you would configure.)

Answer:

Explanation:

NEW QUESTION 13
You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).
You need to add a task or component to the package that allows you to implement the SCD logic. What should you use?

• A. a Script component
• B. an SCD component
• C. an Aggregate component
• D. a Merge component

Answer: D

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

Answer:

Explanation: Only nonclustered columnstore indexes are available in SQL Server 2012 so when the index is created, you cannot update the table (without using partition switching). Therefore you have to load the data BEFORE creating the index.
In SQL Server 2014, you could create a clustered columnstore index which would allow updates to the table after the index is created.

NEW QUESTION 15
HOTSPOT
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.)

Answer:

Explanation:

NEW QUESTION 16
You are developing a SQL Server Integration Services (SSIS) package.
The package sources data from an HTML web page that lists product stock levels.
You need to implement a data flow task that reads the product stock levels from the HTML web page. Which data flow source should you use?

• A. Raw File source
• B. XML source
• C. Custom source component
• D. Flat File source

Answer: C

NEW QUESTION 17
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:

NEW QUESTION 18
DRAG DROP
You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table.
You have the following requirements:
• The fact table must populate a column named TotalCost that computes the sum of the price and tax columns.
• Before the sum is calculated, any records that have a price of zero must be discarded.
You need to create the SSIS package in SQL Server Data Tools.
In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.)

Answer:

Explanation:

NEW QUESTION 19
You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Windows Azure SQL Database database. The package has been deployed to a production server that runs Windows Server 2008 R2 and SQL Server 2012.
The package fails when executed on the production server.
You need to ensure that the package can load the Excel workbook data without errors. You need to use the least amount of administrative effort to achieve this goal.
What should you do?

• A. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
• B. Enable Address Windowing Extensions (AWE) for the local SQL Server instance.
• C. Replace the SSIS Excel source with a SSIS Flat File source.
• D. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.

Answer: D

Explanation: XLSX files, the new file type in Excel 2007/2010 cannot be opened with Excel Source/Destination in SSIS. To use XLSX in SSIS we need to install ACE driver and use OLE DB Source/Destination to read read/write data in XLSX files.
ACE is a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office Excel 7 (*.xls, *.xlsx, and *.xlsb) files to Microsoft SQL Server. If the SQL Server or your development box does not have ACE driver then install that 1st.
Note:
* The most efficient way to link to SQL Server is using an OLEDB connection manager. Incorrect:
Not B:
* Address Windowing Extensions (AWE) is a Microsoft Windows application programming interface that allows a 32-bit software application to access more physical memory than it has virtual address space.
* Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical memory greater than 4GB. Certain data-intensive applications, such as database management systems and scientific and engineering software, need access to very large caches of dat
A. In the case of very large data sets, restricting the cache to fit within an application's 2GB of user address space is a severe restriction.