Q1. HOTSPOT - (Topic 4) 

You are designing a SQL Server Analysis Services (SSAS) cube that contains two measure groups named Sales History and Current Sales. 

The Sales History measure group has the following characteristics: 

.... 

Data changes on a monthly basis. 

The measure group contains a very high data volume. 

Queries that use the measure group only reference aggregate data. 

The measure group includes SUM, MIN, MAX, and COUNT aggregate functions. 

The Current Sales measure group has the following characteristics: 

.... 

Data changes frequently. 

The measure group contains a low data volume. 

Queries that use the measure group often reference non-aggregate data. 

The measure group includes SUM, MIN, MAX, and COUNT aggregate functions. 

You need to select a storage mode for each measure group. The solution must meet the following requirements: 

.. 

For the Sales History measure group, query performance must be optimized over 

data latency. 

For the Current Sales measure group, data latency must be optimized over query 

performance. 

What should you do? To answer, select the appropriate storage mode for each measure 

group in the answer area. 

View Answer

Q2. DRAG DROP - (Topic 4) 

You are developing a SQL Server PowerPivot model to monitor the rate of production of defective products. 

The PowerPivot model contains many tables. The FactProduction table is configured as shown in the following diagram. 

The model requires a key performance indicator (KPI) named Defective Units to measure the rate of production of defective units. The rate of production of defective units is calculated by dividing the sum of the Defects column by the sum of the Units column. 

The KPI has the following requirements. 

. If the rate of production of defective products equals or exceeds 5% then the KPI must report a status of off target (red). . If the rate of production of defective products is less than 5% and equals or exceeds 2% then the KPI must report a status of slightly off target (yellow). . If the rate of production of defective products less than 2% then the KPI must report a status of on target (green). 

You need to develop the KPI to meet the requirements. 

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

Q3. - (Topic 4) 

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 Microsoft Azure SQL Database 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. Map gallery 

C. ESRI shape file 

D. Bing Maps layer 

View Answer

Q4. DRAG DROP - (Topic 4) 

You are developing a SQL Server Analysis Services (SSAS) cube. 

You need to reuse a measure group from a different database. 

In SQL Server Data Tools (SSDT), 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 4) 

You are planning to develop a SQL Server Analysis Services (SSAS) tabular project. The project will be deployed to a SSAS server that has 16 GB of RAM. 

The project will source data from a SQL Server database that contains a fact table named Sales. The fact table has more than 60 billion rows of data. 

You need to select an appropriate design to maximize query performance. 

Which data access strategy should you use? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Configure the database to use DirectQuery mode. Create a clustered index which includes all of the foreign key columns of the fact table. 

B. Configure the database to use In-Memory mode. Create a clustered index which includes all of the foreign key columns of the fact table. 

C. Configure the database to use In-Memory mode. Create a columnstore index on all the columns of the fact table. 

D. Configure the database to use DirectQuery mode. Create a columnstore index on all the columns of the fact table. 

View Answer

Q6. - (Topic 2) 

You need to modify the Sales Regions hierarchy to meet the reporting requirements. 

Which SSAS feature should you use? 

A. Calculation 

B. Translation 

C. Perspective 

D. Action 

View Answer

Q7. DRAG DROP - (Topic 4) 

You are developing a SQL Server Analysis Services (SSAS) multidimensional project that is configured to source data from a Microsoft Azure SQL Database 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

Q8. - (Topic 4) 

You are designing a SQL Server Reporting Services (SSRS) report that sources data from a Microsoft Azure SQL Database database. 

The report must display the value and status of a Key Performance Indicator (KPI). 

Which report item should you use? (Each answer presents a complete solution. Choose all that apply.) 

A. Indicator 

B. Data Bar 

C. Image 

D. Sparkline 

E. Gauge 

View Answer

Q9. - (Topic 4) 

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. Place the measures in a single measure group. 

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

C. Use the additive measure group functions. 

D. Use the semiadditive measure group functions. 

E. Use the Count and Sum measure aggregation functions. 

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

View Answer

Q10. DRAG DROP - (Topic 2) 

You need to optimize the Regions dimension. 

How should you configure the attribute relationships? (To answer, drag the appropriate attribute from the list of attributes to the correct location in the hierarchy relationship. Use only attributes that apply.) 

View Answer