Latest Lead4Pass MCSA 70-767 exam practice questions and 70-767 exam dumps

Lead4Pass MCSA 70-767 Exams "Implementing a Data Warehouse using SQL".


Full Microsoft MCSA 70-767 exam dumps: https://www.lead4pass.com/70-767.html


Microsoft MCSA 70-767 exam tips list:


[p.s.1] Microsoft 70-767 exam practice questions

[p.s.2] Microsoft 70-767 exam Pdf

[p.s.3] Microsoft MCSA up-to-date information

[p.s.4] Lead4pass Coupon 2020


[p.s.1] Share a part of the Microsoft MCSA 70-767 exam practice questions from Lead4Pass for free


QUESTION 1

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 2016. 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 an SSIS Flat File source.

D. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source. Correct 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 the ACE driver and use OLE DB Source/Destination to 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 an ACE driver then install that 1st. Note: * The most efficient way to link to SQL Server is by 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 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.


QUESTION 2

A company plans to load data from a CSV file that is stored in a Microsoft Azure Blob storage container. You need to load the data. How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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. NOTE: Each correct selection is worth one point.

Select and Place:

Correct Answer:


QUESTION 3

You are installing SQL Server Data Quality Services (DQS). You need to give specific users access to the Data Quality Server. Which SQL Server application should you use? A. SQL Server Configuration Manager

B. SQL Server Data Tools

C. SQL Server Management Studio

D. Data Quality Client

Correct Answer: C

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


QUESTION 4

The package uses data from the Products table and the Prices table. Properties of the Prices source are shown in the OLE DB Source Editor exhibit (Click the Exhibit button.) and the Advanced Editor for Prices exhibit (Click the Exhibit button.)

You create a Microsoft SQL Server Integration Services (SSIS) package as shown in the SSIS Package exhibit. (Click the Exhibit button.)

You join the Products and Prices tables by using the reference column. You need to resolve the error with the package. For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

Hot Area:

Correct Answer:

There are two important sort properties that must be set for the source or upstream transformation that supplies data to the Merge and Merge Join transformations: The Merge Join Transformation requires sorted data for its inputs. If you do not use a Sort transformation to sort the data, you must set these sort properties manually on the source or the upstream transformation.

https://docs.microsoft.com/en-us/sql/integration-services/dataflow/transformations/sort-data-for-the-mergeand-merge-join-transformations


QUESTION 5

You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature. You have added a CDC Source component to the data flow to read changed data from the source system. You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations. Which data flow transformation should you use?

A. Audit

B. DQS Cleansing

C. CDC Splitter

D. Pivot

Correct Answer: C


Explanation: The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations

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


QUESTION 6

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answers choices, but the text of the scenario is exactly the same in each question in the series. Start of repeated scenario Contoso. Ltd. has a Microsoft SQL Server environment that includes SQL Server Integration Services (SSIS), a data warehouse, and SQL Server Analysis Services (SSAS) Tabular and multidimensional models. The data warehouse stores data related to your company sales, financial transactions, and financial budgets All data for the data warehouse originate from the company\\'s business financial system. The data warehouse includes the following tables:

The company plans to use Microsoft Azure to store older records from the data warehouse. You must modify the database to enable the Stretch Database capability. Users report that they are becoming confused about which city table to use for various queries. You plan to create a new schema named Dimension and change the name of the dbo.du_city table to Diamension.city. Data loss is not permissible, and you must not leave traces of the old table in the data warehouse. Pal to create a measure that calculates the profit margin based on the existing measures. You must improve performance for queries against the fact. Transaction table. You must implement appropriate indexes and enable the Stretch Database capability. End of repeated scenario You need to resolve the problems reported about the dia__city table. How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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:

Correct Answer:


QUESTION 7

You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.

You need to add the Environment to the project. Which stored procedure should you use?

A. catalog.set_environment_reference_type

B. catalog.set_environment_property

C. catalog.create_environment_reference

D. catalog.create_environment

Correct Answer: C


QUESTION 8

You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server 2016 database. The package is allowed to connect to only one database. An Environment variable contains the name of the database. The OLE DB project connection manager has been parameterized. You need to configure the connection manager property to accept the value of the Environment variable. Which property should you use? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Hot Area:

Correct Answer:


QUESTION 9

DRAG DROP Note: This question is part of a series of questions that present the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answers choices, but the text of the scenarios is exactly the same in each question in this series.

You define a domain-based attribute in the State entity that references the CountryRegion entity. You define another domain-based attribute in the city entity that references the State and CountryRegion entities.


A single derived hierarchy named Geography supports navigation between the CountryRegion, State, and City levels. Subscription views exist for all entities. The subscription views have the same name as the entity on which they are based. You initialize each entity member.


New City entity members are imported daily based on customer city values in a Customer Relationship Management (CRM) database. The CRM database is a SQL Server relational database.


When new cities are imported from the CRM database, the state codes must be standardized to those already defined in the State entity. In the CRM database, sales managers and assigned to countries/regions.


A sales manager may be assigned to one or more countries/regions. A country/region may have one or more assigned sales managers. The CRM database contains a table named ManagerCountryRegion that stores a row for each manager-country/region relationship.


You create the following MDS users and map each user to an Active Directory Domain Services (AD DS) user account: User1, User2, and User3. Both User1 and User2 belong to the Explorer functional area.


Users must be able to complete the tasks described in the following table.

You need to add a top-level to the Geography hierarchy to navigate from manager to CountryRegion. 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:

Correct Answer:

Derived hierarchies come from the relationships between entities in a model. These are domain-based attribute relationships. A domain-based attribute contains values that are populated by members from an entity and can be used as attribute values for other entities.

https://docs.microsoft.com/en-us/sql/master-data-services/master-data-services-overview-mds


QUESTION 10

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 Data Conversion component

B. an Execute SQL task that executes a MERGE statement on the database

C. a Merge component

D. an Expression task

Correct Answer: C

Note: Type of Slowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites the values of the attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2. Using MERGE instead of SCD wizard Replacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately.


QUESTION 11

A SQL Server Integration Services (SSIS) package on a computer is failing periodically in production. The package was created less than one year ago and was deployed to the SSIS catalog. Sometimes the package is started on a SQL Agent schedule; sometimes the package is started manually by an SSIS developer by using the Object Explorer in SQL Server Management Studio. You need to identify the authenticated user responsible for starting the package each time it failed in the past. Where can you find this information?

A. the SQL Server Log

B. the SSISDB.[catalog].[executions] view

C. the SSISDB.[catalog].[event_messages] view

D. the SQL Agent Job History

E. the SQL Agent Error Log

Correct Answer: B


QUESTION 12

A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project Deployment Model. Sometimes the package is started as part of a multistep SQL job. At other times, the package is started manually by a database administrator by using the Object Explorer in SQL Server Management Studio. You need to identify the authenticated user responsible for starting the package each time it executes. How can you find this information?

A. In the SSISD

B.[catalog], query the .[executions] view. B. In the SSISDB.[catalog] , query the [event_messages] view.

C. In SQL Server Management Studio, view the SQL Agent Job History.

D. In SQL Server Management Studio, view the SQL Agent Error Log.

E. In SQL Server Management Studio, view the SQL Server Log.

Correct Answer: A


QUESTION 13

You manage Master Data Services (MDS). You need to create a new entity with the following requirements: Maximize the performance of the MDS system. Ensure that the Entity changelogs are stored. You need to configure the Transaction Log Typesetting. Which type should you use?

A. Full

B. None

C. Attribute

D. Member

E. Simple

Correct Answer: D


[p.s.2] Get Google Drive Microsoft MCSA 70-767 dumps PDF from Lead4Pass for free: https://drive.google.com/file/d/1Dmk8rYFNy6NGkuZ36Cjv-ZZpERKyR9c3/



[p.s.3] Exam 70-767: Implementing a Data Warehouse using SQL: https://docs.microsoft.com/en-us/learn/certifications/exams/70-767


This exam is intended for extract, transform, and load (ETL) and data warehouse developers who create business intelligence (BI) solutions. Their responsibilities include data cleansing, in addition to ETL and data warehouse implementation.


Skills measured

1. Design, implement and maintain a data warehouse (35-40%)

2. Extract, transform, and load data (40-45%)

3. Build data quality solutions (15-20%)


[p.s.4] [12% off]Lead4pass Coupon code 2020: "lead4pass2020"

4.7 Star App Store Review!
Cpl.dev***uke
The Communities are great you rarely see anyone get in to an argument :)
king***ing
Love Love LOVE
Download

Select Collections