Implementing a Data Warehouse using SQL
Question No: 121 – (Topic 2)
You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure.
Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart.
You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented.
What should you do?
Create a conformed dimension for the geography dimension.
Implement change tracking.
Create a degenerate dimension for the geography dimension.
Create a Type 2 slowly changing dimension for the geography dimension.
Question No: 122 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: 123 CORRECT TEXT – (Topic 2)
You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database. The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)
The Insert New Orders Data Flow task must meet the following requirements:
鈥sage of the tempdb database should not be impacted.
鈥oncurrency should be maximized, while only reading committed transactions.
鈥f the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements.
How should you configure the transaction properties? (To answer, select the appropriate setting or settings in the answer area.)
Answer: IsolationLevel = ReadCommited. TransactionOption = Required References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms137749.aspx http://msdn.microsoft.com/en- us/library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx http://msdn.microsoft.com/en-us/library/ms173763.aspx
Question No: 124 DRAG DROP – (Topic 2)
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:
鈥he package must maximize parallel processing of the T-SQL statements.
鈥fter 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.)
Question No: 125 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.)
Question No: 126 DRAG DROP – (Topic 2)
You are developing a SQL Server Integration Services (SSIS) package that downloads data from a Windows Azure SQL Database database.
A stored procedure will be called in an Execute SQL task by using an ODBC connection. This stored procedure has only the @CustomerID parameter of type INT.
A project parameter named CustID will be mapped to the stored procedure parameter
You need to ensure that the value of the CustID parameter is passed to the @CustomerID
stored procedure parameter.
In the Parameter Mapping tab of the Execute SQL task editor, how should you configure the parameter? (To answer, drag the appropriate option or options to the correct location or locations in the answer area.)
Question No: 127 – (Topic 2)
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?
Create an indexed view over the fact table to sum orderTotal by month.
Create a view over the fact table to sum orderTotal by month.
Change the granularity of the fact table to month.
Partition the fact table by productKey.
Question No: 128 – (Topic 2)
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?
Decrease the maximum number of matches per lookup.
Change the similarity threshold to 0.55.
Change the Exhaustive property to True.
Increase the maximum number of matches per lookup.
Question No: 129 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: 130 – (Topic 2)
You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.
You have the following requirements:
鈥nsure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values.
鈥nsure that users can generate the exception report in an XML format.
鈥se the minimum development effort.
You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions.
Use Data Flow tasks to process the clean data.
Use a Data Profiling task. Read the exceptions in Data Profile Viewer.
Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task.
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|