TS:MS SQL Server 2005 Business Intelligence-Implem & Mainte

1.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package has the following features: Two Data Flow tasks and two Control Flow tasks A success constraint between each Data Flow task and the subsequent Control Flow task The package uses one transaction for all the tasks. You need to configure the package so that the two Data Flow tasks use their own transactions. You also need to ensure that each Control Flow task is enlisted in the same transaction as its preceding Data Flow task. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)
A.Change the TransactionOption property to Supported for the package.
B.Change the TransactionOption property to Required for the package.
C.Change the TransactionOption property to Supported for each Data Flow task.
D.Change the TransactionOption property to Required for each Data Flow task.
E.Change the TransactionOption property to Supported for each Control Flow task.
F.Change the TransactionOption property to Required for each Control Flow task.
Correct:A D E
2.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package contains 30 Data Flow tasks and 30 Control Flow tasks. The package runs slower than expected. You need to capture the start time, the finish time, and the elapsed time for the validation and execution of the package. What should you do?
A.Enable the OnProgress event handler and use the Control Flow tasks to write the information to a log.
B.Monitor the Progress tab during the execution of the package, and then monitor the Execution Results tab.
C.Use the Performance Monitor tool to capture the counters from the SQL Server: SSIS Service object. Analyze the output for the required information.
D.Use the Performance Monitor tool to capture the counters from the SQL Server: SSIS Pipeline object. Analyze the output for the required information.
Correct:B
3.Microsoft SQL Server Management Studio (SSMS) is installed on your workstation. Microsoft Business Intelligence Development Studio (BIDS) is not installed on your workstation. You need to create a package that must meet the following requirements: It must be transactional. It must be optimized for 20 tables. It must be stored securely in the msdb database of a remote server. What should you do?
A.Create the package by using DTS Designer.
B.Create the package by using the Package Migration Wizard.
C.Create the package by using the Microsoft SQL Server Import and Export Wizard.
D.On the Microsoft SQL Server 2005 Integration Services (SSIS) menu, click the Create Package submenu.
Correct:C
4.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. While testing, a Transact-SQL user-defined function causes duplicate key values that stop the transformation. To permit the transformation to continue, the package must perform the following tasks: 1. Ascertain which rows are affected. 2. Insert the rows that fail into a table. 3. Continue with the process. You need to change certain properties in the package to meet the requirements. Which three tasks should you perform? (Each correct answer presents part of the solution. Choose three.)
A.Choose the Redirect Row option for the Error property on the key column.
B.Choose the Ignore Failure option for the Error property on the key column.
C.Choose the Fail Component option for the Error property on the key column.
D.Add a DataReader source to use a new Data Flow destination when the Failure constraint is fired.
E.Add a DataReader source to use a new Data Flow destination when the Completion constraint is fired.
F.Edit the Error Output properties for each DataReader source and configure each data source for error handling.
Correct:A D F
5.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package uses at least one sequence container in one transaction. Each transaction controls a Data Flow task and a Control Flow task. Each Data Flow task has a Success constraint. The Control Flow task follows the Success constraint. The package must include the following requirements: Each Data Flow task must use its own transaction. The Control Flow task and the Data Flow task that precedes it must succeed or fail as an atomic unit. A restart point must restart each Data Flow task and the Control Flow task that follows it as an atomic unit. You need to make changes in the control flow designer to meet the outlined requirements. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A.Add all the Data Flow tasks and the Control Flow tasks to one sequence container.
B.Add a sequence container for each Control Flow task and the Data Flow task that precedes it.
C.Change the FailPackageonFailure property to true for each new sequence container that is added to the package.
D.Change the FailPackageonFailure property to false for each new sequence container that is added to the package.
Correct:B C
6.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. An Analysis Services Processing task in the package requires data from a file. The file is exported to a file share. If the file is not available on the file share, the Analysis Services Processing task must import data from a remote Microsoft SQL Server 2005. You need to add steps to the package to import the data. What should you do? (To answer, move the appropriate steps from the list of steps to the answer area and arrange them in the correct order.)

Correct:
Green choice1---->Yellow Choice1
Green choice3---->Yellow Choice2
Green choice6---->Yellow Choice3
7.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The OLE DB data source for the package is a database that is updated frequently. You need to create a package that accomplishes the following tasks: Implement the IRowsetFastLoad interface. Support various data flow destinations. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A.Use an OLE DB data flow destination.
B.Use a Recordset data flow destination.
C.Use a DataReader data flow destination.
D.Create separate data flow destinations within the same Data Flow task.
Correct:A D
8.Your company's business intelligence (BI) model uses Microsoft SQL Server 2005 Integration Services (SSIS). There are 20 packages that use the same configuration. The package configuration is stored in an XML file as a variable. You need to track the changes that affect a package that uses one variable. You also need to modify the package to provide automatic notification by e-mail whenever there are changes to the variable. What should you do? (To answer, move the appropriate steps from the list of steps to the answer area and arrange them in the correct order.)

Correct:
Green choice5---->Yellow Choice1
Green choice3---->Yellow Choice2
Green choice4---->Yellow Choice3
9.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package contains a single OLE DB data source. The package must be deployed on 10 servers. The package uses the XML configuration file to connect to a pre-assigned server. You need to ensure that the package uses the correct server at runtime. What should you do? (To answer, move the appropriate steps from the list of steps to the answer area and arrange them in the correct order.)

Correct:
Green choice4---->Yellow Choice1
Green choice2---->Yellow Choice2
Green choice6---->Yellow Choice3
10.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package is named UpdateTable.dtsx and contains a variable named runID. The package must run by using the dtexec utility. The runID variable must be set at runtime to a value of 5. You need to meet the outlined requirements. What should you do?
A.Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /SET \packages.variables[runID].Value;5.
B.Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /SET \packages.variables.runID=5.
C.Create a text file that specifies the variable name and value in the format package.variables[runID].Value = 5. Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /COM where is the name of the file you just created.
D.Create a text file that specifies the variable name and value in the format runID=5. Execute dtexec /F "c:\ssisPackages\UpdateTable.dtsx" /COM where is the name of the text file you just created.
Correct:A
11.You create 25 Microsoft SQL Server 2005 Integration Services (SSIS) packages on the development server. You decide to deploy the SSIS packages on the test server. You need to create a repeatable process that will allow you to deploy the packages to the package store on the test server if the packages change. You also need to ensure that this is achieved with minimum effort. What should you do?
A.Use Microsoft SQL Server Business Intelligence Development Studio (BIDS) to save each package to the remote server.
B.Use the dtutil utility in a batch file to deploy each of the package .dtsx files to the remote server with the /Fi option.
C.Copy the Microsoft SQL Server Business Intelligence Development Studio (BIDS) files to the remote server and build the SSIS project on the remote server.
D.Create a package deployment utility in Microsoft SQL Server Business Intelligence Development Studio (BIDS) and use the Deployment Wizard to deploy all the packages.
Correct:D
12.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. You need to ensure that the package came from a trusted source. What should you do?
A.Change the CheckSignatureOnLoad property of the package to false.
B.Change the CheckSignatureOnLoad property of the package to true.
C.Create a certificate. Change the CheckSignatureOnLoad property of the package to false.
D.Create a certificate. Change the CheckSignatureOnLoad property of the package to true.
Correct:D
13.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. You create a table and populate the table with the required data. You receive an error message "destination table not found" when you run the package without the package destination objects. You need to set a package property that runs the package without giving the "destination table not found" error message when the configuration setting of the Connection Manager fails. Which package property should you set?
A.DelayValidation = true
B.MaximumErrorCount = 2
C.DisableEventHandler = true
D.FailPackageOnFailure = false
Correct:A
14.You are designing a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package is connected to a database on your development server by using a connection manager. You decide to use the DTExecUI utility to run the package. You need to change the connection of the connection manager to a different server at run time without using a package configuration file. What should you do?
A.Specify an appropriate command file by using the Command Files dialog box.
B.Specify an appropriate Connection String variable in the Set Values dialog box.
C.Specify an appropriate package configuration by using the Configurations dialog box.
D.Specify a different connection string for the connection manager by using the Connection Manager dialog box.
Correct:D
15.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) accounting report. Every other line of output in the report table must have a green background. You need to use the appropriate expression for the BackgroundColor property of the table. Which expression should you use?
A.=Iif((RowNumber("Odd")),"Green","White")
B.=Iif((RowNumber(Nothing)),"Green","White")
C.=Iif((RowNumber(Nothing) MOD 2),"Green","White")
D.=Iif((RowNumber(InScope("Odd"))),"Green","White")
Correct:C
16.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report must use a custom assembly to perform real-time lookup and currency conversion. The assembly has a static class named CurrencyConversion that exists in the namespace FinancialCalc. The class has a method named ToEUR that requires two arguments, Currency and CurrencyCode. You need to reference the ToEUR method in an expression to convert USD to EURO when the report runs. Which expression should you use?
A.=Code.FinancialCalc.CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")
B.=Code!FinancialCalc.CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")
C.=FinancialCalc.CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")
D.=FinancialCalc!CurrencyConversion.ToEUR (Fields!Currency.Value,"USD")
Correct:C
17.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report model. Users must develop their own SSRS reports by using your report model in the Report Builder tool. The data source for the report model is a Microsoft SQL Server 2000 database that contains 900 tables and 700 views. You need to build a report model that allows users access to only the 10 tables that they require for reporting. What should you do?
A.Select the Create roles rule when you generate the report model.
B.Create a data source view and select the Restrict to Schema(s) option.
C.Create a data source view and select only the required tables and views.
D.Clear the Create entities for all tables rule when you generate the report model.
Correct:C
18.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. You notice that while printing a report, blank pages are printed after every page that contains the data. But the report appears correctly when you view it on screen. You need to ensure that the blank pages are not printed. Which report property should you use?
A.Page Size
B.Grid Spacing
C.Interactive Size
D.DataTransform
Correct:A
19.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. You create a report parameter named DisplayCol that contains the following properties: Data Type: Integer Prompt: Display Column Available Values: Non-queried with the following Label/Value pairs: oYes/1 oNo/0 Default Values: Null The report parameter is used to control the visibility of the column. You need to use the appropriate expression in the Hidden property for the column. Which expression should you use?
A."=Parameters!DisplayCol.Value"
B."=Parameters!rc:DisplayCol.Value"
C."=Iif(Parameters!DisplayCol.Value=1,False,True)"
D."=Iif(Parameters!rc:DisplayCol.Value=1,False,True)"
Correct:C
20.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report is based on the multidimensional data from a Microsoft SQL Server 2005 Analysis Services (SSAS) cube. You need to use the Report Wizard to create a Matrix report by using the [Change Count] measure in the Details field. Which Multidimensional Expressions (MDX) query should you use?
A.SELECT NON EMPTY { [Measures].[Change Count] } ON ROWS, DIMENSION PROPERTIES MEMBER_CAPTION { [Measures]. [Change Count] } ON COLUMNS
B.SELECT NON EMPTY { [Measures].[Change Count] } ON COLUMNS, DIMENSION PROPERTIES MEMBER_CAPTION { [Measures]. [Change Count] } ON ROWS
C.SELECT DIMENSION PROPERTIES MEMBER_CAPTION { [Measures].[Change Count] } ON ROWS
D.SELECT DIMENSION PROPERTIES MEMBER_CAPTION { [Measures].[Change Count] } ON COLUMNS
Correct:B
21.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report that supports drilldown. You need to modify the report so that the detailed data is hidden when the report is initially rendered. What should you do?
A.Add the detailed data to a Group. Set the Hidden property of the Group to true.
B.Add the detailed data to a Matrix. Set the IsToggleChild property of the Matrix to true.
C.Add the detailed data to a Subreport. Set the Hidden property of the Subreport to true.
D.Add the detailed data to a Subreport. Set the IsToggleChild property of the Subreport to true.
Correct:A
22.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report uses a dataset that is constructed from an SQL table named Employees. The table has an employeeID field and a managerID field. The managerID field in each row represents the employeeID field. You execute the following Transact-SQL query to get the dataset from the report: SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName) as Name, e.EmployeeID, e.ManagerID FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID You need to display the dataset as a hierarchical, organizational chart, as shown in the Exhibit. (Click the Exhibit button.) What should you do?

A.Use the Level function in the Left property of the Padding property of the cell.
B.Group the report by the employeeID field and set the managerID field as the group parent.
C.Group the report by the managerID field and set the employeeID field as the group parent. Use the Level function in the Left property of the Padding property of the cell.
D.Group the report by the employeeID field and set the managerID field as the group parent. Use the Level function in the Left property of the Padding property of the cell.
Correct:D
23.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report has three columns in a table. The columns are named MachineName, MachineMfr, and MachineModel. The report navigation options must allow users to click on a row from the MachineName column and drill through to another report, and then pass the contents of the MachineName column as a parameter named MachineName. You need to configure the report navigation options to satisfy the user requirements. Which three tasks should you perform? (Each correct answer presents part of the solution. Choose three.)
A.On the General tab, select the data region.
B.On the Visibility tab, select the expression for the report item.
C.On the Navigation tab, select the report for the Hyperlink action.
D.On the Navigation tab, select the bookmark for the Hyperlink action.
E.On the Navigation tab, add an expression to the Hyperlink action parameters.
F.On the Navigation tab, add the name and the value to the Hyperlink action parameters.
Correct:D E F
24.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The report contains financial data. An input parameter named TargetMfr is present in the report. When a value in the Machine_Mfr field matches the selected value in the input parameter, the color of the output in a column is red. Otherwise, the color of the output is green. You need to set the TextBox that contains the color property of the Machine_Mfr field to the appropriate expression. Which expression should you use?
A.=Iif(Fields!Machine_Mfr = Parameters!rc:TargetMfr.Value,"Red","Green")
B.=Iif(Fields!Machine_Mfr.Value = Parameters!TargetMfr.Value,"Red","Green")
C.=Iif(InScope(Machine_Mfr),"Red","Green")
D.=Iif(InScope(TargetMfr),"Red","Green")
Correct:B
25.You are designing a Microsoft SQL Server 2005 Reporting Services (SSRS) report. The query that builds the dataset for the report uses a WHERE clause to filter the rows. The report parameter must perform the following actions: Use the new parameterized query. Run without any user input. Allow users to change the value that is passed to the query by selecting a value from a drop-down list. You need to modify the settings of the Report Properties dialog box to meet the outlined requirements. Which four tasks should you perform? (Each correct answer presents part of the solution. Choose four.)
A.Ensure that the Internal check box is selected.
B.Ensure that the Default values property value is set to Null.
C.Ensure that the Prompt property is set to an appropriate value.
D.Ensure that the Data type property is set to an appropriate value.
E.Ensure that the Default values property value is set to Non-queried by using an appropriate default value.
F.Ensure that the Available values property value is set to Non-queried by using the appropriate labels and values.
Correct:C D E F
26.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. The project contains a cube named Finance. The Finance cube contains the following objects: A measure group named SalesMeasures. A measure named SalesAmount. A time dimension named DimTime. A time dimension hierarchy named Calendar that contains Year on the first level, Quarter on the second level, and Month on the third level. You need to create a trend expression that displays the increase and decrease in the SalesAmount measure for the current DimTime member, irrespective of the level of the Calendar hierarchy. Which expression should you use?
A.Case When [SalesMeasures].[SalesAmount]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then1 When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then -1 End
B.Case When [SalesMeasures].[SalesAmount],[DimTime].[Calendar]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then1 When [SalesMeasures].[SalesAmount],[DimTime].[Calendar]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then -1 End
C.Case When [SalesMeasures].[SalesAmount]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then1 When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then -1 End
D.Case When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then1 When [SalesMeasures].[SalesAmount]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then -1 End
Correct:A
27.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a cube that contains the following objects: A time dimension named Time that has an attribute named TimeKey. A fact table named FactOrders. The Time dimension contains different hierarchies. Two of the columns of the fact table link to the attribute. You need to view the measures for different hierarchies for both the columns in the fact table. What should you do?
A.Create a single dimension usage relationship between the Time dimension and the FactOrders fact table by defining a regular relationship.
B.Create a single dimension usage relationship between the Time dimension and the FactOrders fact table by defining a many-to-many relationship.
C.Create two dimension usage relationships between the Time dimension and the FactOrders fact table by defining a regular relationship for both the dimension usages.
D.Create two dimension usage relationships between the Time dimension and the FactOrders fact table by defining a many-to-many relationship for both the dimension usages.
Correct:C
28.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. The data source for a cube has tables as shown in the following exhibit. (Click the Exhibit button.) The cube contains the following objects: A dimension named DimAccounts that uses a table named Accounts. A dimension named DimCustomers that uses a table named Customers. A measure group named Transactions that uses a fact table named Transactions. A measure group named Customer Accounts that uses a fact table named CustomerAccounts. You need to create a cube dimension to browse through the measures in the Transactions fact table by using the DimCustomers dimension. What should you do?

A.Create a referenced relationship and set the intermediate dimension as DimAccounts.
B.Create a referenced relationship and set the intermediate dimension as DimCustomers.
C.Create a many-to-many relationship and set the intermediate measure group to Transactions.
D.Create a many-to-many relationship and set the intermediate measure group to Customer Accounts.
Correct:D
29.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a cube named ExpenseData that uses a dimension named Expense. The Expense dimension is a parent-child dimension with: a key attribute named Expense. A parent attribute named Expenses of type Expense. The project contains a role named Users. You need to modify the Users role to achieve the following tasks: Restrict role members from viewing the cell values of Expenses under the expense Managerial Expenses. Ensure that all the labels for the Expense dimension are visible. What should you do?
A.Change the access to Read/Write for dimension Expense for the ExpensesData cube.
B.On the Cell Data tab, select the Enable read permissions check box. Enter the [Expense].[Expenses].Parent.Name<>"Managerial Expenses" expression.
C.On the Cell Data tab, select the Enable read permissions check box. Enter the [Expense].[Expenses].Parent.Name="Managerial Expenses" expression.
D.On the Dimension Data tab, select the dimension Expense in the ExpensesData cube in the Dimension drop-down box, and then cancel the selection of all the members under the member Managerial Expenses.
Correct:B
30.You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a dimension that contains a parent-child hierarchy. The dimension has a key attribute named Employee and a parent attribute named Employees. You need to ensure that the dimension identifies the topmost employee member of the hierarchy as the root member. What should you do?
A.Set the IsAggregatable property of the Employee attribute to true.
B.Set the IsAggregatable property of the Employee attribute to false.
C.Set the IsAggregatable property of the Employees attribute to true.
D.Set the IsAggregatable property of the Employees attribute to false.
Correct:D