Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You are reviewing the design of an existing fact table named factSales, which is loaded from a SQL Azure database by a SQL Server Integration Services (SSIS) package each day. The fact table has approximately 1 billion rows and is dimensioned by product, sales date, and sales time of day.
The database administrator is concerned about the growth of the database. Users report 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. Partition the table by product type.
B. Create a view over the fact table to aggregate sales by month.
C. Change the granularity of the fact table to month.
D. Create an indexed view over the fact table to aggregate sales by month.
Answer: C
Q2. DRAG DROP
You are building a fact table in a data warehouse.
The table must have a columnstore index. The table cannot be partitioned. You need to design the fact table and load it with data.
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.)
Answer:
Q3. 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'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.)
A. Partition the destination fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.
B. Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.
C. Partition the destination fact table by customer. Use partition switching both to remove old data and to load new data into each partition.
D. 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.
Answer: B
Q4. You are designing a SQL Server Integration Services (SS1S) package that uploads a file to a table named Orders in a SQL Azure database.
The company's auditing policies have the following requirements:
•An entry must be written to a dedicated SQL Server log table named OrderLog.
•The entry must be written as soon as the file upload task completes.
You need to meet the company's policy requirements. Which event handler should you use?
A. OnProgress
B. Onlnformation
C. OnPostExecute
D. OnComplete
Answer: C
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms140223.aspx
Q5. You are developing a SQL Server Integration Services (SSIS) package that imports data from a relational database to a data warehouse.
You are importing data from a relational table named Projects. The table has change data capture enabled on all columns.
You need to process only the most recent values from rows that have been inserted or updated since the previous execution of the package.
Which query should you use as the data source?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/bb522511.aspx
Reference: http://msdn.microsoft.com/en-us/library/bb510627.aspx
Reference: http://msdn.microsoft.com/en-us/library/cc645937.aspx
Q6. DRAG DROP
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:
•The fact table must populate a column named TotalCost that computes the sum of the price and tax columns.
•Before 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.)
Answer:
Explanation:
1. OLEDB Source
2. Conditional Split
3. Derived Column
4. OLEDB Destination
Q7. DRAG DROP
A new SQL Server Integration Services (SSIS) project is deployed to the SSIS catalog. To troubleshoot some data issues, you must output the data streaming through several
data flows into text files for further analysis. You have the list of data flow package paths and identification strings of the various task components that must be analyzed.
You need to create these output files with the least amount of administrative and development effort.
Which three stored procedures should you execute in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Answer:
Explanation:
Ref: http://msdn.microsoft.com/en-gb/library/hh230989(v=sql.110).aspx
Q8. DRAG DROP
You administer a Microsoft SQL Server database. You want to import data from a text file to the database.
You need to ensure that the following requirements are met:
✑ Data import is performed by using a stored procedure.
✑ Data is loaded as a unit and is minimally logged.
Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Each data import command or recovery model may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)
Answer:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms162802.aspx http://msdn.microsoft.com/en-us/library/ms188365.aspx http://msdn.microsoft.com/en-us/library/ms175937.aspx http://msdn.microsoft.com/en-us/library/aa337544.aspx
Q9. You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. All packages in the project must log custom messages.
You need to produce reports that combine the custom log messages with the system- generated log messages. What should you do?
A. Use an event handler for OnError for the package.
B. Use an event handler for OnError for each data flow task.
C. Use an event handler for OnTaskFailed for the package.
D. View the job history for the SQL Server Agent job.
E. View the All Messages subsection of the All Executions report for the package.
F. Store the System::SourceID variable in the custom log table.
G. Store the System::ServerExecutionID variable in the custom log table.
H. Store the System::ExecutionInstanceGUID variable in the custom log table.
I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow,
K. Deploy the project by using dtutil.exe with the /COPY DTS option.
L. Deploy the project by using dtutil.exe with the /COPY SQL option.
M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
P. Create a SQL Server Agent job to execute the
SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.
Q. Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.
R. Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.
Answer: G
Q10. 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:
•[ID]
•[QuoteDate]
•[Open]
•[Close]
•[High]
•[Low]
•[Volume]
Each of the following queries must be able to use a columnstore index:
•SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.
•SELECT AVG([High] - [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.
•SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.
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?
A. Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].
B. Create three coiumnstore indexes: One containing [QuoteDate] and [Close]
One containing [QuoteDate], [High], and [Low] One containing [QuoteDate] and [Volume]
C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].
D. Create two columnstore indexes:
One containing [ID], [QuoteDate], [Volume], and [Close] One containing [ID], [QuoteDate], [High], and [Low]
Answer: C
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/gg492088.aspx Reference: http://msdn.microsoft.com/en-us/library/gg492153.aspx
Q11. CORRECT TEXT
You administer a Microsoft SQL Server 2021 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.)
Answer:
Tick the “Unicode” checkbox. Set the Format option to “Fixed Width”
Untick the “Column names in the first data row” checkbox.
Explanation:
Each record is of the same length = Fixed Width. The file supports international characters = Unicode.
The date follows the U.S. date format = Locale (already configured).
The first row of the file contains the first row of data = Uncheck “Column names in the first data row”
Q12. DRAG DROP
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.)
Answer:
Q13. DRAG DROP
You are designing a SQL Server Integration Services (SSIS) package. The package moves order-related data to a staging table named Order. Every night the staging data is truncated and then all the recent orders from the online store database are inserted into the staging table.
Your package must meet the following requirements:
•If the truncate operation fails, the package execution must stop and report an error.
•If the Data Flow task that moves the data to the staging table fails, the entire refresh operation must be rolled back.
•For auditing purposes, a log entry must be entered in a SQL log table after each execution of the Data Flow task.
The TransactionOption property for the package is set to Required. You need to design the package to meet the requirements.
How should you design the control flow for the package? (To answer, drag the appropriate setting from the list of settings to the correct location or locations in the answer area.)
Answer:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://msdn.microsoft.com/en-us/library/ms141144.aspx
Q14. You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes the dimUser and dimDistrict dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse; and the dimDistrict table contains information about sales districts.
The system is accessed by users from certain districts, 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 districts. Some users must be permitted to see sales data from multiple districts.
What should you do?
A. Add a district column to the dimUser table.
B. Partition the factSales table on the district column.
C. Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables.
D. For each district, create a view of the factSales table that includes a WHERE clause for the district.
Answer: C
Q15. You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The package and all three Execute SQL tasks have their TransactionOption property set to Supported.
You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes.
What should you do?
A. Move the three Execute SQL tasks into a Sequence container.
B. Move the three Execute SQL tasks into a Foreach Loop container.
C. Change the TransactionOption property of all three Execute SQL tasks to Required.
D. Change the TransactionOption property of the package to Required.
Answer: D
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms137749.aspx http://msdn.microsoft.com/en-
us/library/microsoft.sqlserver.dts.runtime.dtstransactionoption.aspx
Q16. DRAG DROP
You are editing a SQL Server Integration Services (SSIS) package that contains a task with a sensitive property.
You need to create a project parameter and configure it so that its value is encrypted when it is deployed to the SSIS catalog.
Which three steps 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.)
Answer:
Q17. CORRECT TEXT
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:
•Usage of the tempdb database should not be impacted.
•Concurrency should be maximized, while only reading committed transactions.
•If 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