Microsoft 70-467

Designing Business Intelligence Solutions with Microsoft SQL Server 2012

(Page 1 out of 12)
Showing 15 of 176 Questions
Exam Version: 11.0
Question No : 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

Answer : B



Question No : 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.

Answer : D



Question No : 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

Answer : B,C



Question No : 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.

Answer : A



Question No : 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.

Answer : C,D

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 No : 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.

Answer : B,E



Question No : 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

Answer : B,C,D

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 No : 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.

Answer : B



Question No : 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.

Answer : B



Question No : 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.)


Answer :



Question No : 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

Answer : A



Question No : 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.)


Answer :



Question No : 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.


Answer :

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 No : 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.

Answer : A



Question No : 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.

Answer : D



(Page 1 out of 12)
Showing of 176 Questions
Exam Version: 11.0