최신 070-463 무료덤프 - Microsoft Implementing a Data Warehouse with Microsoft SQL Server 2012/2014
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?
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?
정답: D
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.)
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.)
정답:
Explanation
Box 2:
Box 3:
Box 1:
Box 4:
Note:
Step 1-2:
* SQL Server Data Quality Services (DQS) is a new feature in SQL Server 2012 that contains the following two components: Data Quality Server and Data Quality Client.
* You have to use the SQL Server 2012 Setup to install DQS components. When you run the SQL Server Setup, you have to go through a series of installation wizard pages to select appropriate options based on your requirements.
/ Feature Selection
Data Quality Services under Database Engine Services to install the Data Quality Server.
Data Quality Client to install Data Quality Client.
Step 3-4:
* The Data Quality Services (DQS) security infrastructure is based upon the SQL Server security infrastructure. A database administrator grants a user a set of permissions by associating the user with a DQS role. Doing so determines the DQS resources that the user has access to and the functional activities that the user is allowed to perform.
* There are four roles for DQS. One is the database administrator (DBA) who deals primarily with product installation, database maintenance, and user management. This role primarily uses the SQL Server Management Studio, rather than within the Data Quality Client application. Their server role is sysadmin.
The three other roles are information workers, data stewards who use the product directly by working in the Data Quality Client application. These roles include the following:
/ The DQS Administrator (dqs_administrator role) can do everything in the scope of the product.
/ The DQS KB Editor (dqs_kb_editor role) can perform all of the DQS activities, except for administration.
/ The DQS KB Operator (dqs_kb_operator role) can edit and execute a project. They cannot perform any kind of knowledge management; they cannot create or change a knowledge base. They can see the activity monitoring data, but cannot terminate an activity or perform administrative duties.
Ref: http://msdn.microsoft.com/en-gb
A new SQL Server Integration Services (SSIS) project is deployed to the SSIS catalog.
To troubleshoot some data issues, you must output the data streaming through several data flows into text files for further analysis. You have the list of data flow package paths and identification strings of the various task components that must be analyzed.
You need to create these output files with the least amount of administrative and development effort.
Which three stored procedures should you execute in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
To troubleshoot some data issues, you must output the data streaming through several data flows into text files for further analysis. You have the list of data flow package paths and identification strings of the various task components that must be analyzed.
You need to create these output files with the least amount of administrative and development effort.
Which three stored procedures should you execute in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
정답:
Explanation
Box 1:
Box 2:
Box 3:
Ref: http://msdn.microsoft.com/en-gb/library/hh230989(v=sql.110).aspx
You have a SQL Server Integration Services (SSIS) package that runs on a server named SQL1 and connects to a SQL Server database on a server named SQL2.
You schedule the package to run via a SQL Server Agent job.
The package fails and generates the following error message: "Unknown server or Access is Denied".
You discover that the package is configured to authenticate by using Windows authentication and that the SQL Server Agent on SQL1 uses NT Service\SQLSERVERAGENT as a service account.
You verify that other jobs run on the server without errors.
You create a user account named CONTOSO\JobProcess1 and provide all of the required access to the tables on SQL2.
You need to ensure that the package runs successfully via the SQL Server Agent job.
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.
You schedule the package to run via a SQL Server Agent job.
The package fails and generates the following error message: "Unknown server or Access is Denied".
You discover that the package is configured to authenticate by using Windows authentication and that the SQL Server Agent on SQL1 uses NT Service\SQLSERVERAGENT as a service account.
You verify that other jobs run on the server without errors.
You create a user account named CONTOSO\JobProcess1 and provide all of the required access to the tables on SQL2.
You need to ensure that the package runs successfully via the SQL Server Agent job.
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.
정답:
Explanation
You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model.
The project will be deployed to an SSIS catalog folder where Environments have already been created. You need to deploy the project. What should you do?
The project will be deployed to an SSIS catalog folder where Environments have already been created. You need to deploy the project. What should you do?
정답: L
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?
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
You are developing a SQL Server Integration Services (SSIS) package.
The package must run a parameterized query against a Windows Azure SQL Database database.
You need to use the least amount of development effort to meet the package requirement.
Which task should you use? (To answer, select the appropriate task in the answer area.)
The package must run a parameterized query against a Windows Azure SQL Database database.
You need to use the least amount of development effort to meet the package requirement.
Which task should you use? (To answer, select the appropriate task in the answer area.)
정답:
Explanation
Running Parameterized SQL Commands
SQL statements and stored procedures frequently use input parameters, output parameters, and return codes.
The Execute SQL task supports the Input, Output, and ReturnValue parameter types. You use the Input type for input parameters, Output for output parameters, and ReturnValue for return codes.
Ref: http://msdn.microsoft.com/en-us/library/ms141003.aspx
In SSIS there are two tasks than can be used to execute SQL statements: Execute T-SQL Statement and Execute SQL. What is the difference between the two?
The Execute T-SQL Statement task tasks less memory, parse time, and CPU time than the Execute SQL task, but is not as flexible. If you need to run parameterized queries, save the query results to variables, or use property expressions, you should use the Execute SQL task instead of the Execute T-SQL Statement task.
Ref: http://www.sqlservercentral.com/blogs/ja
messerra/2012/11/08/ssis-execute-sql-task-vs-execute-t-sql-statement-task/
You deploy a server that has SQL Server installed.
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package to execute.
In which order should you perform all the actions?
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package to execute.
In which order should you perform all the actions?
정답:
Explanation
You are developing a SQL Server Integration Services (SSIS) package that downloads data from a Windows Azure SQL Database database.
A stored procedure will be called in an Execute SQL task by using an ODBC connection. This stored procedure has only the @CustomerID parameter of type INT.
A project parameter named CustID will be mapped to the stored procedure parameter @CustomerID.
You need to ensure that the value of the CustID parameter is passed to the @CustomerID stored procedure parameter.
In the Parameter Mapping tab of the Execute SQL task editor, how should you configure the parameter? (To answer, drag the appropriate option or options to the correct location or locations in the answer area.)
A stored procedure will be called in an Execute SQL task by using an ODBC connection. This stored procedure has only the @CustomerID parameter of type INT.
A project parameter named CustID will be mapped to the stored procedure parameter @CustomerID.
You need to ensure that the value of the CustID parameter is passed to the @CustomerID stored procedure parameter.
In the Parameter Mapping tab of the Execute SQL task editor, how should you configure the parameter? (To answer, drag the appropriate option or options to the correct location or locations in the answer area.)
정답:
Explanation
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?
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?
정답: B
설명: (DumpTOP 회원만 볼 수 있음)
You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a Microsotf Azure SQL Data Warehouse instance. The package contains a data flow task that has 10 source-to-destination execution trees.
Only five of the source-to-destination execution trees are running in parallel.
You need to ensure that all the execution trees run in parallel.
Only five of the source-to-destination execution trees are running in parallel.
You need to ensure that all the execution trees run in parallel.
정답: A
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?
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?
정답: D
설명: (DumpTOP 회원만 볼 수 있음)
You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment model.
The project contains many packages. It is deployed on a server named SQLTest1. The project will be deployed to several servers that run SQL Server 2012.
The project accepts one required parameter. The data type of the parameter is a string.
A SQL Agent job is created that will call the Loading.dtsx package in the project. A job step is created for the SSIS package.
The job must pass the value of an SSIS Environment Variable to the project parameter. The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter.
You need to configure SSIS on the SQLTest1 server to pass the Environment Variable to the package.
Which four actions should you perform in sequence by using SQL Server Management Studio?
(To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
The project contains many packages. It is deployed on a server named SQLTest1. The project will be deployed to several servers that run SQL Server 2012.
The project accepts one required parameter. The data type of the parameter is a string.
A SQL Agent job is created that will call the Loading.dtsx package in the project. A job step is created for the SSIS package.
The job must pass the value of an SSIS Environment Variable to the project parameter. The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter.
You need to configure SSIS on the SQLTest1 server to pass the Environment Variable to the package.
Which four actions should you perform in sequence by using SQL Server Management Studio?
(To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
정답:
Explanation
Box 1:
Box 2:
Box 3:
Box 4:
You are developing a SQL Server Integration Services (SSIS) package.
You create a script task in the package.
You need to dynamically set the file name of a connection object named File1 by using the script task Which line of code should you add to the script task?
You create a script task in the package.
You need to dynamically set the file name of a connection object named File1 by using the script task Which line of code should you add to the script task?
정답: A
설명: (DumpTOP 회원만 볼 수 있음)
You are a data warehouse developer responsible for developing data cleansing processes.
Duplicate employees exist in an employee dimension.
You need to map, discover, and manage domain values based on the employee dimension.
Which Data Quality Services (DQS) option should you use? (To answer, select the appropriate option in the answer area.)
Duplicate employees exist in an employee dimension.
You need to map, discover, and manage domain values based on the employee dimension.
Which Data Quality Services (DQS) option should you use? (To answer, select the appropriate option in the answer area.)
정답:
Explanation
A new dedicated server is used to execute resource-intensive SQL Server Integration Services (SSIS) 2012 packages. The environment that you are deploying the packages to has the following constraints:
* The operating system is Windows Server 2008 R2.
* The SSIS packages are stored in the SSIS catalog.
* Some of these SSIS packages use 32-bit custom components.
You need to install only the components that are required to deploy and run the packages on the new server.
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.)
* The operating system is Windows Server 2008 R2.
* The SSIS packages are stored in the SSIS catalog.
* Some of these SSIS packages use 32-bit custom components.
You need to install only the components that are required to deploy and run the packages on the new server.
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.)
정답:
Explanation
Box 1:
Box 2:
Box 3:
Note:
Step 1:
Step 2:
If you require 32-bit versions of the Integration Services runtime and tools to run certain packages in 32-bit mode, you must also install SQL Server Data Tools (SSDT).
Note:
For a complete installation of Integration Services, together with the tools and documentation for developing and managing packages, select both Integration Services and the following Shared Features:
* SQL Server Data Tools to install the tools for designing packages.
* Management Tools - Complete to install SQL Server Management Studio for managing packages.
* Client Tools SDK to install managed assemblies for Integration Services programming.
Step 3: How to Modify the Service Configuration File to Connect to a Named Instance of SQL Server To connect to a named instance of SQL Server, you must modify the service configuration file, MsDtsSrvr.ini.xml.