Implementing a Data Warehouse with Microsoft SQL Server 2012
Question No: 101 – (Topic 2)
You install a SQL Server 2012 database engine instance on a production server. A month later, you install SQL Server 2012 Integration Services (SSIS).
You must develop an SSIS project and deploy it to the server by using the Project Deployment model.
Operations log records that are outside the configured retention period must be cleaned automatically.
You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met.
What should you do? (Each correct answer presents part of the solution. Choose all that
Enable CLR Integration.
Enable FILESTREAM with Full Access.
Enable the Resource Governor.
Change the recovery mode of the msdb database to FULL.
Change the Server-wide Default Logging Level in SSISDB to Verbose.
Start the SQL Server Browser service.
Start the SQL Server Agent service.
Question No: 102 – (Topic 2)
You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL statement should you use?
CREATE TABLE Customer (SourceID int NOT NULL IDENTITY, CustomerID int NOT NULL IDENTITY,
CustomerName varchar(255) NOT NULL);
CREATE TABLE Customer (SourceID int NOT NULL,
CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerName varchar(255) NOT NULL);
CREATE TABLE Customer
(SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);
CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED
Question No: 103 DRAG DROP – (Topic 2)
You plan to deploy a SQL Server Integration Services (SSIS) project by using the project deployment model.
You need to monitor control flow tasks to determine whether any of them are running longer than usual. 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.)
Connect to SSISDB.
Query against execution_component_phases and executions.
Question No: 104 – (Topic 2)
You are developing a SQL Server Integration Services (SSIS) package.
The package is stored as the myPackage.dtsx file in the root directory of the C drive of the SSIS server.
You need to run the package from the command prompt.
Which command should you use?
dtexec /sql myPackage
dtexec /d quot;C:\File System\myPackage.dtsxquot;
dtexec /f quot;C:\myPackage.dtsxquot;
dtexec /com quot;myPackage.dtsxquot;
Question No: 105 – (Topic 2)
You are designing an extract, transform, load (ETL) process for loading data from a SQL Server database into a large fact table in a data warehouse each day with the prior day#39;s sales data.
The ETL process for the fact table must meet the following requirements:
->Load new data in the shortest possible time.
->Remove data that is more than 36 months old.
->Ensure that data loads correctly.
->Minimize record locking.
->Minimize impact on the transaction log.
You need to design an ETL process that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Partition the destination fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.
Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.
Partition the destination fact table by customer. Use partition switching both to remove old data and to load new data into each partition.
Partition the destination fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table.
Question No: 106 – (Topic 2)
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:
鈥he deployment model must support changing the content of connection strings by using parameters at execution time.
鈥he deployment model must automatically log events to the SSISDB database.
鈥aximize 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.)
Use a project deployment model. Modify connection manager properties to use project parameters.
Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.
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.
Use a project deployment model. Modify connection manager properties to use package parameters.
Question No: 107 DRAG DROP – (Topic 2)
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse.
You are developing the part of the SSIS package that populates the ProjectDates dimension table. The business key of the ProjectDates table is the ProjectName column.
The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table:
鈥xpectedStartDate – New values should be tracked over time.
鈥ctualStartDate – New values should not be accepted.
鈥xpectedEndDate – New values should replace existing values.
鈥ctualEndDate – New values should be tracked over time. You use the SSIS Slowly Changing Dimension Transformation.
You must configure the Change Type value for each source column.
Which Change Type values should you select? (To answer, drag the appropriate value from the list of values to the correct location or locations in the answer area.)
Question No: 108 – (Topic 2)
You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors.
Products for each distributor source must be combined for insertion into a single product table destination.
You need to select the appropriate data flow transformation to meet this requirement.
Which transformation types should you use? (Each answer represents a complete solution. Choose all that apply.)
Slowly Changing Dimension
Question No: 109 DRAG DROP – (Topic 2)
You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table.
You have the following requirements:
鈥he fact table must populate a column named TotalCost that computes the sum of the price and tax columns.
鈥efore the sum is calculated, any records that have a price of zero must be discarded.
You need to create the SSIS package in SQL Server Data Tools.
In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.)
Question No: 110 DRAG DROP – (Topic 2)
You are creating a sales data warehouse. When a product exists in the product dimension, you update the product name. When a product does not exist, you insert a new record.
In the current implementation, the DimProduct table must be scanned twice, once for the insert and again for the update. As a result, inserts and updates to the DimProduct table take longer than expected.
You need to create a solution that uses a single command to perform an update and an insert.
How should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag the appropriate answer choice from the list of options to the correct location or locations in the answer area.
You may need to drag the split bar between panes or scroll to view content.)
Dumps4cert ExamCollection Testking Lowest Price Guarantee Yes No No Up-to-Dated Yes No No Real Questions Yes No No Explanation Yes No No PDF VCE Yes No No Free VCE Simulator Yes No No Instant Download Yes No No