Implementing a Data Warehouse with Microsoft SQL Server 2012
Question No: 41 DRAG DROP – (Topic 1)
You are developing a SQL Server Integration Services (SSIS) package to insert new data into a data mart. The package uses a Lookup transformation to find matches between the source and destination.
The data flow has the following requirements:
鈥ew rows must be inserted.
鈥ookup failures must be written to a flat file.
In the Lookup transformation, the setting for rows with no matching entries is set to Redirect rows to no match output. You need to configure the package to direct data into the correct destinations.
How should you design the data flow outputs? (To answer, drag the appropriate transformation from the list of answer options to the correct location in the answer area.)
Records that have no match in the destination are new records and so should be inserted. Any errors should be recorded in the flat file.
Question No: 42 – (Topic 1)
You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode.
You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode.
What should you do?
Add StartDate and EndDate columns to the customer dimension.
Add an IsCurrent column to the customer dimension.
Enable Snapshot Isolation on the data warehouse.
Add CurrentValue and PreviousValue columns to the customer dimension.
Question No: 43 DRAG DROP – (Topic 1)
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.)
Question No: 44 – (Topic 1)
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?
Create a time mapping table.
Change the level of granularity in both fact tables to be the same.
Merge the fact tables.
Create a view on the sales table.
Question No: 45 – (Topic 1)
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:
鈥he INSERT statement must use the value of a string package variable. The variable name is StringVar.
鈥he 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?
INSERT INTO dbo.Table (variablevalue) VALUES (@StringVar)
INSERT INTO dbo.Table (variablevalue) VALUES ($Project::StringVar)
INSERT INTO dbo.Table (variablevalue) VALUES (?)
INSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar)
Question No: 46 – (Topic 1)
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?
Create an Integration Services package and use a Fuzzy Lookup transform.
Create a Master Data Services (MDS) model.
Create a Data Quality Project.
Create an Integration Services package and use a DQS Cleansing transform.
Create an Integration Services package and use a Fuzzy Grouping transform.
Answer: C Explanation:
Question No: 47 – (Topic 1)
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?
Change the Exhaustive property to True.
Change the similarity threshold to 0.55.
Change the similarity threshold to 0.40.
Increase the maximum number of matches per lookup.
Answer: B Explanation:
Question No: 48 DRAG DROP – (Topic 1)
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.)
Question No: 49 – (Topic 1)
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:
Each of the following queries must be able to use a columnstore index:
鈥ELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN #39;20100101#39; AND #39;20101231#39;.
鈥ELECT AVG([High] – [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN #39;20100101#39; AND #39;20101231#39;.
鈥ELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN #39;20100101#39; AND #39;20101231#39;.
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?
Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].
Create three coiumnstore indexes: One containing [QuoteDate] and [Close]
One containing [QuoteDate], [High], and [Low] One containing [QuoteDate] and [Volume]
Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].
Create two columnstore indexes:
One containing [ID], [QuoteDate], [Volume], and [Close] One containing [ID], [QuoteDate], [High], and [Low]
Question No: 50 – (Topic 1)
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 2012.
Which tool should you use?
SSIS Upgrade Wizard in SQL Server 2008 BIDS
SSIS Upgrade Wizard in SQL Server Data Tools
SQL Server DTExecUI utility (dtexecui.exe)
SQL Server dtexec utility (dtexec.exe)
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|