Q1. - (Topic 9) 

You are designing a strategy for an enterprise reporting solution that uses SQL Server Reporting Services (SSRS). 

Many of the SSRS reports will use common utilities and functions, including the following: 

.. 

Report utility functions and business logic in code Standardized report formatting properties such as fonts and colors for report branding 

Formatting may change and new functions may be added as the reporting solution evolves. 

You need to create a strategy for deploying the formatting and code across the entire enterprise reporting solution. You must also ensure that reports can be easily updated to reflect formatting and function changes. 

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

A. Create a report as a template. Apply standardized formatting to the template. Store code in the Code section of the template. 

B. Build a web service that retrieves formatting properties and runs code. Call the web service through a report dataset. 

C. Store the formatting properties and code in database objects. Use stored procedures to populate a default value for report parameters and map each parameter to a corresponding formatting property. 

D. Create an assembly that contains formatting properties and code. Deploy the assembly on the Reporting Server and reference the assembly from each report. 

View Answer

Q2. - (Topic 9) 

You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables. 

You have the following requirements: 

. The deployment model must support changing the content of connection strings by 

using parameters at execution time. 

. The deployment model must automatically log events to the SSISDB database. 

. Maximize performance at execution time. 

You need to design a solution that meets the requirements. 

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

A. Use a project deployment model. Modify connection manager properties to use project parameters. 

B. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments. 

C. Use a package deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package. 

D. Use a project deployment model. Modify connection manager properties to use package parameters. 

View Answer

Q3. - (Topic 9) 

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. - (Topic 9) 

... 

A SQL Server Analysis Services (SSAS) cube contains a large measure group. The fact table supporting the measure group is loaded with new data throughout the day. 

You have the following requirements: 

Ensure that the cube displays current data as quickly as possible. 

Maximize availability of the cube. 

Maximize query performance for all aggregation levels. 

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

Which partitioning strategy should you choose? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Create one partition for the current day that uses multidimensional OLAP (MOLAP) with proactive caching as a storage mode. 

B. Create one partition for the current month that uses hybrid OLAP (HOLAP) as a storage mode. 

C. Create one partition for the current day that uses relational OLAP (ROLAP) as a storage mode. 

D. Create one partition for the current day that uses multidimensional OLAP (MOLAP) as a storage mode. Process the partition each night. 

View Answer

Q5. - (Topic 8) 

You are deploying the Research model. 

You need to ensure that the data contained in the model can be refreshed. 

What should you do? 

A. Import MarketResearch.xlsx to a new tabular database on the Tabular instance. 

B. Assign the Tabular instance service account permissions to the MarketResearch.xlsx network location. 

C. Create a SQL Server Integration Services (SSIS) package that imports data from MarketResearch.xlsx nightly. Load the data to CUBE1. 

D. Upload MarketResearch.xlsx to Library1. 

View Answer

Q6. HOTSPOT - (Topic 9) 

... 

You are designing a SQL Server Integration Services (SSIS) package configuration strategy. 

The package configuration must meet the following requirements: Include multiple properties in a configuration. Force packages to load all settings in the configuration. Support Encrypting File System (EFS) formats. You need to select the appropriate configuration. 

Which configuration type should you use? 

To answer, select the appropriate option from the drop-down list in the dialog box. 

View Answer

Q7. - (Topic 9) 

You are designing a partitioning strategy for a large fact table in a Manufacturing data warehouse. 

Tens of millions of new inventory fact records are loaded into the data warehouse weekly, outside of business hours. Most queries against the database are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level. 

A. Partition the inventory fact table by month, and compress each partition. 

B. Partition the inventory fact table by day, and compress each partition. 

C. Partition the inventory fact table by year. 

D. Partition the inventory fact table by week. 

View Answer

Q8. - (Topic 10) 

You are conducting a design review of a multidimensional project. 

In the Customer Geography dimension, all non-key attributes relate directly to the key attribute. 

The underlying data of the Customer Geography dimension supports relationships between attributes. 

You need to increase query and dimension processing performance. 

What should you do? 

A. For the dimension attributes of the Customer Geography dimension, define appropriate attribute relationships. 

B. For the Customer Geography dimension, set the ProcessingPriority property to 1. 

C. For the Customer Geography dimension, set the ProcessingMode property to LazyAggregations. 

D. For the dimension attributes of the Customer Geography dimension, set the GroupingBehavior property to EncourageGrouping. 

View Answer

Q9. - (Topic 10) 

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

In the data warehouse, a table named Employee Security defines a relationship between a salesperson's name, logon ID, and assigned sales territory. 

You need to ensure that each salesperson has access to data from only the sales territory assigned to that salesperson. You also need to minimize the development effort. 

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

A. Create a separate tabular project for each sales territory. Grant each salesperson access to the corresponding tabular model of the assigned sales territory. 

B. Create a new Active Directory Domain Services (AD DS) security group and add each salesperson as a member. Then create a new role with Read permission. Add the group as a member to the new role. 

C. Create a new role with Read permission and then add each salesperson's logon as a member to the role. 

D. Add the Employee Security table to the model, define the relationships, and then implement dynamic security by using row filters. Grant each salesperson access to the model. 

View Answer

Q10. - (Topic 9) 

You administer a SQL Server Reporting Services (SSRS) instance in native mode. 

You need to assign a predefined role that meets the following requirements: 

. Members of the role must be able to update shared data sources. 

. Members of the role must not be able to consume reports or manage 

subscriptions. 

. The role must provide only the minimum permissions required. 

Which role should you assign? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. The Content Manager role 

B. The Read and Process role 

C. The Publisher role 

D. The Browser role 

View Answer