Certification Exam Prep Questions For Designing Self-Service Business Intelligence and Big Data Solutions (MS-20467)

QuickStart is now offering assessment questions for Designing Self-Service Business Intelligence and Big Data Solutions (MS-20467). Whether you are deciding which exam to sign up for, or simply want to practice the materials necessary to complete certification for this course, we have provided a practice assessment to better aid in certification. 100% of the questions are real questions; from a recent version of the test you will take for Designing Self-Service Business Intelligence and Big Data Solutions (MS-20467).


Arrow
 

1

You are designing a subscription strategy for a SQL Server Reporting Services (SSRS) report. You have an application that populates a table with user-specific subscription schedules and report formats. You need to ensure that users can receive reports by email according to their preferences.

What should you do? (More than one answer choice can reach the goal. Choose the BEST answer.)

2

You are modifying a SQL Server Reporting Services (SSRS) report for a SQL Server Analysis Services (SSAS) cube. The report defines a report parameter of data type Date/Time with which users can filter the report by a single date. The parameter value cannot be directly used to filter the Multidimensional Expressions (MDX) query for the dataset. You need to ensure that the report displays data filtered by the user-entered value. You must achieve this goal by using the least amount of development effort.

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

3

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
Incorrect.
B. The Publisher role
Correct!
C. The Read and Process role
Incorrect.
D. The Browser role
Incorrect.
4

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

5

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:

• Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time.
• Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database.
• Maximize performance at execution time.
• Minimize development effort.

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

6

You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours. Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level. You need to partition the table to maximize the performance of queries.

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

7

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

8

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.

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

10

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

11

You are designing a multidimensional OLAP (MOLAP) cube. The MOLAP cube must meet the following requirements:

• Ensure that workloads for aggregation tuning can be automatically collected.
• Require the least amount of effort to perform manual aggregation tuning.
• Minimize impact on the performance of previously tuned queries.

You need to design a MOLAP cube that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

12

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

13

You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.

You have the following requirements:

• Ensure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values.
• Ensure that users can generate the exception report in an XML format.
• Use the minimum development effort.

You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

Practice Exam - Designing Self-Service Business Intelligence

$99.00

More Information:

  • Learning Style: On Demand
  • Learning Style: Practice Exam
  • Difficulty: Beginner
  • Course Duration: 1 Hour
  • Course Info: Download PDF
  • Certificate: See Sample

Need Training for 5 or More People?

Customized to your team's need:

  • Annual Subscriptions
  • Private Training
  • Flexible Pricing
  • Enterprise LMS
  • Dedicated Customer Success Manager

Outline

Reviews

Write Your Own Review
Only registered users can write reviews. Please Sign in or create an account

Hit button to validate captcha