Implementing Data Models and Reports with Microsoft SQL Server (070-466) Free Practice Test

Question 1

You are developing a SQL Server Analysis Services (SSAS) multidimensional project.
A fact table is related to a dimension table named DimScenario by a column named ScenarioKey.
The dimension table contains three rows for the following scenarios:
* Actual
* Budget Q1
* Budget Q3
You need to create a dimension to allow users to view and compare data by scenario.
What should you do?

A. Add a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension.

B. Add a measure that uses the Count aggregate function to an existing measure group.

C. Use the Business Intelligence Wizard to define dimension intelligence.

D. Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a many-to-many relationship to link the dimension to the measure group.

E. Use role playing dimensions.

F. Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a regular relationship between the dimension and measure group.

G. Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a many-to-many relationship to link the dimension to the measure group.

H. Add a measure group that has one measure that uses the DistinctCount aggregate function.

I. Add a measure that uses the DistinctCount aggregate function to an existing measure group.

J. Create a dimension with one attribute hierarchy. Set the ValueColumn property, set the IsAggregatable property to False, and then set the DefaultMember property. Configure the cube dimension so that it does not have a relationship with the measure group. Add a calculated measure that uses the MemberValue attribute property.

K. Create a dimension. Then add a cube dimension and link it several times to the measure group.

L. Add a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions.

M. Create a dimension. Create regular relationships between the cube dimension and the measure group. Configure the relationships to use different dimension attributes.

N. Create a new named calculation in the data source view to calculate a rolling sum. Add a measure that uses the Max aggregate function based on the named calculation.

O. Create several dimensions. Add each dimension to the cube.

Correct Answer: F

Question 2

A multidimensional SQL Server Analysis Services (SSAS) database will be tested next week.
During the test period, users will access the database for 30 days. Multidimensional Expressions (MDX) queries generated during the test period must represent the variety of queries that will be used in the production environment.
After testing completes, you need to implement aggregations for every partition in the solution while minimizing development effort. You need to ensure that the aggregations are optimal.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. During the test period, run SQL Server Profiler for 10 minutes every day and record all queries executed in those 10 minutes. Use the Aggregation Design Wizard to design the aggregations.

B. During the test period, identify long-running queries by using SQL Server Profiler. Use those queries to design aggregations by using the DesignAggregations command in XML for Analysis (XMLA).

C. Develop a SQL Server Integration Services (SSIS) package by using a Script task and then use Analysis Management Objects (AMO) to design the aggregations.

D. Set up a query log and record all user queries during the test period. After completion of the test, use the Usage Based Optimization Wizard to define aggregations for each partition.

Correct Answer: C

Question 3

You have a SQL Server 2012 database named DB1. You have a backup device named Device1.
You discover that the log file for the database is full. You need to ensure that DB1 can complete transactions.
The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?

A. BACKUP LCG DB1 TO Device1 WITH COPY_ONLY

B. BACKUP LOG D31 TO Device1 WITH TRUNCATE ONLY

C. BACKUP LOG DB1 TO Device1 WITH NCRECCVERY

D. BACKUP LOG DB1 TO Device1

Correct Answer: D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 4

You are developing a SQL Server Analysis Services (SSAS) tabular project.
A model contains tables and columns that must not be visible to the user. The columns and tables cannot be removed because they are used in calculations. The calculations are used to calculate the budget and forecast for the current quarter.
You need to hide the tables and columns.
What should you do?

A. After adding the budget calculations to the model, in the Properties window for the applicable tables and columns, set the Visible property to True.

B. Before adding the forecast calculations to the model, right-click the applicable tables and columns and select the Hide from Client Tools option.

C. Before adding the forecast calculations to the model, right-click the applicable tables and columns and select the Hide option.

D. After adding the budget calculations to the model, in the Properties window for the applicable tables and columns, set the Enabled property to False.

Correct Answer: B

Question 5

You are managing a SQL Server Reporting Services (SSRS) instance.
A website must pass credentials to the local security authority for Reporting Services.
You need to configure Reporting Services to issue a challenge/response when a connection is made without credentials.
Which authentication type should you configure in the RSReportServer.config file?

A. RSWindowsKerberos and RSWindowsNegotiate

B. RSWindowsKerberos and RSWindowsNTLM

C. RSWindowsBasic

D. RSWindowsKerberos only

Correct Answer: C

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 6

You need to create the KPI in the AdhocReports project.
What should you do?

A. Create a measure by using the SUM([OrderQuantity]) expression. Then use the CREATE KPI CURRENTCUBE statement to define the KPI and target value.

B. Create a KPI based on the OrderQuantity column and then set the target value.

C. Create a measure by using the COUNT([OrderQuantity]) expression and create a KPI based on the measure. Then set the target value.

D. Create a measure by using the SUM([OrderQuantity]) expression and create a KPI based on the measure. Then set the target value.

Correct Answer: A

Question 7

You manage a SQL Server Reporting Services (SSRS) instance in SharePoint integrated mode.
You identify a problem with the operations performed by the Report Server Web service.
You need to view the Report Server trace logs. In which directory should you view the files?

A. %ProgramFiles%\Common Files\Microsoft Shared\Web Server Extensions\14\Web Services\ReportServer\LogFiles

B. %ProgramFiles%\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting
Services\LogFiles

C. %ProgramFiles%\Common Files\ReportServer\LogFiles

D. %ProgramFiles%\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting
Services\LogFiles

Correct Answer: B

Question 8

DRAG DROP
You are developing a SQL Server Analysis Services (SSAS) multidimensional project.
You identify that a dimension, which has a large number of attributes, is issuing a separate (SELECT DISTINCT) query for each attribute even though they all come from a single database table. The table does not have a large number of rows.
You need to configure the processing of the dimension to issue only a single SQL query to the underlying database, and processing must continue if any errors are encountered.
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:

Question 9

You are managing a SQL Server Reporting Services (SSRS) instance in native mode. A system role named Developer Support is present on the server.
Members of the Developer Support role cannot modify the report execution timeout period.
You need to enable members of the Developer Support role to modify the report execution timeout period.
Which task should you add to the Developer Support role?

A. Execute report definitions

B. Manage jobs

C. Manage report server properties

D. Manage shared schedules

Correct Answer: C

Question 10

You need to create the AD Sales measure.
Which aggregation function should you use?

A. Average

B. AverageOfChildren

C. Sum

D. ByAccount

Correct Answer: B

Question 11

You need to ascertain why Marc did not receive his reports.
What should you do?

A. Use the Windows Event Viewer to search the Application log for errors.

B. Search the registry for errors.

C. Search the ReportServerService_<timestamp>.log file for errors.

D. Use SQL Server Management Studio to search the SQL Server logs for errors.

Correct Answer: C

Welcome to TestSimulate

Microsoft Implementing Data Models and Reports with Microsoft SQL Server (070-466) Free Practice Test