Q1. - (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. 

View Answer

Q2. DRAG DROP - (Topic 10) 

You install a SQL Server Analysis Services (SSAS) instance in tabular mode on a server. 

While processing a very large tabular model, you receive an out-of-memory error. You identify that the amount of physical memory in the server is insufficient. Additional physical memory cannot be installed in the server. 

You need to configure the server to allow paging to disk by using the operating system page file (pagefile.sys). 

Which four 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

Q3. - (Topic 9) 

You are modifying a star schema data mart that feeds order data from a SQL Azure database into a SQL Server Analysis Services (SSAS) cube. The data mart contains two large tables that include flags and indicators for some orders. There are 100 different flag columns, each with 10 different indicator values. Some flags reuse indicators. The tables both have a granularity that matches the fact table. 

You have the following requirements: 

. Allow users to slice data by all flags and indicators. . Modify the date dimension table to include a surrogate key of a numeric data type and add the surrogate key to the fact table. . Use the most efficient design strategy for cube processing and queries. 

You need to modify the schema. 

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

A. Define the surrogate key as an INT data type. Combine the distinct flag/indicator combinations into a single dimension. 

B. Define the surrogate key as an INT data type. Create a single fact dimension in each table for its flags and indicators. 

C. Define the surrogate key as a BIGINT data type. Combine the distinct flag/indicator combinations into a single dimension. 

D. Define the surrogate key as a BIGINT data type. Create a single fact dimension in each table for its flags and indicators. 

View Answer

Q4. DRAG DROP - (Topic 10) 

You are developing a SQL Server Analysis Services (SSAS) multidimensional project that is configured to source data from a SQL Azure database. The cube is processed each night at midnight. 

The largest partition in the cube takes 12 hours to process, and users are unable to access the cube until noon. The partition must be available for querying as soon as possible after processing commences. 

You need to ensure that the partition is available for querying as soon as possible, without using source data to satisfy the query. 

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

Q5. - (Topic 9) 

You are designing a partitioning strategy for a SQL Server Analysis Services (SSAS) cube. 

New data is loaded in real-time into the data warehouse that feeds the cube. Between 10 million and 15 million rows of data are loaded into the main fact table each day from a Microsoft Azure SQL Database. 

You have the following requirements: . Maximize cube query performance during business hours. 

. Ensure that data is available in the cube as soon as possible after it is loaded into the data warehouse. 

You need to design a partitioning strategy that meets the requirements. 

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

A. Partition the cube by day for history, using hybrid OLAP (HOLAP) storage mode. Create a daily partition that uses multidimensional OLAP (MOLAP) storage mode during the day. Reprocess the partition incrementally during the day. 

B. Partition the cube by week for history. Create a daily partition that uses multidimensional OLAP (MOLAP) storage mode. Process the partition periodically to add new data. 

C. Partition the cube by day for history, using multidimensional OLAP (MOLAP) storage mode. Create a daily partition that uses proactive caching during the day. Reprocess the partition in full MOLAP storage mode at night 

D. Partition the cube by day for history, using multidimensional OLAP (MOLAP) storage mode. Create a daily partition that uses relational OLAP (ROLAP) storage mode during the day. Reprocess the partition in full MOLAP storage mode at night. 

View Answer

Q6. - (Topic 10) 

You are developing a SQL Server Analysis Services (SSAS) tabular database. To maximize performance, queries must be resolved only by using cache. 

You need to configure the appropriate query mode. 

Which query mode should you select? 

A. DirectQuery with In-Memory 

B. In-Memory 

C. In-Memory with DirectQuery 

D. DirectQuery 

View Answer

Q7. - (Topic 10) 

You are designing a SQL Server Reporting Services (SSRS) report for a bank. The bank has Automated Teller Machines (ATMs) in several regions. ATM operational data is stored in a SQL Azure database. 

The report must use a map to display the location and status of the ATMs as shown in the following exhibit. (Click the Exhibit button.) 

You need to ensure that the report displays only a user selected map region. 

Which source of spatial data should you use for the map? 

A. SQL Server spatial query 

B. Bing Maps layer 

C. ESRI shape file 

D. Map gallery 

View Answer

Q8. - (Topic 9) 

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. 

View Answer

Q9. DRAG DROP - (Topic 10) 

You are planning the installation of PowerPivot for SharePoint that will be used by your company's sales and marketing team. 

You install SharePoint Server 2010 Enterprise Edition with Service Pack 1. 

You need to install the PowerPivot for SharePoint instance. Then you need to configure the Default Account username used to provision shared services in the SharePoint farm. 

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

You need to modify the Sales cube to support the planning requirements. 

Which SSA5 feature should you use? 

A. At KPI 

B. A translation 

C. A writeback partition 

D. A perspective 

View Answer