Q1. A company runs SQL Server Database Engine and SQL Server Reporting Services (SSRS) in native mode. Reports are based on data that is cached in multiple shared datasets. Source data is purged each day at midnight for regulatory compliance purposes. The shared datasets may continue to cache data that should not be used in reports. Shared report schedules are often paused during nightly server maintenance windows. 

Reports must not return purged data. 

You need to create a fully automated solution to ensure that reports do not deliver purged data. 

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

A. Create a shared schedule. Configure the datasets to expire on the shared schedule. 

B. Write a script that calls the flushcache method to clear individual items from the SSRS cache. Create a SQL Server Agent job that runs rs.exe with the script as an input file, and schedule the job to run every day after the purge process completes. 

C. Create a SQL Server Agent job that uses a Transact-SQL (T-SQL) step to delete the data from the dbo.ExecutionCache table in the ReportServerTempDB database. Schedule the job to run every day after the purge process completes. 

D. Republish the cached datasets by using SQL Server Data Tools. 

View Answer

Q2. You need to select the appropriate storage settings for the cube. 

Which settings should you choose? 

A. Relational OLAP (ROLAP) with proactive caching enabled 

B. Multidimensional OLAP (MOLAP) with proactive caching enabled and a rebuild interval of 24 hours 

C. Hybrid OLAP (HOLAP) with proactive caching disabled 

D. Hybrid OLAP (HOLAP) with proactive caching enabled 

View Answer

Q3. An existing cube dimension that has 30 attribute hierarchies is performing very poorly. You have the following requirements: 

. Implement drill-down browsing. 

. Reduce the number of attribute hierarchies but ensure that the information contained within them is available to users on demand. 

. Optimize performance. 

You need to redesign the cube dimension to meet the requirements. 

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

A. set the AggregateFunction property to Sum on all measures. Use the SCOPE statement in a Multidimensional Expressions (MDX) calculation to tune the aggregation types. 

B. Set the AttributeHierarchyOptimizedState property to FullyOptimized on the attribute hierarchies. 

C. Create user-defined hierarchies. For the attributes sourced by the levels of the user-defined hierarchies, set the RelationshipType property to Rigid. Run incremental processing. 

D. Remove as many attribute hierarchies as possible from the dimension. Reintroduce the information in the attribute hierarchies as properties. Implement natural hierarchies and set the AttributeHierarchyVisible property to False for attributes used as levels in the natural hierarchies. 

View Answer

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

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

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

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

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

View Answer

Q5. You need to create the factCustomerContact table. 

Which type of table should you create? 

A. A fact table with a non-additive measure 

B. A factless fact table 

C. A periodic snapshot fact table 

D. A fact table with an additive measure 

View Answer

Q6. You need to implement the Customer Sales and Manufacturing data models. 

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

A. Use the Database Synchronization Wizard to upgrade the database to tabular mode. 

B. Use SQL Server Integration Services (SSIS) to copy the database design to the SSAS instance, and specify tabular mode as the destination. 

C. Use SQL Server Data Tools (SSDT) to redevelop and deploy the projects. 

D. Use the current SSAS instance. 

E. Install a new instance of SSAS in tabular mode. 

View Answer

Q7. You need to re-establish subscriptions on SSRS01. 

What should you do? 

A. Manually failover the active node. 

B. Install prerequisites and upgrade shared components on Node1 and Node2. 

C. Generate a SQL Server 2012 configuration file by running the SQL Server Setup executable. 

D. Upgrade Node1 by using the SQL Server 2012 Upgrade wizard. 

View Answer

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

View Answer

Q9. You need to configure a parameter for the database connection string. 

What should you do? 

A. Use a required package parameter. 

B. Use a required project parameter. 

C. Use a package configuration. 

D. Use a global variable. 

View Answer

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