Implementing a Data Warehouse with Microsoft SQL Server 2012
Question No: 151 – (Topic 3)
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?
Data Cleansing transformation
Fuzzy Lookup transformation
Term Lookup transformation
Data Profiling task
Question No: 152 – (Topic 3)
You are designing a data warehouse hosted on Windows Azure SQL Database. The data warehouse currently includes the dimUser and dimRegion dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse, and the dimRegion table contains information about sales regions.
The system is accessed by users from certain regions, 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 regions. Some users must be permitted to see sales data from multiple regions.
What should you do?
For each region, create a view of the factSales table that includes a WHERE clause for the region.
Create a userRegion table that contains primary key columns from the dimUser and dimRegion tables.
Add a region column to the dimUser table.
Partition the factSales table on the region column.
Question No: 153 – (Topic 3)
You are the administrator of a server that hosts Data Quality Server for a large retail company.
The server had a hardware failure during business hours.
You need to restore the server that hosts Data Quality Server to another server. You have a recent backup of all the required databases.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
Restore the DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA databases to another server as soon as possible.
Execute the DQS_MAIN.internal_core.RestoreDQDatabases stored procedure with the appropriate parameter.
Restore only the DQS_MAIN and DQS_STAGING_DATA databases to another server as soon as possible.
Execute the DQS_MAIN.internal_core.InitServer stored procedure with the appropriate parameter.
Explanation: Steps to restore DQS Databases:
->Restore DQS_MAIN database.
->Restore the DQS_PROJECTS database.
->Restore the DQS_STAGING_DATA database.
->In Object Explorer, right-click the server, and then click New Query.
->In the Query Editor window, copy the following SQL statements, and
replace lt;PASSWORDgt; with the password that you provided during the DQS installation for the database master key:
USE [DQS_MAIN] GO
EXECUTE [internal_core].[RestoreDQDatabases] #39;lt;PASSWORDgt;#39; GO
->Press F5 to execute the statements. Check the Results pane to verify that the statements have executed successfully.
Backup and restore of SQL Server databases are common operations that database administrators perform for preventing loss of data in a case of disaster by recovering data from the backup databases. Data Quality Server is primarily implemented by two SQL Server databases: DQS_MAIN and DQS_PROJECTS. The backup and restore procedures of the Data Quality Services (DQS) databases are similar to any other SQL Server databases.
Question No: 154 DRAG DROP – (Topic 3)
You have a SQL Server data warehouse that contains all of the customer data for your company. You plan to deploy an archiving solution.
You need to move a table named OLDCUSTOMERS to a new storage device. OLDCUSTOMERS contains a clustered index.
Which four actions should you perform in sequence?
Explanation: Box 1: Enable page compression Box2: Execute a Merge statement
Box3: Remove the clustered index Box4: Create a data file.
Question No: 155 – (Topic 3)
You develop a SQL Server Integration Services (SSIS) package that imports Windows Azure SQL Database data into a data warehouse every night.
The Windows Azure SQL Database 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?
Change the similarity threshold to 0.40.
Decrease the maximum number of matches per lookup.
Change the similarity threshold to 0.85.
Increase the maximum number of matches per lookup.
Explanation: * Similarity threshold
Set the similarity threshold at the component level by using the slider. The closer the value is to 1, the closer the resemblance of the lookup value to the source value must be to qualify as a match. Increasing the threshold can improve the speed of matching since fewer candidate records need to be considered.
Question No: 156 – (Topic 3)
You are installing the Data Quality Server component of Data Quality Services.
You need to provision the hardware and install the software for the server that runs the Data Quality Server.
You must ensure that the minimum Data Quality Server prerequisites are met. What should you do?
Install SQL Server 2012 Database Engine.
Install Microsoft SharePoint Server 2010 Enterprise Edition with PowerPivot.
Make sure the server has at least 4 GB of RAM.
Install Microsoft Internet Explorer 6.0 SP1 or later.
Explanation: Data Quality Server Minimum System Requirements
SQL Server 2012 Database Engine.
Memory (RAM): Minimum: 2 GB
Recommended: 4 GB or more
Note: 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.
Question No: 157 – (Topic 3)
You have a secured database that contains all of the customer data for your company. You plan to use a project deployment model.
You need to create a SQL Server Integration Services (SSIS) package that connects to the database by using a dedicated username and password. The solution must ensure that the password used for the connection string is encrypted.
Which two actions should you perform? (Select Two)
Select the Sensitive check box for the catalog environment.
Set the sensitive property of the package parameter to True.
Modify the set_execution parameter_value stored procedure.
Set the package protection level to EncrypSensitiveWithPassword.
Question No: 158 DRAG DROP – (Topic 3)
You need to design a data load strategy for a data warehouse fact table. The solution must
use an ordered data load.
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.
Question No: 159 – (Topic 3)
You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job.
You make changes to the package that should improve performance.
You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal.
What should you do?
After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.
Configure the package to send you an email upon completion that includes information about the duration of the package. After 10 executions, view the emails.
Enable logging to the Application Event Log in the package control flow for the OnInformation event. After 10 executions, view the Application Event Log.
Enable logging to the Application Event Log in the package control flow for the OnPostExecute event. After 10 executions, view the Application Event Log.
Explanation: The All Executions Report displays a summary of all Integration Services executions that have been performed on the server. There can be multiple executions of the sample package. Unlike the Integration Services Dashboard report, you can configure
the All Executions report to show executions that have started during a range of dates. The dates can span multiple days, months, or years.
The report displays the following sections of information.
Shows the current filter applied to the report, such as the Start time range.
Shows the start time, end time, and duration for each package execution. You can view a list of the parameter values that were used with a package execution, such as values that were passed to a child package using the Execute Package task.
Question No: 160 – (Topic 3)
Your team is creating SQL Server Integration Services (SSIS) packages that have several dependencies. The packages use parameters for configuration purposes.
Your company#39;s IT policies include the following change control requirements:
->After a package is ready for deployment, your team must hand over the process to junior IT personnel.
->The process must guarantee that when a package has been validated, that same
package (and all its dependencies) is deployed to production.
->The process must be repeatable and reliable and must be executed with the least administrative and training effort by junior IT personnel.
You need to use the most appropriate deployment unit to satisfy the company policies, while minimizing issues such as incorrect version or configuration.
Which type of deployment unit should you use?
SSIS deployment manifest