It is more faster and easier to pass the Microsoft 70-767 exam by using Download Microsoft Implementing a SQL Data Warehouse (beta) questuins and answers. Immediate access to the Improve 70-767 Exam and find the same core area 70-767 questions with professionally verified answers, then PASS your exam with a high score now.
Q52. DRAG DROP
You are editing a SQL Server Integration Services (SSIS) package that contains a task with a sensitive property.
You need to create a project parameter and configure it so that its value is encrypted when it is deployed to the SSIS catalog.
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:
Q53. 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
Q54. You are troubleshooting an existing SQL Server Integration Services (SSIS) package. On several occasions, the package execution does not finish and no data seems to have been transferred.
You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.
What should you do?
A. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
B. Use an msi file to deploy the package on the server.
C. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
D. Create a reusable custom logging component and use it in the SSIS project.
E. Open a command prompt and run the dtutil /copy command.
F. Configure the SSIS solution to use the Project Deployment Model.
G. Add an OnError event handler to the SSIS project.
H. Open a command prompt and run the gacutil command.
I. Open a command prompt and run the dtexec /dumperror /conn command.
J. Open a command prompt and run the dtexec /rep /conn command.
K. Configure the output of a component in the package data flow to use a data tap.
Answer: C
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms141212.aspx http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2021-part-1-of-2/
http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2021-part-2-of-2/
Q55. To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS) package logging methodology.
The methodology has the following requirements:
•Centralized logging in SQL Server
•Simple deployment
•Availability of log information through reports or T-SQL
•Automatic purge of older log entries
•Configurable log details
You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort.
What should you do?
A. Deploy the package by using an msi file.
B. Use the gacutil command.
C. Create an OnError event handler.
D. Create a reusable custom logging component.
E. Use the dtutil /copy command.
F. Use the Project Deployment Wizard.
G. Run the package by using the dtexec /rep /conn command.
H. Add a data tap on the output of a component in the package data flow.
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.
Answer: J
Explanation: References:
http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/ms180378(v=sql.110).aspx
Q56. You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.
You have the following requirements:
•The deployment model must support changing the content of connection strings by using parameters at execution time.
•The deployment model must automatically log events to the SSISDB database.
•Maximize performance at execution time.
You need to design a solution that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use a project deployment model. Modify connection manager properties to use project parameters.
B. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.
C. Use a package deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
D. Use a project deployment model. Modify connection manager properties to use package parameters.
Answer: A
Q57. 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
Q58. 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. Create a time mapping table.
B. Change the level of granularity in both fact tables to be the same.
C. Merge the fact tables.
D. Create a view on the sales table.
Answer: B
Q59. DRAG DROP
A SQL Server Integration Services (SSIS) package named DataFeed interacts with an external vendor data feed. The package is executed several times a day, either as part of other packages' control flow or by itself. The external data feed is unreliable because network failures and slow response times are frequent. The package is currently deployed on the file system.
To analyze the reliability of the external data feed, you must collect execution data. Every time the DataFeed package is executed, the following information must be logged:
✑ Start Time
✑ End Time
✑ Execution Result
✑ Execution Duration
You need to design a logging solution that meets the requirements by using the least amount of administrative and development effort.
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.)
Answer:
Explanation:
Box 1:
Box 2:
Box 3:
Ref: http://msdn.microsoft.com/en-us/library/hh479592.aspx
Q60. DRAG DROP
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.)
Answer:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms141724.aspx http://msdn.microsoft.com/en-us/library/ms137728.aspx
Q61. HOTSPOT
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:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms141821.aspx http://msdn.microsoft.com/en-us/library/ms189697.aspx
Q62. DRAG DROP
You are designing an extract, transform, load (ETL) process with SQL Server Integration Services (SSIS). Two packages, Package A and Package B, will be designed. Package A will execute Package B.
Both packages must reference a file path corresponding to an input folder where files will be located for further processing.
You need to design a solution so that the file path can be easily configured with the least administrative and development effort.
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.)
Answer:
Q63. 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
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/gg492088.aspx Reference: http://msdn.microsoft.com/en-us/library/gg492153.aspx
Q64. You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.
You have the following requirements:
•Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time.
•Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database.
•Maximize performance at execution time.
•Minimize development effort.
You need to design a solution that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created.
B. Use a project deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
C. Use a package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime.
D. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.
Answer: A
Q65. CORRECT TEXT
You are designing a package control flow. The package moves sales order data from a SQL Azure 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.)
Answer:
IsolationLevel = ReadCommited. TransactionOption = Required References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms137749.aspx http://msdn.microsoft.com/en- us/library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx http://msdn.microsoft.com/en-us/library/ms173763.aspx
Q66. You are creating a SQL Server Master Data Services (MDS) model for a company.
The source data for the company is stored in a single table that contains the manager-to- subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?
A. Natural
B. Explicit
C. Parent
D. Recursive
Answer: D
Explanation:
Reference: http://technet.microsoft.com/en-us/library/ff487006.aspx Reference: http://msdn.microsoft.com/en-us/library/ee633747.aspx Reference: http://technet.microsoft.com/en-us/library/ee633759.aspx
Q67. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure.
The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model.
Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule. Each job passes a different folder path to the package.
You need to configure the package to accept the folder path from each job. Which package configuration should you use?
A. .dtsConfig file
B. Registry Entry
C. Environment Variable
D. Parent Package Variable
E. XML Configuration File
Answer: C
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms345179.aspx http://msdn.microsoft.com/en-us/library/ms141708.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://msdn.microsoft.com/en-us/library/hh213296.aspx http://msdn.microsoft.com/en-us/library/hh213293.aspx
Q68. 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 Group, does not yet have a domain.
You need to complete the mapping of the Group column. What should you do?
A. Map a composite domain to the source column.
B. Create a composite domain that includes the Group column.
C. Add a domain for the Group column.
D. Add a column mapping for the Group column.
Answer: C