Microsoft SQL Server 2008 - Business Intelligence Development and Maintenance (70-448) Dump

QUESTION 1

You are an ETL developer for a mortgage company. In your application you have to load data from a Microsoft Excel source and an OLE DB source.

The Excel source contains vender data and has approximately 1000 records.

The OLE source contains loan data and has approximately 1,000,000 records.

Sample vendor data (Excel source) is shown in the following table:

VendorID VendorName

1 VendorA

2 VendorB

3 VendorC

4 VendorD

5 VendorE

Sample loan data (OLE DB source) is shown in the following table:

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

The CIO asks you to generate an Excel file with the loan list information for each vendor. You build a SQL Server 2008 Integration Services (SSIS) package that can generate the Excel files dynamically. The Excel files should have the vendor name as the prefix and the date as the suffix (e.g., VendorA_DDMMYYYY.xlsx).

You get the error message shown in the exhibit. (Click the Exhibit button.)

You need to sort both sources in order to merge join the data.

What should you do?

A. Set the IsSorted property to True for both the OLE DB and Excel sources.

B. Use the sort transformation after the OLE DB source and set the IsSorted property to True for the Excel destination.

C. Sort the data in the select statement of the OLE DB source and use sort transformation after the Excel source.

D. Set the ISSorted property to True for the OLE DB, sort the data in the selct statement of the OLE DB source, and use sort transformation after the Excel source.

Correct Answer: D

    

QUESTION 2

You are an ETL developer for a mortgage company. Your application has 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 155000

I-500 4 160000

I-600 4 170000

I-700 5 180000

You need to build a SQL Server 2008 Integration Services (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 property in the Excel file destination connection manager should you set with a variable?

A. Provider

B. Name

C. Excel File Path

D. Initial Catalog

Correct Answer: C

    

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

B. String

C. Object

D. Int32

E. Int64

Correct Answer: C

    

QUESTION 4

You company network includes a SQL Server 2008 R2 database server with the SQL Server 2008 Analysis Services (SSAS) role enabled.

You design a cube named Materials with a measure group named MaterialsInventory. This group has a measure, QualityOnHand, which shows the total stock in inventory for the company by the time.

You need to determine the inventory at the end of the quarter. What should you do?

A. Define the QuantityOnHand measure as a semi-additive measure.

B. Define the QuantityOnHand measure as an additive measure.

C. Define the MaterialsInventory group as a semi-additive measure group.

D. Define the MaterialsInventory group as an additive measure group.

Correct Answer: A

    

QUESTION 5

You create a SQL Server 2008 Reporting Services (SSRS) report. This report includes a matrix containing three groups. The last group is Customer Name.

When a user clicks on a customer name, a new report should open with detailed customer information.

You need to enable this functionality. What option should you use?

A. Enable Interactive Sorting

B. Go to Bookmark

C. Go to Report

D. Go to URL

Correct Answer: C

    

QUESTION 6

You are working as an ETL developer for a payroll company. You configure a SQL Server 2008 Integration Services (SSIS) package to use checkpoints.

The SSIS package truncates the dbo.Employee_Attendance table, loads the new employees in the dbo.Employee table, and then loads dbo.Employee_Attendance for a particular month.

The control flow for the package is shown in the Control Flow exhibit. (Click the Exhibit button.)

The data flow for the package is shown in the Data Flow exhibit. (Click the Exhibit button.)

The package fails at the Derived Column transformation within the data flow task.

You need to determine where the package execution will begin after you correct the errors and run the package again.

Where does the package execution begin?

A. Truncate Employee_ Attendance table in the Execute SQL task

B. Derived Column Transformation in the data Flow task

C. Load Employee in the Data Flow task.

D. Load Emp_Attendance in the Data Flow task.

Correct Answer: C

QUESTION 7

You are an ETL developer. The CIO asks you to develop a SQL Server 2008 Integration Services (SSIS) package to load data from a source to a destination system. As part of the solution you have to use the script component as a source to load the data in the SQL Server table dbo.Employee.

The table dbo.Employee has the following fields:

Emp_ID int NOT NULL Emp_Name varchar (100) NOT NULL Emp_City varchar(50) NOT NULL Emp_State varchar(2) Emp_Zip varchar(10)

The Script Transformation Editor, which displays the column properties of the script component, is shown in the following exhibit. (Click the Exhibit button.)

You need to code the CreateNewOutputRows() method to assign values to the buffer.

Which code snippet should you use?

A. Public override void CreateNewOutputRows() ( OutoutBuffer0.AddRow(); OutoutBuffer0.EmpID = 1; OutoutBuffer0.EmpName = “Jeff Price”; OutoutBuffer0.EmpCity = “Any City”; OutoutBuffer0.EmpState = “TX”; OutoutBuffer0.EmpZip = “88001″; )

B. Public override void CreateNewOutputRows() ( InputBuffer.AddRow(); InputBuffer.EmpID = 1; InputBuffer.EmpName = “Jeff Price”; InputBuffer.EmpCity = “Any City”; InputBuffer.EmpState = “TX”; InputBuffer.EmpZip = “88001″; )

C. Public override void CreateNewOutputRows() ( EmployeeBuffer.AddRow(); EmployeeBuffer.EmpID = 1; EmployeeBuffer.EmpName = “Jeff Price”; EmployeeBuffer.EmpCity = “Any City”; EmployeeBuffer.EmpState = “TX”; EmployeeBuffer.EmpZip = “88001″; )

D. Public override void CreateNewOutputRows() ( OutoutBuffer.AddRow(); OutoutBuffer.EmpID = 1; OutoutBuffer.EmpName = “Jeff Price”; OutoutBuffer.EmpCity = “Any City”; OutoutBuffer.EmpState = “TX”; OutoutBuffer.EmpZip = “88001″; )

Correct Answer: C

    

QUESTION 8

You create a SQL Server 2008 Analysis Services (SSAS) solution.

Your solution contains a cube with a dimension named Factories. The dimension has a property names ProductionLines. This solution also includes data about the suers employed at each factory.

You need to provide a way to browse the data within the dimension.

What should you do?

A. Change the DataSourceFolder properties.

B. Create a URL action on the User feature.

C. Set “get values from a query” in the Report Parameters dialog.

D. Configure a drillthrough action on the Factories dimension.

Correct Answer: C

    

QUESTION 9

You have a SQL Server 2008 R2 Analysis Services (SSAS) solution.

You add a new Data Source View (DSV) to the solution. The DSV includes a SalesOrder table with columns for OrderTotal and CostTotal.

You need to derive the profit for each order without modifying the underlying data.

What should you do?

A. Add a Named Calculation to the SalesOrder table, using OrderTotal CostTotal as the expression.

B. Add a Named Query to the DSV, using Select *,OrderTotal CostTotal from SalesOrder as the expression.

C. Create a view, SalesProfit, as Select *, OrderTotal CostTotal as Profit from SalesOrder.

D. Add a Named Query to the DSV and then ass a Named Calculation to that query, using OrderTotal CostTotal as the expression.

E. Create a view, SalesProfit, as Select * from SalesOrder.

F. Add a Named Query to the DSV, using Select * from SalesOrder as the expression.

G. Add a Named Calculation to the SalesOrder table, using OrderTotal,CostTotal as the expression.

H. Add a Named Calculation to the SalesOrder table, using OrderTotal + CostTotal as the expression.

Correct Answer: AB

QUESTION 10

You create a SQL Server 2008 R2 Analysis Services (SSAS) solution.

You create a new data mining query for you instance.

Which tool should you use?

A. SQL Server Configuration Manager.

B. SQL Server Business Intelligence Development Studio

C. SQL Server Error and Usage Reporting.

D. SQL Server management Studio

Correct Answer: D

QUESTION 11

You create a SQL Server 2008 R2 Analysis Services (SSAS) solution.

You are configuring security settings for a new database role with the following requirements:

Only the West member can be viewed by the database role. All other existing members and any newly added members of the Region attribute will not be visible.

Members in attribute hierarchies that include a member of the Region attribute remain visible. The members in other attribute hierarchies remain visible.

The state of Washington is visible.

The aggregated totals for the ALL Level and Region attributes include only the values for members that are visible.

You need to ensure that the database role has these security settings.

What should you do? (Each correct answer presents part of the solution. Choose two.)

A. Set the Region attribute’s IsAllowed = False, DeniedSet = “Except(Region..Members,{West})”, ApplyDenied = False, and VisualTools = False.

B. Set the Region attribute’s IsAllowed = True, DeniedSet = “Except(Region..Members,{West})”, ApplyDenied = False, and VisualTools = True.

C. Set the State attribute’s IsAllowed = True, AllowedSet = “Except(Region..Members,{West})”, ApplyDenied = True, and VisualTools = False.

D. Set the State attribute’s IsAllowed = True, AllowedSet = “Except(Region..Members,{West} + {Washington})”, ApplyDenied = False, and VisualTools = False.

Correct Answer: BD

QUESTION 12

You create a SQL Server 2008 R2 Reporting Services (SSRS) solution. The organization requires a drilldown report that color coordinates the values of the student’s’ test scores. All average scores 70% or higher should have a green background. All lower average scores should have a red background.

You need to enable this functionality.

Which expression should you use?

A. =iif(avg(Fields!TestScore.Value) >= .70,”Green”,”Red”

B. =iif(Fields!TestScore.Value >= .70 THEN “Green” ELSE “Red”)

C. =iif(Fields!TestScore.Value >= .70,Green”,”Red”)

D. =iif(avg(Fields!TestScore.Value) >= .70 THEN “Green”,”Red”

Correct Answer: A

    

QUESTION 13

You create a SQL Server 2008 R2 Reporting Services (SSRS) solution.

The school district needs to be able to retrieve the median value of all tests scores.

You build a custom function named Median that accepts the test scores and returns the median test score.

You need to run this custom function.

Which expression should you use?

A. =Execute Function Median(Fields!TestScores)

B. =Code.Function.Median()

C. =Code.Median(Fields!TestScores)

D. =Execute.Median(Fields!TestScores)

Correct Answer: C

QUESTION 14

You are developing a SQL Server 2008 Reporting Services (SSRS) report which utilizes a matrix.

You define a group based on the Name column in the main report dataset. The format of the data in the column includes “FirstName  LastName.”

You need to group the matrix by FirstName.

Which expression should you use?

A. =First(Fields!Name.Valuse)

B. =Left(Fields!Name.Value,9)

C. =Left(Fields!Name.Value,Len(Fields!ClassName.Value)

D. =(Fields!Name.Value,InStr(Fields!ClassName.Value,”-”))

Correct Answer: D

QUESTION 15

You are developing a SQL Server 2008 Reporting Services (SSRS) report that will be run by managers only. Each manager’s EmpID will be passed into the report at runtime.

The managers want to be able to see a graphical display of sales grouped by product. Each store’s data should be displayed on a separate page.

You need to enable this functionality.

What should you use?

A. Image

B. Matrix

C. Table

D. List

Correct Answer: D

QUESTION 16

You are developing a SQL Server 2008 Reporting Service (SSRS) report for a DotNetNuke (DNN) application. The DNN application and SSRS are located on the same server. A custom module has been built in DNN to display the reports.

You need to enable the DNN application to connect to the report.

What should you do?

 A. Modify the RSReportServer.config file.

B. Deploy the report to the Report Server.

C. Use the SSRS Web service.

D. Modify the properties of ServerURL.

Correct Answer: B

    

QUESTION 17

You are working as reporting administrator for a product development company. The company uses SQL Server 2008 Reporting Services (SSRS) deployed in a native mode.

You need to deploy multiple reports via InstallShield.

Which three components are you required to use? (Each correct answer presents part of the solution. Choose three.)

A. Rsconfig utility

B. RS.exe

C. ReportService2005.asmx

D. Report Builder

E. .RSS File

Correct Answer: BCE

QUESTION 18

You are an ETL developer. You maintain a SQL Server 2008 Integration Services (SSIS) instance.

You need to develop a parent package that meets the following requirements:

The parent package can execute child packages by using multiple execute Package tasks.

A notification needs to be sent before executing the parent package and after executing all child packages.

If any packages have errors during execution, the subsequent package should run after the failure.

Only one error notification email should be sent that lists all package errors from the parent package.

What should you do? (Each correct answer presents part of the solution. Choose three.)

A. Set the precedence constraints between child packages to success.

B. Set the precedence constraints between child packages Completion.

C. Add a Send Mail Task to the child packages’ OnPostExecution event handler.

D. A Add a Send Mail Task to the child packages’ OnPostExecution event handler.

E. dd a Send Mail Task to the parent packages’ OnError event handler.

Correct Answer: BDE

QUESTION 19

You maintain a SQL Server 2008 Analysis Services (SSAS) database.

You plan to troubleshoot query performance by using SQL Profiler.

You need to replay the queries issued to the SSAS database.

Which three trace events should you use? (Each correct answer presents part of the solution.

Choose three.)

A. Audit Login event with all its data columns.

B. Query Begin event with all its data columns.

C. Query End event class with all its data columns.

D. Audit Logout event class with all its data columns.

E. Command Events class with all its data columns.

Correct Answer: ABC

QUESTION 20

You maintain a SQL Server 2008 Analysis Services (SSAS) database on a default instance.

You need to collect information to determine if performance issues are related to hardware limitations or latch wait contention.

Which three objects should you use? (To answer, select the appropriate objects in the answer area. Each correct answer presents part of the solution. Choose three.)

A. Correct

B. Wrong

C. Wrong

D. Correct

E. Wrong

F. Correct

Correct Answer: ADF

QUESTION 21

You maintain a SQL server 2008 Analysis Services (SSAS) data warehouse for a school district. All tests are administered via a third-party application. Results are collected in your SSAS data warehouse and moved to a Scores fact table for analysis. This warehouse also includes a Student Dimension which contains attributes related to each student.

The school year is starting, and the district administration wants the Student Dimension updated hourly so that new students can be identified and tracked. However, test scores located in the Scores fact table should not be updated as part of this incremental processing strategy.

You need to implement this functionality. Which option should you use?

A. Process Add

B. Process Update

C. Process Index

D. Process Full

Correct Answer: B

    

QUESTION 22

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 distinct dataset that includes all possible values and attach it to the parameter.

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

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

D. Configure the Internal Visibility option for the parameter.

E. Develop a report for each environment.

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. Configure the Hidden Visibility property.

Correct Answer: F

    

QUESTION 23

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 create a SQL Server 2008 Reporting Services (SSRS) report. The report is hosted in a SharePoint environment. The UserID is passed into the report automatically when the user views the report. The UserID will be used to link to other pages on the SharePoint site via url.

You need to prevent the user from seeing their UserID.

What should you do?

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

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

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

D. Configure the Internal Visibility option for the parameter.

E. Develop a report for each environment.

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. Configure the Hidden Visibility property.

Correct Answer: H

QUESTION 24

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 inthe 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 maintain a SQL Server 2008 Reporting Services (SSRS) instance for a shoe company.

An existing report on shoe sales allows the user to see the company’s revenue across many different attributes.

You need to create a drop down list that filters the report by one or more shoe colors.

You alter the main dataset and configure the report parameter to accept multiple values.

 What should you do next?

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

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

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

D. Configure the Internal Visibility option for the parameter.

E. Develop a report for each environment.

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. Configure the Hidden Visibility property.

Correct Answer: A

QUESTION 25

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 are SQL Server 2008 Reporting Services (SSRS) developer. You develop a report with two parameters names State and City in that order.

When the State parameter is selected, the City parameter should be filtered by the State selected.

You need to enable this functionality.

What should you do?

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

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

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

D. Configure the Internal Visibility option for the parameter.

E. Develop a report for each environment.

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. Configure the Hidden Visibility property.

Correct Answer: B

QUESTION 26

You maintain a SQL Server 2008 Reporting Services Instance.

Some users need to view and run reports in the USA Region folder on the Report Server.

You need to assign these users the minimum permissions required.

Which role should you use?

A. Publisher

B. Content Manager

C. Report Builder

D. Brower

Correct Answer: D

QUESTION 27

You are developing a SQL Server 2008 Integration Services (SSIS) data flow that loads data to a SQL Server 2008 database.

You need to ensure that the data flow updates the existing records and inserts the updated records into the SQL Server database by using data from the rows in your data flow.

Which three data flow components should you use? (Each correct answer presents part of the solution. Choose three.)

A. Conditional Split Transformation

B. OLE DB Destination

C. OLE DB Command Transformation

D. Data Conversion Transformation

E. Lookup Transformation

Correct Answer: BCE

      

QUESTION 28

You are developing a SQL Server 2008 Integration Services (SSIS) data flow that extracts data from two sources: a Mircosoft Excel sources and ADO.net. The

ADO.net source is based on a SELECT statement that uses an ORDER BY clause.

The data flow is as shown in the following exhibit. (Click the Exhibit button.)

When the paths from the two sources are connected to the Merge Join component, the following error message is displayed:

Data Flow Task: the input is not sorted. The `input “Merge Join Left Input”(48)’ must be sorted.

You need to ensure that data from each query is included in each row in the data flow.

What should you do? (Each correct answer presents part of the solution. Choose two.)

A. Add a Sort transformation for the Excel source.

B. Add an OLE DB Command transformation for each data flow.

C. Update the sorting properties in the ADO.net source component.

D. Replace the Merge Join transformation with a Union All transformation.

Correct Answer: AB

    

QUESTION 29

You design a SQL Server 2008 Integration Service (SSIS) data flow that inserts data from a source query to a destination table.

You need to insert only those rows that do not already exist in the destination table. You also need to avoid the records in a status column with “Inactive” status.

What should you do? (Each correct answer presents part of the solution. Choose two.)

A. Use the Merge transformation.

B. Use the Lookup transformation.

C. Use the Union All transformation.

D. Use the Fuzzy Lookup transformation.

E. Use the Conditional Split Transformation.

Correct Answer: BE

QUESTION 30

You develop a series of SQL Server 2008 Integration Services (SSIS) packages.

You need to ensure that the following requirements are met:

The package contains configurations.

The configuration information is centrally stored for all packages.

There are dependencies among the packages.

Which three components should you use to configure the package? (Each correct answer presents part of the solution. Choose three.)

A. XML configuration file.

B. Environment variable.

C. Registry entry

D. Parent package variable.

E. SQL Server 2008 table.

Correct Answer: ADE

QUESTION 31

You build and deploy a SQL Server 2008 Integration Service (SSIS) package.

During package development, the XML configuration is not specified.

You need to apply the configuration file to the deployed package stored in files system.

Which parameter should you set in the DTEXEC command line utility?

A.   CheckF

B.   Set

C.   conf

D.   L

Correct Answer: C

QUESTION 32

You develop a SQL Server 2008 Integration Services (SSIS) package.

You need to modify the package to log event data from a text file to the SQL Server database.

What should you do?

A. Modify the log provider.

B. Modify the Connection Manager.

C. Modify the Flat File Destination.

D. Move the package file to the new path.

Correct Answer: A

QUESTION 33

You use SSIS to consolidate customer data from a relational database and several Microsoft Excel files into a single data warehouse.

The data warehouse is missing information that exists in the Excel files.

You need to discover why the data warehouse is missing data.

What should you verify first to begin troubleshooting?

A. That system event logs are not full.

B. That MSDTC has been started on the system.

C. That SSRS has been installed and configured.

D. That SSAS has been installed and configured.

Correct Answer: B

QUESTION 34

You maintain for SQL Server 2008 Integration (SSIS) instance. You develop an SSIS package by using development environment database connections.

The package fails to execute in the production environment. Connection strings in the package are empty.

You need to change the package protection levels.

What should you do?

A. Use the EncryptAllWithUserKey protection level.

B. Use the EncryptAllWithPassword protection level.

C. Use the ServerStorage protection level.

D. Use the DontSaveSensitive protection level.

Correct Answer: B

QUESTION 35

You design a package by using SQL Server 2008 Integration Services (SSIS). The package is designed by using your local development environment.

The package extracts data from a remote server and inserts it in the PROD database on the local SQL Server 2008 instance. You deploy the package to the

MSDB database on the production server.

You need to ensure that users can view, execute, and delete their own packages.

What should you do?

A. Assign the users to the db_ssisoperator role.

B. Assign the users to the db_ssisltduser role.

C. Assign the users to a custom database role in the PROD database.

D. Assign the users to the db_ssisadmin role.

Correct Answer: B

QUESTION 36

You create a SQL Server 2008 Analysis Services (SSAS) solution.

You enable proactive caching for a partition.

You need maintain SSAS performance by preventing the partition from entering ROLAP mode.

What should you do?

A. Set the OnlineMode property for the partition to Immediate.

B. Set the ProcessingMode property for the partition to Regular.

C. Set the OnlineMode property for thepartition to OncacheComplete.

D. Set the ProcessingMode property for the partition to LazyAggregations.

Correct Answer: C

QUESTION 37

You maintain a SQL Server 2008 Analysis Services (SSAS) instance. The instance contains a database that is used by the members of the Engineering group.

You configure a new role named Engineering and Design by using the following allowed permission set:

{[Employees].[Department].[Engineering and Design]}

You assign the Engineering group to the Engineering and Design role.

You need to ensure that users in the Engineering and Design role can view data only for their department.

What should you do?

A. Add your account temporarily to the Engineering and Design role.

B. Add the User ID = [Engineering and Design]; parameter to the connection string.

C. Add the Roles = [Engineering and Design]; parameter to the connection string.

D. Select the Enable Visual Totals check box for the Engineering and Design role.

Correct Answer: C

QUESTION 38

You maintain a SQL Server 2008 Analysis Service (SSAS) database in a test environment. You plan to deploy changes from as SSAS project in a development environment to the test environment are replaced by the deployment.

What should you do?

A. Update the database by using the Deployment Wizard.

B. Update the database by using the Synchronize Database Wizard.

C. Back up the development database and restore it to the test environment.

D. Process the database by using the Business Intelligence Design Studio (BIDS).

Correct Answer: C

QUESTION 39

You maintain a SQL Server 2008 Analysis Services (SSAS) database.

You are troubleshooting query performance by using SQL Profiler.

You are identifying the queries that take a long time to run. You determine the time used by the Storage Engine.

You need to determine the time used by the Formula Engine.

What trace event should you use?

A. Query Cube End

B. Query Subcube

C. MDX Script End

D. Get Data from Cache

Correct Answer: A

QUESTION 40

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 more than 10 commands prior to disconnecting.

Which DMV should you use?

A. Discover_Traces

B. Discover_Sessions

C. Discover_Commands

D. Discover_Connections

Correct Answer: B

QUESTION 41

You maintain a SQL Server 2008 Analysis Services (SSAS) database on a default instance.

You plan to document the Analysis Services performance information.

You need to collect information related to cache hits from the memory when the database is processed.

Which object should you use?

A. MSAS 2008: Memory

B. MSAS 2008: Cache

C. MSAS 2008: Proc Aggregations

D. MSAS 2008: Storage Engine Query

Correct Answer: D

    

QUESTION 42

You maintain a SQL Server 2008 Analysis Services (SSAS) instance.

You plan to update only the Products dimension while processing the cube by using an XML for Analysis (XMLA) script.

You need to ensure that only the dimensions are updated.

What should you do?

A. Execute a processIndex command.

B. Execute a processUpdate command.

C. Execute a processStructure command.

D. Execute a processDefault command.

Correct Answer: B

QUESTION 43

You develop a report by using SQL Server 2008 Reporting Services (SSRS). The report has a dataset that contains 25 columns of data. You plan to render the dataset to a Microsoft Excel worksheet.

You need to implement drilldown technology when rendering to Excel.

Which type of data region should you use?

A. List

B. Textbox

C. Rectangle

D. Matrix

Correct Answer: D

QUESTION 44

You design a report by using SQL Server 2008 Reporting Services (SSRS).

The report will be included in a Windows Forms application that is distributed to remote users. These users have access to the report server.

You need to ensure that the application renders the report correctly.

What should you do?

A. Use the ReportViewer control along with the LocalReport property.

B. Use the ReportViewer control along with the RemoteReport property.

C. Deploy the report to the report server with a shared dataset.

D. Deploy the report to the report server after adding an assembly creating a connection to the Windows form application.

Correct Answer: B

    

QUESTION 45

You maintain a report in a SQL Server 2008 Reporting Services (SSRS) instance.

The report contains query parameters that allow users to filter the contents of the report.

The report requires users to enter parameter values before it will run.

The users wish to simplify the process so that the report will run without input.

You need to enable this functionality.

What should you do?

A. Add a default value to all parameters

B. Modify the dataset to use the Table query type.

C. Remove the filters from the dataset queries and use the parameters to filter the data source.

D. Move the queries to the stored procedures and use the stored procedures to populate and filter the datasets of the report.

Correct Answer: A

QUESTION 46

You develop reports by using SQL Server 2008 Reporting Services (SSRS) Enterprise Edition. You need to use data-driven subscription to distribute reports based on dynamic subscription data. Which role should you use?

A. Content Manager

B. Publisher

C. System User

D. Browser

Correct Answer: A

QUESTION 47

You manage the security in SQL Server 2008 R2 Reporting Services (SSRS).

You create a new role.

You need to grant the following permissions to the role:

View and modify security settings for reports, folders, resources, and shared data sources.

Create and delete reports.

Modify report properties.

View content, definition, and property for reports.

Which three actions should you perform? (Each correct answer presents part of the solution, Choose three.)

A. Assign the Manage Resources task to the role.

B. Assign the Manage Reports task to the role.

C. Assign the Consume Reports task to the role.

D. Assign Set Security Policies for Items task to the role.

E. Assign the Manage All Subscriptions task to the role.

Correct Answer: BCD

QUESTION 48

You manage the security in SQL Server 2008 Reporting Services (SSRS). The report server contains a folder named Human Resources Report.

All groups can view and modify reports in any folder.

You need to ensure that only the Human Resources group can view and modify reports in the Human Resources Reports folder.

What should you do? (Each correct answer presents part of the solution. Choose two.)

A. Remove all groups from the Human Resources Reports folder.

B. Create a Human Resources role.

C. Add the Human Resources group to the Human Resources Reports folder and assign the Content Manager role.

D. Assign the Human Resources role to the Human Resources Reports folder.

E. Assign the Human Resources group to the root folder.

Correct Answer: AC

QUESTION 49

You are implementing a disaster recovery strategy for SQL Server 2008 Reporting Services (SSRS).

You want to be able to restore the report catalog database in the event of a failure.

You need to back up the encryption key.

Which command line utility should you use?

A. Rsconfig.exe

B. Rs.exe

C. rskeymgmt.exe

D. dtsutil.exe

Correct Answer: AC

    

QUESTION 50

You develop a SQL Server 2008 Integration Services (SSIS) project. You receive information on sales leads from the sales department. These leads are contained in Microsoft Excel files. You have to load these leads on a daily basis by designing, configuring, deploying, and scheduling steps.

Because this is a daily task, loading Excel files with the Import and Export Data (32-bit or 64-bit) wizard is NOT an option.

When designing the SSIS package, you receive the error shown below and in the Exhibit. (Click the Exhibit button.)

Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE

DB provider is available.

You need to resolve the error.

Which change should you make to the debug options?

A. Set Run64BitRuntime to True.

B. Set Run64BitRuntime to False.

C. Set RunInOptimizedmode to True.

D. Set RunInOptimizedmode to False.

Correct Answer: B

QUESTION 51

You work for an organization with global operations. You are responsible for the SQL Server 2008 Integration Services (SSIS) instance. You develop SSIS packages to load daily and weekly feeds. These packages are deployed via the SQL Server method.

These servers reside in geographies with a high risk of natural disaster.

You need to implement a disaster recovery plan to back up the SSIS packages for availability.

What should you back up?

A. The tempdb database

B. The MSDB database

C. The model database

D. The Microsoft SQL Server90DTSPackage folder.

Correct Answer: B

QUESTION 52

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 this series. Information and details provided in a question apply only to the question.

You maintain the SQL Server 2008 Integration Services (SSIS) instance for a healthcare supply chain data management company. Your company uses third-party event based scheduler.

You develop a package named Memberinformation.dtsx and save it in the SSISpackages folder on the company network.

You need to deploy this package to the ProdSSISpackages file system on the production server. You need to rename the package to

Memberinformation_Prod.dtsx, and the event based scheduler will execute the renamed package.

Which command syntax should you use?

A. dtutil   SQL Memberinformation   COPY DTS;c:SSISpackagesMemberinformation

B. dtutil   FILE c:SSISpackagesMemberinformation.dtsx   COPY FILE;W: ProdSSISpackages Memberinformation_Prod.dtsx

C. dtutil   FILE c: SSISpackages|Memberinformation.dstx   DestServer DBPROD   COPY SQL; Memberinformation.dtsx

D. dtutil   FILE c: SSISpackages|Memberinformation.dstx   COPY SQL; Memberinformation.dtsx

E. dtexec   f ” Z: Memberinformation.dtsx ”   conf ”

Z: Memberinformationconfig,cfg ”

F. dtexec   sq Memberinformation    verify (a400e860-38d5-11c5-11ce-ae62-08002b2b79ef)

G. dtexec   sq Memberinformation   ser productionServer    va

H. dtexec   f “c: Memberinformation .dtsx”   1 ” DTS.LogProviderTextFile;Y:Loginformation.txt”

Correct Answer: B

    

QUESTION 53

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 the question apply only to that question.

You maintain the SQL Server 2008 integration Services (SSIS) instance for a healthcare supply chain data management company. Your company uses third-party event based scheduler.

You develop a package named Memberinformation.dtsx and save it in the SSIS packages folder on the company network.

You need to deploy this package by connecting with the DBPROD production server SSIS engine. The event based scheduler will execute the deployed SSIS package.

Which command syntax should you use

A. dtutil   SQL Memberinformation   COPY DTS;c:SSISpackagesMemberinformation

B. dtutil   FILE c:SSISpackagesMemberinformation.dtsx   COPY FILE;W: ProdSSISpackages Memberinformation_Prod.dtsx

C. dtutil   FILE c: SSISpackages|Memberinformation.dstx   DestServer DBPROD   COPY SQL; Memberinformation.dtsx

D. dtutil   FILE c: SSISpackages|Memberinformation.dstx   COPY SQL; Memberinformation.dtsx

E. dtexec   f ” Z: Memberinformation.dtsx ”   conf ”

Z: Memberinformationconfig,cfg ”

F. dtexec   sq Memberinformation    verify (a400e860-38d5-11c5-11ce-ae62-08002b2b79ef)

G. dtexec   sq Memberinformation   ser productionServer    va

H. dtexec   f “c: Memberinformation .dtsx”   1 ” DTS.LogProviderTextFile;Y:Loginformation.txt”

Correct Answer: C

    

QUESTION 54

Note: This question is part of a series of questions that use the same of 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 maintain the SQL Server 2008 Integration Services (SSIS) instances for a an insurance company.

You develop a package named Memberinformation.dtsx and save it in the SSISpackages folder on the company network. The package is configured externally to

the file Mamberinformationconfig.cfg.

You need to execute the package.

Which command syntax should you use?

A. dtutil   SQL Memberinformation   COPY DTS;c:SSISpackagesMemberinformation

B. dtutil   FILE c:SSISpackagesMemberinformation.dtsx   COPY FILE;W: ProdSSISpackages Memberinformation_Prod.dtsx

C. dtutil   FILE c: SSISpackages|Memberinformation.dstx   DestServer DBPROD   COPY SQL; Memberinformation.dtsx

D. dtutil   FILE c: SSISpackages|Memberinformation.dstx   COPY SQL; Memberinformation.dtsx

E. dtexec   f ” Z: Memberinformation.dtsx ”   conf ”

Z: Memberinformationconfig,cfg ”

F. dtexec   sq Memberinformation    verify (a400e860-38d5-11c5-11ce-ae62-08002b2b79ef)

G. dtexec   sq Memberinformation   ser productionServer    va

H. dtexec   f “c: Memberinformation .dtsx”   1 ” DTS.LogProviderTextFile;Y:Loginformation.txt”

Correct Answer: E

    

QUESTION 55

Note: This question is part of a series of questions that use the same of 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 maintain the SQL Server 2008 Integration Services (SSIS) instance for a healthcare device manufacturing company.

You develop a package named Memberinformation.dtsx with Windows authentication. You deploy it to SQL Server.

You need to validate Memberinformation.dtsx packages without package execution.

Which command syntax should you use?

A. dtutil   SQL Memberinformation   COPY DTS;c:SSISpackagesMemberinformation

B. dtutil   FILE c:SSISpackagesMemberinformation.dtsx   COPY FILE;W: ProdSSISpackages Memberinformation_Prod.dtsx

C. dtutil   FILE c: SSISpackages|Memberinformation.dstx   DestServer DBPROD   COPY SQL; Memberinformation.dtsx

D. dtutil   FILE c: SSISpackages|Memberinformation.dstx   COPY SQL; Memberinformation.dtsx

E. dtexec   f ” Z: Memberinformation.dtsx ”   conf ”

Z: Memberinformationconfig,cfg ”

F. dtexec   sq Memberinformation    verify (a400e860-38d5-11c5-11ce-ae62-08002b2b79ef)

G. dtexec   sq Memberinformation   ser productionServer    va

H. dtexec   f “c: Memberinformation .dtsx”   1 ” DTS.LogProviderTextFile;Y:Loginformation.txt”

Correct Answer: G

    

QUESTION 56

You are designing a SQL Server 2008 Integration Services (SSIS) package.

The package includes a Data Flow task that copies several million rows. The data flow requires a match against a reference table that contains 10,000 rows. Each row in the reference table will be used during the data flow.

Same row are not matching due to a mismatched case type

You need to ensure that the data flow executes to match all rows correctly.

Which data flow component should you select?

A. A Fuzzy Lookup transformation

B. A Lookup transformation along with the Full cache option

C. A Lookup transformation along with the No cache option

D. A Lookup transformation along with the Partial cache option

Correct Answer: C

QUESTION 57

You create a SQL Server 2008 Analysis Services (SSAS) solution. Your solution has a date dimension named Data along with a hierarchy named Calendar. The

Calendar hierarchy has levels for the Year, Quarter, Month, and Date attributes.

You need to create a named set that refers to July of the year 2007.

Which expression should you write for the named set?

A. PeriodsToDate ([DinTime] . [Yr-Qtr-Mth]. [Year], [DimTime] . [Yr-Qtr-Mth] . [Month]. [July 2007])

B. PeriodsToDate ([DinTime] . [Yr-Qtr-Mth]. [Month], [DimTime] . [Yr-Qtr-Mth] . [Month]. [July 2007])

C. ParallelPeriod ([DimTime] . [Yr-Qtr-Mth] . [Year] , 7, [DimTime] . [Yr-Qtr-Mth] . [Month] . [July 2007])

D. ParallelPeriod ([DimTime] . [Yr-Qtr-Mth] . [Month] , 7, [DimTime] . [Yr-Qtr-Mth] . [Month] . [July 2007])

Correct Answer: B

QUESTION 58

You create a report by using SQL Server 2008 Reporting Services (SSRS).

The report is complex and takes several minutes to generate.

You need to configure the report server to run in the morning and have the users access that copy of the report for the remainder of the day.

What should you do?

A. Implement report snapshots

B. Implement report execution caching

C. Configure the data source to use snapshot isolation

D. Configure the data source to use a database snapshot

Correct Answer: A

QUESTION 59

Two months ago you created a report by using SQL Server 2008 Reporting Services (SSRS).

The report is already in production and is currently being emailed to the CID every Monday at 8:30am. The CIO wants to report also to be emailed to members of the finance management team.

You need t enable this functionality.

What should you do?

 A. Select the report you created, and click the New Subscription button.

B. Select the My Subscriptions page, and the click the New Data-driven Subscription button.

C. Select the report you created, click the Subscription tab, and then click the New Data-driven Subscription button.

D. Select the report you created, click the Subscriptions tab, and then click the New Subscription button.

Correct Answer: C

QUESTION 60

You maintain a SQL Server 2008 Analysis Services (SSAS) database.

When you process the Analysis Services database using the Process Add option in Management Studio, you discover that a KeyNotFound error occurs.

You need to modify the ErrorConfiguration property so that the error condition id reported and processing continues.

Which object should you modify?

A. Cube

B. Partition

C. Dimension

D. Measure group

Answer: D

QUESTION 61

You are developing a SQL Server 2008 Integration Services (SSIS) data flow that loads data to a SQL Server 2008 database.

You need to ensure that the data flow updates the existing records in the SQL Server database by using data from the rows in your data flow.

Which data flow component should you use?

A. SQL Server Destination

B. OLE DB Destination

C. OLE DB Command Transformation

D. Data Conversion Transformation

Correct Answer: C

    

QUESTION 62

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 the Data Viewer tool.

B. Use the Data Profiling task.

C. Use the Audit transformation.

D. Use breakpoints in the Script component.

Correct Answer: A

QUESTION 63

You are developing a SQL Server 2008 Integration Services (SSIS) data flow that extracts data from two sources.

Each source database is located on a different server. Each source is based on a SELECT statement that uses an ORDER BY clause.

The data flow is as shown in the following diagram.

When the paths from the two sources are connected to a Merge Join component, the following error message is displayed:

“Data Flow Task: The input is not sorted. The ‘input “Merge Join Left Input” (80)’ must be sorted.”

You need to ensure that data from each query is included in each row in the data flow. You want to achieve this goal by using minimum amount of execution time.

What should you do?

 

A. Add a Sort transformation for each data flow.

B. Add an OLE DB Command transformation for each data flow.

C. Update the sorting properties in each source component.

D. Replace the Merge Join transformation with a Union All transformation.

Correct Answer: C

QUESTION 64

You are designing a SQL Server 2008 Integration Services (SSIS) packagE.The package includes a Data Flow task that copies several million rows. The data flow requires a match against a reference table that contains 10,000 rows. Each row in the reference table will be used during the data flow.

You need to ensure that the data flow executes in the minimum amount of time.

Which data flow component should you select?

A. a Fuzzy Lookup transformation

B. a Lookup transformation along with the Full cache option

C. a Lookup transformation along with the No cache option 

D. a Lookup transformation along with the Partial cache option

Correct Answer: B

QUESTION 65

You design a SQL Server 2008 Integration Services (SSIS) data flow that inserts data from a source query to a destination table.

You need to insert only those rows that do not already exist in the destination tablE.What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

A. Use the Merge transformation.

B. Use the Lookup transformation.

C. Use the Union All transformation.

D. Use the Merge Join transformation.

E. Use the Fuzzy Lookup transformation.

F. Use the Fuzzy Grouping transformation.

Correct Answer: BD

QUESTION 66

You modify a SQL Server 2008 Integration Services (SSIS) packagE.The package extracts data from a text file and loads this data to a SQL Server 2008 table as shown in the following two exhibits. You need to update the package to extract data from multiple text files of the same directory.

Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)

 

A. Add a variable to the package.

B. Add a variable to the data flow task.

C. Add a Foreach Loop container to the package.

D. Add a For Loop container to the package. 

E. Add an expression to the Flat File Source component.

F. Add an expression to the Flat File connection manager.

Correct Answer: ACF

QUESTION 67

You implement a SQL Server 2008 Integration Services (SSIS) package.

The package contains the following components:

A Foreach Loop container for the Collection property as shown in the following exhibit. (Click the Exhibit button.)

A Foreach Loop container for the Variable Mappings property as shown in the following exhibit. (Click the

Exhibit button.)

A String variable named FileName.

A String variable named DirectoryPath.

You need to update the package so that the DirectoryPath variable returns the directory path for the file path stored in the FileName variable.

Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

A. Set the RaiseChangedEvent property of the User::FileName variable to True.

B. Set the EvaluateAsExpression property of the User::DirectoryPath variable to True.

C. Add a Script task to the OnPostExecute event handler of the Foreach Loop container.

D. Set the Expression property of the User::DirectoryPath variable to the following expression. SUBSTRING( @[User::FileName], 1, LEN( @[User::FileName] ) – FINDSTRING( REVERSE( @[User::FileName] ), “\”, 1 ))

E. Set the Expression property of the User::FileName variable to the following expression. SUBSTRING( @[User::FileName], 1, LEN( @[User::FileName] ) – FINDSTRING( REVERSE( @[User::FileName] ), “\”, 1 ))

Correct Answer: BD

    

QUESTION 68

You implement a SQL Server 2008 Integration Services (SSIS) package. The package contains the following components: A data flow task contained in a For Loop container A Flat File destination component that uses a Flat File connection manager An OnError event handler When an error occurs in the package, you plan to write diagnostic information from an expression that includes the version of the package to a text file.

You need to ensure that the required information is written to the text file.

What should you do?

A. Use the VersionGUID variable.

B. Use the ExecutionGUID variable. 

C. Use the SourceID variable.

D. Use the TaskID variable.

Correct Answer: A

QUESTION 69

You develop a SQL Server 2008 Integration Services (SSIS) package.

The structure of the package is as shown in the following exhibit.

You need to define a variable that contains sensitive information that is only available to the Sequence Container, the Data Flow Task, and the Execute SQL Task.

What should you do?

 

A. Create the variable at the package scope.

B. Create the variable at the Data Flow Task scope.

C. Create the variable at the Execute SQL Task scope.

D. Create the variable at the Sequence Container scope.

Correct Answer: D

    

QUESTION 70

You are developing a SQL Server 2008 Integration Services (SSIS) package.

The package uses an OLE DB connection manager to connect to a third-party database server. The database does not support Integrated Windows authentication.

You create an XML configuration file that provides configuration for the connection manager. You add the configuration file to the package.

When the package executes, you receive an error message which indicates that login failure occurred.

You need to execute the package successfully.

What should you do?

A. Edit the XML configuration file.

B. Use an indirect XML configuration.

C. Run the package on the same server as the third-party database server. 

D. Use a Microsoft ADO.NET connection manager instead of an OLE DB connection manager.

Correct Answer: A

QUESTION 71

You build and deploy a SQL Server 2008 Integration Services (SSIS) package.

During package development, the XML configuration is not specified.

You need to apply the configuration to the deployed package. What should you do?

A. Use the Deployment utility.

B. Use the SETX command line utility.

C. Use the DTUTIL command line utility.

D. Use the DTEXEC command line utility.

Correct Answer: D

QUESTION 72

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 the SSIS log provider for the SQL Profiler.

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

C. Use a Flat File Destination component.

D. Use a Raw File Destination component.

E. Add an Error output to the Data Flow component

Correct Answer: CE

 QUESTION 73

You are editing a SQL Server 2008 Integration Services (SSIS) package by using Business Intelligence

Development Studio. The package uses a custom data flow component. The package runs successfully.

You attempt to add another instance of the custom data flow component to the package. The data flow component does not appear in the toolbox or in the Choose Toolbox Items dialog box.

You need to add the custom data flow component to the toolbox.

What should you do?

A. Copy the assembly to the appropriate folder.

B. Add the assembly to the Global Assembly Cache.

C. Register the assembly by using the REGSVR32 utility.

D. Register the assembly by using the Assembly Registration utility (REGASM.EXE).

Correct Answer: A

QUESTION 74

You plan to use SQL Server 2008 Integration Services (SSIS) on a SQL Server 2008 failover cluster.

You have a complex package that takes a long time to run.

You need to ensure that if the package executes during a failover, the package can be resumed at the point of failure.

What should you do?

A. Cluster the SSIS service. Add the SSIS service to its own cluster resource group.

B. Cluster the SSIS service. Add the SSIS service to the SQL Server cluster resource group.

C. Implement checkpoints within your package. Restart the package whenever a failover occurs.

D. Implement transactions within your package. Restart the package whenever a failover occurs.

Correct Answer: C

    

QUESTION 75

You maintain SQL Server 2008 Integration Services (SSIS) on a SQL Server 2008 instance.

You need to design a failure recovery plan for packages that are deployed to a SQL Server.

What should you do?

A. Back up the master database.

B. Back up the model database.

C. Back up the MSDB database.

D. Back up the tempdb databse.

Correct Answer: C

QUESTION 76

You maintain a SQL Server 2008 Integration Services (SSIS) instancE.The instance stores packages in a SQL Server 2008 instance.

You need to deploy the package of an instance of SSIS to another SSIS instance that stores its packages in a shared directory.

What should you do?

A. Execute the dtutil command.

B. Execute the dtexec command.

C. Execute the dtexecui command.

D. Execute the dtswizard command.

Correct Answer: A

QUESTION 77

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. Modify the AllowConfigurationChanges property of the project.

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

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

Correct Answer: A

QUESTION 78

You develop a SQL Server 2008 Integration Services (SSIS) package. The package downloads several files from a remote FTP server by using multiple FTP tasks, and then processes data by using multiple

Execute SQL tasks.

You need to configure the package to restart at the point of failure after the package downloads the files.

What should you do?

A. Configure the FTP tasks to support transactions.

B. Configure the Execute SQL tasks to support transactions.

C. Move all the FTP tasks to a sequence container.

D. Configure the package to use a checkpoint.

Correct Answer: D

QUESTION 79

You are developing a SQL Server 2008 Integration Services (SSIS) package.

The package performs the following tasks:

Processes multiple files by using a ForEach Loop container.

Imports the contents of the files to a table by using a Data Flow task.

Logs the results of the status into a table by using an Execute SQL task.

You need to ensure that all the tasks of the package except the Execute SQL task execute within a single transaction.

Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

A. In the Foreach Loop container, configure the TransactionOption property to NotSupported.

B. In the Data Flow task, configure the TransactionOption property to Required.

C. In the package, configure the TransactionOption property to Required.

D. In the package, configure the TransactionOption property to NotSupported.

E. In the Execute SQL task, configure the TransactionOption property to NotSupported.

F. In the Execute SQL task, configure the TransactionOption property to Supported.

Correct Answer: CE

QUESTION 80

You develop a package by using SQL Server 2008 Integration Services (SSIS). You test the package by using your local development environment.

You deploy the package to the file system in the production environment. The users report that the connection strings contained in the package are blank. The package fails to execute.

You need to change the protection level of the package to ensure that users can view and execute the package.

Which protection level should you use?

A. DontSaveSensitive

B. EncryptAllWithUserKey

C. EncryptAllWithPassword

D. ServerStorage

Correct Answer: C

QUESTION 81

You design a package by using SQL Server 2008 Integration Services (SSIS). The package is designed by using your local development environment. The package extracts data from a remote server and inserts it in the PROD database on the local SQL Server 2008 instance. You deploy the package to the MSDB database on the production server. You need to ensure that only users that belong to an appropriate database role can modify and execute the deployed package. You also need to ensure that these users cannot access other packages that are deployed to the server. What should you do?

A. Assign the users to the db_ssisoperator role.

B. Assign the users to the db_ssisltduser role.

C. Assign the users to a custom database role in the PROD database. 

D. Assign the users to a custom database role in the MSDB database.

Correct Answer: D

QUESTION 82

You develop a package by using SQL Server 2008 Integration Services (SSIS). You test the package by using your local development environment.

The package is deployed to a production server.

You need to configure the protection level of the package to ensure that only the user who deployed the package can execute it.

Which protection level should you use?

A. EncryptAllWithPassword

B. EncryptSensitiveWithPassword

C. DontSaveSensitive

D. EncryptAllWithUserKey

Correct Answer: D

QUESTION 83

You administer an instance of a SQL Server 2008 server.

The server is used to execute SQL Server 2008 Integration Services (SSIS) packages.

You need to ensure that the server executes only correctly signed packages.

What should you do?

A. Set the BlockedSignatureStates registry entry to NoAdministrativeRestriction.

B. Set the package protection level on all packages to DontSaveSensitive.

C. Set the package protection level on all packages to EncryptSensitiveWithPassword.

D. Set the BlockedSignatureStates registry entry to Block invalid and untrusted signatures and unsigned packages.

Correct Answer: D

QUESTION 84

You create a SQL Server 2008 Integration Services (SSIS) package by using SQL Server 2008.

You use a development server named SQL_DEV1. There is no network connectivity between the development server and the production server.

You deploy the package to the SQL_PROD1 server.

The package fails to execute. The error message states that the package cannot connect to the SQL_DEV1 server.

You need to modify the package to refer to the SQL_PROD1 server when being executed.

What should you do?

A. Modify the properties of the package to use the DontSaveSensitive package protection level.

B. Modify the deployment manifest file in Microsoft Notepad, and then modify the connection string manually.

C. On the Connection Managers tab of the Execute Package utility, select the Connection Manager check box, and then modify the connection string.

D. Modify the properties of the package to use the ServerStorage package protection level.

Correct Answer: C

QUESTION 85

You create a SQL Server 2008 Analysis Services (SSAS) solution.

You create a dimension named DimCustomer that contains the following three attributes:

Customer

City

Country

The Customer attribute is the key. The DimCustomer dimension is related to a measure group named Sales that has the Granularity property set to City.

You need to ensure that you can retrieve measure values based on the City and Country attributes.

What should you do?

A. Set the source attribute to Customer and the related attribute to City. Set the source attribute to City and the related attribute to Country.

B. Set the source attribute to City and the related attribute to Customer. Set the source attribute to Country and the related attribute to City.

C. Set the source attribute to Customer and the related attribute to City. Set the source attribute to Customer and the related attribute to Country.

D. Set the source attribute to City and the related attribute to Customer. Set the source attribute to Country and the related attribute to Customer.

Correct Answer: A

QUESTION 86

You create a SQL Server 2008 Analysis Services (SSAS) solution.

You create a dimension named DimProduct that contains an attribute named Price. You use the

DimProduct dimension in a cube.

You need to ensure that aggregations are not associated with the Price attribute.

What should you do?

A. Set the IsAggregatable property of the Price attribute to False.

B. Set the AttributeHierarchyEnabled property of the Price attribute to False.

C. Set the MembersWithData property of the Price attribute to NonLeafDataHidden.

D. Set the GroupingBehavior property of the Price attribute to DiscourageGrouping.

Correct Answer: B

QUESTION 87

You create a SQL Server 2008 Analysis Services (SSAS) solution. You have a dimension named Account and a time dimension named Time. You have a fact table that contains a column named CurrentBalance. CurrentBalance contains the current account balance. You need to create a measure named AccountBalance from the CurrentBalance column to display the  closing account balance for a time period. What should you do?

A. Set the AggregationFunction property of AccountBalance to Sum.

B. Set the AggregationFunction property of AccountBalance to ByAccount.

C. Set the AggregationFunction property of AccountBalance to LastNonEmpty.

D. Set the AggregationFunction property of AccountBalance to FirstNonEmpty.

Correct Answer: C

QUESTION 88

You create a SQL Server 2008 Analysis Services (SSAS) solution.

The solution includes a table named FactOrders that contains two columns as shown in the following table:

Column Name

Description OrderKey primary key for FactOrders CustomerKey relates to the customer who places the order You need to create a measure that returns the number of customers who have placed orders. What should you do?

A. Set the Usage property to Count of rows and the Source Column property to OrderKey.

B. Set the Usage property to Distinct count and the Source Column property to OrderKey.

C. Set the Usage property to Distinct count and the Source Column property to CustomerKey.

D. Set the Usage property to Count of rows and the Source Column property to CustomerKey.

Correct Answer: C

QUESTION 89

You create a SQL Server 2008 Analysis Services (SSAS) solution. The cube structure is as shown in the following exhibit. (Click the Exhibit button.)

The Ownership column contains decimal values that represent percentage of ownership. The sum of all the values in the Ownership column for a single DimAccount member is 100%.

You create the following measures:

Ownership

Amount

You need to ensure that when the Amount measure is calculated against the DimCustomer dimension, the weighted value is calculated based on the Ownership measure.

What should you do?

A. Set the MeasureExpression property of the Amount measure to [Amount]*[Ownership].

B. Set the FormatString property of the Amount measure to [Amount]*[Ownership].

C. Set the MeasureExpression property of the Amount measure to [DimCustomer].[Amount]*[Ownership]. 

D. Set the FormatString property of the Amount measure to [DimCustomer].[Amount]*[Ownership].

Correct Answer: A

QUESTION 90

You develop a SQL Server 2008 Analysis Services (SSAS) database.

Your database includes several measure groups. A measure group named Sales Data requires frequent updates than other measure groups.

You need to ensure that the Sales Data measure group will automatically process on a schedule managed by SSAS.

What should you do?

A. Modify the ProactiveCaching property.

B. Modify the ProcessingPriority property.

C. Modify the ProcessingMode property.

D. Modify the DataAggregation property.

Correct Answer: A

QUESTION 91

You create a SQL Server 2008 Analysis Services (SSAS) solution. You add a data source view (DSV) to the solution. The DSV has a table named Employee that includes the following columns: EmployeeKey ManagerKey

The ManagerKey column references the manager of the employees.

You need to define the relationship between the employees and their manager in the DSV. What should you do?

A. Create a Named Calculation that uses EmployeeKey and ManagerKey

B. Create a relationship by using EmployeeKey as the source column and ManagerKey as the destination column.

C. Create a Named Query that uses a relationship with EmployeeKey as the source column and ManagerKey as the destination column.

D. Create a relationship by using ManagerKey as the source column and EmployeeKey as the destination column.

Correct Answer: D

QUESTION 92

You create a SQL Server 2008 Analysis Services (SSAS) solution. You have a table named Orders that contains the following columns: OrderKey Shipper OrderAmount You create a dimension named DimOrders from the Orders table. You set the OrderKey as a key column  and the Shipper column as an attribute. You create a measure group named FactOrders from the Orders table. You need to create a dimension relationship to ensure that the OrderAmount column can be aggregated by the Shipper column. What should you do?

A. Set the relationship between the DimOrders dimension and the FactOrders measure group to No Relationship.

B. Create a fact dimension relationship between the DimOrders dimension and the FactOrders measure group.

C. Create a regular dimension relationship between the DimOrders dimension and the FactOrders measure group. Set the Granularity attribute to Shipper and the measure group columns to OrderAmount.

D. Create a regular dimension relationship between the DimOrders dimension and the FactOrders measure group. Set the Granularity attribute to OrderKey and the measure group columns to OrderAmount.

Correct Answer: B

QUESTION 93

You create a SQL Server 2008 Analysis Services (SSAS) solution.

The cube structure is as shown in the following exhibit. (Click the Exhibit button.)

You need to ensure that the Amount measure can be aggregated for the DimCustomer dimension.

What should you do?

A. Create a regular relationship between DimCustomer and FactTransaction.

B. Create a referenced relationship between DimCustomer and FactCustomerAccount. Set DimAccount as an intermediate dimension.

C. Create a many-to-many relationship between DimCustomer and FactTransaction. Set FactCustomerAccount as an intermediate measure group.

D. Create a many-to-many relationship between DimCustomer and FactCustomerAccount. Set FactTransaction as an intermediate measure group.

Correct Answer: C

QUESTION 94

You create a SQL Server 2008 Analysis Services (SSAS) solution.

The solution contains a time dimension named DimTime. You have a table named FactSales that contains the two columns named OrderDate and ShipDate. The two columns refer to the DimTime dimension.

You need to ensure that the DimTime dimension can be used to browse through measures in the FactSales table for both OrderDate and ShipDate.

What should you do?

A. Create a reference relationship between the DimTime dimension and the FactSales measure group.

B. Create a many-to-many relationship between the DimTime dimension and the FactSales measure group.

C. Create two regular relationships between the DimTime dimension and the FactSales measure group. Use identical measure group columns.

D. Create two regular relationships between the DimTime dimension and the FactSales measure group. Use different measure group columns.

Correct Answer: D

QUESTION 95

You create a SQL Server 2008 Analysis Services (SSAS) solution.

You create a key performance indicator (KPI) named GPMargin for your solution. You set the Value expression of the KPI in the following manner.

[Measures].[Amount],[Account].[Accounts].[Operating Expenses] /

[Measures].[Amount],[Account].[Accounts].[Gross Margin]

Your solution has a time dimension named DimTime.

You write the following Multidimensional Expressions (MDX) statement. (Line numbers are included for reference only.)

01 IIf(

02 & &

03ParallelPeriod

04( [DimTime].[Calendar].[Quarter],1,[DimTime].[Calendar].CurrentMember

05)?), 1, -1 )

You need to set a Trend expression of the KPI to show a difference in the values compared with the previous quarter.

Which MDX code segment should you insert at line 02?

A. KPIValue( “GPMargin” ) >?( KPIValue( “GPMargin” ),

B. KPITrend( “GPMargin” ) >?( KPIValue ( “GPMargin” ),

C. KPIStatus( “GPMargin” ) >?( KPIValue( “GPMargin” ),

D. KPIGoal( “GPMargin” ) >?( KPIValue( “GPMargin” ),

Correct Answer: A

QUESTION 96

You create a SQL Server 2008 Analysis Services (SSAS) solution. Your solution contains a time dimension named DimTime that contains a hierarchy named Yr-Qtr-Mth.

You have a measure named Amount from a measure group named Measures.

You need to create a calculated member to display the Amount measure in the previous time period for the current level of the Yr-Qtr-Mth hierarchy.

Which expression should you use?

A. ([Measures].[Amount],OPENINGPERIOD( [DimTime].[Yr-Qtr-Mth].FirstSibling.Level))

B. ([Measures].[Amount],LASTPERIODS( 0,[DimTime].[Yr-Qtr-Mth].PrevMember))

C. ([Measures].[Amount],[DimTime].[Yr-Qtr-Mth].PrevMember)

D. ([Measures].[Amount],[DimTime].[Yr-Qtr-Mth].FirstSibling) 

Correct Answer: C

QUESTION 97

You need to increase the value of the SalesAmount measure by 50% for each month in the first quarter of

2007. You also need to ensure that the value for the first quarter of 2007 remains unchanged.

What should you do?

A. Add the following code segment at line 04. Scope([Measures].[Amount], [DimTime].[Date].[Quarter]. [Q1 2007] );

This=[DimTime].[Date].currentmember * 1.5;

End Scope;

B. Add the following code segment at line 01. Scope([Measures].[Amount], [DimTime].[Date].[Quarter]. [Q1 2007] ); This=[DimTime].[Date].currentmember * 1.5; End Scope;

C. Add the following code segment at line 04. Scope([Measures].[SalesAmount], Descendants([DimTime].[Date].[Quarter]. [Q1 2007] [DimTime].[Date].[Month]) ); This=[DimTime].[Date].currentmember * 1.5; End Scope;

D. Add the following code segment at line 01. Scope([Measures].[SalesAmount], Descendants([DimTime].[Date].[Quarter].[Q1 2007] [DimTime].[Date].[Month]) ); This=[DimTime].[Date].currentmember * 1.5; End Scope;

Correct Answer: C

QUESTION 98

You create a SQL Server 2008 Analysis Services (SSAS) solution. Your solution has a date dimension named Date along with a hierarchy named Calendar. The Calendar hierarchy has levels for the Year,

Quarter, Month, and Date attributes.

You need to create a named set that refers to the first seven months of the year 2007.

Which expression should you write for the named set?

A. PeriodsToDate([DimTime].[Yr-Qtr-Mth].[Year], [DimTime].[Yr-Qtr-Mth].[Month].[July 2007])

B. PeriodsToDate([DimTime].[Yr-Qtr-Mth].[Month], [DimTime].[Yr-Qtr-Mth].[Month].[July 2007])

C. ParallelPeriod([DimTime].[Yr-Qtr-Mth].[Year], 7, [DimTime].[Yr-Qtr-Mth].[Month].[July 2007])

D. ParallelPeriod ([DimTime].[Yr-Qtr-Mth].[Month], 7, [DimTime].[Yr-Qtr-Mth].[Month].[July 2007])

Correct Answer: A

QUESTION 99

You create a data mining model by using SQL Server 2008 Analysis Services (SSAS).

You create a mining structure by using the following Data Mining Extensions (DMX) code.

CREATE MINING STRUCTURE [Bike Buyer](

[Customer Key] LONG KEY,

[Age] LONG DISCRETIZED(Automatic, 10),

[Bike Buyer] LONG DISCRETE,

[Commute Distance] TEXT DISCRETE,

[Yearly Income] DOUBLE CONTINUOUS

)

You need to create a mining model that can be processed by using this mining structure. You also need to ensure that the model uses the Microsoft Decision Trees algorithm.

Which DMX statement should you use?

A. ALTER MINING STRUCTURE [Bike Buyer] ADD MINING MODEL [Decision Tree] ( [Customer Key], [Age], [Bike Buyer] PREDICT, [Commute Distance], [Yearly Income]  )?USING Microsoft_Decision_Trees WITH DRILLTHROUGH

B. INSERT INTO [Decision Tree] ( [Customer Key], [Age], [Bike Buyer], [Commute Distance],

[Yearly Income]

)

OPENQUERY([Bike Buyer],

?’Select [Customer Key], [Age], [Bike Buyer]

, [Commute Distance],[Yearly Income]‘

)

C. SELECT * INTO [Decision Tree] USING [Microsoft_Decision_Trees] FROM [Bike Buyer]

D. CREATE MINING MODEL [Decision Tree] ( [Customer Key] LONG KEY, [Age] LONG DISCRETIZED(Automatic, 10), [Bike Buyer] LONG DISCRETE, [Commute Distance] TEXT DISCRETE, [Yearly Income] DOUBLE CONTINUOUS )?USING Microsoft_Decision_Trees

Correct Answer: A

QUESTION 100

You implement a SQL Server 2008 Analysis Services (SSAS) solution. You use data mining extensions

(DMX) to query a data mining model.

You write the following DMX code segment to query a Bike Buyer data mining model in a Decision Tree data mining structure. (Line numbers are included for reference only.)

01 SELECT

02 [Decision Tree].[Bike Buyer] AS Buyer

03 FROM

04 [Decision Tree]

06 (SELECT 28 AS [Age],

07 ’2-5 Miles’ AS [Commute Distance],

08 ’1′ AS [House Owner Flag],

09 1 AS [Number Cars Owned],

10 2 AS [Total Children]) AS t

You need to ensure that the input columns automatically map to the columns in the data mining structure.

Which code fragment should you insert at line 05?

A. NATURAL PREDICTION JOIN

B. PREDICTION JOIN

C. INNER JOIN

D. OPEN QUERY

Correct Answer: A

QUESTION 101

You create a SQL Server 2008 Analysis Services (SSAS) solution.

The solution has the following components:

A dimension named DimTime that contains an attribute named Month

A measure group named FactSales that references the DimTime dimension

You need to ensure that the aggregations designed on FactSales always include the Month attribute.

What should you do?

A. Set the Usage property appropriately.

B. Set the IsAggregatable property appropriately.

C. Set the AggregationUsage property appropriately.

D. Set the AttributeHierarchyEnabled property appropriately.

Correct Answer: C

QUESTION 102

You create a SQL Server 2008 Analysis Server (SSAS) solution.

Your solution contains a measure group named FactMeasures with a single partition.

You need to ensure that the following requirements are met:

Queries that use FactMeasures always refer to the latest version of the source detail datA.

All aggregations that are part of FactMeasures are stored in a multidimensional format.

Which storage mode should you set the ProactiveCaching property of the partition to?

A. Real-time hybrid online analytical processing (HOLAP)

B. Real-time relational online analytical processing (ROLAP)

C. Automatic multidimensional online analytical processing (MOLAP)

D. Scheduled multidimensional online analytical processing (MOLAP)

Correct Answer: A

QUESTION 103

You create a SQL Server 2008 Analysis Services (SSAS) solution.

You enable proactive caching for a partition.

You need to ensure that SSAS can query relational data when multidimensional storage is being updated.

What should you do?

A. Set the OnlineMode property for the partition to Immediate.

B. Set the ProcessingMode property for the partition to Regular.

C. Set the OnlineMode property for the partition to OnCacheComplete.

D. Set the ProcessingMode property for the partition to LazyAggregations.

Correct Answer: A

QUESTION 104

You are a server administrator of a SQL Server 2008 Analysis Services (SSAS) instance. The instance contains a database that is used by the members of the Sales group.

You configure a new role named Northern Region by using the “{[Customers].[Region].[Region].[Northern]}” allowed permission set.

You assign the Sales group to the Northern Region role.

You need to verify that users in the Northern Region role can view data only for their region.

What should you do?

A. Temporarily add your account to the Northern Region role.

B. Add the User ID=Northern Region; parameter to the connection string.

C. Add the Roles=Northern Region; parameter to the connection string.

D. Select the Enable Visual Totals check box for the Northern Region role.

Correct Answer: C

QUESTION 105

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 Bikes role.

B. Add the manager to the Northeast Region role.

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

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

Correct Answer: C

QUESTION 106

You update a role named Managers for the Human Resources cube in your SQL Server 2008 Analysis

Services (SSAS) database. The database contains a dimension named Employee.

You plan to configure the security for the Managers role.

You need to allow the Managers role to access aggregate data based on only three or less employees.

What should you do?

A. Add members of the [Employee].[Employees] hierarchy that contain three or less children to the denied set for the Managers role.

B. Add members of the [Employee].[Employees] hierarchy that contain more than three children to the allowed set for the Managers role.

C. Add the following expression to the Allow Reading property in Cell Data in the Managers role. Descendants([Employee].[Employees].CurrentMember,,LEAVES).Count <= 3

D. Add the following expression to the Allow Reading property in Cell Data in the Managers role. IIF(Descendants([Employee].[Employees].CurrentMember,,LEAVES).Count <= 3, [Employee].[Employees].CurrentMember, null)

Correct Answer: C

QUESTION 107

You maintain a SQL Server 2008 Analysis Services (SSAS) database that contains a dimension named

Customer.

You need to configure the Dimension Data settings to meet the following requirements:

Deny access to the {[Customer].[Country].&[Germany],[Customer].[Country].&[France]} set of attribute members.

New members added to the attribute are visible by default.

What should you do?

A. Add all the country members except those of France and Germany to the Allowed Set property.

B. Add the following set to the Denied Set property. {[Customer].[Country].&[Germany] ,[Customer].[Country].&[France]}

C. Add the following set to the Denied Set property.  Except([Customer].[Country].[Country] ,{[Customer].[Country].&[Germany] ,[Customer].[Country].&[France]})

D. Add the following set to the Allowed Set property. Extract({[Customer].[Country].&[Germany] ,[Customer].[Country].&[France]} ,[Customer].[Country])

Correct Answer: B

QUESTION 108

You maintain a SQL Server 2008 Analysis Services (SSAS) database on a production server. The database contains a cube.

You add new functionality to the cube on a development server.

You need to deploy the new functionality from the development server to the production server by ensuring that the effect on the users is minimized.

What should you do?

A. Use the Synchronize Database Wizard.

B. Copy the appropriate files from the development server to the production server.

C. Detach the production database. Copy the appropriate files to the production server, and then attach the database on the production server.

D. Modify the Target Server property to the production server by using Business Intelligence Development Studio (BIDS). Deploy the solution.

Correct Answer: A

QUESTION 109

You maintain a SQL Server 2008 Analysis Services (SSAS) database. You create a new measure group in one of the cubes.

You have a program that maintains partitions on the production copy of the cube.

You need to update the cube definition on the production server without overwriting any existing partitions.

What should you do?

A. Use the Deployment Wizard along with the appropriate options.

B. Execute an UPDATE CUBE statement in Microsoft SQL Server Management Studio (SSMS).

C. Use the Analysis Services Destination component in Microsoft SQL Server Integration Services (SSIS).

D. Use the Business Intelligence Development Studio (BIDS) and set the processing option to Do Not Process.

Correct Answer: A

QUESTION 110

You maintain a SQL Server 2008 Analysis Services (SSAS) instance.

You need to configure the analysis services query log for the SSAS instance.

Which tool should you use?

A. SQL Server Configuration Manager

B. SQL Server Management Studio

C. SQL Server Error and Usage Reporting

D. SQL Server Business Intelligence Development Studio

Correct Answer: B

QUESTION 111

You maintain a SQL Server Analysis Services (SSAS) database. The database is configured by using multiple security roles.

The database is accessed by a Microsoft ASP.NET application that runs on a remote computer. The application is configured to use Windows Authentication.

You need to ensure that the users of the application can successfully access the SSAS database. You also need to ensure that security restrictions of the roles are applied.

What should you do?

A. Configure Kerberos authentication.

B. Configure Analysis Services for HTTP authentication.

C. Set the AnonymousConnectionsEnabled policy to True.

D. Set the SecurityRequireClientAuthentication property to True.

Correct Answer: A

QUESTION 112

You maintain a SQL Server 2008 Analysis Services (SSAS) instance. The instance contains a database. You change the DataDir property of the instance to a new folder.

You need to ensure that the instance uses the new folder in the minimum amount of downtime. What should you do?

A. Process the database.

B. Synchronize the database.

C. Restart the Analysis Services service.

D. Delete the files in the original folder, and then restart the Analysis Services service.

Correct Answer: A

QUESTION 113

You maintain a SQL Server 2008 Analysis Services (SSAS) instance.

You plan to run the Usage-Based Optimization Wizard.

You need to enable query logging.

What should you do?

A. Set the LogDir server property to a valid path.

B. Set the QueryLogSampling server property to 10.

C. Set the AllowedBrowsingFolders server property to include the folder from the LogDir setting.

D. Set the QueryLogConnectionString server property to a valid connection string.

Correct Answer: D

   

QUESTION 114

You maintain a SQL Server 2008 Analysis Services (SSAS) database.

You plan to troubleshoot query performance by using SQL Profiler.

You need to identify the queries that take a long time to run.

Which trace event should you use?

A. Query Cube End

B. Query Subcube

C. MDX Script End

D. Get Data from Cache

Correct Answer: B

QUESTION 115

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_Traces

B. Discover_Sessions

C. Discover_Commands

D. Discover_Connections

Correct Answer: C

QUESTION 116

You maintain a SQL Server 2008 Analysis Services (SSAS) database on a default instancE.You plan to document the Analysis Services performance information.

You need to collect information related to the temporary file usage when the database is processed.

Which object should you use?

A. MSAS 2008: Memory

B. MSAS 2008: Cache

C. MSAS 2008: Proc Aggregations

D. MSAS 2008: Storage Engine Query

Correct Answer: C

QUESTION 117

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 cube

B. The partition

C. The dimension 

D. The measure group

Correct Answer: C

QUESTION 118

You maintain a SQL Server 2008 Analysis Services (SSAS) database that contains a Product dimension.

The dimension contains the Category and Subcategory attributes.

The attributes have a Rigid relationship typE.The data source for the Product dimension changes the relationship between the Category and Subcategory values.

You need to execute an XML for Analysis (XMLA) command to process the dimension to successfully reflect the changE.What should you do?

A. Use the ProcessAdd command.

B. Use the ProcessUpdate command.

C. Use the ProcessIndexes command.

D. Use the ProcessClear and the ProcessDefault commands.

Correct Answer: D

QUESTION 119

You maintain a SQL Server 2008 Analysis Services (SSAS) database.

You plan to refresh data in a partition every two hours.

You need to implement the incremental processing strategy.

Which XML for Analysis (XMLA) command should you use?

A. ProcessAdd

B. ProcessData

C. ProcessFull

D. ProcessIndex

Correct Answer: A

QUESTION 120

You create a SQL Server 2008 Reporting Services (SSRS) report that contains a dataset.

The dataset retrieves data by executing a stored procedure. The report contains a table and a matrix that use the dataset.

You need to limit data that is displayed in the table while ensuring that the matrix is unaffected.

What should you do?

A. Add a filter to the dataset.

B. Add a filter to the matrix.

C. Add a filter to the table.

D. Add a parameter to the stored procedure that filters the result set.

Correct Answer: C

QUESTION 121

You create a SQL Server 2008 Reporting Services (SSRS) report. The report contains a report header and a report footer.

The report preview shows that the report is rendered correctly. You discover that when the report is rendered as a PDF file, even-numbered pages are rendered blank.

You need to modify the report to prevent the rendering of blank pages.

What should you do?

A. Reduce the overall width of the report.

B. Reduce the page size of the report.

C. Reduce the height of the report footer.

D. Reduce the height of the report header.

Correct Answer: A

QUESTION 122

You create a SQL Server 2008 Reporting Services (SSRS) report.

The report contains a tablE.You need to ensure that alternate rows in the table have a pale green

backgrounD.Which code segment should you use for each text box?

A. =iif(RowNumber(Nothing) / 2, “PaleGreen”, “White”)

B. =iif(RowNumber(Nothing) Mod 2, “PaleGreen”, “White”)

C. =iif(CountRows() / 2, “PaleGreen”, “White”)

D. =iif(CountRows() Mod 2, “PaleGreen”, “White”)

Correct Answer: B

QUESTION 123

You create a report in SQL Server 2008 Reporting Services (SSRS) solution.

You write the following code segment in the report.

Private Shared Function GetCmFromInches(ByVal InchValue As Decimal) As Decimal

If IsNothing(InchValue) Then

Return Nothing

Else

Return InchValue * 2.54

End If

End Function

The code segment generates errors when compiled.

You need to ensure that the following requirements are met:

The code segment compiles successfully.

The GetCmFromInches function can be called from other reports.

What should you do? 

A. Remove the Private keyword from the function definition.

B. Remove the Shared keyword from the function definition.

C. Declare the function as Public Shared instead of Private Shared.

D. Create a custom assembly by using Microsoft Visual Basic .NET. Declare the function as Public Shared.

Correct Answer: D

QUESTION 124

You develop a SQL Server 2008 Reporting Services (SSRS) project.

The report will display data from a SQL Server 2005 Analysis Services databasE.You need to ensure that the report displays member properties and extended properties.

What should you do?

A. Use the SQL Server data source.

B. Use the SQL Server Analysis Services data source.

C. Use the OLE DB for SQL Server data source.

D. Use the OLE DB for the Analysis Services 8.0 data source.

Correct Answer: B

QUESTION 125

You develop a SQL Server 2008 Reporting Services (SSRS) report.

A Multidimensional Expressions (MDX) query used in the report contains a parameter to display sales data for various product categories. You write the following query.

SELECT NON EMPTY {[Measures].[Reseller Sales-Order Quantity] } ON COLUMNS,

NON EMPTY

{([Date].[Calendar Year].[Calendar Year].ALLMEMBERS *

[Product].[Category].[Category].ALLMEMBERS ) }

ON CHAPTERS

FROM

(SELECT (STRTOSET(@ProductCategory,CONSTRAINED))

ON COLUMNS FROM [Adventure Works Cube])

You discover that the query generates an error when executed.

You need to ensure that the query executes successfully.

What should you do?

A. Remove the NON EMPTY clauses.

B. Replace the CHAPTERS axis with the ROWS axis.

C. Replace the CHAPTERS axis with the SECTIONS axis.

D. Rewrite the query to display the Order Quantity on the COLUMNS axis, the Calendar Year on the ROWS axis, and the Category data on the PAGES axis.

Correct Answer: B

QUESTION 126

You develop a SQL Server 2008 Reporting Services (SSRS) solution.

You add a report that contains a single dataset and a query parameter.

You need to ensure that the report executes without user interaction from the Report Manager.

What should you do?

A. Configure the report to use snapshot execution.

B. Configure available values for the parameter from the dataset. 

C. Configure the default value for the parameter based on an expression.

D. Configure the parameter data to refresh whenever the parameter is refreshed.

Correct Answer: C

QUESTION 127

You develop a SQL Server 2008 Reporting Services (SSRS) report.

The report contains a parameter named @SalesTaxRateID.

You set the value of the @SalesTaxRateID parameter in the report by selecting a specific region.

You need to configure the report to prevent users from modifying the value of the @SalesTaxRateID parameter.

What should you do?

A. Clear the Prompt value.

B. Set the Hidden property.

C. Set the Internal property.

D. Remove the default values.

Correct Answer: C

QUESTION 128

You develop a SQL Server 2008 Reporting Services (SSRS) report.

The report contains a parameter named Category that is used to filter a list of products. The list uses a dataset that contains the following query:

SELECT p.ProductID, p.Name, p.ListPrice

FROM Production.Product AS p

WHERE p.Category = @Category;

You modify the report parameter to allow multiple values.

You need to modify the report to help search for multiple Category values by using minimum amount of development effort.

What should you do?

A. Implement the query in a view.

B. Implement the query in a table-valued user-defined function.

C. Modify the query to use the IN operator instead of the = operator.

D. Implement the query in a stored procedure that uses dynamic SQL to execute the query.

Correct Answer: C

QUESTION 129

You are developing a sales report by using SQL Server 2008 Reporting Services (SSRS).

The report contains a table.

You need to implement a technology that allows users to open a new report when they click on a field in a table.

Which SSRS technology should you use?

A. Subreport 

B. Tablix Data Region

C. Drillthrough action

D. Interactive Sorting

Correct Answer: C

QUESTION 130

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 TruE. 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 False.

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

Correct Answer: A

QUESTION 131

You design a report by using SQL Server 2008 Reporting Services (SSRS).

The report uses a Table data region and contains a dataset. The dataset has a column named LastName.

You need to ensure that the report items are grouped by the first character in the LastName column.

Which expression in the Group properties dialog box should you use?

A. =First(Fields!LastName)

B. =Fields!LastName.Value

C. =First(Fields!LastName.Value)

D. =Fields!LastName.Value.Substring(0,1)

Correct Answer: D

QUESTION 132

You design a report by using SQL Server 2008 Reporting Services (SSRS).

The report will be included in a Windows Forms application that is distributed to remote users. These users have no access to the report server.

You need to ensure that the application renders the report correctly.

What should you do?

A. Use the ReportViewer control along with the LocalReport property.

B. Use the ReportViewer control along with the RemoteReport property. 

C. Use the Render method of the Reporting Services Simple Object Access Protocol (SOAP) API.

D. Use the RenderStream method of the Reporting Services Simple Object Access Protocol (SOAP) API.

Correct Answer: A

QUESTION 133

You create a report by using SQL Server 2008 Reporting Services (SSRS).

The report is complex and takes several minutes to generate.

You need to configure the report server to cache the report for 30 minutes after the initial request.

What should you do?

A. Implement report snapshots.

B. Implement report execution caching.

C. Configure the data source to use snapshot isolation.

D. Configure the data source to use a database snapshot.

Correct Answer: B

QUESTION 134

You maintain a report in a SQL Server 2008 Reporting Services (SSRS) instancE.The report contains query parameters that allow users to filter the contents of the report. The report queries the data source randomly.

You configure the report to be rendered from a report execution snapshot.

You need to ensure that users can continue to use parameters to filter the report.

What should you do?

A. Add a default value to all parameters.

B. Modify the datasets to use the Table query type.

C. Remove the filters from the dataset queries. Use the parameters to filter the datasets.

D. Move the queries to the stored procedures. Use the stored procedures to populate and filter the datasets of the report.

Correct Answer: C

QUESTION 135

You create a report by using SQL Server 2008 Reporting Services (SSRS).

You plan to deploy the report on a report server. The report will be delivered on a regular basis to a dynamic list of subscribers.

You need to create a new subscription on the report server.

What should you do?

A. Select the report you created, and then click the New Subscription button.

B. Select the My Subscriptions page, and then click the New Data-driven Subscription button.

C. Select the report you created. Click the Subscriptions tab, and then click the New Data-driven Subscription button.

D. Select the report you created. Click the Subscriptions tab, and then click the New Subscription button.

Correct Answer: C

   

QUESTION 136

You develop a SQL Server 2008 Reporting Services (SSRS) report.

The report uses the “User!UserID” expression in a text box within the report header.

You need to ensure that data-driven subscriptions successfully execute for this report.

What should you do?

A. Remove the expression from the report header.

B. Configure the report to use no credentials.

C. Configure the report to use stored credentials.

D. Modify the expression in the report header as “User!UserName.”

Correct Answer: A

QUESTION 137

You maintain a SQL Server 2008 Reporting Services (SSRS) instance.

Your instance supports several different rendering extensions.

You need to configure the server so that all reports render only to Microsoft Excel.

What should you do?

A. Modify the Global.asax filE.B.Modify the Web.config file.

B. Modify the RSMgrPolicy.config file.

C. Modify the RSReportServer.config file.

Correct Answer:

QUESTION 138

You install and configure SQL Server 2008 Reporting Services (SSRS) instance.

The users of the report server plan to deliver reports through e-mail messages.

You need to configure the report server for e-mail message delivery by using a remote SMTP server.

What should you do?

A. Modify the rssrvpolicy.config file.

B. Modify the RSReportServer.config file.

C. Modify the Web.config file.

D. Modify the Global.asax file.

Correct Answer: B

QUESTION 139

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 to use separate ReportServer databases.

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

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

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

Correct Answer: B

QUESTION 140

You are managing the security in SQL Server 2008 Reporting Services (SSRS).

You assign a new user to a role.

You need to grant the permissions to the user to publish reports and manage content only in a private folder.

What should you do?

A. Assign the user to the Publisher role.

B. Assign the user to the My Reports role.

C. Assign the user to the Content Manager and the Browser roles.

D. Assign the user to the Content Manager and the Report Builder roles.

Correct Answer: B