[2021-New] Microsoft 70-463 Dumps With Update Exam Questions (11-20)

70-463 Dumps

70-463 Exam Questions - Online Test

70-463 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes the dimUser and dimDistrict dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse; and the dimDistrict table contains information about sales districts.

The system is accessed by users from certain districts, as well as by area supervisors and users from the corporate headquarters.

You need to design a table structure to ensure that certain users can see sales data for only certain districts. Some users must be permitted to see sales data from multiple districts.

What should you do?

A. Add a district column to the dimUser table.

B. Partition the factSales table on the district column.

C. Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables.

D. For each district, create a view of the factSales table that includes a WHERE clause for the district.

Answer: C

Q2. 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:

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

Answer: A

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

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

Q4. To support the implementation of new reports, Active Directory data will be downloaded to a SQL Server database by using a SQL Server Integration Services (SSIS) 2012 package.

The following requirements must be met:

All the user information for a given Active Directory group must be downloaded to a SQL Server table. The download process must traverse the Active Directory hierarchy recursively.

You need to configure the package to meet the requirements by using the least development effort.

What should you use?

A. script component

B. custom component

C. XML source

D. script task

Answer: A

Q5. You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse.

The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis.

You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum.

What should you do?

A. Add an OnError event handler to the SSIS project.

B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.

C. Use an msi file to deploy the package on the server.

D. Open a command prompt and run the gacutil command.

E. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.

F. Open a command prompt and run the dtutil /copy command.

G. Create a reusable custom logging component and use it in the SSIS project.

H. Configure the SSIS solution to use the Project Deployment Model.

I. Configure the output of a component in the package data flow to use a data tap.

J. Open a command prompt and run the dtexec /rep /conn command.

K. Open a command prompt and run the dtexec /dumperror /conn command.

Answer: I

Explanation:

References: http://technet.microsoft.com/en-us/library/hh230989.aspx

http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/ms162820.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx http://technet.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx

Q6. You are developing a data flow to load sales data into a fact table. In the data flow, you configure a Lookup Transformation in full cache mode to look up the product data for the sale.

The lookup source for the product data is contained in two tables.

You need to set the data source for the lookup to be a query that combines the two tables.

Which page of the Lookup Transformation Editor should you select to configure the query? To answer, select the appropriate page in the answer area.

Answer:

Q7. 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 can’t be open 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.

* 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 data. 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.

Incorrect: Not B:

Q8. 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:

Q9. 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] FROM Quotes 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 coiumnstore 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]

Answer: C

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

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

Q10. You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application to modify an existing knowledge base.

In the mapping configuration, two of the three columns are mapped to existing domains in the knowledge base. The third column, named Team Type, does not yet have a domain.

You need to complete the mapping of the Team Type column.

What should you do?

A. Add a column mapping for the Team Type column.

B. Map a composite domain to the source column.

C. Create a composite domain that includes the Team Type column.

D. Add a domain for the Team Type column.

Answer: D

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

http://msdn.microsoft.com/en-us/library/hh213015.aspx http://msdn.microsoft.com/en-us/library/gg524799.aspx