Msbi 70-448

2.

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?

Configure the FTP tasks to support transactions.
Configure the Execute SQL tasks to support transactions.
Move all the FTP tasks to a sequence container
Configure the package to use a checkpoint.

Correct Answer

A. Configure the package to use a checkpoint.

Explanation

The correct answer is to configure the package to use a checkpoint. This means that the package will save its progress at certain points during execution, allowing it to restart from the last checkpoint in case of failure. This is the most efficient and reliable way to ensure that the package can continue from where it left off after downloading the files from the FTP server. Configuring the FTP tasks or Execute SQL tasks to support transactions would not address the need to restart at the point of failure. Moving the FTP tasks to a sequence container would not provide the necessary functionality for restarting the package.

Rate this question:

3.

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?

Cluster the SSIS service. Add the SSIS service to its own cluster resource group.
Cluster the SSIS service. Add the SSIS service to the SQL Server cluster resource group.
Implement checkpoints within your package. Restart the package whenever a failover occurs.
Implement transactions within your package. Restart the package whenever a failover occurs.

Correct Answer

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

Explanation

To ensure that the package can be resumed at the point of failure during a failover, the recommended approach is to implement checkpoints within the package. Checkpoints allow the package to save its progress at specific points, so if a failover occurs, the package can be restarted from the last checkpoint instead of the beginning. This saves time and resources by avoiding the need to rerun the entire package. Restarting the package whenever a failover occurs ensures that it continues execution seamlessly after the failover event.

Rate this question:

4.

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?

DontSaveSensitive
EncryptAllWithUserKey
EncryptAllWithPassword
ServerStorage

Correct Answer

A. EncryptAllWithPassword

Explanation

The correct answer is "EncryptAllWithPassword". This protection level ensures that sensitive information, such as connection strings, is encrypted using a password. Users can view and execute the package, but they will need to enter the password to access the sensitive information. This prevents unauthorized access to the sensitive data in the package.

Rate this question:

5.

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?

Subreport
Tablix Data Region
Drillthrough action
Interactive Sorting

Correct Answer

A. Drillthrough action

Explanation

The correct answer is Drillthrough action. Drillthrough action is a feature in SSRS that allows users to navigate from one report to another by clicking on a specific field in a report. In this case, when users click on a field in the table, they will be able to open a new report. This feature provides an interactive and dynamic way for users to access additional information or details related to the data they are viewing.

Rate this question:

6.

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 Fuzzy Lookup transformation
Lookup transformation along with the Full cache option
Lookup transformation along with the No cache option
Lookup transformation along with the Partial cache option

Correct Answer

A. Lookup transformation along with the Full cache option

Explanation

The correct answer is "Lookup transformation along with the Full cache option". This option should be selected because it allows the data flow to execute in the minimum amount of time. The Full cache option loads the entire reference table into memory before the data flow begins, ensuring that each row in the reference table is readily available for the match. This eliminates the need for frequent disk reads and improves the performance of the data flow.

Rate this question:

7.

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?

Set the LogDir server property to a valid path.
Set the QueryLogSampling server property to 10.
Set the AllowedBrowsingFolders server property to include the folder from the LogDir setting.
Set the QueryLogConnectionString server property to a valid connection string.

Correct Answer

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

Explanation

To enable query logging for the Usage-Based Optimization Wizard, you need to set the QueryLogConnectionString server property to a valid connection string. This connection string will specify the location where the query logs will be stored. By setting this property, you can ensure that the query logs are captured and available for analysis and optimization purposes.

Rate this question:

8.

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?

Use the Data Viewer tool.
Use the Data Profiling task.
Use the Audit transformation.
Use breakpoints in the Script component.

Correct Answer

A. Use the Data Viewer tool.

Explanation

Using the Data Viewer tool allows you to monitor the data that passes through each path in the data flow in SSIS. It provides a visual representation of the data and allows you to inspect the data at different points in the data flow. This tool helps to ensure that data flow logic is retained and provides a way to troubleshoot and debug the package during development. The Data Profiling task is used for analyzing data quality and does not provide real-time monitoring of data flow. The Audit transformation is used for auditing data changes and does not provide real-time monitoring. Using breakpoints in the Script component can be used for debugging, but it does not provide real-time monitoring of data flow.

Rate this question:

9.

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?

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

Correct Answer

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

Explanation

The expression =Fields!LastName.Value.Substring(0,1) should be used in the Group properties dialog box. This expression will group the report items by the first character in the LastName column. It uses the Substring function to extract the first character (starting at index 0) from the LastName value.

Rate this question:

10.

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 f 03 ParallelPeriod 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?

KPIValue( "GPMargin" ) >?( KPIValue( "GPMargin" ),
KPITrend( "GPMargin" ) >?( KPIValue ( "GPMargin" ),
KPIStatus( "GPMargin" ) >?( KPIValue( "GPMargin" ),
KPIGoal( "GPMargin" ) >?( KPIValue( "GPMargin" ),

Correct Answer

A. KPIValue( "GPMargin" ) >?( KPIValue( "GPMargin" ),

Explanation

The MDX code segment "KPIValue( "GPMargin" ) >?( KPIValue( "GPMargin" )" should be inserted at line 02. This code segment compares the current value of the KPI "GPMargin" with the previous quarter's value of "GPMargin" using the ">" operator. This will allow the Trend expression of the KPI to show the difference in values compared to the previous quarter.

Rate this question:

11.

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] 05 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?

NATURAL PREDICTION JOIN
PREDICTION JOIN
INNER JOIN
OPENQUERY

Correct Answer

A. NATURAL PREDICTION JOIN

Explanation

The code fragment "NATURAL PREDICTION JOIN" should be inserted at line 05. This will ensure that the input columns automatically map to the columns in the data mining structure.

Rate this question:

12.

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?

Use the Synchronize Database Wizard.
Copy the appropriate files from the development server to the production server.
Detach the production database. Copy the appropriate files to the production server, and then attach the database on the production server
Detach the production database. Copy the appropriate files to the production server, and then attach the database on the production server

Correct Answer

A. Use the Synchronize Database Wizard.

Explanation

The best option to minimize the impact on users while deploying new functionality from the development server to the production server is to use the Synchronize Database Wizard. This wizard allows for a controlled and synchronized deployment process, ensuring that only the necessary changes are applied to the production server while preserving the existing data and structure. This method helps to minimize downtime and disruption for users accessing the cube.

Rate this question:

13.

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?

Configure Kerberos authentication.
Configure Analysis Services for HTTP authentication.
Set the AnonymousConnectionsEnabled policy to True.
Set the Security\RequireClientAuthentication property to True.

Correct Answer

A. Configure Kerberos authentication.

Explanation

Configuring Kerberos authentication is the correct answer because the scenario involves a remote ASP.NET application that uses Windows Authentication to access the SSAS database. Kerberos authentication is the recommended authentication method for Windows-based applications in a distributed environment. By configuring Kerberos authentication, the application can authenticate the users and securely access the SSAS database while also applying the necessary security restrictions based on the configured security roles.

Rate this question:

14.

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?

Create the variable at the package scope.
Create the variable at the Data Flow Task scope.
Create the variable at the Execute SQL Task scope.
Create the variable at the Sequence Container scope.

Correct Answer

A. Create the variable at the Sequence Container scope.

Explanation

Creating the variable at the Sequence Container scope ensures that it is only available to the Sequence Container, the Data Flow Task, and the Execute SQL Task. By creating the variable at the package scope, it would be accessible to all components within the package, which may not be desired. Similarly, creating the variable at the Data Flow Task or Execute SQL Task scope would limit its availability only to those specific tasks, which may not fulfill the requirement of making it available to all three components. Therefore, creating the variable at the Sequence Container scope is the correct choice.

Rate this question:

15.

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?

Temporarily add your account to the Northern Region role.
Add the User ID=Northern Region; parameter to the connection string.
Add the Roles=Northern Region; parameter to the connection string.
Select the Enable Visual Totals check box for the Northern Region role.

Correct Answer

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

Explanation

Adding the "Roles=Northern Region;" parameter to the connection string will ensure that users in the Northern Region role can only view data for their region. This parameter specifies the roles that the user is a member of, and by including only the Northern Region role, it restricts the data that the user can access to only their region.

Rate this question:

16.

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 Office Excel worksheet by using the Excel rendering engine. You need to ensure that the report columns are properly aligned when rendering to Excel. Which type of data region should you use?

List
Table
Rectangle
Matrix

Correct Answer

A. Table

Explanation

A table should be used as the data region in order to ensure that the report columns are properly aligned when rendering to Excel. Tables are specifically designed to display tabular data and provide a structured layout for columns and rows. This allows for consistent alignment of data when exporting to different formats, such as Excel.

Rate this question:

17.

You maintain a SQL Server 2008 Reporting Services (SSRS) instance. The network security model for the domain that hosts the instance changes. You need to ensure that the instance will only support Kerberos authentication. What should you do?

Edit the RSReportServer.config file.
Edit the rssrvpolicy.config file
Edit the Site Settings of the instance by using the Report Manager.
Edit the Server Settings by using Microsoft SQL Server Management Studio (SSMS).

Correct Answer

A. Edit the RSReportServer.config file.

Explanation

To ensure that the SQL Server 2008 Reporting Services (SSRS) instance only supports Kerberos authentication, you need to edit the RSReportServer.config file. This configuration file contains settings for the SSRS instance, including authentication options. By modifying the configuration file, you can specify that only Kerberos authentication is allowed, ensuring that other authentication methods, such as NTLM, are disabled.

Rate this question:

18.

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?

Modify the properties of the package to use the DontSaveSensitive package protection level.
Modify the deployment manifest file in Microsoft Notepad, and then modify the connection string manually.
On the Connection Managers tab of the Execute Package utility, select the Connection Manager check box, and then modify the connection string
Modify the properties of the package to use the ServerStorage package protection level.

Correct Answer

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

Explanation

The correct answer is to select the Connection Manager check box on the Connection Managers tab of the Execute Package utility, and then modify the connection string. This is because the package is failing to execute due to the inability to connect to the SQL_DEV1 server. By selecting the Connection Manager check box and modifying the connection string, the package will be configured to refer to the SQL_PROD1 server instead, allowing it to execute successfully.

Rate this question:

19.

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?

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

Explanation

The correct answer is to use the ALTER MINING STRUCTURE statement to add a mining model named "Decision Tree" to the existing mining structure "Bike Buyer". This statement specifies the columns from the mining structure that will be used in the mining model, including [Customer Key], [Age], [Bike Buyer], [Commute Distance], and [Yearly Income]. The model will use the Microsoft Decision Trees algorithm. The WITH DRILLTHROUGH option allows users to view detailed information about the data used in the model.

Rate this question:

20.

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?

Edit the XML configuration file.
Use an indirect XML configuration.
Run the package on the same server as the third-party database server.
Use a Microsoft ADO.NET connection manager instead of an OLE DB connection manager.

Correct Answer

A. Edit the XML configuration file.

Explanation

The correct answer is to edit the XML configuration file. This is because the error message indicates a login failure, which suggests that the connection information in the configuration file is incorrect. By editing the XML configuration file, you can provide the correct login credentials for the third-party database server and resolve the login failure issue.

Rate this question:

21.

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?

Use the Deployment utility.
Use the SETX command line utility.
Use the DTUTIL command line utility.
Use the DTEXEC command line utility.

Correct Answer

A. Use the DTEXEC command line utility.

Explanation

The DTEXEC command line utility is used to execute SSIS packages and allows for the application of configurations to the deployed package. This utility provides options to specify XML configuration files, allowing for the configuration to be applied to the package during execution. Therefore, using the DTEXEC command line utility is the correct choice to apply the configuration to the deployed package.

Rate this question:

22.

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?

Copy the assembly to the appropriate folder.
Add the assembly to the Global Assembly Cache.
Register the assembly by using the REGSVR32 utility.
Register the assembly by using the Assembly Registration utility (REGASM.EXE).

Correct Answer

A. Copy the assembly to the appropriate folder.

Explanation

To add the custom data flow component to the toolbox, you should copy the assembly of the component to the appropriate folder. By doing this, the component will become available in the toolbox and the Choose Toolbox Items dialog box, allowing you to add it to the package.

Rate this question:

23.

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?

Real-time hybrid online analytical processing (HOLAP)
Real-time relational online analytical processing (ROLAP)
Automatic multidimensional online analytical processing (MOLAP)
Scheduled multidimensional online analytical processing (MOLAP)

Correct Answer

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

Explanation

The correct answer is Real-time hybrid online analytical processing (HOLAP). HOLAP is a storage mode that allows for a combination of both real-time relational online analytical processing (ROLAP) and automatic multidimensional online analytical processing (MOLAP). This means that the latest version of the source detail data is always available for queries, while still benefiting from the performance advantages of storing aggregations in a multidimensional format. This makes HOLAP the ideal choice for meeting both requirements specified in the question.

Rate this question:

24.

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?

MSAS 2008: Memory
MSAS 2008: Cache
MSAS 2008: Proc Aggregations
MSAS 2008: Storage Engine Query

Correct Answer

A. MSAS 2008: Proc Aggregations

Explanation

The object that should be used to collect information related to the temporary file usage when the database is processed is "MSAS 2008: Proc Aggregations". This object specifically focuses on the processing of aggregations, which can result in temporary file usage. By monitoring this object, you can gather performance information related to temporary file usage during the processing of the database.

Rate this question:

25.

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?

Set the BlockedSignatureStates registry entry to NoAdministrativeRestriction
Set the package protection level on all packages to DontSaveSensitive.
Set the package protection level on all packages to EncryptSensitiveWithPassword.
Set the BlockedSignatureStates registry entry to Block invalid and untrusted signatures and unsigned packages

Correct Answer

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

Explanation

To ensure that the server executes only correctly signed packages, you should set the BlockedSignatureStates registry entry to "Block invalid and untrusted signatures and unsigned packages". This will prevent the execution of any packages that are not correctly signed or do not have a valid signature. By blocking these packages, you can ensure that only trusted and signed packages are executed on the server.

Rate this question:

26.

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?

The cube
The partition
The dimension
The measure group

Correct Answer

A. The dimension

Explanation

To fix the Duplicate Key error and allow processing to continue, you should modify the dimension. This is because dimensions are responsible for defining the structure and organization of data in a cube. By modifying the ErrorConfiguration property of the dimension, you can specify how errors related to duplicate keys should be handled during processing.

Rate this question:

27.

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?

Reduce the overall width of the report.
Reduce the page size of the report.
Reduce the height of the report footer
Reduce the height of the report header

Correct Answer

A. Reduce the overall width of the report.

Explanation

Reducing the overall width of the report will prevent the rendering of blank pages when the report is rendered as a PDF file. This is because the current width of the report is causing the content to overflow onto the next page, resulting in blank pages for even-numbered pages. By reducing the width, the content will fit within the page boundaries and eliminate the blank pages.

Rate this question:

28.

You maintain a SQL Server 2008 Reporting Services (SSRS) database server. The instance contains a large number of reports that take a long time to execute. The reports contain multiple execution snapshots. You have a new production server along with SSRS installed. You need to migrate the reports and their snapshots to the production server in the minimum amount of time. What should you do?

Use the rs.exe utility.
Use the rskeymgmt.exe utility
Deploy the solutions by using Business Intelligence Development Studio (BIDS).
Copy the Report Server databases to the production server. Configure the server by using the Reporting Services Configuration tool.

Correct Answer

A. Copy the Report Server databases to the production server. Configure the server by using the Reporting Services Configuration tool.

Explanation

The most efficient way to migrate the reports and their snapshots to the production server in the minimum amount of time is to copy the Report Server databases to the production server and configure the server using the Reporting Services Configuration tool. This method allows for the quick transfer of all the necessary data and settings to the new server, ensuring that the reports and their snapshots are available without the need for additional deployment or configuration steps.

Rate this question:

29.

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?

SQL Server Destination
OLE DB Destination
OLE DB Command Transformation
Data Conversion Transformation

Correct Answer

A. OLE DB Command Transformation

Explanation

The OLE DB Command Transformation should be used in this scenario. This component allows you to execute a SQL command for each row in the data flow, which can be used to update existing records in the SQL Server database.

Rate this question:

30.

You implement a SQL Server 2008 Integration Services (SSIS) package. The safer , easier way to help you pass any IT exams. 11 / 36 The package contains the following components: ¡¤A data flow task contained in a For Loop containe ¡¤A Flat File destination componen that uses a Flat File connection manager ¡¤An OnError event handle 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?

Use the VersionGUID variable.
Use the ExecutionGUID variable.
Use the SourceID variable.
Use the TaskID variable.

Correct Answer

A. Use the VersionGUID variable.

Explanation

The VersionGUID variable is used to retrieve the version of the package. Since the requirement is to write the version of the package to a text file when an error occurs, using the VersionGUID variable would be the appropriate choice.

Rate this question:

31.

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?

Assign the users to the db_ssisoperator role.
Assign the users to the db_ssisltduser role.
Assign the users to a custom database role in the PROD database.
Assign the users to a custom database role in the MSDB database.

Correct Answer

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

Explanation

To ensure that only the appropriate users can modify and execute the deployed package, the users should be assigned to a custom database role in the MSDB database. This will allow you to grant the necessary permissions to the users specifically for the package in question, while restricting their access to other packages deployed on the server. Assigning the users to the db_ssisoperator role or the db_ssisltduser role may not provide the necessary level of control and customization required for this scenario. Assigning the users to a custom database role in the PROD database is also not the correct approach, as the package is deployed to the MSDB database.

Rate this question:

32.

You maintain an instance of SQL Server 2008 Reporting Services (SSRS). The instance contains several reports. You deploy a report project on the instance by using the Business Intelligence Development Studio (BIDS). You discover that the production data sources on the instance are replaced by the test data sources of the report project. You need to ensure that when the report project is deployed in future, the existing reports are unaffected. What should you do?

Configure the OverwriteDataSources property
Configure the TargetServerURL property
Configure the TargetDataSourceFolder property
Configure the TargetReportFolder property

Correct Answer

A. Configure the OverwriteDataSources property

33.

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

Use the SSIS log provider for the SQL Profiler.
Use the SSIS log provider for the text file.
Use a Flat File Destination component.
Use a Raw File Destination component.
Add an Error output to the Data Flow component

Correct Answer(s)

A. Use a Flat File Destination component.
A. Add an Error output to the Data Flow component

Explanation

To write all rows that generate errors to a text file in the SQL Server 2008 Integration Services (SSIS) package, you should perform the following two actions:

1. Use a Flat File Destination component: This component allows you to specify a text file as the destination for the error rows.

2. Add an Error output to the Data Flow component: By adding an Error output to the Data Flow component, you can redirect the error rows to the Flat File Destination component.

Using the SSIS log provider for the SQL Profiler or the SSIS log provider for the text file is not necessary for this specific requirement. The Raw File Destination component is also not needed as it is used for storing binary data.

Rate this question:

34.

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?

Set the AggregationFunction property of AccountBalance to Sum.
Set the AggregationFunction property of AccountBalance to ByAccount.
Set the AggregationFunction property of AccountBalance to LastNonEmpty.
Set the AggregationFunction property of AccountBalance to FirstNonEmpty.

Correct Answer

A. Set the AggregationFunction property of AccountBalance to LastNonEmpty.

Explanation

The correct answer is to set the AggregationFunction property of AccountBalance to LastNonEmpty. This is because the requirement is to display the closing account balance for a time period. The LastNonEmpty aggregation function will return the last non-empty value for the selected time period, which aligns with the requirement.

Rate this question:

35.

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?

Query Cube End
Query Subcube
MDX Script End
Get Data from Cache

Correct Answer

A. Query Subcube

Explanation

The trace event that should be used to identify the queries that take a long time to run is "Query Subcube". This event captures the duration of each query execution and can help identify queries that are taking a long time to complete. By analyzing the duration of the queries, you can pinpoint performance bottlenecks and optimize the queries or the underlying database structure to improve performance.

Rate this question:

36.

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: ¡¤Thecode segment compiles successfully. ¡¤The GetCmFromInches function can be called from other reports. What should you ?

Remove the Private keyword from the function definition.
Remove the Shared keyword from the function definition.
Declare the function as Public Shared instead of Private Shared.
Create a custom assembly by using Microsoft Visual Basic .NET. Declare the function as Public Shared.

Correct Answer

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

37.

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?

Remove the NON EMPTY clauses
Replace the CHAPTERS axis with the ROWS axis
Replace the CHAPTERS axis with the SECTIONS axis.
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

A. Replace the CHAPTERS axis with the ROWS axis

38.

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?

Set the OnlineMode property for the partition to Immediate.
Set the ProcessingMode property for the partition to Regular.
Set the OnlineMode property for the partition to OnCacheComplete.
Set the ProcessingMode property for the partition to LazyAggregations.

Correct Answer

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

Explanation

Setting the OnlineMode property for the partition to Immediate will ensure that SSAS can query relational data when multidimensional storage is being updated. This means that the partition will be available for querying as soon as the data is updated, without waiting for the entire update process to complete.

Rate this question:

39.

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?

SQL Server Configuration Manager
SQL Server Management Studio
SQL Server Error and Usage Reporting
SQL Server Business Intelligence Development Studio

Correct Answer

A. SQL Server Management Studio

Explanation

SQL Server Management Studio should be used to configure the analysis services query log for the SSAS instance. This tool provides a comprehensive interface for managing and configuring SQL Server components, including Analysis Services. It allows users to configure various settings, such as query logs, for the SSAS instance.

Rate this question:

40.

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?

Add a filter to the dataset.
Add a filter to the matrix.
Add a filter to the table
Add a parameter to the stored procedure that filters the result set.

Correct Answer

A. Add a filter to the table

Explanation

Adding a filter to the table will limit the data that is displayed in the table while ensuring that the matrix is unaffected. By applying a filter to the table, only the desired data will be shown in the table, while the matrix will continue to display all relevant data. This allows for specific data filtering without impacting the overall display of the report.

Rate this question:

41.

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?

¡¤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
On the column that contains detail information, set the hidden prperty to False. ¡¤Set the Visibility can be toggled by another report item property to False
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
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. ¡¤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

Explanation

To meet the requirements, you should set the hidden property of the column that contains detail information to True. This will ensure that the detail information is initially hidden when the report is rendered. Additionally, you should set the Visibility can be toggled by another report item property to True. This will allow the users to click a column header and toggle the visibility of the detail information.

Rate this question:

42.

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?

Build the Integration Services project.
Modify the AllowConfigurationChanges property of the project.
Copy each package. Rename each copy with a .SSISDeploymentManifest file extension.
View the code of each Data Transformation Services (DTS) package. Copy the first xml node to a file with a .SSISDeploymentManifest file extension.

Correct Answer

A. Build the Integration Services project.

Explanation

To create a deployment manifest for all packages in the project, you should build the Integration Services project. Building the project will generate the necessary deployment files, including the deployment manifest. This manifest file contains information about the project and its packages, which is used for deploying the project to a target environment.

Rate this question:

43.

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: xEmployeeKey ¡¤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?

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

Correct Answer

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

Explanation

To define the relationship between the employees and their manager in the DSV, you should create a relationship by using ManagerKey as the source column and EmployeeKey as the destination column. This means that the ManagerKey column in the Employee table will be used to establish the relationship with the EmployeeKey column in the same table. This relationship will allow for hierarchical analysis and reporting in the SSAS solution, enabling users to analyze data based on the manager-employee relationship.

Rate this question:

44.

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?

Use the Deployment Wizard along with the appropriate options.
Execute an UPDATE CUBE statement in Microsoft SQL Server Management Studio (SSMS).
Use the Analysis Services Destination component in Microsoft SQL Server Integration Services (SSIS)
Use the Business Intelligence Development Studio (BIDS) and set the processing option to Do Not Process.

Correct Answer

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

Explanation

The correct answer is to use the Deployment Wizard along with the appropriate options. The Deployment Wizard allows you to update the cube definition on the production server without overwriting any existing partitions. It provides options to choose what to deploy, such as dimensions, measures, and partitions, and you can select to deploy only the changes made to the cube. This ensures that the existing partitions are not affected while updating the cube definition.

Rate this question:

45.

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?

Use the ProcessAdd command.
Use the ProcessUpdate command.
Use the ProcessIndexes command.
Use the ProcessClear and the ProcessDefault commands.

Correct Answer

A. Use the ProcessClear and the ProcessDefault commands.

Explanation

The correct answer is to use the ProcessClear and the ProcessDefault commands. This is because the data source for the Product dimension changes the relationship between the Category and Subcategory values. Using the ProcessClear command will remove all existing data from the dimension, and then using the ProcessDefault command will process the dimension with the new data source, ensuring that the changes are reflected accurately.

Rate this question:

46.

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?

=iif(RowNumber(Nothing) / 2, "PaleGreen", "White")
=iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White")
=iif(CountRows() / 2, "PaleGreen", "White")
=iif(CountRows() Mod 2, "PaleGreen", "White")

Correct Answer

A. =iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White")

Explanation

The correct code segment to use for each text box is "=iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White")". This code uses the Mod operator to check if the row number is divisible by 2. If it is, it returns true and sets the background color to "PaleGreen". If it is not divisible by 2, it returns false and sets the background color to "White". This ensures that alternate rows in the table have a pale green background.

Rate this question:

47.

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?

Clear the Prompt value.
Set the Hidden property.
Set the Internal property.
Remove the default values

Correct Answer

A. Set the Internal property.

Explanation

Setting the Internal property will prevent users from modifying the value of the @SalesTaxRateID parameter. This property makes the parameter internal to the report and not visible or editable by users. This ensures that the parameter value remains fixed and cannot be changed by users when viewing or running the report.

Rate this question:

48.

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.(in the exhibit): 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 ?

Add a Sort transformation for each data flow.
Add an OLE DB Command transformation for each data flow.
Update the sorting properties in each source component.
Replace the Merge Join transformation with a Union All transformation.

Correct Answer

A. Update the sorting properties in each source component.

Explanation

By updating the sorting properties in each source component, we can ensure that the input data from each source is sorted before it is passed to the Merge Join component. This will resolve the error message and allow the data from each query to be included in each row in the data flow. Adding a Sort transformation for each data flow or replacing the Merge Join transformation with a Union All transformation would also work, but these options would require additional execution time. Adding an OLE DB Command transformation is not necessary in this scenario.

Rate this question:

49.

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?

Use the SQL Server data source.
Use the SQL Server Analysis Services data source.
Use the OLE DB for SQL Server data source.
Use the OLE DB for the Analysis Services 8.0 data source.

Correct Answer

A. Use the SQL Server Analysis Services data source.

Explanation

The correct answer is to use the SQL Server Analysis Services data source. This is because the report needs to display data from a SQL Server 2005 Analysis Services database, and the SQL Server Analysis Services data source is specifically designed to connect to and retrieve data from Analysis Services databases.

Rate this question:

MSBI 70-448

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?

Quiz Preview

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?

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?

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?

You maintain a SQL Server 2008 Reporting Services (SSRS) instance. The network security model for the domain that hosts the instance changes. You need to ensure that the instance will only support Kerberos authentication. What should you do?

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?

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?

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?

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

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?

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?

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?

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?

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?

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?