Limited Time Discount Offer 30% Off - Ends in 02:00:00

×

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

Purchase Individually

  • 70-467 Questions & Answers

    Premium File

    176 Questions & Answers
    Last Update: May 30, 2020

    $57.13
    $39.99
  • 70-467 Training Course

    Training Course

    153 Lectures

    $28.56
    $19.99

Download Free Microsoft 70-467 Exam Questions

Questions & Answers for Microsoft 70-467

Showing 1-15 of 176 Questions

Question #1 - Topic 1

You need to create the Sales Reporting shared SSRS data source.
Which SSRS data connection type should you use?

A. OData

B. Microsoft SQL Server

C. ODBC

D. OLE DB

Question #2 - Topic 1

You need to grant appropriate permissions to the SSISOwners SQL Server login.
What should you do?

A. Map the login to the SSISDB database. Assign the user to the ssis_admin role.

B. Map the login to the msdb database. Assign the user to the db_owner role.

C. Map the login to the msdb database. Assign the user to the db_ssisadmin role.

D. Map the login to the SSISDB database. Assign the user to the db_ssisadmin role.

E. Map the login to the SSISDB database. Assign the user to the db_owner role.

F. Map the login to the msdb database. Assign the user to the ssis_admin role.

Question #3 - Topic 1

You need to select an appropriate tool for creating the Regional Sales report.
Which tools or tools should you use? (Each correct answer presents a complete solution.
Choose all that apply.)

A. Power View, using a table configured for vertical multiples

B. Excel 2010, using a PivotTable

C. Report Builder, using a Matrix

D. Power View, using a table configured for horizontal multiples

Question #4 - Topic 1

You need to configure data refresh for the Manufacturing Performance PowerPivot
workbook.
What should you do? (Each correct answer presents part of the solution. Choose ail that
apply.)

A. Configure the PowerPivot Data Refresh Timer Job to run every 60 minutes.

B. Restore the PowerPivot workbook to an SSAS instance in tabular mode.

C. Script a process command and configure a SQL Server Agent job to execute the command every 60 minutes.

D. Restore the PowerPivot workbook to an SSAS instance in PowerPivot for SharePoint mode.

Question #5 - Topic 1

You need to configure the Scenario attribute to ensure that business users appropriately
query the Sales Plan measure.
What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)

A. Set the AttributeHierarchyVisible property to False.

B. Set the IsAggregatable property to False.

C. Set the Usage property to Parent.

D. set the DefaultMember property to the Forecast member.

E. Set the AttributeHierarchyEnabled property to False.

F. Set the RootMemberIf property to ParentIsMissing.

Explanation:
The Sales measure group is based on the FactSales table. The Sales Plan measure group
is based on the FactSalesPlan table. The Sales Plan measure group has been configured
with a multidimensional OLAP (MOLAP) writeback partition. Both measure groups use
MOLAP partitions, and aggregation designs are assigned to all partitions.

Question #6 - Topic 1

You need to create the calculation for SalespersonName.
What should you do? (Each correct answer presents a complete solution. Choose all that
apply.)

A. Create a computed column in the data warehouse's DimSalesperson table. Include the column in the Sales Reporting model's Salesperson table.

B. Modify the data warehouse's DimSalesperson table and add a new column. Use an UPDATE statement to populate the new column with values. Update the SSIS package developed to populate the data warehouse's DimSalesperson table to use a Derived Column transformation to produce the calculation.

C. Configure the Sales Reporting model's Salesperson table properties to be based on a query. Define a derived column in the query.

D. Add a calculated column to the Sales Reporting model's Salesperson table by using the Data Analysis Expressions (DAX) language CONCATENATE function.

E. Create a view in the data warehouse that defines a derived column based on the DimSalesperson table. Base the Sales Reporting model's Salesperson table on the view. Include the column in the Sales Reporting model's Salesperson table.

F. Add a calculated column to the Sales Reporting model's Salesperson table by using the Data Analysis Expressions (DAX) language ADDCOLUMNS function.

Question #7 - Topic 1

You need to select an appropriate tool for creating the Regional Sales report.
Which tool or tools should you use? (Each correct answer presents a complete solution.
Choose all that apply.)

A. Excel 2010, using the CUBE functions

B. Power View, using a Matrix

C. Excel 2010, using a PivotTable

D. Report Builder, using a Matrix

Explanation:
B: Working with a matrix in Power View
A matrix is a type of visualization that is similar to a table in that it is made up of rows and
columns. However, a matrix can be collapsed and expanded by rows and/or columns. If it
contains a hierarchy, you can drill down/drill up.
C: Using an Excel Pivot Table, connecting to the Cube, will give you the option to drill down
the cube. Using conditional formatting you can highlight specific value ranges.
D: Matrices provide functionality similar to crosstabs and pivot tables. At run time, as the
report data and data regions are combined, a matrix grows horizontally and vertically on
the page. Values in matrix cells display aggregate values scoped to the intersection of the
row and column groups to which the cell belongs. You can format the rows and columns to
highlight the data you want to emphasize. You can also include drilldown toggles that
initially hide detail data; the user can then click the toggles to display more or less detail as
needed.
* From scenario:
Management has requested a new report named Regional Sales. This report must be
based on the Sales cube and must allow users to filter by a specific year and present a grid
with every region on the columns and the Products hierarchy on the rows. The hierarchy
must initially be collapsed and allow the user to drill down through the hierarchy to analyze
sales. Additionally, sales values that are less than $5000 must be highlighted in red.

Question #8 - Topic 1

You need to fix the PowerPivot data refresh problem by using the least amount of
administrative effort.
What should you do?

A. Use the PowerPivot Configuration Tool and select the Upgrade Features, Services, Applications and Solutions option.

B. Use the PowerPivot Configuration Tool and select the Configure or Repair PowerPivot for SharePoint option.

C. Reinstall SSAS in PowerPivot for SharePoint mode by using the SQL Server 2012 installation media.

D. In SharePoint Central Administration, create a target application and configure the PowerPivot service application settings to use the target application.

Question #9 - Topic 1

You need to configure package execution logging to meet the requirements.
What should you do?

A. Configure logging in each ETL package to log the OnError, OnInformation, and Diagnostic events.

B. Set the SSIS catalog's Server-wide Default Logging Level property to Performance.

C. Set the SSIS catalog's Server-wide Default Logging Level property to Basic.

D. Set the SSIS catalog's Server-wide Default Logging Level property to Verbose.

E. Configure logging in each ETL package to log the OnError, OnPreExecute, and OnPostExecute events.

Question #10 - Topic 1

You need to configure the attribute relationship types for the Salesperson dimension.
Which configuration should you use?
To answer, drag the appropriate pair of attributes and attribute relationships from the list to
the correct location or locations in the answer area. (Answer choices may be used once,
more than once, or not all.)

Question #11 - Topic 1

You need to define the trend calculation for the sales performance KPI.
Which KPI trend MDX expression should you use?

A. CASE WHEN [Sales Variance %] < ([Sales Variance %], [Date].[Calendar].PrevMember) THEN -1 WHEN [Sales Variance %] = ([Sales Variance %], [Date].[Calendar].PrevMember) THEN 0 ELSE 1 END

B. IIF([Sales Variance %3 < ([Sales Variance %], [Date].[Calendar].PrevMember), 1, 0)

C. IIF([Sales Variance %] < ([Sales Variance %], [Date].[Calendar].PrevMember), 0, 1)

D. CASE WHEN [Sales Variance %] < ([Sales Variance %], [Date].[Calendar].PrevMember) THEN 1 WHEN [Sales Variance %] = ([Sales Variance %], [Date].[Calendar].PrevMember) THEN 0 ELSE -1 END

Question #12 - Topic 1

You need to extend the schema design to store the product dimension data.
Which design should you use?
To answer, drag the appropriate table or tables to the correct location or locations in the
answer area. (Fill from left to right. Answer choices may be used once, more than once, or
not all.)

Question #13 - Topic 1

You need to complete the following UPDATE statement to initialize the budget sales values
for 2012.
Which MDX weight value expression should you use?
To answer, drag the appropriate weight value expression to the answer area.


Topic 2, Contoso, Ltd Case A
General Background
You are the SQL Server Administrator for Contoso, Ltd. You have been tasked with
upgrading all existing SQL Server instances to SQL Server 2012.
Technical Background
The corporate environment includes an Active Directory Domain Services (AD DS) domain
named contoso.com. The forest and domain levels are set to Windows Server 2008. All
default containers are used for computer and user accounts. All servers run Windows
Server 2008 R2 Service Pack 1 (SP1). All client computers run Windows 7 Professional
SP1. All servers and client computers are members of the contoso.com domain.
The current SQL Server environment consists of a single instance failover cluster of SQL
Server 2008 R2 Analysis Services (SSAS). The virtual server name of the cluster is
Node1 and Node2. Node1 is currently the
active node. In anticipation of the upgrade, the prerequisites and shared components have
been upgraded on both nodes of the cluster, and each node was rebooted during a weekly
maintenance window.
A single-server deployment of SQL Server 2008 R2 Reporting Services (SSRS) in native
mode is installed on a server named SSRS01. The Reporting Server service is configured
to use a domain service account. SSRS01 hosts reports that access the SSAS databases
for sales data as well as modeling data for the Research team. SSRS01 contains 94
reports used by the organization. These reports are generated continually during business
hours. Users report that report subscriptions on SSRS01 are not being delivered. You run
the reports on demand from Report Manager and find that the reports render as expected.
A new server named SSRS02 has been joined to the domain, SSRS02 will host a single-
server deployment of SSRS so that snapshots of critical reports are accessible during the
upgrade.
The server configuration is shown in the exhibit. (Click the Exhibit button.)
[Microsoft-70-4

Question #14 - Topic 2

You need to re-establish subscriptions on SSRS01.
What should you do?

A. Start the SQL Server Agent on SSRS01.

B. Restore the ReportServer database.

C. Restore the ReportServerTempDB database.

D. Use the SQL Server Configuration Manager to reset the SQL Service account credentials.

Question #15 - Topic 2

You need to perform the pre-upgrade database backup operation by using SQL Server
Management Studio (SSMS).
How should you configure the backup options?

A. Select the Apply compression check box. Select the Encrypt backup file check box and supply a password.

B. Clear the Apply compression check box. Select the Encrypt backup file check box and supply a password.

C. Clear the Apply compression check box. Clear the Encrypt backup file check box.

D. Select the Apply compression check box. Clear the Encrypt backup file check box.

Try Our Special 30% Discount Offer for
Premium 70-467 VCE File

  • #

    Verified by experts

70-467 Questions & Answers

70-467 Premium File

  • Real Questions
  • Last Update: May 30, 2020
  • 100% Accurate Answers
  • Fast Exam Update

$39.99

$57.13

SPRING SALE: 30% DISCOUNT
This is ONE TIME OFFER

Exam-Labs Special Discount
You save
30%

Enter Your Email Address to Receive Your 30% Discount Code

SPRING SALE: 30% DISCOUNT

Exam-Labs Special Discount
You save
30%

Use Discount Code:

A confirmation link was sent to your e-mail.

Please check your mailbox for a message from support@exam-labs.com and follow the directions.

Download Free Demo of VCE Exam Simulator

Experience Avanset VCE Exam Simulator for yourself.

Simply submit your e-mail address below to get started with our interactive software demo of your free trial.

  • Realistic exam simulation and exam editor with preview functions
  • Whole exam in a single file with several different question types
  • Customizable exam-taking mode & detailed score reports