070-467 by Microsoft Valid Free Exam Practice Test

Question 1

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

A. Add a subreport for each hierarchy level. B. Add a report part for each hierarchy level. C. Retrieve all customers and use drilldown groupings to show or hide hierarchy levels. D. Display only the children of the current hierarchy level and drill through to the selected member.

Correct Answer: D

Question 2

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 may achieve the goal. Select the BEST answer.)

A. Create a standard subscription for each subscription schedule. B. Create a data-driven subscription for each record in the schedule table. C. Create a standard subscription for each record in the table. D. Create one data-driven subscription. Schedule the subscription to frequently retrieve user preferences.

Correct Answer: D

Question 3

You need to restrict access to data in the tables in the data warehouse.
What should you do?

A. Configure application roles. B. Configure column-level permissions. C. Create views and grant permissions to the views. D. Configure database roles.

Correct Answer: C

Question 4

You are the administrator of a SQL Server Integration Services (SSIS) catalog. You have access to the original password that was used to create the SSIS catalog.
A full database backup of the SSISDB database on the production server is made each day. The server used for disaster recovery has an operational SSIS catalog.
The production server that hosts the SSISDB database fails.
Sensitive data that is encrypted in the SSISDB database must not be lost.
You need to restore the production SSIS catalog to the disaster recovery server.
Which three steps should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Correct Answer:

Explanation
Box 1:

Box 2:

Box 3:

Question 5

A SQL Server Analysis Services (SSAS) cube named Sales includes a dimension named Date that defines a hierarchy named Calendar. The Calendar hierarchy consists of Year, Quarter, and Month levels. The Type properties of the Date dimension and its attributes are appropriately configured to mark the dimension as a time dimension.
You are creating a Multidimensional Expressions (MDX) query that will return sales for each month of 2011 and a three-month moving average of sales (labeled 3MMA), as shown in the following diagram.

You need to complete the MDX query.
Which set expression should you use? To answer, drag the appropriate expression to the answer area.

Correct Answer:

Explanation

Question 6

You need to implement the aggregation designs for the cube.
What should you do?

A. Create relational indexes on the source tables. B. Use the CREATE CACHE statement. C. Use the Aggregation Design Wizard. D. Use the Usage-Based Optimization Wizard.

Correct Answer: C

Question 7

You have a business intelligence (BI) infrastructure that contains three servers. The servers are configured as shown in the following table.

You need to recommend a health monitoring solution for the BI infrastructure.
The solution must meet the following requirements:
* Monitor the status of the Usage Data Collection feature.
* Monitor the number of end-users accessing the solution.
* Monitor the amount of cache used when the users query data.
Which health monitoring solution should you recommend using on each server? To answer, drag the appropriate monitoring solutions to the correct servers. Each monitoring solution may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Correct Answer:

Explanation
Box 1: PowerPivot server health rules
Box 2:Dynamic management views
Box 3: PowerPivot server health rules

Question 8

You are developing a SQL Server Analysis Services (SSAS) cube. A dimension named Customer is based on the DimCustomer table. A subset of the data source view is shown in the following graphic.

You need to relate the Customer dimension to the AccountBalance measure group.
Which relationship type should you choose? To answer, select the appropriate option from the drop-down list in the dialog box.

Correct Answer:

Explanation

Question 9

You develop a SQL Server Integration Services (SSIS) project named Project1 by using SQL Server Data Tools (SSDT). Project1 contains a package named Package1.
You add a project parameter named EnvironmentText to Project1.
In SQL Server Management Studio (SSMS), you create the SSIS catalog.
You have the following requirements:
* Configure Package1 so it can run within either a development environment or a test environment.
* Pass the value of an SSIS environment variable to the EnvironmentText project parameter.
* The value of the environment variable must be different for each SSIS environment.
You need to deploy the SSIS project and configure the SSIS environment.
Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Correct Answer:

Explanation
Box 1:

Box 2:

Box 3:

Question 10

You are developing a SQL Server Analysis Services (SSAS) cube. A dimension named Category is based on the DimCategory table.
A subset of the data source view is shown in the following graphic.

You need to relate the Category dimension to the Sales measure group.
Which relationship type should you choose?
To answer, select the appropriate option from the drop-down list in the dialog box.

Correct Answer:

Explanation

Note:
Referenced Relationship
Link a dimension to a fact table indirectly through a dimension that is linked directly through a primary key-foreign key relationship.

Question 11

You have a SQL Server Reporting Services (SSRS) 2008 instance integrated with an Internet-facing Microsoft SharePoint 2010 farm. PerformancePoint Services and Excel Services are installed and configured.
You need to upgrade to SSRS 2012 with the minimum downtime and hardware requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Perform an in-place upgrade of the SSRS environment. B. Install SSRS 2012 on a new SharePoint farm and migrate content to the new farm. C. Migrate SSRS content to new servers that run SSRS 2012. D. Migrate SSRS content to a separate instance of SSRS 2012 on the same server.

Correct Answer: C

Question 12

You need to design a data warehouse schema to support a multidimensional cube. The fact table will contain multiple columns representing order dates, shipping dates, and billing dates. The cube will contain a measure group based on the fact table.
What is the best design to achieve the goal? More than one answer choice may achieve the goal. Select the BEST answer.

A. In the database, create a time table for each date column in the fact table.
From the data source view, join each date column in the fact table to the corresponding time table.
Create dimensions for each time table. B. In the database, create a time table that uses an integer primary key and a datetime column.
Create a dimension based on the time table.
From the Cube Designer, use the Dimension Usage tab to define a relationship between the measure group date columns and the datetime column of the time dimension. C. In the database, create a time table containing a column that corresponds to each date column in the fact table.
From the data source view, join each date column in the fact table to the corresponding column in the time table.
Create a dimension for the time table. D. In the database, create a time table that uses an integer primary key and a datetime column.
Create a dimension based on the time table.
From the Cube Designer, use the Dimension Usage tab to define a relationship between the measure group date columns and the key column of the time dimension.

Correct Answer: C

Question 13

You are designing a SQL Server Reporting Services (SSRS) solution.
A report project must access multiple SQL Server databases. Each database is on a different instance. The databases have identical schema and security configurations.
You have the following requirements:
* The report must support subscriptions.
* Users must be able to select the host when running the report.
What should you do?
To answer, drag the appropriate phrase or phrases 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.)

Correct Answer:

Explanation

Note:
The report need a data source.
Through a report parameter the user can select among the available SQL Server instances.
This selection is used through an expression-based connection string.
Authentication is handled through stored credentials.

Question 14

You are designing aggregations for a SQL Server Analysis Services (SSAS) cube.
You need to ensure that every aggregation excludes the Category attribute.
Which option should you select?
To answer, select the appropriate option in the answer area.

Correct Answer:

Explanation

Question 15

You need to select the appropriate storage settings for the cube.
Which settings should you choose?

A. Hybrid OLAP (HOLAP) with proactive caching disabled B. Hybrid OLAP (HOLAP) with proactive caching enabled C. Multidimensional OLAP (MOLAP) with proactive caching enabled and a rebuild interval of 24 hours D. Relational OLAP (ROLAP) with proactive caching enabled

Correct Answer: A

Microsoft Designing Business Intelligence Solutions with Microsoft SQL Server - 070-467 Exam Practice Test