Origexams shares the latest Microsoft MCSA 70-767 exam dumps for free exam practice tests and online downloads! “Implementing a Data Warehouse using SQL” 70-767 exam. Ready to pass the 70-767 exam please click https://www.pass4itsure.com/70-767.html (full exam dump)
Microsoft MCSA 70-767 Exam pdf
[July PDF] Free Microsoft 70-767 pdf dumps download from Google Drive: https://drive.google.com/open?id=1CyxzLVMoosxvSdnoolsGzfUZZh-Ivx83
[April PDF] Free Microsoft 70-767 pdf dumps download from Google Drive: https://drive.google.com/open?id=1wKk2Jjmw4h11JT2uwPHRl3JEzWB_KQ4x
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 Online Exam Practice Questions
QUESTION 1
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may
be correct for more than one question in the series. Each question is independent of the other questions in this series.
Information and details provided in a question apply only to that question.
You are implementing a Microsoft SQL Server data warehouse with a multi-dimensional data model. You have a fact
table that includes sales data for all products. The model includes a dimension named Geography that stores all
geographies. You create a dimension that has a foreign key and provides the ability to analyze sales by the following
sales channels: Internet or retail store.
You need to update the data model to allow business users to analyze Internet sales by geography without changing the
overall structure of the data model. What should you do?
A. star schema
B. snowflake schema
C. conformed dimension
D. slowly changing dimension (SCD)
E. fact table
F. semi-additive measure
G. non-additive measure
H. dimension table reference relationship
Correct Answer: D
QUESTION 2
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.
Correct Answer: I
http://technet.microsoft.com/en-us/library/hh230989.aspx
http://www.rafael-salas.com/2016/01/ssis-2016-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
QUESTION 3
You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.
The reference data to be used in the transformation does not change.
You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance.
What should you do?
A. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
C. Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.
D. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
E. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
Correct Answer: A
Reference: http://msdn.microsoft.com/en-us/library/ms137786.aspx
QUESTION 4
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. Join the two fact tables.
B. Merge the fact tables.
C. Create a time dimension that can join to both fact tables at their respective granularity.
D. Create a surrogate key for the time dimension.
Correct Answer: C
Explanation: References:
http://msdn.microsoft.com/en-us/library/ms174537.aspx http://technet.microsoft.com/en-us/library/ms174832.aspx
http://msdn.microsoft.com/en-us/library/ms174884.aspx http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vsnatural-keys-for-primary-key/
http://www.agiledata.org/essays/keys.html
http://www.databasejournal.com/features/mssql/article.php/3922066/SQL-Server-Natural-Key-Verses-Surrogate-Key.htm
http://www.jamesserra.com/archive/2016/01/surrogate-keys/
QUESTION 5
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
Correct Answer: A
Reference: http://msdn.microsoft.com/en-us/library/hh213137.aspx
QUESTION 6
You are designing a data warehouse that uses SQL Server 2016. You are preparing to update the contents of a fact
table that contains a non-clustered columnstore index.
You need to run an update statement against the table.
What should you do first?
A. Pause the columnstore index.
B. Change the recovery model of the database to Bulk-logged.
C. Change the non-clustered columnstore index to be a clustered columnstore index.
D. Drop the columnstore index.
Correct Answer: D
QUESTION 7
You are designing a SQL Server Integration Services (SSIS) 2016 package that imports data from a Windows Azure
SQL Database database into a SQL Server database. The SSIS package has the following requirements:
Every night, a very large amount of data is imported into the staging database.
Package processing time must be minimized.
The package must run on its own dedicated server when it is deployed to production.
Transaction log activity when data is imported must be minimized.
You need to design the package to meet the requirements.
Which destination component should you use?
A.
DataReader
B.
OLE DB
C.
ADO.NET
D.
SQL Server
Correct Answer: B
QUESTION 8
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 9
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.
Correct Answer: C
QUESTION 10
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) 2016 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
Correct Answer: A
QUESTION 11
You are editing a SQL Server Integration Services (SSIS) package.
You need to edit the Script task that has an expression defined.
Which task should you select? To answer, select the appropriate setting or settings in the answer area.
Hot Area:
Correct Answer:
QUESTION 12
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
Correct Answer: C
QUESTION 13
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:
Share Pass4itsure discount codes for free
About Pass4itsure!
Pass4itsure offers the latest exam practice questions and answers free of charge! Update all exam questions throughout the year, with a number of professional exam experts! To make sure it works! Maximum pass rate, best value for money! It helps you pass the exam easily on your first attempt.
Summarize:
How do I pass the Microsoft MCSA 70-767 exam? You need to be prepared for it! You need the latest and most effective learning materials and proper practices to pass the 70-767 exam. “This exam tests a candidate’s knowledge of data center physical infrastructure, data center networking concepts, and data center storage networking”. Pass4itsure offers you the latest exam materials! You can use the materials to prepare to help you achieve excellent results!
This maybe you’re interested