TS:MS SQL Server 2008.Business Intelligence Dev and Maintenan (070-448) Free Practice Test

Question 1

You are designing a SQL Server 2008 Integration Services (SSIS) package. The package includes a
Data Flow task.
You need to modify the package to write all rows that generate errors to a text file.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

A. Use a Flat File Destination component.

B. Use the SSIS log provider for the SQL Profiler.

C. Add an Error output to the Data Flow component

D. Use a Raw File Destination component.

E. Use the SSIS log provider for the text file.

Correct Answer: A,C

Question 2

You maintain a SQL Server 2008 Analysis Services (SSAS) database.
When you process the Analysis Services database, you discover that a Duplicate Key error occurs.
You need to modify the ErrorConfiguration property so that the error condition is reported and processing
continues.
Which object should you modify?

A. The partition

B. The dimension

C. The cube

D. The measure group

Correct Answer: B

Question 3

You are an ETL developer for a mortgage company. In your application you have two tables names Vendor and Loan.
Vendor
VendorID VendorName
1 VendorA
2 VendorB
3 VendorC
4 VendorD
5 VendorE
Loan
LoanNumber VendorID LoanAmount
I-100 1 120000
I-200 1 130000
I-300 2 150000
I-400 3 150000
I-500 4 160000
I-600 4 170000
I-700 5 180000
You need to build a SQL Server 2008 Integration Service (SSIS) package that meets the following requirements: The package should be able to generate the Excel files dynamically. Each file should have the vendor name as a prefix and the date as the suffix (e.g., VendorA_DDMMYYYY.xlsx). All the vendor names should be added to a variable and loop through the variable to generate and load
separate files for each vendor dynamically. Which Type of variable should you create?

A. Object

B. String

C. Int64

D. Table

E. Int32

Correct Answer: A

Question 4

You are developing a SQL Server 2008 Integration Services (SSIS) project in Business Intelligence
Development Studio.
You configure the project properties as shown in the following image.
You need to create a deployment manifest for all packages in the project.
What should you do?

A. Build the Integration Services project.

B. View the code of each Data Transformation Services (DTS) package. Copy the first <DTS:Property> xml node to a file with a .SSISDeploymentManifest file extension.

C. Modify the AllowConfigurationChanges property of the project.

D. Copy each package. Rename each copy with a .SSISDeploymentManifest file extension.

Correct Answer: A

Question 5

You design a SQL Server 2008 Integration Services (SSIS) package that contains several data flows.
You need to monitor the data that passes through each path in the data flow by using the Business
Intelligence Development Studio. You also need to ensure that data flow logic is retained.
What should you do?

A. Use breakpoints in the Script component.

B. Use the Audit transformation.

C. Use the Data Profiling task.

D. Use the Data Viewer tool.

Correct Answer: D

Question 6

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You develop a SQL Server 2008 Reporting services (SSRS) instance of a report model.
The organization requires that users of a report be able to dynamically change the server environment.
You need to create a parameter that gives the user the option to choose the server before report execution.
What should you do?

A. Create a dynamic data source from a dataset by using the IN operator.

B. Configure the Hidden Visibility property.

C. Configure the Internal Visibility option for the parameter.

D. Create a dynamic dataset that utilizes the WHERE clause to filter by the parameter.

E. Create a distinct dataset that includes all possible values and attach it to the parameter.

F. Create a dynamic data source from a dataset that includes a list of distinct servers.

G. Create a dynamic dataset that includes a list of the different users.

H. Develop a report for each environment.

Correct Answer: F

Question 7

You plan to deploy SQL Server 2008 Reporting Services (SSRS) on a SQL Server 2008 failover cluster.
Internet Information Services (IIS) is installed on both nodes of the cluster. The IIS is virtualized on the
Network Load Balancing (NLB) cluster.
You need to deploy SSRS in a fault-tolerant scale-out deployment.
What should you do?

A. Install SSRS on both cluster nodes and cluster the SSRS service by using a new resource group.

B. Install SSRS on both cluster nodes and cluster the SSRS service by using the SQL Server resource group.

C. Install SSRS on both cluster nodes to use separate ReportServer databases.

D. Install SSRS on both cluster nodes to use the same ReportServer database.

Correct Answer: D

Question 8

You administer a SQL Server 2008 Analysis Services (SSAS) database. A sales manager is responsible for the sales of bikes in the Northeast region. You plan to grant the necessary permissions to the sales manager to access the database. You have two roles named Northeast Region and Bikes. The schemas of the two roles are as shown in the following tablE.Role Allowed set
Northeast Region
{[Sales Territory].[Sales Territory Region].&[Northeast]}
Bikes
{[Product].[Product Category].[Product Category].&[Bikes]}
The Visual Totals property is set to True for both roles.
You need to ensure that the sales manager can view only the members in the Product dimension that relate
to the Bikes category in the Northeast region.
What should you do?

A. Add the manager to the Northeast Region role.

B. Add the manager to the Bikes role.

C. Add the manager to a new role that has the following two components: {[Sales Territory].[Sales Territory Region].AllMembers} as the denied set {[Sales Territory].[Sales Territory Region].&[Northeast]} as the allowed set. {[Product].[Product Category].[Product Category].AllMembers} as the denied set. {[Product].[Product Category].[Product Category].&[Bikes]} as the allowed set.

D. Add the manager to a new role that has the following two components: {[Sales Territory].[Sales Territory Region].&[Northeast]} as the allowed set. {[Product].[Product Category].[Product Category].&[Bikes]} as the allowed set.

Correct Answer: D

Question 9

You administer a SQL Server 2008 Analysis Services (SSAS) instancE.You plan to troubleshoot query
performance by using a Dynamic Management View (DMV).
You need to identify query sessions that execute a query for more than 30,000 ms.
Which DMV should you use?

A. Discover_Connections

B. Discover_Traces

C. Discover_Sessions

D. Discover_Commands

Correct Answer: D

Question 10

You design a report by using SQL Server 2008 Reporting Services (SSRS).
Detail information is displayed each time the users render the report.
You need to ensure that the following requirements are met:
Summary information is displayed when the report is initially rendered.
Detail information is displayed only when the users click a column header.
What should you do?

A. On the column that contains detail information, set the hidden property to FalsE. Set the Visibility can be toggled by another report item property to True.

B. On the column that contains detail information, set the hidden property to FalsE. Set the Visibility can be toggled by another report item property to False.

C. On the column that contains detail information, set the hidden property to TruE. Set the Visibility can be toggled by another report item property to True.

D. On the column that contains detail information, set the hidden property to TruE. Set the Visibility can be toggled by another report item property to False.

Correct Answer: C

Welcome to TestSimulate

Microsoft TS:MS SQL Server 2008.Business Intelligence Dev and Maintenan (070-448) Free Practice Test