Msbi 70-448

1. 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?

Modify the Global.asax file.

Modify the Web.config file.

Modify the RSMgrPolicy.config file.

Modify the RSReportServer.config file.

The RSReportServer.config file is the configuration file for the SQL Server Reporting Services. By modifying this file, you can configure various settings for the server, including rendering extensions. To make all reports render only to Microsoft Excel, you would need to modify the RSReportServer.config file and set the appropriate configuration for the Excel rendering extension. This file is the correct choice for configuring server-wide settings for Reporting Services.

Explanation

The RSReportServer.config file is the configuration file for the SQL Server Reporting Services. By modifying this file, you can configure various settings for the server, including rendering extensions. To make all reports render only to Microsoft Excel, you would need to modify the RSReportServer.config file and set the appropriate configuration for the Excel rendering extension. This file is the correct choice for configuring server-wide settings for Reporting Services.

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.

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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" ),

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.

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.

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

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.

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.

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 attachthe database on the production server

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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

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.

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.

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

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

27. 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 theReporting Services Configuration tool.

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.

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.

28. 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 unsignedpackages

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.

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.

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

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.

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.

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

not-available-via-ai

Explanation

not-available-via-ai

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

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.

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.

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

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.

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.

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

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.

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.

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

not-available-via-ai

Explanation

not-available-via-ai

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

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.

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.

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

not-available-via-ai

Explanation

not-available-via-ai

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

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.

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.

Submit

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.

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.

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.

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

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.

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.

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.

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.

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.

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?

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.

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.

42. 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 destinationcolumn.

Create a Named Query that uses a relationship with EmployeeKey as the source column andManagerKey as the destination column

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

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.

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.

43. 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 NotProcess.

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

49. You maintain a report in a SQL Server 2008 Reporting Services (SSRS) instance. The report contains The safer , easier way to help you pass any IT exams. 33 / 36 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?

Add a default value to all parameters.

Modify the datasets to use the Table query type.

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

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

By removing the filters from the dataset queries and using the parameters to filter the datasets, users will still be able to use parameters to filter the report even when it is rendered from a report execution snapshot. This allows for flexibility in filtering the report based on user preferences.

Explanation

By removing the filters from the dataset queries and using the parameters to filter the datasets, users will still be able to use parameters to filter the report even when it is rendered from a report execution snapshot. This allows for flexibility in filtering the report based on user preferences.

50. You maintain a SQL Server 2008 Reporting Services (SSRS) instance. A developer accidentally deploys 1000 reports to the SSRS instance. You need to restore the SSRS instance to its original state. Which database should you restore?

The master database

The MSDB database

The Report Server database

The Report Server Temporary database

The Report Server database should be restored because it is the main database that stores all the configuration, metadata, and report definitions for the SSRS instance. Restoring this database will revert the SSRS instance back to its original state before the accidental deployment of the 1000 reports. Restoring the other databases (master, MSDB, and Report Server Temporary) will not have the same effect.

Explanation

The Report Server database should be restored because it is the main database that stores all the configuration, metadata, and report definitions for the SSRS instance. Restoring this database will revert the SSRS instance back to its original state before the accidental deployment of the 1000 reports. Restoring the other databases (master, MSDB, and Report Server Temporary) will not have the same effect.

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

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.

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.

52. 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?

Configure the report to use snapshot execution.

Configure available values for the parameter from the dataset.

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

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

Configuring the default value for the parameter based on an expression ensures that the report executes without user interaction from the Report Manager. By setting a default value using an expression, the report will automatically run with that value without requiring the user to manually input a parameter value. This allows for a seamless and automated execution of the report.

Explanation

Configuring the default value for the parameter based on an expression ensures that the report executes without user interaction from the Report Manager. By setting a default value using an expression, the report will automatically run with that value without requiring the user to manually input a parameter value. This allows for a seamless and automated execution of the report.

53. 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?

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

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

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

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

To create a new subscription on the report server, you should select the report you created, click the Subscriptions tab, and then click the New Data-driven Subscription button. This option allows you to deliver the report to a dynamic list of subscribers on a regular basis. By using a data-driven subscription, you can customize the delivery of the report based on data from a query or dataset.

Explanation

To create a new subscription on the report server, you should select the report you created, click the Subscriptions tab, and then click the New Data-driven Subscription button. This option allows you to deliver the report to a dynamic list of subscribers on a regular basis. By using a data-driven subscription, you can customize the delivery of the report based on data from a query or dataset.

54. 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?

Modify the ProactiveCaching property.

Modify the ProcessingPriority property.

Modify the ProcessingMode property.

Modify the DataAggregation property.

By modifying the ProactiveCaching property, you can configure SSAS to automatically process the Sales Data measure group on a schedule. Proactive caching allows you to define a processing schedule for measure groups, which determines when and how often the data is processed. This ensures that the Sales Data measure group is updated frequently, as required, without the need for manual intervention.

Explanation

By modifying the ProactiveCaching property, you can configure SSAS to automatically process the Sales Data measure group on a schedule. Proactive caching allows you to define a processing schedule for measure groups, which determines when and how often the data is processed. This ensures that the Sales Data measure group is updated frequently, as required, without the need for manual intervention.

55. 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?

Add the manager to the Bikes role.

Add the manager to the Northeast Region role.

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, the manager should be added 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.

This will restrict the manager's access to only the Bikes category in the Northeast region, as specified.

Explanation

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, the manager should be added 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.

This will restrict the manager's access to only the Bikes category in the Northeast region, as specified.

56. You create a SQL Server 2008 Analysis Services (SSAS) solution. You create a dimension named DimCustomer that contains the following three attributes: ¡¤Custome ¡¤Cit ¡¤Countr 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?

To ensure that measure values can be retrieved based on the City and Country attributes, you need to set the source attribute to Customer and the related attribute to City. This means that the Customer attribute will be used as the key for the dimension and the City attribute will be related to it. Additionally, you should also set the source attribute to City and the related attribute to Country, which means that the City attribute will be used as the key for another dimension and the Country attribute will be related to it. This configuration allows for retrieving measure values based on both the City and Country attributes.

Explanation

To ensure that measure values can be retrieved based on the City and Country attributes, you need to set the source attribute to Customer and the related attribute to City. This means that the Customer attribute will be used as the key for the dimension and the City attribute will be related to it. Additionally, you should also set the source attribute to City and the related attribute to Country, which means that the City attribute will be used as the key for another dimension and the Country attribute will be related to it. This configuration allows for retrieving measure values based on both the City and Country attributes.

57. 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?

Create a regular relationship between DimCustomer and FactTransaction.

Create a referenced relationship between DimCustomer and FactCustomerAccount. Set DimAccountas an intermediate dimension.

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

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

not-available-via-ai

Explanation

not-available-via-ai

58. 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?

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

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

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

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

The correct answer is ([Measures].[Amount],[DimTime].[Yr-Qtr-Mth].PrevMember) because it uses the PrevMember function to reference the previous member in the Yr-Qtr-Mth hierarchy. This will display the Amount measure for the previous time period at the current level of the hierarchy.

Explanation

The correct answer is ([Measures].[Amount],[DimTime].[Yr-Qtr-Mth].PrevMember) because it uses the PrevMember function to reference the previous member in the Yr-Qtr-Mth hierarchy. This will display the Amount measure for the previous time period at the current level of the hierarchy.

59. 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?

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

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

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

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

To allow the Managers role to access aggregate data based on only three or less employees, we need to add the following expression to the Allow Reading property in Cell Data in the Managers role: Descendants([Employee].[Employees].CurrentMember,,LEAVES).Count. This expression counts the number of leaf members (employees) in the [Employee].[Employees] hierarchy, and only allows access if the count is three or less. This ensures that the Managers role can only access aggregate data for a maximum of three employees.

Explanation

To allow the Managers role to access aggregate data based on only three or less employees, we need to add the following expression to the Allow Reading property in Cell Data in the Managers role: Descendants([Employee].[Employees].CurrentMember,,LEAVES).Count. This expression counts the number of leaf members (employees) in the [Employee].[Employees] hierarchy, and only allows access if the count is three or less. This ensures that the Managers role can only access aggregate data for a maximum of three employees.

60. 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].[Counry].&[Germany],[Customer].[Country].&[France]} set of attribute members. ¡¤New members added to the attribute are visible by default What should you do?

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

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

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

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

To meet the requirements, you should add the set {[Customer].[Country].&[Germany], [Customer].[Country].&[France]} to the Denied Set property. This will deny access to the attribute members for Germany and France.

Explanation

To meet the requirements, you should add the set {[Customer].[Country].&[Germany], [Customer].[Country].&[France]} to the Denied Set property. This will deny access to the attribute members for Germany and France.

61. 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?

Process the database.

Synchronize the database.

Restart the Analysis Services service.

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

Processing the database ensures that any changes made to the DataDir property are applied and the instance starts using the new folder. This can be done without any downtime as the processing operation can be performed while the instance is still running. Synchronizing the database may not be necessary in this scenario as it is mainly used to update the data in the database with the data from the source. Restarting the Analysis Services service or deleting the files in the original folder would cause downtime and are not necessary to achieve the desired outcome.

Explanation

Processing the database ensures that any changes made to the DataDir property are applied and the instance starts using the new folder. This can be done without any downtime as the processing operation can be performed while the instance is still running. Synchronizing the database may not be necessary in this scenario as it is mainly used to update the data in the database with the data from the source. Restarting the Analysis Services service or deleting the files in the original folder would cause downtime and are not necessary to achieve the desired outcome.

62. 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?

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

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

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

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

not-available-via-ai

Explanation

not-available-via-ai

63. 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?

Use the ReportViewer control along with the LocalReport property.

Use the ReportViewer control along with the RemoteReport property.

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

Using the ReportViewer control along with the LocalReport property allows the report to be rendered correctly within the Windows Forms application. This is because the LocalReport property enables the application to access and display the report locally, without the need for a connection to the report server. This ensures that remote users, who have no access to the report server, can still view the report accurately within the application.

Explanation

Using the ReportViewer control along with the LocalReport property allows the report to be rendered correctly within the Windows Forms application. This is because the LocalReport property enables the application to access and display the report locally, without the need for a connection to the report server. This ensures that remote users, who have no access to the report server, can still view the report accurately within the application.

64. 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?

Remove the expression from the report header.

Configure the report to use no credentials

Configure the report to use stored credentials.

Modify the expression in the report header as "User!UserName."

By removing the expression from the report header, the report will no longer rely on the "User!UserID" expression. This is necessary because data-driven subscriptions do not support the use of certain built-in fields, such as "User!UserID". Therefore, removing the expression will ensure that the data-driven subscriptions can successfully execute for the report.

Explanation

By removing the expression from the report header, the report will no longer rely on the "User!UserID" expression. This is necessary because data-driven subscriptions do not support the use of certain built-in fields, such as "User!UserID". Therefore, removing the expression will ensure that the data-driven subscriptions can successfully execute for the report.

65. 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?

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

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

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

Install SSRS on both cluster nodes and cluster the SSRS service by using the SQL Server resourcegroup.

Installing SSRS on both cluster nodes to use the same ReportServer database allows for a fault-tolerant scale-out deployment. This means that if one node fails, the other node can continue to serve the reports from the shared database. Additionally, clustering the SSRS service using the SQL Server resource group ensures that the service is highly available and can failover to the other node in case of a failure.

Explanation

Installing SSRS on both cluster nodes to use the same ReportServer database allows for a fault-tolerant scale-out deployment. This means that if one node fails, the other node can continue to serve the reports from the shared database. Additionally, clustering the SSRS service using the SQL Server resource group ensures that the service is highly available and can failover to the other node in case of a failure.

66. You design a SQL Server 2008 Integration Services (SSIS) package. The package extracts data from flat files and loads this data to a SQL Server 2008 database. The package contains two Boolean variables named @IsWeekday and @IsHoliday. When the package is executed on week days, the @IsWeekday variable is set to True. When the package is executed on holidays, the @IsHoliday variable is set to True. You need to implement the following business logic in the control flow of the package: ¡¤When the @IsWeekday variable is set to True, the Perform Weekday Processing Data Flow task is executed. ¡¤When the @IsWeekday variable is set to False, the Perform Weekend Processing Data Flow task is executed. ¡¤When the @IsHoliday variable is set to True, the Perform Holiday Processing Data Flow task is executed. Which control flow logic should you implement?

The control flow logic that should be implemented is an "Expression and Constraint" logic. This logic uses expressions and constraints to determine which tasks should be executed based on the values of the variables. The Perform Weekday Processing Data Flow task should have a constraint that is evaluated when the @IsWeekday variable is True. The Perform Weekend Processing Data Flow task should have a constraint that is evaluated when the @IsWeekday variable is False. The Perform Holiday Processing Data Flow task should have a constraint that is evaluated when the @IsHoliday variable is True. This logic ensures that the appropriate data flow tasks are executed based on the values of the variables.

Explanation

The control flow logic that should be implemented is an "Expression and Constraint" logic. This logic uses expressions and constraints to determine which tasks should be executed based on the values of the variables. The Perform Weekday Processing Data Flow task should have a constraint that is evaluated when the @IsWeekday variable is True. The Perform Weekend Processing Data Flow task should have a constraint that is evaluated when the @IsWeekday variable is False. The Perform Holiday Processing Data Flow task should have a constraint that is evaluated when the @IsHoliday variable is True. This logic ensures that the appropriate data flow tasks are executed based on the values of the variables.

67. 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?

Execute the dtutil command.

Execute the dtexec command.

Execute the dtexecui command.

Execute the dtswizard command.

The correct answer is to execute the dtutil command. The dtutil command is used to deploy packages in SSIS. It allows you to manage and manipulate packages, including deploying them to another SSIS instance. By executing the dtutil command, you can easily transfer the package from one instance to another, even if the destination instance stores its packages in a shared directory.

Explanation

The correct answer is to execute the dtutil command. The dtutil command is used to deploy packages in SSIS. It allows you to manage and manipulate packages, including deploying them to another SSIS instance. By executing the dtutil command, you can easily transfer the package from one instance to another, even if the destination instance stores its packages in a shared directory.

68. 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?

Set the IsAggregatable property of the Price attribute to False.

Set the AttributeHierarchyEnabled property of the Price attribute to False.

Set the MembersWithData property of the Price attribute to NonLeafDataHidden.

Set the GroupingBehavior property of the Price attribute to DiscourageGrouping.

Setting the AttributeHierarchyEnabled property of the Price attribute to False ensures that aggregations are not associated with the attribute. This property determines whether the attribute can be used to build hierarchies in the dimension. By setting it to False, the Price attribute will not be used in any hierarchies and therefore, aggregations will not be created based on it.

Explanation

Setting the AttributeHierarchyEnabled property of the Price attribute to False ensures that aggregations are not associated with the attribute. This property determines whether the attribute can be used to build hierarchies in the dimension. By setting it to False, the Price attribute will not be used in any hierarchies and therefore, aggregations will not be created based on it.

69. 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?

Assign the user to the Publisher role

Assign the user to the My Reports role.

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

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

Assigning the user to the My Reports role will grant them the necessary permissions to publish reports and manage content in a private folder. The My Reports role specifically allows users to create, publish, and manage their own reports within their private folders, while restricting their access to other folders and content in the SSRS environment. This role is appropriate for users who need to have control over their own reports without having broader administrative capabilities.

Explanation

Assigning the user to the My Reports role will grant them the necessary permissions to publish reports and manage content in a private folder. The My Reports role specifically allows users to create, publish, and manage their own reports within their private folders, while restricting their access to other folders and content in the SSRS environment. This role is appropriate for users who need to have control over their own reports without having broader administrative capabilities.

70. 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?

ProcessAdd

ProcessData

ProcessFull

ProcessIndex

The correct XMLA command to use in this scenario is ProcessAdd. This command is used for incremental processing, which means that only new or modified data will be processed and added to the partition. This allows for faster processing time and avoids reprocessing the entire partition every two hours.

Explanation

The correct XMLA command to use in this scenario is ProcessAdd. This command is used for incremental processing, which means that only new or modified data will be processed and added to the partition. This allows for faster processing time and avoids reprocessing the entire partition every two hours.

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

Use the Merge transformation.

Use the Lookup transformation.

Use the Union All transformation.

Use the Merge Join transformation.

Use the Fuzzy Lookup transformation.

Use the Fuzzy Grouping transformation.

The Lookup transformation can be used to check if a row already exists in the destination table by comparing it with the existing data. If a match is found, the row can be skipped or redirected to a different path. The Merge Join transformation can be used to combine the source and destination data based on a common key. By selecting the unmatched rows from the source, only the rows that do not already exist in the destination table will be inserted.

Explanation

The Lookup transformation can be used to check if a row already exists in the destination table by comparing it with the existing data. If a match is found, the row can be skipped or redirected to a different path. The Merge Join transformation can be used to combine the source and destination data based on a common key. By selecting the unmatched rows from the source, only the rows that do not already exist in the destination table will be inserted.

Submit

72. You create a SQL Server 2008 Analysis Services (SSAS) solution. You have a table named Orders that contains the following columns: ¡¤OrderKe xShipper ¡¤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?

Set the relationship between the DimOrders dimension and the FactOrders measure group to NoRelationship.

Create a fact dimension relationship between the DimOrders dimension and the FactOrders measuregroup.

not-available-via-ai

Explanation

not-available-via-ai

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

Add a variable to the package.

Add a variable to the data flow task.

Add a Foreach Loop container to the package.

Add a For Loop container to the package.

Add an expression to the Flat File Source component.

Add an expression to the Flat File connection manager.

To extract data from multiple text files of the same directory, three actions should be performed. Firstly, a variable should be added to the package to store the file path. Secondly, a Foreach Loop container should be added to the package to loop through the files in the directory. Lastly, an expression should be added to the Flat File connection manager to dynamically set the file path using the variable. These actions allow the package to iterate through each file in the directory and extract the data into the SQL Server 2008 table.

Explanation

To extract data from multiple text files of the same directory, three actions should be performed. Firstly, a variable should be added to the package to store the file path. Secondly, a Foreach Loop container should be added to the package to loop through the files in the directory. Lastly, an expression should be added to the Flat File connection manager to dynamically set the file path using the variable. These actions allow the package to iterate through each file in the directory and extract the data into the SQL Server 2008 table.

Submit

74. 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?

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

Create a many-to-many relationship between the DimTime dimension and the FactSales measuregroup.

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

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

Creating two regular relationships between the DimTime dimension and the FactSales measure group with different measure group columns allows the DimTime dimension to be used to browse through measures in the FactSales table for both OrderDate and ShipDate. This means that users can analyze the data based on both the order date and the ship date using the DimTime dimension.

Explanation

Creating two regular relationships between the DimTime dimension and the FactSales measure group with different measure group columns allows the DimTime dimension to be used to browse through measures in the FactSales table for both OrderDate and ShipDate. This means that users can analyze the data based on both the order date and the ship date using the DimTime dimension.

75. 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 ?

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

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

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

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

The correct answer is PeriodsToDate([DimTime].[Yr-Qtr-Mth].[Year], [DimTime].[Yr-Qtr-Mth].[Month].[July 2007]). This expression uses the PeriodsToDate function to calculate the set of dates from the beginning of the year to the specified month (July 2007). It specifies the Year level of the Calendar hierarchy as the level to aggregate the dates. This will give the first seven months of the year 2007 as the result.

Explanation

The correct answer is PeriodsToDate([DimTime].[Yr-Qtr-Mth].[Year], [DimTime].[Yr-Qtr-Mth].[Month].[July 2007]). This expression uses the PeriodsToDate function to calculate the set of dates from the beginning of the year to the specified month (July 2007). It specifies the Year level of the Calendar hierarchy as the level to aggregate the dates. This will give the first seven months of the year 2007 as the result.

76. You manage the security in SQL Server 2008 Reporting Services (SSRS). The report server contains a folder named Management Reports. All groups can view and modify reports in any folder. You need to ensure that only the Managers group can view and modify reports in the Management Reports folder. What should you do?

Remove all groups from the Management Reports folder.¡¤Add the Managers group to the folder and assign theContent Manager role.

¡¤Create a Managers role¡¤Assign the Managers role to the Management Reports folder.Remove all other groups from the Management Reports folder

Create a Managers role.¡¤Assign the Managers role to the root folder.¡¤Removeall groups from the Management Reports folder.

To ensure that only the Managers group can view and modify reports in the Management Reports folder, you should remove all groups from the Management Reports folder. Then, add the Managers group to the folder and assign them the Content Manager role. This will restrict access and permissions to only the Managers group, allowing them to view and modify reports in the Management Reports folder.

Explanation

To ensure that only the Managers group can view and modify reports in the Management Reports folder, you should remove all groups from the Management Reports folder. Then, add the Managers group to the folder and assign them the Content Manager role. This will restrict access and permissions to only the Managers group, allowing them to view and modify reports in the Management Reports folder.

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

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

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

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

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

To update the package so that the DirectoryPath variable returns the directory path for the file path stored in the FileName variable, two actions should be performed. First, the EvaluateAsExpression property of the User::DirectoryPath variable should be set to True. This allows the variable to be evaluated as an expression. Second, the Expression property of the User::DirectoryPath variable should be set to the expression "SUBSTRING( @[User::FileName], 1, LEN( @[User::FileName] ) - FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ))". This expression uses string manipulation functions to extract the directory path from the file path stored in the FileName variable.

Explanation

To update the package so that the DirectoryPath variable returns the directory path for the file path stored in the FileName variable, two actions should be performed. First, the EvaluateAsExpression property of the User::DirectoryPath variable should be set to True. This allows the variable to be evaluated as an expression. Second, the Expression property of the User::DirectoryPath variable should be set to the expression "SUBSTRING( @[User::FileName], 1, LEN( @[User::FileName] ) - FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ))". This expression uses string manipulation functions to extract the directory path from the file path stored in the FileName variable.

Submit

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

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

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

In the package, configure the TransactionOption property to Required.

In the package, configure the TransactionOption property to NotSupported.

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

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

To ensure that all tasks except the Execute SQL task execute within a single transaction, the TransactionOption property needs to be configured accordingly. Setting the TransactionOption property to "Required" in the package will ensure that all tasks within the package are part of the same transaction. Additionally, setting the TransactionOption property to "NotSupported" in the Execute SQL task will exclude it from the transaction, allowing it to execute independently.

Explanation

To ensure that all tasks except the Execute SQL task execute within a single transaction, the TransactionOption property needs to be configured accordingly. Setting the TransactionOption property to "Required" in the package will ensure that all tasks within the package are part of the same transaction. Additionally, setting the TransactionOption property to "NotSupported" in the Execute SQL task will exclude it from the transaction, allowing it to execute independently.

Submit

79. 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 hat references the DimTime dimension You need to ensure that the aggregations designed on FactSales always include the Month attribute. What should you do?

Set the Usage property appropriately

Set the IsAggregatable property appropriately.

Set the AggregationUsage property appropriately

Set the AttributeHierarchyEnabled property appropriately

not-available-via-ai

Explanation

not-available-via-ai

80. You create a SQL Server 2008 Analysis Services (SSAS) solution. Your solution has the following components: ¡¤A hierarchy named Date in a time dimension named DimTim ¡¤A measure named SalesAmoun You add the following code segment to the calculation script of the cube. (Line numbers are included for reference only.) 01. . . 02Freeze([Measures].[SalesAmount], 03[DimTime].[Date].[Quarter].[Q1 2007]); 04. . . 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?

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

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

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;

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;

The correct answer is to add the code segment at line 04. This code segment uses the Scope function to specify that the calculation should only apply to the descendants of the Q1 2007 quarter in the Date hierarchy. It then multiplies the current member of the Date hierarchy by 1.5, effectively increasing the SalesAmount measure by 50% for each month in the first quarter of 2007. The End Scope statement ensures that the calculation is applied only to the specified scope and does not affect other quarters or measures.

Explanation

The correct answer is to add the code segment at line 04. This code segment uses the Scope function to specify that the calculation should only apply to the descendants of the Q1 2007 quarter in the Date hierarchy. It then multiplies the current member of the Date hierarchy by 1.5, effectively increasing the SalesAmount measure by 50% for each month in the first quarter of 2007. The End Scope statement ensures that the calculation is applied only to the specified scope and does not affect other quarters or measures.

81. You administer a SQL Server 2008 Reporting Services (SSRS) instance. The instance contains several reports that use stored credentials to access data from a SQL Server 2008 instance. A computer that hosts the instance recovers from a hardware failure and has a newly installed SSRS instance. You need to ensure that the reports hosted on the instance continue to function correctly. What should you do?

Reinitialize the Report Server.

Restore the encryption keys

Recreate the encryption keys

Recreate the Report Server database

Recreating the encryption keys is the correct answer because when a new SSRS instance is installed, the encryption keys used to secure the stored credentials are not automatically transferred to the new instance. Therefore, to ensure that the reports continue to function correctly, the encryption keys need to be recreated on the new instance. This will allow the reports to access the data from the SQL Server 2008 instance using the stored credentials.

Explanation

Recreating the encryption keys is the correct answer because when a new SSRS instance is installed, the encryption keys used to secure the stored credentials are not automatically transferred to the new instance. Therefore, to ensure that the reports continue to function correctly, the encryption keys need to be recreated on the new instance. This will allow the reports to access the data from the SQL Server 2008 instance using the stored credentials.

MSBI 70-448