Q1. - (Topic 2) 

You need to configure SSRS to meet the maximum number of snapshots requirement. 

What should you do? (Each answer presents a complete solution. Choose all that apply.) 

A. In SharePoint Central Administration, set the System Snapshot Limit option to 5. 

B. In Reporting Services Configuration Manager, set the System Snapshot Limit option to 

5. 

C. For each report, set the Limit number of snapshots option to 5. 

D. Use PowerShell to set the System Snapshot Limit option to 5. 

View Answer

Q2. - (Topic 9) 

You are defining a named set by using Multidimensional Expressions (MDX) in a sales cube. 

The cube includes a Customer dimension that contains a Geography hierarchy and a Gender attribute hierarchy. 

You need to return only the female customers in the Geography 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 

View Answer

Q3. - (Topic 9) 

You are redesigning a SQL Server Analysis Services (SSAS) database that contains a cube named Sales. Before the initial deployment of the cube, partition design was optimized for processing time. The cube currently includes five partitions named FactSalesl through FactSales5. Each partition contains from 1 million to 2 million rows. 

The FactSales5 partition contains the current year's information. The other partitions contain information from prior years; one year per partition. Currently, no aggregations are defined on the partitions. 

You remove fact rows that are more than five years old from the fact table in the data source and configure query logs on the SSAS server. 

Several queries and reports are running very slowly. 

You need to optimize the partition structure and design aggregations to improve query performance and minimize administrative overhead. 

What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Use the Aggregation Design Wizard to create aggregations for the current partitions. 

B. Combine all the partitions into a single partition. Use the Usage-Based Optimization Wizard to create aggregations. 

C. Combine all the partitions into a single partition. Use the Aggregation Design Wizard to create aggregations. 

D. Use the Usage-Based Optimization Wizard to create aggregations for the current partitions. 

View Answer

Q4. - (Topic 6) 

You need to improve the performance of data warehouse queries. 

What should you do? 

A. Create columnstore indexes. 

B. Create clustered indexes. 

C. Create indexed views. 

D. Create bitmap indexes. 

View Answer

Q5. DRAG DROP - (Topic 9) 

You have a business intelligence (BI) infrastructure. 

You create a SQL Server Integration Services (SSIS) project that contains the packages for the BI infrastructure. 

You need to automate the deployment of the SSIS project. The solution must ensure that the deployment can occur as part of an unattended Transact-SQL script. 

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. 

View Answer

Q6. - (Topic 10) 

You are modifying a SQL Server Analysis Services (SSAS) multidimensional database. 

You have identified a dimension that is no longer used by any cubes. 

You need to delete the dimension. 

What should you do? 

A. Write a T-SQL command to drop the dimension from the database. 

B. Script the deletion of the dimension as an XMLA command for execution against the production model. 

C. Use the SQL Server Analysis Services Migration Wizard. 

D. Deploy the project from the development environment by using SQL Server Management Studio. 

View Answer

Q7. - (Topic 9) 

... .. 

You are designing a fact table in a SQL Server database. 

The fact table must meet the following requirements: 

Include a columnstore index. 

Allow users to choose up to 10 dimension tables and up to five facts at one time. 

Maximize performance of queries that aggregate measures by using any of the 10 

dimensions. 

Support billions of rows. 

Use the most efficient design strategy. 

You need to design the fact table to meet the requirements. 

What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the dimensional key columns. 

B. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the measure columns. 

C. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on the dimensional key columns and the measure columns. 

D. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on only the measure columns. 

View Answer

Q8. - (Topic 4) 

You need to design the recovery strategy for SSRS01. 

What should the strategy include? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Re-create the SQL Server Agent jobs that are used to trigger schedules. 

B. Restore the ReportServer and ReportServerTempDB databases with recovery. 

C. Restore the ReportServer and ReportServerTempDB databases with no recovery. 

D. Restore the msdb database. 

E. Restore the Report Server encryption key. 

F. Restore the database encryption key. 

View Answer

Q9. DRAG DROP - (Topic 9) 

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.) 

View Answer

Q10. - (Topic 10) 

You are developing a SQL Server Analysis Services (SSAS) cube for the sales department at your company. 

The sales department requires the following set of metrics: 

Unique count of customers 

Unique count of products sold 

Sum of sales 

You need to ensure that the cube meets the requirements while optimizing query response time. 

What should you do? (Each answer presents a complete solution. Choose all that apply.) 

A. Use the Define Semiadditive Behavior page to enable semi-additive behavior. 

B. Use ROLAP storage for all partitions. 

C. Use the Define Semiadditive Behavior page to disable semi-additive behavior. 

D. Use the Distinct Count and Sum measure aggregation functions. 

E. Place the distinct count measures in separate measure groups. 

F. Create a single measure group containing all measures 

View Answer