Implementing a Data Warehouse using SQL – Microsoft: 70-767 exam. Share the latest 70-767 dumps and Practice test questions for free. Real and effective exam questions and answers. 70-767 pdf online Download, Examthings share 40 Practice test questions for FREE! Get the full 70-767 exam dumps: https://www.leads4pass.com/70-767.html (Total questions:372 Q&A)
[PDF] Free Microsoft MCSA 70-767 pdf dumps download from Google Drive: https://drive.google.com/open?id=1NKCEjU9z6NU6YK89sShYlQZF0NCeVCH6
[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1AwBFPqkvdpJBfxdZ3nGjtkHQZYdBsRVz
Exam 70-767: Implementing a Data Warehouse using SQL – Microsoft: https://www.microsoft.com/en-us/learning/exam-70-767.aspx
Latest effective Microsoft MCSA 70-767 Exam Practice Tests
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 a 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 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 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
You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL
Azure database. The project uses the Package Deployment Model. This project is deployed to SQL Server on a test
server.
You need to ensure that the project is deployed to the SSIS catalog on the production server.
What should you do?
A. Open a command prompt and run the dtexec /dumperror /conn command.
B. Create a reusable custom logging component and use it in the SSIS project.
C. Open a command prompt and run the gacutil command.
D. Add an OnError event handler to the SSIS project.
E. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe
utility.
F. Open a command prompt and run the dtexec /rep /conn command.
G. Open a command prompt and run the dtutil /copy command.
H. Use an msi file to deploy the package on the server.
I. Configure the SSIS solution to use the Project Deployment Model.
J. Configure the output of a component in the package data flow to use a data tap.
K. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
Correct Answer: I
Explanation: References:
http://msdn.microsoft.com/en-us/library/hh231102.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx
http://msdn.microsoft.com/en-us/library/hh213373.aspx
QUESTION 3
You are implementing a Microsoft SQL Server data warehouse with a multi-dimensional data model.
Orders are stored in a table named Factorder. The addresses that are associated with all orders are stored in a fact
table named FactAddress. A key in the FoctAddress table specifies the type of address for an order.
You need to ensure that business users can examine the address data by either of the following:
shipping address and billing address
shipping address or billing address type Which data model should you use?
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: H
QUESTION 4
Yo ure transactional database to an on-premise reporting database. The package will run several times a day, while
new sales orders are being added to the transactional database. The current design of the package control flow is
shown in the answer area. (Click the Exhibit button.)
The Insert New Orders Data Flow task must meet the following requirements:
•
Usage of the tempdb database should not be impacted.
•
Concurrency should be maximized, while only reading committed transactions.
•
If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements.
How should you configure the transaction properties? (To answer, select the appropriate setting or settings in the
answer area.) 
Correct Answer: IsolationLevel = ReadCommited. TransactionOption = Required
http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms137749.aspx
http://msdn.microsoft.com/enus/ library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx
http://msdn.microsoft.com/en-us/library/ms173763.aspx
QUESTION 5
You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server
2008 Business Intelligence Development Studio (BIDS).
The package includes custom scripts that must be upgraded.
You need to upgrade the package to SQL Server 2016.
Which tool should you use?
A. SQL Server dtexec utility (dtexec.exe)
B. SQL Server DTExecUI utility (dtexecui.exe)
C. SSIS Upgrade Wizard in SQL Server Data Tools
D. SQL Server Integration Services Deployment Wizard
Correct Answer: C
Explanation: Use the SSIS Package Upgrade Wizard to upgrade SQL Server 2005 Integration Services (SSIS)
packages and SQL Server 2008 Integration Services (SSIS) packages to the package format for the current (2016)
release of SQL Server Integration Services.
QUESTION 6
Your company is evaluating the data cleansing capabilities of SQL Server Data Quality Services (DQS).
A stand-alone server will be used to host DQS, as well as all related services necessary to run this service.
You need to install the DQS components on the server by using the minimum amount of steps possible. You also need
to grant permissions to a login named DataStewards to enable DataStewards to edit and execute DQS projects.
Which four 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:
QUESTION 7
You are implementing a new SQL Server Integration Services (SSIS) 2016 package that loads data from various flat
files and a Windows Azure SQL Database database.
Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable as the
transaction date.
You need to set the CurrentDate variable to the current execution date when the package starts. You need to achieve
this goal by using the least amount of development effort.
What should you use to set the variable?
A. An Expression task
B. A Script component
C. A Script task
D. An Execute SQL task
Correct Answer: A
Explanation: Ref: http://msdn.microsoft.com/en-us/library/ms141214.aspx http://blog.sqltechie.com/2016/11/expression-
task-in-ssis-2016.html
QUESTION 8
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 multi-dimensional models.
The data warehouse stores data related to your company sales, financial transactions and financial budgets. All data for
the data warehouse 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.dia_city table to Dimension.city. Data loss is not
permissible, and you must not leave traces of the old table in the data warehouse.
You must implement a partitioning scheme for the fact.Transaction table to move older data to less expensive storage.
Each partition will store data for a single calendar year, as shown in the exhibit (Click the Exhibit button.) You must
align
the partitions.
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 configure the fact. Transaction table.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate
Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct
order.
Select and Place: 
QUESTION 9
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 have a database named DB1 that has change data capture enabled.
A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data
warehouse by querying the change data capture tables.
Users report that an application that uses DB1 is suddenly unresponsive.
You discover that the Integration Services job causes severe blocking issues in the application. You need to ensure that
the users can run the application as quickly as possible. Your SQL Server login is a member of only the ssis.admin
database role.
Which stored procedure should you execute?
A. catalog.deploy_project
B. catalog.restore_project
C. catalog.stop.operation
D. sys.sp.cdc.addjob
E. sys.sp.cdc.changejob
F. sys.sp_cdc_disable_db
G. sys.sp_cdc_enable_db
H. sys.sp_cdc.stopJob
Correct Answer: E
sys.sp_cdc_change_job modifies the configuration of a change data capture cleanup or capture job in the current
database.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-
transact-sql? view=sql-server-2017
QUESTION 10
You have a server named SQL1 that has SQL Server Integration Services (SSIS) installed. SQL1 has eight CPUs, 8 GB
of RAM, RAID-1 storage, and a 10-gigabit Ethernet connection.
A package named Package1 runs on SQL1. Package1 contains 10 data tasks. Package1 queries 3 GB of data from the
source system.
You review the performance statistics on SQL1 and discover that Package1 executes slower than expected.
You need to identify the problem that is causing package1 to execute slowly.
Which performance monitor counter data should you review?
A. SQLServer:SSIS Pipeline 10.0:Buffers in use
B. Processor\% Idle Time
C. SQLServer:SSIS Pipeline 10.0:Buffers spooled
D. SQLServer:SSIS Pipeline 10.0:Private buffer memory
Correct Answer: B
QUESTION 11
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.
You have an on-premises Microsoft SQL Server instance and a Microsoft Azure SQL Data Warehouse instance. You
move data from the on-premises database to the data warehouse once each day by using a SQL Server Integration
Services (SSIS) package.
You observe that the package no longer completes within the allotted time. You need to determine which tasks are
taking a long time to complete.
Solution: You alter the package to log the start and completion times for a task to a table in the on-premises SQL Server
instance. Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
QUESTION 12
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 sections, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You have a Microsoft Azure SQL Data Warehouse instance. You run the following Transact-SQL statement:
The query fails to return results.
You need to determine why the query fails.
Solution: You run the following Transact-SQL statement: 
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
To use submit_time we must use sys.dm_pdw_exec_requests table.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-pdw-
exec-requests-transact-sql?view=aps-pdw-2016-au7
QUESTION 13
You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server
2016. Quality Assurance (QA) testers in the company must have permission to perform the following tasks on specific
projects:
View and validate projects and packages
View Environments and Environment variables
Execute packages
You need to grant the minimum possible privileges to the QA testers.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A.
In the SSISDB database, add QA Tester logons to the ssis_admin role.
B.
In the msdb database, add QA Tester logons to the db_ssisoperator role.
C.
Grant Modify permission in the projects to the QA Tester logons.
D.
Grant Read permission in the SSIS catalog folder, the projects, and the Environments to the QA Tester logons.
E.
Grant Execute permission in the projects to the QA Tester logons.
F.
In the msdb database, add QA Tester logons to the db_ssisItduser role.
Correct Answer: BD
QUESTION 14
You are developing a SQL Server Integration Services (SSIS) package.
The package control flow will contain many tasks. The tasks will execute consecutively and none will execute more than
once. Certain groups of tasks will share variable and transaction scope.
You need to group tasks together while enabling them to be collapsed and expanded as a group.
Which item should you use from the SSIS Toolbox? (To answer, select the appropriate item in the answer area.)
Hot Area:
QUESTION 15
You have a data warehouse that contains all of the sales data for your company.
You need to design a SQL Server Integration Services (SSIS) package that reports the average value for the one of the
columns in a data flow.
What should you add to the package?
A. Data profiling tasks that have a Column Null profile.
B. Data profiling tasks that have Column value Distribution Profile.
C. Data Profiling tasks that have Column Statistics profile.
D. the Data Mining Query task.
Correct Answer: B
QUESTION 16
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.
Correct Answer: A
http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/gg492277.aspx
QUESTION 17
You are deploying a new SQL Server Integration Services (SSIS) package to several servers. The package must meet
the following requirements:
•
.NET Common Language Runtime (CLR) integration in SQL Server must not be enabled.
•
The Connection Managers used in the package must be configurable without editing the package.
•
The deployment procedure must be automated as much as possible. You need to set up a deployment strategy that
meets the requirements. What should you do?
A.
Use the gacutil command.
B.
Use the dtutil /copy command.
C.
Use the Project Deployment Wizard.
D.
Create an OnError event handler.
E.
Create a reusable custom logging component.
F.
Run the package by using the dtexec /rep /conn command.
G.
Run the package by using the dtexec /dumperror /conn command.
H.
Run the package by using the dtexecui.exe utility and the SQL Log provider.
I.
Add a data tap on the output of a component in the package data flow.
J.
Deploy the package by using an msi file.
K.
Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Correct Answer: B
QUESTION 18
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.
Your company uses Microsoft SQL Server to deploy a data warehouse to an environment that has a SQL Server
Analysis Services (SSAS) instance. The data warehouse includes the Fact.Order table as shown in the following table
definition. The table has no indexes.
You must minimize the amount of space that indexes for the Fact.Order table consume. You run the following queries
frequently. Both queries must be able to use a columnstore index: 
You need to ensure that the queries complete as quickly as possible.
Solution: You create one columnstore index that includes the [Order Date Key], [Tax Amount], and [Total Excluding Tax]
columns.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
You should use a columnstore index.
Columnstore indexes are the standard for storing and querying large data warehousing fact tables. This index uses
column-based data storage and query processing to achieve gains up to 10 times the query performance in your data
warehouse over traditional row-oriented storage.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2017
QUESTION 19
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 (?)
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 20
You administer a SQL Server Integration Services (SSIS) solution in the SSIS catalog. A SQL Server Agent job is used
to execute a package daily with the basic logging level.
Recently, the package execution failed because of a primary key violation when the package inserted data into the
destination table.
You need to identify all previous times that the package execution failed because of a primary key violation.
What should you do?
A. Use an event handler for OnError for the package.
B. Use an event handler for OnError for each data flow task.
C. Use an event handler for OnTaskFailed for the package.
D. View the job history for the SQL Server Agent job.
E. View the All Messages subsection of the All Executions report for the package.
F. Store the System::SourceID variable in the custom log table.
G. Store the System::ServerExecutionID variable in the custom log table.
H. Store the System::ExecutionInstanceGUID variable in the custom log table.
I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
K. Deploy the project by using dtutil.exe with the /COPY DTS option.
L. Deploy the project by using dtutil.exe with the /COPY SQL option.
M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
P. Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and
SSISDB.catalog.start_execution stored procedures.
Q. Create a table to store error information. Create an error output on each data flow destination that writes OnError
event text to the table.
R. Create a table to store error information. Create an error output on each data flow destination that writes
OnTaskFailed event text to the table.
Correct Answer: E
QUESTION 21
You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server
2008 Business Intelligence Development Studio (BIDS).
The package includes custom scripts that must be upgraded.
You need to upgrade the package to SQL Server 2016.
Which tool should you use?
A. SQL Server Integration Services Deployment Wizard
B. SQL Server Configuration Manager
C. SSIS Upgrade Wizard in SQL Server Management Studio
D. SSIS Upgrade Wizard in SQL Server 2008 BIDS
Correct Answer: C
Explanation: You can upgrade packages that were created in earlier versions of Integration Services to the Integration
Services format that SQL Server 2016 uses. SQL Server provides the SSIS Package Upgrade Wizard to help in this
process. Because you can configure the wizard to backup up your original packages, you can continue to use the
original packages if you experience upgrade difficulties.
You can run the SSIS Package Upgrade Wizard from SQL Server Data Tools (SSDT), from SQL Server Management
Studio, or at the command prompt.
Note:
* When you upgrade an instance of SQL Server 2005 or SQL Server 2008 to the current release of SQL Server, your
existing SQL Server 2008 Integration Services (SSIS) packages are not automatically upgraded to the package format
that the current release SQL Server Integration Services uses. You will have to select an upgrade method and manually
upgrade your packages.
QUESTION 22
You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse
every night.
The SQL Azure data contains many misspellings and variations of abbreviations. To import the data, a developer used
the Fuzzy Lookup transformation to choose the closest- matching string from a reference table of allowed values. The
number of rows in the reference table is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.
The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading
performance.
What should you do?
A. Change the Exhaustive property to True.
B. Change the similarity threshold to 0.55.
C. Change the similarity threshold to 0.40.
D. Increase the maximum number of matches per lookup.
Correct Answer: B
http://msdn.microsoft.com/en-us/library/ms137786.aspx
QUESTION 23
You are installing SQL Server Data Quality Services (DQS).
You need to give users belonging to a specific Active Directory group access to the Data Quality Server.
Which SQL Server application should you use?
A. Data Quality Client with administrative credentials
B. SQL Server Configuration Manager with local administrative credentials
C. SQL Server Data Tools with local administrative permissions
D. SQL Server Management Studio with administrative credentials
Correct Answer: D
QUESTION 24
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:
QUESTION 25
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 sections, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You are developing a Microsoft SQL Server Integration Services (SSIS) projects. The project consists of several
packages that load data warehouse tables.
You need to extend the control flow design for each package to use the following control flow while minimizing
development efforts and maintenance:
Solution: You add the control flow to a control flow package part. You add an instance of the control flow package part
to each data warehouse load package. Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
A package consists of a control flow and, optionally, one or more data flows. You create the control flow in a package by
using the Control Flow tab in SSIS Designer. References: https://docs.microsoft.com/en-us/sql/integration-
services/control-flow/control- flow
QUESTION 26
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 27
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.
You have a Microsoft SQL server that has Data Quality Services (DQS) installed.
You need to review the completeness and the uniqueness of the data stored in the matching policy.
Solution: You profile the data.
Does this meet the goal?
A. Yes
B. No
Correct Answer: B
Use a matching rule.
References: https://docs.microsoft.com/en-us/sql/data-quality-services/create-a-matching-policy?view=sql-server-2017
QUESTION 28
You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure
database to a data warehouse. Before the product data is loaded, you create a batch record by using an Execute SQL
task
named Create Batch.
After successfully loading the product data, you use another Execute SQL task named Set Batch Success to mark the
batch as successful.
You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load
Products task fails.
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.)
Select and Place: 
QUESTION 29
You have a Microsoft SQL Server Integration Services (SSIS) package that loads data into a data warehouse each night
from a transactional system. The package also loads data from a set of Comma-Separated Values (CSV) files that are
provided by your company’s finance department.
The SSIS package processes each CSV file in a folder. The package reads the file name for the current file into a
variable and uses that value to write a log entry to a database table.
You need to debug the package and determine the value of the variable before each file is processed.
Which four 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:
You debug control flows.
The Foreach Loop container is used for looping through a group of files. Put the breakpoint on it.
The Locals window displays information about the local expressions in the current scope of the Transact-SQL
debugger.
References: https://docs.microsoft.com/en-us/sql/integrationservices/troubleshooting/debugging-control-flow
http://blog.pragmaticworks.com/looping-through-a-result-set-with-the-foreach-loop
QUESTION 30
You are designing a data warehouse for a fresh food distribution business that stores sales by individual product. It
stores sales targets by product category. Products are classified into subcategories and categories.
Each product is included in only a single product subcategory, and each subcategory is included in only a single
category.
The data warehouse will be a data source for an Analysis Services cube.
The data warehouse contains two fact tables:
•
factSales, used to record daily sales by product
•
factProductTarget, used to record the monthly sales targets by product category
Reports must be developed against the warehouse that reports product sales by product, category and subcategory,
and product sales targets.
You need to design the product dimension. The solution should use as few tables as possible while supporting all the
requirements.
What should you do?
A.
Create two product tables, dimProduct and dimProductCategory. Connect factSales to dimProduct and
factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity
attributes.
B.
Create one product table, dimProduct, which contains product detail, category, and subcategory columns. Connect
factSales to dimProduct with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for
factProductTarget.
C.
Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory, and a fourth bridge table
that joins products to their appropriate category and subcategory table records with foreign key constraints. Direct the
cube developer to use key granularity attributes.
D.
Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory. Connect factSales to all
three product tables and connect factProductTarget to dimProductCategory with foreign key constraints. Direct the cube
developer to use key granularity attributes.
Correct Answer: B
QUESTION 31
You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist and Song columns.
The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base.
The values in the Song column in the dataset can be a close match with the values in the Song domain.
You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates.
How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.)
Select and Place:
QUESTION 32
You are building a SQL Server Integration Services (SSIS) package to load data from all files that are automatically
copied to a directory each night through an external FTP process.
You need to load data from all copied files to a destination table in SQL Server.
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.)
Select and Place:
QUESTION 33
You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. All
packages in the project must log custom messages.
You need to produce reports that combine the custom log messages with the system- generated log messages. What
should you do?
A. Use an event handler for OnError for the package.
B. Use an event handler for OnError for each data flow task.
C. Use an event handler for OnTaskFailed for the package.
D. View the job history for the SQL Server Agent job.
E. View the All Messages subsection of the All Executions report for the package.
F. Store the System::SourceID variable in the custom log table.
G. Store the System::ServerExecutionID variable in the custom log table.
H. Store the System::ExecutionInstanceGUID variable in the custom log table.
I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow,
K. Deploy the project by using dtutil.exe with the /COPY DTS option.
L. Deploy the project by using dtutil.exe with the /COPY SQL option.
M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
P. Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and
SSISDB.catalog.start_execution stored procedures.
Q. Create a table to store error information. Create an error output on each data flow destination that writes OnError
event text to the table.
R. Create a table to store error information. Create an error output on each data flow destination that writes
OnTaskFailed event text to the table.
Correct Answer: G
QUESTION 34
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.)
Select and Place:
QUESTION 35
You are developing a data flow transformation to merge two data sources. One source contains product data and the
other source contains data about the country in which the product was manufactured. Both data sources contain a two-
character CountryCode column and both use SQL Server. Both data sources contain an ORDER BY clause to sort the
data by the CountryCode column in ascending order.
You use a Merge Join transformation to join the data.
You need to ensure that the Merge Join transformation works correctly without additional transformations. What should
you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Change the ORDER BY clause on the product source to order by ProductName.
B. Change the Merge Join transformation to a Merge transformation.
C. Set the appropriate SortKeyPosition properties on the data sources.
D. Set the IsSorted property on both data sources.
Correct Answer: CD
QUESTION 36
Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and
nothing is processed.
You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.
What should you do?
A. Create a reusable custom logging component.
B. Use the gacutil command.
C. Use the Project Deployment Wizard.
D. Run the package by using the dtexec /rep /conn command.
E. Add a data tap on the output of a component in the package data flow.
F. Create an OnError event handler.
G. Use the dtutil /copy command.
H. Deploy the package by using an msi file.
I. Run the package by using the dtexec /dumperror /conn command.
J. Run the package by using the dtexecui.exe utility and the SQL Log provider.
K. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Correct Answer: J
http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx
QUESTION 37
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:
QUESTION 38
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 sections, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You have a Microsoft Azure SQL Data Warehouse instance that must be available six months a day for reporting.
You need to pause the compute resources when the instance is not being used.
Solution: You use SQL Server Configuration Manager.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
To pause a SQL Data Warehouse database, use any of these individual methods.
Pause compute with Azure portal
Pause compute with PowerShell
Pause compute with REST APIs
References:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage- compute-overview
QUESTION 39
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.
You are the administrator of a Microsoft SOL Server Master Data Services (MDS) instance. The instance contains a
model named Geography and a model named customer. The Geography model contains an entity named
countryRegion.
You need to ensure that the countryRegion entity members are available in the customer model.
Solution: In the Geography model, publish a business rule with a Change Value action.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
QUESTION 40
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.
Hot Area:
Share 40 of the latest and effective 70-767 exam dumps and Practice test questions for free,100% real and effective exam questions and answers! Get the full 70-767 dumps:https://www.leads4pass.com/70-767.html (Total questions:372 Q&A)
[PDF] Free Microsoft MCSA 70-767 pdf dumps download from Google Drive: https://drive.google.com/open?id=1NKCEjU9z6NU6YK89sShYlQZF0NCeVCH6
[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1AwBFPqkvdpJBfxdZ3nGjtkHQZYdBsRVz
Lead4pass Promo Code 12% Off
related: https://www.certificationdemo.com/update-latest-microsoft-70-533-dumps/