You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly
Changing Dimension (SCD). You need to add a task or component to the package that allows you
to implement the SCD logic. What should you use?
A. a Data Conversion component
B. an Execute SQL task that executes a MERGE statement on the database
C. a Merge component
D. an Expression task
Correct Answer: C
You are designing a data warehouse for a fresh food distribution business that stores sates by individual product. It stores sales targets by product category. Products are classified into subcategories and categories. Each product is included in only a single product subcategory, and each subcategory is included in only a single category. The data warehouse will be a data source for an Analysis Services cube. The data warehouse contains two fact tables:
factSales, used to record daily sales by product
factProductTarget, used to record the monthly sales targets by product category
Reports must be developed against the warehouse that reports product sales by product, category and subcategory, and product sales targets. You need to design the product dimension. The solution should use as few tables as possible while supporting all the requirements. What should you do?
A. Create two product tables, dimProduct and dimProductCategory. Connect factSales to
dimProduct and factProductTarget to dimProductCategory with foreign key constraints.
Direct the cube developer to use key granularity attributes.
B. Create one product table, dimProduct, which contains product detail, category, and
subcategory columns. Connect factSales to dimProduct with a foreign key constraint. Direct
the cube developer to use a non-key granularity attribute for factProductTarget.
C. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory,
and a fourth bridge table that joins products to their appropriate category and subcategory
table records with foreign key constraints. Direct the cube developer to use key granularity
D. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory.
Connect factSales to all three product tables and connect factProductTarget to
dimProductCategory with foreign key constraints. Direct the cube developer to use key
Correct Answer: B
You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day.
The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table. You need to reduce the report processing time and minimize the growth of the database. What should you do?
A. Create an indexed view over the fact table to sum orderTotal by month.
B. Create a view over the fact table to sum orderTotal by month.
C. Change the granularity of the fact table to month.
D. Partition the fact table by productKey.
Correct Answer: B
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:
Each Environment defines a single Environment Variable named ConnectionString of type string.
The value of each variable consists of the connection string to the test or production data warehouses. You need 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.
Select and Place:
You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on SQL Azure. One of the columns in the data source is named ProductCode. Some of the data to be loaded will reference products that need special processing logic in the data flow. You need to enable separate processing streams for a subset of rows based on the source product code. Which data flow transformation should you use?
B. Source Assistant
C. Script Task
D. Conditional Split
Correct Answer: D
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?
A. Change the Exhaustive property to True.
B. Decrease the maximum number of matches per lookup.
C. Change the similarity threshold to 0.85.
D. Increase the maximum number of matches per lookup.
Correct Answer: B
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 apply.)
A. Enable CLR Integration.
B. Enable FILESTREAM with Full Access.
C. Enable the Resource Governor.
D. Change the recovery mode of the msdb database to FULL.
A. E Change the Server-wide Default Logging Level in SSISDB to Verbose.
E. Start the SQL Server Browser service.
F. Start the SQL Server Agent service.
Correct Answer: AD
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?
A. SQL Server Configuration Manager
B. SQL Server Agent
C. SSIS Upgrade Wizard in SQL Server Management Studio
D. SQL Server DTExecUI utility (dtexecui.exe)
Correct Answer: C
A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project
Deployment Model. Sometimes the package is started as part of a multistep SQL job. At other
times, the package is started manually by a database administrator by using the Object Explorer
in SQL Server Management Studio. You need to identify the authenticated user responsible for
starting the package each time it executes. How can you find this information?
A. In the SSISDB.[catalog], query the .[executions] view.
B. In the SSISDB.[catalog] , query the [event_messages] view.
C. In SQL Server Management Studio, view the SQL Agent Job History.
D. In SQL Server Management Studio, view the SQL Agent Error Log.
E. In SQL Server Management Studio, view the SQL Server Log.
Correct Answer: A
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.