Microsoft 70-467 Exam Questions Instant Access

Question 1

You need to implement the aggregation designs for the cube.

What should you do?

AUse the Usage-Based Optimization Wizard.

BUse the Aggregation Design Wizard.

CPartition the cube by month.

DImplement cache warming in SSAS via an SSIS package.

Answer : B

Question 2

You need to choose the appropriate key to use when designing a dimension table based on the Customer table.

What should you do?

AUse a surrogate key.

BUse a natural key.

CUse the CustomerNumber column as the key.

DConcatenate the CustomerName and CustomerNumber columns and use the concatenated string as the key.

EUse the CustomerName column as the key.

Answer : A

Question 3

You deploy a PowerPivot workbook to a PowerPivot Gallery in a Microsoft SharePoint site. Workbook data comes from two different sources: Source A and Source B.

The workbook contains five tables with a total of 20 million rows from Source A, and three small lookup tables from Source B. Data from Source A is updated throughout the day. Data from Source B is updated at 3:00 P.M.

You have the following requirements:

Refresh the PowerPivot workbook with updated lookup data as soon as possible.

Minimize load on the source systems and the SharePoint environment during business hours.

Minimize user involvement in the data refresh process.

You enable automatic data refresh for the workbook.

You need to configure a data refresh schedule for the workbook that meets the requirements.

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

ASet the default schedule to refresh outside of business hours. Do not manually refresh the workbook.

BSet the default schedule to refresh outside of business hours. Manually refresh the workbook at 9:00 A.M. every day.

CSet the default schedule to refresh at 9:00 A.M. every day. Create a separate schedule for Source A's connection to refresh outside of business hours.

DSet the default schedule to refresh at 3:00 P.M. every day. Do not create individual source schedules.

ESet the default schedule to refresh at 9:00 A.M. every day. Do not create individual source schedules.

Answer : C

Question 4

You are designing a complex report that displays sales metrics for a customer hierarchy. The customer hierarchy has six levels and contains approximately 1 million members.

You have the following requirements:

Allow users to easily display and navigate data.

Minimize report processing time.

Simplify the report design and maintenance processes.

You need to design a report that meets the requirements.

How should you design the report? (More than one answer choice may achieve the goal. Select the BEST answer.)

ARetrieve all customers and use drilldown groupings to show or hide hierarchy levels.

BAdd a report part for each hierarchy level.

CAdd a subreport for each hierarchy level.

DDisplay only the children of the current hierarchy level and drill through to the selected member.

Answer : D

Question 5

A group of report writers develop reports. The report writers currently use Report Builder 1.0 and report models. The report writers are not trained on SQL Server query writing. Their report assignments come from various departments within the organization.

The company is upgrading to SQL Server 2012 with SQL Server Reporting Services (SSRS) in native mode. After the upgrade, reports will retrieve data from a large new data warehouse that will reside on an instance of the SQL Server 2012 Database Engine. Reports for each department will retrieve data from only a subset of the data warehouse tables.

You are designing the data access strategy. You have the following requirements:

Ensure that report writers can create only reports that display data which they have database permissions to view.

Minimize the effort required to update connection strings for all reports developed by the report writers.

Minimize the number of fields visible in a specific report for report writers who work for multiple departments.

You need to design the data access strategy.

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

ACreate report models by using SQL Server Data Tools (SSDT). Include data sources and data source views for each department's data requirements. Configure each data source to use integrated security.

BCreate one shared data source that uses integrated security. Create an embedded dataset for each report.

CCreate one shared data source that uses integrated security. Create one shared dataset that includes all tables required by the report writers.

DCreate one shared data source that uses integrated security. Create a shared dataset for each department's data requirements.

Answer : D

Question 6

You are creating a Multidimensional Expressions (MDX) calculation for Projected Revenue in a cube.

For Product A, Projected Revenue is defined as 150 percent of the Total Sales of the product. For all other products, Projected Revenue is defined as 110 percent of the Total Sales of the product.

You need to calculate the Projected Revenue as efficiently as possible.

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

AOption A

BOption B

COption C

DOption D

Answer : C

Question 7

You need to restrict access to data in the tables in the data warehouse.

What should you do?

AConfigure column-level permissions.

BConfigure database roles.

CCreate views and grant permissions to the views.

DConfigure application roles.

Answer : C

Microsoft Designing Business Intelligence Solutions with Microsoft SQL Server 2012 70-467 Exam Questions