Microsoft Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 - 70-463 FREE EXAM DUMPS QUESTIONS & ANSWERS
You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a data warehouse that is hosted on Windows Azure SQL Database.
The package contains a data flow task that has seven source-to-destination execution trees.
Only three of the source-to-destination execution trees are running in parallel.
You need to ensure that all the execution trees run in parallel.
What should you do?
The package contains a data flow task that has seven source-to-destination execution trees.
Only three of the source-to-destination execution trees are running in parallel.
You need to ensure that all the execution trees run in parallel.
What should you do?
Correct Answer: D
Vote an answer
You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012.
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.)
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.)
Correct Answer: A,E
Vote an answer
You have a data warehouse that is hosted in a SQL Server instance. The data in the data warehouse is loaded by running bcp.exe.
You discover that the transaction logs regularly fill up the local hard disk on the server and that a WRITELOG wait is present.
You need to reduce the amount of disk space used to store the transaction logs and to remove the WRITELOG wait.
Which two database property settings should you modify? To answer, select the appropriate settings in the answer area.
You discover that the transaction logs regularly fill up the local hard disk on the server and that a WRITELOG wait is present.
You need to reduce the amount of disk space used to store the transaction logs and to remove the WRITELOG wait.
Which two database property settings should you modify? To answer, select the appropriate settings in the answer area.
Correct Answer:
Explanation
You are designing an extract, transform, and load (ETL) solution that loads data into dimension tables. The ETL process involves many transformation steps.
You need to ensure that the design can provide:
* Auditing information for compliance and business user acceptance
* Tracking and unique identification of records for troubleshooting and error correction What should you do?
You need to ensure that the design can provide:
* Auditing information for compliance and business user acceptance
* Tracking and unique identification of records for troubleshooting and error correction What should you do?
Correct Answer: A
Vote an answer
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?
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?
Correct Answer: A
Vote an answer
You administer a Microsoft SQL Server 2012 database. The database contains a table that has the following definition:
You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
* The first row of the file contains the first row of data.
* Each record is of the same length.
* The date follows the U.S. date format.
* The file supports international characters.
What should you do? (To answer, simply select the option or options in the answer area that you would configure.)
You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
* The first row of the file contains the first row of data.
* Each record is of the same length.
* The date follows the U.S. date format.
* The file supports international characters.
What should you do? (To answer, simply select the option or options in the answer area that you would configure.)
Correct Answer:
Tick the "Unicode" checkbox.
Set the Format option to "Fixed Width"
Untick the "Column names in the first data row" checkbox.
Explanation
Each record is of the same length = Fixed Width.
The file supports international characters = Unicode.
The date follows the U.S. date format = Locale (already configured).
The first row of the file contains the first row of data = Uncheck "Column names in the first data row"
Set the Format option to "Fixed Width"
Untick the "Column names in the first data row" checkbox.
Explanation
Each record is of the same length = Fixed Width.
The file supports international characters = Unicode.
The date follows the U.S. date format = Locale (already configured).
The first row of the file contains the first row of data = Uncheck "Column names in the first data row"
You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse. The package consists of several data flow tasks.
The package experiences intermittent errors in the data flow tasks.
If any data flow task fails, all package error information must be captured and written to a SQL Server table by using an OLE DB connection manager.
You need to ensure that the package error information is captured and written to the table.
What should you do?
The package experiences intermittent errors in the data flow tasks.
If any data flow task fails, all package error information must be captured and written to a SQL Server table by using an OLE DB connection manager.
You need to ensure that the package error information is captured and written to the table.
What should you do?
Correct Answer: I
Vote an answer
You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a data warehouse that is hosted on Windows Azure SQL Database. 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.
What should you do?
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.
What should you do?
Correct Answer: C
Vote an answer
A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse.
The SSIS catalog includes two Environments:
* Development
* QA
Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses.
You need to be able to execute deployed packages by using either of the defined Environments.
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 SSIS catalog includes two Environments:
* Development
* QA
Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses.
You need to be able to execute deployed packages by using either of the defined Environments.
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.)
Correct Answer:
Explanation
Box 1:
Box 2:
Box 3:
You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort.
What should you do?
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort.
What should you do?
Correct Answer: A
Vote an answer
Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).
You are preparing to install SQL Server 2012 Master Data Services (MDS).
You need to ensure that the database requirements are met.
What should you install?
You need to ensure that the database requirements are met.
What should you install?
Correct Answer: D
Vote an answer
Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).
You are implementing a SQL Server Integration Services (SSIS) 2012 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 date stored in a control table of the Windows Azure SQL Database database 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?
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 date stored in a control table of the Windows Azure SQL Database database 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?
Correct Answer: C
Vote an answer
You are designing a SQL Server Integration Services (SSIS) package to execute 12 Transact-SQL (T-SQL) statements on a SQL Azure database. The T-SQL statements may be executed in any order. The T-SQL statements have unpredictable execution times.
You have the following requirements:
*The package must maximize parallel processing of the T-SQL statements.
*After all the T-SQL statements have completed, a Send Mail task must notify administrators.
You need to design the SSIS package. 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 have the following requirements:
*The package must maximize parallel processing of the T-SQL statements.
*After all the T-SQL statements have completed, a Send Mail task must notify administrators.
You need to design the SSIS package. 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.)
Correct Answer:
Explanation
Box 1:
Box 2:
Box 3:
You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data.
The CDC Source component has the following requirements:
* The output must include metadata columns that indicate which source columns have changed.
* The output must return only one change row per source row that is modified in the current CDC processing range.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
The CDC Source component has the following requirements:
* The output must include metadata columns that indicate which source columns have changed.
* The output must return only one change row per source row that is modified in the current CDC processing range.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
Correct Answer:
Explanation
Ref: http://msdn.microsoft.com/en-gb/library/hh231004.aspx