Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 9)
You are designing an extract, transform, load (ETL) process for loading data from a SQL
....
Azure 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.
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 fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.
B. Partition the fact table by customer. Use partition switching both to remove old data and to load new data into each partition.
C. Partition the fact table by date. Use partition switching and staging tables both to remove old data and to load new data.
D. Partition the fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table.
Answer: C
Q2. DRAG DROP - (Topic 9)
You are designing a SQL Server Reporting Services (SSRS) solution.
A report project must access multiple SQL Server databases. Each database is on a different instance. The databases have identical schema and security configurations.
You have the following requirements:
. The report must support subscriptions.
. Users must be able to select the host when running the report.
What should you do?
To answer, drag the appropriate phrase or phrases from the list to the correct location or locations in the answer area. (Answer choices may be used once, more than once, or not all.)
Answer:
Q3. - (Topic 6)
You need to select and configure a tool for the monitoring solution. What should you choose?
A. Performance Monitor configured with the MSAS11:Storage Engine Query counter
B. Performance Monitor configured with the MSAS11:Processing counter
C. SQL Server Profiler configured with the Query Processing: Query Subcube event
D. SQL Server Profiler configured with the Queries Events: Query Begin event
Answer: A
Q4. - (Topic 8)
You need to deploy a solution for the planned self-service reports that will be used by the sales department managers.
What is the best solution you should deploy? More than one answer choice may achieve the goal. Select the BEST answer.
A. A filter
B. A KPI
C. A calculated column
D. A measure
Answer: B
Q5. - (Topic 9)
You are defining a named set by using Multidimensional Expressions (MDX) in a sales cube.
The cube includes a Product dimension that contains a Category hierarchy and a Color attribute hierarchy.
You need to return only the blue products in the Category hierarchy.
Which set should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q6. - (Topic 9)
A group of report writers develop reports. The report writers currently use Report Builder
1.0 and report models. The report writers are not trained on SQL Server query writing. Their report assignments come from various departments within the organization.
The company is upgrading to SQL Server 2012 with SQL Server Reporting Services (SSRS) in native mode. After the upgrade, reports will retrieve data from a large new data warehouse that will reside on an instance of the SQL Server 2012 Database Engine. Reports for each department will retrieve data from only a subset of the data warehouse tables.
You are designing the data access strategy. You have the following requirements:
. Ensure that report writers can create only reports that display data which they have database permissions to view. . Minimize the effort required to update connection strings for all reports developed by the report writers. . Minimize the number of fields visible in a specific report for report writers who work for multiple departments.
You need to design the data access strategy.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create report models by using SQL Server Data Tools (SSDT). Include data sources and data source views for each department's data requirements. Configure each data source to use integrated security.
B. Create one shared data source that uses integrated security. Create an embedded dataset for each report.
C. Create one shared data source that uses integrated security. Create one shared dataset that includes all tables required by the report writers.
D. Create one shared data source that uses integrated security. Create a shared dataset for each department's data requirements.
Answer: D
Q7. - (Topic 1)
You need to create the hierarchy in the AdhocReports project in time for the next production release cycle.
What should you do?
A. Multi-select all of the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.
B. Use the RELATED() function to consolidate the columns in the DimSalesTerritory table, multi-select the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.
C. Use the RELATEDTABLEQ function to consolidate the tables, multi-select the columns in the hierarchy, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.
D. Use the RELATED() function to consolidate the columns in the DimEmployee table, multi-select the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.
Answer: D
Q8. HOTSPOT - (Topic 9)
You administer a SQL Server Analysis Services (SSAS) cube.
You plan to optimize the cube partitions by using usage-based optimization.
You need to configure the SSAS instance.
Which properties should you configure? To answer, select the appropriate properties in the
dialog box in the answer area.
Answer:
Q9. HOTSPOT - (Topic 9)
A company maintains separate environments for development, test, and production. The company uses the project deployment model for SQL Server Integration Services (SSIS) deployments.
You create an SSIS project to perform a daily refresh of the data warehouse and data models. The project has five packages.
You need to configure the project to ensure that the development, test, and production teams can run each package without manually adding server-specific information.
Which program should you use?
To answer, select the appropriate program in the answer area.
Answer:
Q10. - (Topic 3)
You need to select an appropriate tool for creating the Regional Sales report.
Which tools or tools should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. Power View, using a table configured for vertical multiples
B. Excel 2010, using a PivotTable
C. Report Builder, using a Matrix
D. Power View, using a table configured for horizontal multiples
Answer: B,C
Q11. - (Topic 10)
You are developing a new SQL Server Reporting Services (SSRS) report in SQL Server
Data Tools (SSDT). This report has a table named Table1 and a textbox named Textbox1.
Table1 is initially visible but the user must be able to choose when to hide it.
You need to develop the report to meet the requirement.
What should you do? (More than one answer choice may achieve the goal. Select the
BEST answer.)
A. Add a parameter to the report so users can choose the display state of Table1.
B. For the properties of Table1, configure the Display can be toggled by this report item option to use Textbox1.
C. Configure Textbox1 to drill through to rerun the report to toggle the display of Table1.
D. For the properties of Table1, configure the Display can be toggled by this report item option to use Table1.
Answer: B
Q12. - (Topic 10)
You are working with a SQL Server Reporting Services (SSRS) instance in native mode. An item role named Reports Writer is present on the server.
The Reports Writer role cannot view and modify report caching parameters.
You need to ensure that the Reports Writer role can view and modify report caching parameters.
What should you do?
A. Add the Manage individual subscriptions task to the Reports Writer role.
B. Add the Manage report history task to the Reports Writer role.
C. Add the View data sources task to the Reports Writer role.
D. Add the Manage all subscriptions task to the Reports Writer role.
Answer: B
Q13. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) multidimensional database.
The underlying data source does not have a time dimension table.
You need to implement a time dimension.
What should you do?
A. Use the SQL Server Data Tools Dimension Wizard and generate a time table in the data source.
B. Create a time dimension by using the Define dimension intelligence option in the Business Intelligence Wizard.
C. Create a time dimension by using the Define time intelligence option in the Business Intelligence Wizard.
D. Add an existing SSAS database time dimension as a cube dimension.
Answer: A
Q14. DRAG DROP - (Topic 10)
You manage a SQL Server Reporting Services (SSRS) instance running in native mode.
You are troubleshooting a performance problem and need to know which reports are frequently executed. You discover that the report server execution logs are empty, despite significant report activity.
You need to ensure that the server is configured for report execution logging.
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:
Q15. DRAG DROP - (Topic 9)
A company has a data warehouse that is rapidly increasing in size.
You plan to improve query performance by partitioning a very large fact table.
You need to identify the columns that are good partitioning candidates and the range that
each partition should include.
Which component should you add to the package?
To answer, drag the appropriate component from the list of items to the answer area.
Answer:
Q16. - (Topic 4)
You need to roll back the compatibility level of the Research database.
What should you do?
A. Restore a backup of the previous version of the database.
B. Use an ALTER DATABASE statement to set the compatibility option.
C. Change the CompatibilityLevel property in the XMLA script, and then execute the script.
D. In SQL Server Management Studio (SSMS), change the compatibility level in the database properties.
Answer: A