Dumpscertification shares online exam exercise questions all year round! Microsoft MCSA 70-767 exam “Implementing a SQL Data Warehouse (beta)”
https://www.leads4pass.com/70-767.html (401 Q&As).Continue to study and we provide an updated cisco 70-767 exam practice questions and answers. You can practice the test online!
Watch the Microsoft MCSA 70-767 video tutorial online
Table of Contents:
- Latest Microsoft MCSA 70-767 pdf
- Test your Microsoft MCSA 70-767 exam level
- Related 70-767 Popular Exam resources
- Get Lead4Pass Coupons (12% OFF)
- What are the advantages of Lead4pass?
Latest Microsoft MCSA 70-767 pdf
[PDF] Free Microsoft MCSA 70-767 pdf dumps download from Google Drive: https://drive.google.com/open?id=1NKCEjU9z6NU6YK89sShYlQZF0NCeVCH6
Exam 70-767: Implementing a Data Warehouse using SQL：https://www.microsoft.com/en-us/learning/exam-70-767.aspx
Skills measured
This exam measures your ability to accomplish the technical tasks listed below.
- Design, implement and maintain a data warehouse (35–40%)
- Extract, transform and load data (40–45%)
- Build data quality solutions (15–20%)
Who should take this exam?
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.
Microsoft MCSA 70-767 Exam Practice Questions
QUESTION 1
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 2
You administer a Microsoft SQL Server 2016 database. The database contains a table named Employee. Part of the
Employee table is shown in the exhibit.
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists
within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table
properties to ensure data integrity and visibility. On which column in the Employee table should you create a unique
constraint?
A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Correct Answer: D
QUESTION 3
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
Correct Answer: C
Explanation: 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 4
You are reviewing the design of a student dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to student attributes such as
ParentOccupation.
You need to redesign the dimension to enable the full historical reporting of changes to multiple student attributes
including ParentOccupation.
What should you do?
A. Add CurrentValue and PreviousValue columns to the student dimension.
B. Enable Snapshot Isolation on the data warehouse.
C. Add an IsCurrent column to the student dimension.
D. Add StartDate and EndDate columns to the student dimension.
Correct Answer: D
Explanation: Adding a start and end date will give you this ability as when a record is inserted and given a start and end
date, you\\’ll have the ability to determine when they were active therefore giving you a retention of historical changes
QUESTION 5
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains
a unique solution that might meet the stated goals. Some question sets might have more than one correct solution,
while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
Each night you receive a comma separated values (CSV) file that contains different types of rows. Each row type has a
different structure. Each row in the CSV file is unique. The first column in every row is named Type. This column
identifies
the data type.
For each data type, you need to load data from the CSV file to a target table. A separate table must contain the number
of rows loaded for each data type. Solution: You create a SQL Server Integration Services (SSIS) package as shown in
the exhibit. (Click the Exhibit tab.)
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
The conditional split is correctly placed before the count.
QUESTION 6
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:
Correct 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
QUESTION 7
You are developing a SQL Server Integration Services (SSIS) package.
The package uses custom functionality that accesses a SQL Server database. The custom functionality must be
implemented by using Language Integrated Query (LINQ).
You need to ensure that the LINQ code can be debugged at design time.
What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the
answer area.)
Select and Place:
Correct Answer:
QUESTION 8
You are the administrator of a Microsoft SQL Server Master Data Services (MDS) model. The model was developed to
provide consistent and validated snapshots of master data to the ETL processes by using subscription views. A new
model version has been created.
You need to ensure that the ETL processes retrieve the latest snapshot of master data.
What should you do?
A. Add a version flag to the last committed version, and create new subscription views that use this version flag.
B. Update the subscription views to use the last committed version.
C. Add a version flag to the new version, and update the subscription views to use this version flag.
D. Add a version flag to the new version, and create new subscription views that use this version flag.
Correct Answer: D
QUESTION 9
A database has tables named Table1, Table2, and Table3.
1.
Table1 has a foreign key relationship with Table2.
2.
Table2 has a foreign key relationship with Table3.
3.
Table1 does not have a direct relationship with Table3.
You need to recommend an appropriate dimension usage relationship.
What should you recommend?
A. many-to-one relationship
B. referenced relationship
C. regular dimension relationship
D. fact relationship
Correct Answer: B
A reference dimension relationship between a cube dimension and a measure group exists when the key column for the
dimension is joined indirectly to the fact table through a key in another dimension table, as shown in the following
illustration.
Incorrect Answers:
C: A regular dimension relationship between a cube dimension and a measure group exists when the key column for the
dimension is joined directly to the fact table. References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models-olap-logical-cube-objects/dimension-relationships
QUESTION 10
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 sources should you use? Select Two
A. Raw File source
B. XML source
C. Custom source component
D. Flat File source
E. script component
Correct Answer: CE
QUESTION 11
You need to configure Microsoft SQL Server Integration Services (SSIS) for maximum insert performance.
The Integration Services package is configured as shown in the following exhibit.(Missing exhibit)
Use the drop-down menus to select the answer choice that completes each statement based on the information
presented in the graphic.
NOTE: Each correct selection is worth one point.
Correct Answer:
QUESTION 12
You are developing a Microsoft SQL Server Integration Services (SSIS) package. You enable the SSIS log provider for
the Windows event log. You configure the package to use the ScriptTaskLogEntry event. You create a custom Script
task.
You need to ensure that when the script completes, it writes the execution status to the event log on the server that
hosts SSIS.
Which code segment should you add to the Script task?
A. Dts.TaskResult = (int)ScriptResults.Failure
B. Dts.Events.FireWarning (0, “SSIS”, “Script executed with return result ” Dts.TaskResult, String.Empty, 0)
C. System.Diagnostics.EventLog.writeEntryC\\’SSIS”, “Script executed with return result ” + Dts.TaskResult,
System.Diagnostics.EventLogEntryType.Information)
D. Dts.TaskResult = (int)ScriptResults.Success
Correct Answer: D
QUESTION 13
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 answer 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 warenouse originates 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:
Related 70-767 Popular Exam resources
Get Lead4Pass Coupons(12% OFF)
What are the advantages of Lead4pass?
We have a number of Cisco, Microsoft, IBM, CompTIA and other exam experts. We update exam data throughout the year.
Top exam pass rate! We have a large user base. We are an industry leader! Choose Lead4Pass to pass the exam with ease!
Summarize:
The free Microsoft MCSA 70-767 exam dumps can help you improve your skills and exam experience! To pass the cisco 70-767 exam at once: https://www.leads4pass.com/70-767.html We make Microsoft 70-767 videos and 70-767 pdf for you to learn! I hope you can pass the exam easily.