MSBI 70-448

Approved & Edited by ProProfs Editorial Team

The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.

Learn about Our Editorial Process

| By Ranaguha

Ranaguha

Community Contributor

Quizzes Created: 3 | Total Attempts: 6,985

Questions: 81 | Attempts: 2,639 | Updated: Mar 22, 2023

Questions

Settings

Feedback

During the Quiz End of Quiz

Play as

Quiz Flashcard

Create your own Quiz

MSBI 70-448 Cert dump

Questions and Answers

1.

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?
- A.
- B.
- C.
- D.
Correct Answer
A.

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.

Rate this question:

1
0
2.

You are developing a SQL Server 2008 Integration Services (SSIS) data flow that loads data to a SQL Server 2008 database. You need to ensure that the data flow updates the existing records in the SQL Server database by using data from the rows in your data flow. Which data flow component should you use?
- A.
  
  SQL Server Destination
- B.
  
  OLE DB Destination
- C.
  
  OLE DB Command Transformation
- D.
  
  Data Conversion Transformation
Correct Answer
C. OLE DB Command Transformation

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

Rate this question:
3.

You design a SQL Server 2008 Integration Services (SSIS) package that contains several data flows. You need to monitor the data that passes through each path in the data flow by using the Business Intelligence Development Studio. You also need to ensure that data flow logic is retained. What should you do?
- A.
  
  Use the Data Viewer tool.
- B.
  
  Use the Data Profiling task.
- C.
  
  Use the Audit transformation.
- D.
  
  Use breakpoints in the Script component.
Correct Answer
A. Use the Data Viewer tool.

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

Rate this question:
4.

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 ?
- A.
  
  Add a Sort transformation for each data flow.
- B.
  
  Add an OLE DB Command transformation for each data flow.
- C.
  
  Update the sorting properties in each source component.
- D.
  
  Replace the Merge Join transformation with a Union All transformation.
Correct Answer
C. Update the sorting properties in each source component.

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

Rate this question:
5.

You are designing a SQL Server 2008 Integration Services (SSIS) package. The package includes a Data Flow task that copies several million rows. The data flow requires a match against a reference table that contains 10,000 rows. Each row in the reference table will be used during the data flow. You need to ensure that the data flow executes in the minimum amount of time. Which data flow component should you select?
- A.
  
  A Fuzzy Lookup transformation
- B.
  
  Lookup transformation along with the Full cache option
- C.
  
  Lookup transformation along with the No cache option
- D.
  
  Lookup transformation along with the Partial cache option
Correct Answer
B. Lookup transformation along with the Full cache option

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

Rate this question:
6.

You design a SQL Server 2008 Integration Services (SSIS) data flow that inserts data from a source query to a destination table. You need to insert only those rows that do not already exist in the destination table. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
- A.
  
  Use the Merge transformation.
- B.
  
  Use the Lookup transformation.
- C.
  
  Use the Union All transformation.
- D.
  
  Use the Merge Join transformation.
- E.
  
  Use the Fuzzy Lookup transformation.
- F.
  
  Use the Fuzzy Grouping transformation.
Correct Answer(s)
B. Use the Lookup transformation.
D. Use the Merge Join transformation.

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.

Rate this question:
7.

You modify a SQL Server 2008 Integration Services (SSIS) package. The package extracts data from a text file and loads this data to a SQL Server 2008 table as shown in the following two exhibits. You need to update the package to extract data from multiple text files of the same directory. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)
- A.
  
  Add a variable to the package.
- B.
  
  Add a variable to the data flow task.
- C.
  
  Add a Foreach Loop container to the package.
- D.
  
  Add a For Loop container to the package.
- E.
  
  Add an expression to the Flat File Source component.
- F.
  
  Add an expression to the Flat File connection manager.
Correct Answer(s)
A. Add a variable to the package.
C. Add a Foreach Loop container to the package.
F. Add an expression to the Flat File connection manager.

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.

Rate this question:
8.

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.)
- A.
  
  Set the RaiseChangedEvent property of the User::FileName variable to True.
- B.
  
  Set the EvaluateAsExpression property of the User::DirectoryPath variable to True.
- C.
  
  Add a Script task to the OnPostExecute event handler of the Foreach Loop container.
- D.
  
  Set the Expression property of the User::DirectoryPath variable to the following expression. SUBSTRING( @[User::FileName], 1, LEN( @[User::FileName] ) - FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ))
- E.
  
  Set the Expression property of the User::FileName variable to the following expression. SUBSTRING( @[User::FileName], 1, LEN( @[User::FileName] ) - FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ))
Correct Answer(s)
B. Set the EvaluateAsExpression property of the User::DirectoryPath variable to True.
D. Set the Expression property of the User::DirectoryPath variable to the following expression. SUBSTRING( @[User::FileName], 1, LEN( @[User::FileName] ) - FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ))

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.

Rate this question:
9.

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?
- A.
  
  Use the VersionGUID variable.
- B.
  
  Use the ExecutionGUID variable.
- C.
  
  Use the SourceID variable.
- D.
  
  Use the TaskID variable.
Correct Answer
A. Use the VersionGUID variable.

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

Rate this question:
10.

You develop a SQL Server 2008 Integration Services (SSIS) package. The structure of the package is as shown in the following exhibit. You need to define a variable that contains sensitive information that is only available to the Sequence Container, the Data Flow Task, and the Execute SQL Task. What should you do?
- A.
  
  Create the variable at the package scope.
- B.
  
  Create the variable at the Data Flow Task scope.
- C.
  
  Create the variable at the Execute SQL Task scope.
- D.
  
  Create the variable at the Sequence Container scope.
Correct Answer
D. Create the variable at the Sequence Container scope.

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

Rate this question:
11.

You are developing a SQL Server 2008 Integration Services (SSIS) package. The package uses an OLE DB connection manager to connect to a third-party database server. The database does not support Integrated Windows authentication. You create an XML configuration file that provides configuration for the connection manager. You add the configuration file to the package. When the package executes, you receive an error message which indicates that login failure occurred. You need to execute the package successfully. What should you do?
- A.
  
  Edit the XML configuration file.
- B.
  
  Use an indirect XML configuration.
- C.
  
  Run the package on the same server as the third-party database server.
- D.
  
  Use a Microsoft ADO.NET connection manager instead of an OLE DB connection manager.
Correct Answer
A. Edit the XML configuration file.

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

Rate this question:
12.

You build and deploy a SQL Server 2008 Integration Services (SSIS) package. During package development, the XML configuration is not specified. You need to apply the configuration to the deployed package. What should you do?
- A.
  
  Use the Deployment utility.
- B.
  
  Use the SETX command line utility.
- C.
  
  Use the DTUTIL command line utility.
- D.
  
  Use the DTEXEC command line utility.
Correct Answer
D. Use the DTEXEC command line utility.

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

Rate this question:
13.

You are designing a SQL Server 2008 Integration Services (SSIS) package. The package includes a Data Flow task. You need to modify the package to write all rows that generate errors to a text file. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
- A.
  
  Use the SSIS log provider for the SQL Profiler.
- B.
  
  Use the SSIS log provider for the text file.
- C.
  
  Use a Flat File Destination component.
- D.
  
  Use a Raw File Destination component.
- E.
  
  Add an Error output to the Data Flow component
Correct Answer(s)
C. Use a Flat File Destination component.
E. Add an Error output to the Data Flow component

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

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

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

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

Rate this question:
14.

You are editing a SQL Server 2008 Integration Services (SSIS) package by using Business Intelligence Development Studio. The package uses a custom data flow component. The package runs successfully. You attempt to add another instance of the custom data flow component to the package. The data flow component does not appear in the toolbox or in the Choose Toolbox Items dialog box. You need to add the custom data flow component to the toolbox. What should you do?
- A.
  
  Copy the assembly to the appropriate folder.
- B.
  
  Add the assembly to the Global Assembly Cache.
- C.
  
  Register the assembly by using the REGSVR32 utility.
- D.
  
  Register the assembly by using the Assembly Registration utility (REGASM.EXE).
Correct Answer
A. Copy the assembly to the appropriate folder.

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

Rate this question:
15.

You plan to use SQL Server 2008 Integration Services (SSIS) on a SQL Server 2008 failover cluster. You have a complex package that takes a long time to run. You need to ensure that if the package executes during a failover, the package can be resumed at the point of failure. What should you do?
- A.
  
  Cluster the SSIS service. Add the SSIS service to its own cluster resource group.
- B.
  
  Cluster the SSIS service. Add the SSIS service to the SQL Server cluster resource group.
- C.
  
  Implement checkpoints within your package. Restart the package whenever a failover occurs.
- D.
  
  Implement transactions within your package. Restart the package whenever a failover occurs.
Correct Answer
C. Implement checkpoints within your package. Restart the package whenever a failover occurs.

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

Rate this question:
16.

You maintain a SQL Server 2008 Integration Services (SSIS) instance. The instance stores packages in a SQL Server 2008 instance. You need to deploy the package of an instance of SSIS to another SSIS instance that stores its packages in a shared directory. What should you do?
- A.
  
  Execute the dtutil command.
- B.
  
  Execute the dtexec command.
- C.
  
  Execute the dtexecui command.
- D.
  
  Execute the dtswizard command.
Correct Answer
A. Execute the dtutil command.

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.

Rate this question:
17.

You are developing a SQL Server 2008 Integration Services (SSIS) project in Business Intelligence Development Studio. You configure the project properties as shown in the following image. You need to create a deployment manifest for all packages in the project. What should you do?
- A.
  
  Build the Integration Services project.
- B.
  
  Modify the AllowConfigurationChanges property of the project.
- C.
  
  Copy each package. Rename each copy with a .SSISDeploymentManifest file extension.
- D.
  
  View the code of each Data Transformation Services (DTS) package. Copy the first xml node to a file with a .SSISDeploymentManifest file extension.
Correct Answer
A. Build the Integration Services project.

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

Rate this question:
18.

You develop a SQL Server 2008 Integration Services (SSIS) package. The package downloads several files from a remote FTP server by using multiple FTP tasks, and then processes data by using multiple Execute SQL tasks. You need to configure the package to restart at the point of failure after the package downloads the files. What should you do?
- A.
  
  Configure the FTP tasks to support transactions.
- B.
  
  Configure the Execute SQL tasks to support transactions.
- C.
  
  Move all the FTP tasks to a sequence container
- D.
  
  Configure the package to use a checkpoint.
Correct Answer
D. Configure the package to use a checkpoint.

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

Rate this question:
19.

You are developing a SQL Server 2008 Integration Services (SSIS) package. The package performs the following tasks: ¡¤Processes multiple files by using a ForEach Loop container ¡¤Imports the contents of the files to a table by using a Data Flow task Logs the results of the status into a table by using an Execute SQL task You need to ensure that all the tasks of the package except the Execute SQL task execute within a single transaction. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
- A.
  
  In the Foreach Loop container, configure the TransactionOption property to NotSupported.
- B.
  
  In the Data Flow task, configure the TransactionOption property to Required.
- C.
  
  In the package, configure the TransactionOption property to Required.
- D.
  
  In the package, configure the TransactionOption property to NotSupported.
- E.
  
  In the Execute SQL task, configure the TransactionOption property to NotSupported.
- F.
  
  In the Execute SQL task, configure the TransactionOption property to Supported.
Correct Answer(s)
C. In the package, configure the TransactionOption property to Required.
E. In the Execute SQL task, configure the TransactionOption property to NotSupported.

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.

Rate this question:
20.

You develop a package by using SQL Server 2008 Integration Services (SSIS). You test the package by using your local development environment. You deploy the package to the file system in the production environment. The users report that the connection strings contained in the package are blank. The package fails to execute. You need to change the protection level of the package to ensure that users can view and execute the package. Which protection level should you use?
- A.
  
  DontSaveSensitive
- B.
  
  EncryptAllWithUserKey
- C.
  
  EncryptAllWithPassword
- D.
  
  ServerStorage
Correct Answer
C. EncryptAllWithPassword

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

Rate this question:
21.

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

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

Rate this question:
22.

You administer an instance of a SQL Server 2008 server. The server is used to execute SQL Server 2008 Integration Services (SSIS) packages. You need to ensure that the server executes only correctly signed packages. What should you do?
- A.
  
  Set the BlockedSignatureStates registry entry to NoAdministrativeRestriction
- B.
  
  Set the package protection level on all packages to DontSaveSensitive.
- C.
  
  Set the package protection level on all packages to EncryptSensitiveWithPassword.
- D.
  
  Set the BlockedSignatureStates registry entry to Block invalid and untrusted signatures and unsigned packages
Correct Answer
D. Set the BlockedSignatureStates registry entry to Block invalid and untrusted signatures and unsigned packages

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

Rate this question:
23.

You create a SQL Server 2008 Integration Services (SSIS) package by using SQL Server 2008. You use a development server named SQL_DEV1. There is no network connectivity between the development server and the production server. You deploy the package to the SQL_PROD1 server. The package fails to execute. The error message states that the package cannot connect to the SQL_DEV1 server. You need to modify the package to refer to the SQL_PROD1 server when being executed. What should you do?
- A.
  
  Modify the properties of the package to use the DontSaveSensitive package protection level.
- B.
  
  Modify the deployment manifest file in Microsoft Notepad, and then modify the connection string manually.
- C.
  
  On the Connection Managers tab of the Execute Package utility, select the Connection Manager check box, and then modify the connection string
- D.
  
  Modify the properties of the package to use the ServerStorage package protection level.
Correct Answer
C. On the Connection Managers tab of the Execute Package utility, select the Connection Manager check box, and then modify the connection string

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

Rate this question:
24.

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?
- A.
  
  - Set the source attribute to Customer and the related attribute to City - Set the source attribute to City and the related attribute to Country
- B.
  
  ¡¤Set the source attribute to City and the related attribute to Customer ¡¤Set the source attribute to ountry and the related attribute to City.
- C.
  
  Set the source attribute to Customer and the related attribute to City ¡¤Set the source attribute to Customer and the related attribute to Country
- D.
  
  Set the source attribute to City and the related attribut to Customer. ¡¤Set the source attribute to Country and the related attribute to Customer
Correct Answer
A. - Set the source attribute to Customer and the related attribute to City - Set the source attribute to City and the related attribute to Country

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.

Rate this question:
25.

You create a SQL Server 2008 Analysis Services (SSAS) solution. You create a dimension named DimProduct that contains an attribute named Price. You use the DimProduct dimension in a cube. You need to ensure that aggregations are not associated with the Price attribute. What should you do?
- A.
  
  Set the IsAggregatable property of the Price attribute to False.
- B.
  
  Set the AttributeHierarchyEnabled property of the Price attribute to False.
- C.
  
  Set the MembersWithData property of the Price attribute to NonLeafDataHidden.
- D.
  
  Set the GroupingBehavior property of the Price attribute to DiscourageGrouping.
Correct Answer
B. Set the AttributeHierarchyEnabled property of the Price attribute to False.

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.

Rate this question:
26.

You create a SQL Server 2008 Analysis Services (SSAS) solution. You have a dimension named Account and a time dimension named Time. You have a fact table that contains a column named CurrentBalance. CurrentBalance contains the current account balance. You need to create a measure named AccountBalance from the CurrentBalance column to display the closing account balance for a time period. What should you do?
- A.
  
  Set the AggregationFunction property of AccountBalance to Sum.
- B.
  
  Set the AggregationFunction property of AccountBalance to ByAccount.
- C.
  
  Set the AggregationFunction property of AccountBalance to LastNonEmpty.
- D.
  
  Set the AggregationFunction property of AccountBalance to FirstNonEmpty.
Correct Answer
C. Set the AggregationFunction property of AccountBalance to LastNonEmpty.

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

Rate this question:
27.

You create a SQL Server 2008 Analysis Services (SSAS) solution. The cube structure is as shown in the following exhibit. (Click the Exhibit button.) The Ownership column contains decimal values that represent percentage of ownership. The sum of all the values in the Ownership column for a single DimAccount member is 100%. You create the following measures: ¡¤Ownership ¡¤Amount You need to ensure that when the Amount measure is calculated against the DimCustomer dimension, the weighted value is calculated based on the Ownership measure. What should you do?
- A.
  
  Set the MeasureExpression property of the Amount measure to [Amount]*[Ownership].
- B.
  
  Set the FormatString property of the Amount measure to [Amount]*[Ownership].
- C.
  
  Set the MeasureExpression property of the Amount measure to [DimCustomer].[Amount]*[Ownership].
- D.
  
  Set the FormatString property of the Amount measure to [DimCustomer].[Amount]*[Ownership].
Correct Answer
A. Set the MeasureExpression property of the Amount measure to [Amount]*[Ownership].
28.

You develop a SQL Server 2008 Analysis Services (SSAS) database. Your database includes several measure groups. A measure group named Sales Data requires frequent updates than other measure groups. You need to ensure that the Sales Data measure group will automatically process on a schedule managed by SSAS. What should you do?
- A.
  
  Modify the ProactiveCaching property.
- B.
  
  Modify the ProcessingPriority property.
- C.
  
  Modify the ProcessingMode property.
- D.
  
  Modify the DataAggregation property.
Correct Answer
A. Modify the ProactiveCaching property.

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.

Rate this question:
29.

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?
- A.
  
  Create a Named Calculation that uses EmployeeKey and ManagerKey
- B.
  
  Create a relationship by using EmployeeKey as the source column and ManagerKey as the destination column.
- C.
  
  Create a Named Query that uses a relationship with EmployeeKey as the source column and ManagerKey as the destination column
- D.
  
  Create a relationship by using ManagerKey as the source column and EmployeeKey as the destination column.
Correct Answer
D. Create a relationship by using ManagerKey as the source column and EmployeeKey as the destination column.

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

Rate this question:
30.

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?
- A.
  
  Set the relationship between the DimOrders dimension and the FactOrders measure group to No Relationship.
- B.
  
  Create a fact dimension relationship between the DimOrders dimension and the FactOrders measure group.
- C.
  
  Create a regular dimension relationship between the DimOrders dimension and the FactOrders measure group. Set the Granularity attribute to Shipper and the measure group columns to OrderAmount.
- D.
  
  Create a regular dimension relationship between the DimOrders dimension and the FactOrders measure group. Set the Granularity attribute to OrderKey and the measure group columns to OrderAmount.
Correct Answer
B. Create a fact dimension relationship between the DimOrders dimension and the FactOrders measure group.
31.

You create a SQL Server 2008 Analysis Services (SSAS) solution. The cube structure is as shown in the following exhibit. (Click the Exhibit button.) You need to ensure that the Amount measure can be aggregated for the DimCustomer dimension. What should you do?
- A.
  
  Create a regular relationship between DimCustomer and FactTransaction.
- B.
  
  Create a referenced relationship between DimCustomer and FactCustomerAccount. Set DimAccount as an intermediate dimension.
- C.
  
  Create a many-to-many relationship between DimCustomer and FactTransaction. Set FactCustomerAccount as an intermediate measure group.
- D.
  
  Create a many-to-many relationship between DimCustomer and FactCustomerAccount. Set FactTransaction as an intermediate measure group.
Correct Answer
C. Create a many-to-many relationship between DimCustomer and FactTransaction. Set FactCustomerAccount as an intermediate measure group.
32.

You create a SQL Server 2008 Analysis Services (SSAS) solution. The solution contains a time dimension named DimTime. You have a table named FactSales that contains the two columns named OrderDate and ShipDate. The two columns refer to the DimTime dimension. You need to ensure that the DimTime dimension can be used to browse through measures in the FactSales table for both OrderDate and ShipDate. What should you do?
- A.
  
  Create a reference relationship between the DimTime dimension and the FactSales measure group.
- B.
  
  Create a many-to-many relationship between the DimTime dimension and the FactSales measure group.
- C.
  
  Create two regular relationships between the DimTime dimension and the FactSales measure group. Use identical measure group columns
- D.
  
  Create two regular relationships between the DimTime dimension and the FactSales measure group. Use different measure group columns.
Correct Answer
D. Create two regular relationships between the DimTime dimension and the FactSales measure group. Use different measure group columns.

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.

Rate this question:
33.

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?
- A.
  
  KPIValue( "GPMargin" ) >?( KPIValue( "GPMargin" ),
- B.
  
  KPITrend( "GPMargin" ) >?( KPIValue ( "GPMargin" ),
- C.
  
  KPIStatus( "GPMargin" ) >?( KPIValue( "GPMargin" ),
- D.
  
  KPIGoal( "GPMargin" ) >?( KPIValue( "GPMargin" ),
Correct Answer
A. KPIValue( "GPMargin" ) >?( KPIValue( "GPMargin" ),

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

Rate this question:
34.

You create a SQL Server 2008 Analysis Services (SSAS) solution. Your solution contains a time dimension named DimTime that contains a hierarchy named Yr-Qtr-Mth. You have a measure named Amount from a measure group named Measures. You need to create a calculated member to display the Amount measure in the previous time period for the current level of the Yr-Qtr-Mth hierarchy. Which expression should you use?
- A.
  
  ([Measures].[Amount],OPENINGPERIOD( [DimTime].[Yr-Qtr-Mth].FirstSibling.Level))
- B.
  
  ([Measures].[Amount],LASTPERIODS( 0,[DimTime].[Yr-Qtr-Mth].PrevMember))
- C.
  
  ([Measures].[Amount],[DimTime].[Yr-Qtr-Mth].PrevMember)
- D.
  
  ([Measures].[Amount],[DimTime].[Yr-Qtr-Mth].FirstSibling)
Correct Answer
C. ([Measures].[Amount],[DimTime].[Yr-Qtr-Mth].PrevMember)

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.

Rate this question:
35.

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?
- A.
  
  Add the following code segment at line 04. Scope([Measures].[Amount], [DimTime].[Date].[Quarter]. [Q1 2007] ); This=[DimTime].[Date].currentmember * 1.5; End Scope;
- B.
  
  Add the following code segment at line 01. Scope([Measures].[Amount], [DimTime].[Date].[Quarter]. [Q1 2007] ); This=[DimTime].[Date].currentmember * 1.5; End Scope;
- C.
  
  Add the following code segment at line 04. Scope([Measures].[SalesAmount], Descendants([DimTime].[Date].[Quarter]. [Q1 2007] [DimTime].[Date].[Month]) ); This=[DimTime].[Date].currentmember * 1.5; End Scope;
- D.
  
  Add the following code segment at line 01. Scope([Measures].[SalesAmount], Descendants([DimTime].[Date].[Quarter].[Q1 2007] [DimTime].[Date].[Month]) ); This=[DimTime].[Date].currentmember * 1.5; End Scope;
Correct Answer
C. 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;

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.

Rate this question:
36.

You create a SQL Server 2008 Analysis Services (SSAS) solution. Your solution has a date dimension named Date along with a hierarchy named Calendar. The Calendar hierarchy has levels for the Year, Quarter, Month, and Date attributes. You need to create a named set that refers to the first seven months of the year 2007. Which expression should you write for the named set ?
- A.
  
  PeriodsToDate([DimTime].[Yr-Qtr-Mth].[Year], [DimTime].[Yr-Qtr-Mth].[Month].[July 2007])
- B.
  
  PeriodsToDate([DimTime].[Yr-Qtr-Mth].[Month], [DimTime].[Yr-Qtr-Mth].[Month].[July 2007])
- C.
  
  ParallelPeriod([DimTime].[Yr-Qtr-Mth].[Year], 7, [DimTime].[Yr-Qtr-Mth].[Month].[July 2007])
- D.
  
  ParallelPeriod ([DimTime].[Yr-Qtr-Mth].[Month], 7, [DimTime].[Yr-Qtr-Mth].[Month].[July 2007])
Correct Answer
A. PeriodsToDate([DimTime].[Yr-Qtr-Mth].[Year], [DimTime].[Yr-Qtr-Mth].[Month].[July 2007])

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.

Rate this question:
37.

You create a data mining model by using SQL Server 2008 Analysis Services (SSAS). You create a mining structure by using the following Data Mining Extensions (DMX) code. CREATE MINING STRUCTURE [Bike Buyer]( [Customer Key] LONG KEY, [Age] LONG DISCRETIZED(Automatic, 10), [Bike Buyer] LONG DISCRETE, [Commute Distance] TEXT DISCRETE, [Yearly Income] DOUBLE CONTINUOUS ) You need to create a mining model that can be processed by using this mining structure. You also need to ensure that the model uses the Microsoft Decision Trees algorithm. Which DMX statement should you use?
- A.
  
  ALTER MINING STRUCTURE [Bike Buyer] ADD MINING MODEL [Decision Tree] ( [Customer Key], [Age], [Bike Buyer] PREDICT, [Commute Distance], [Yearly Income] )?USING Microsoft_Decision_Trees WITH DRILLTHROUGH
- B.
  
  INSERT INTO [Decision Tree] ( [Customer Key], [Age], [Bike Buyer], [Commute Distance], [Yearly Income] ) OPENQUERY([Bike Buyer], ?'Select [Customer Key], [Age], [Bike Buyer] , [Commute Distance],[Yearly Income]' )
- C.
  
  SELECT * INTO [Decision Tree] USING [Microsoft_Decision_Trees] FROM [Bike Buyer] D. CREATE MINING MODEL [Decision Tree] ( [Customer Key] LONG KEY, [Age] LONG DISCRETIZED(Automatic, 10), [Bike Buyer] LONG DISCRETE, [Commute Distance] TEXT DISCRETE, [Yearly Income] DOUBLE CONTINUOUS )?USING Microsoft_Decision_Trees
Correct Answer
A. ALTER MINING STRUCTURE [Bike Buyer] ADD MINING MODEL [Decision Tree] ( [Customer Key], [Age], [Bike Buyer] PREDICT, [Commute Distance], [Yearly Income] )?USING Microsoft_Decision_Trees WITH DRILLTHROUGH

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

Rate this question:
38.

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?
- A.
  
  NATURAL PREDICTION JOIN
- B.
  
  PREDICTION JOIN
- C.
  
  INNER JOIN
- D.
  
  OPENQUERY
Correct Answer
A. NATURAL PREDICTION JOIN

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

Rate this question:
39.

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?
- A.
  
  Set the Usage property appropriately
- B.
  
  Set the IsAggregatable property appropriately.
- C.
  
  Set the AggregationUsage property appropriately
- D.
  
  Set the AttributeHierarchyEnabled property appropriately
Correct Answer
C. Set the AggregationUsage property appropriately
40.

You create a SQL Server 2008 Analysis Server (SSAS) solution. Your solution contains a measure group named FactMeasures with a single partition. You need to ensure that the following requirements are met: ¡¤Queries that use FactMeasures always refer to the latest version of the source detail data. ¡¤All aggregations that are part of FactMeasures are stored in a multidimensional format Which storage mode should you set the ProactiveCaching property of the partition to?
- A.
  
  Real-time hybrid online analytical processing (HOLAP)
- B.
  
  Real-time relational online analytical processing (ROLAP)
- C.
  
  Automatic multidimensional online analytical processing (MOLAP)
- D.
  
  Scheduled multidimensional online analytical processing (MOLAP)
Correct Answer
A. Real-time hybrid online analytical processing (HOLAP)

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

Rate this question:
41.

You create a SQL Server 2008 Analysis Services (SSAS) solution. You enable proactive caching for a partition. You need to ensure that SSAS can query relational data when multidimensional storage is being updated. What should you do?
- A.
  
  Set the OnlineMode property for the partition to Immediate.
- B.
  
  Set the ProcessingMode property for the partition to Regular.
- C.
  
  Set the OnlineMode property for the partition to OnCacheComplete.
- D.
  
  Set the ProcessingMode property for the partition to LazyAggregations.
Correct Answer
A. Set the OnlineMode property for the partition to Immediate.

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

Rate this question:
42.

You are a server administrator of a SQL Server 2008 Analysis Services (SSAS) instance. The instance contains a database that is used by the members of the Sales group. You configure a new role named Northern Region by using the "{[Customers].[Region].[Region].[Northern]}" allowed permission set. You assign the Sales group to the Northern Region role. You need to verify that users in the Northern Region role can view data only for their region. What should you do?
- A.
  
  Temporarily add your account to the Northern Region role.
- B.
  
  Add the User ID=Northern Region; parameter to the connection string.
- C.
  
  Add the Roles=Northern Region; parameter to the connection string.
- D.
  
  Select the Enable Visual Totals check box for the Northern Region role.
Correct Answer
C. Add the Roles=Northern Region; parameter to the connection string.

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

Rate this question:
43.

You administer a SQL Server 2008 Analysis Services (SSAS) database. A sales manager is responsible for the sales of bikes in the Northeast region. You plan to grant the necessary permissions to the sales manager to access the database. You have two roles named Northeast Region and Bikes. The schemas of the two roles are as shown in the following table. Role Allowed set Northeast Region {[Sales Territory].[Sales Territory Region].&[Northeast]} Bikes {[Product].[Product Category].[Product Category].&[Bikes]} The Visual Totals property is set to True for both roles. You need to ensure that the sales manager can view only the members in the Product dimension that relate to the Bikes category in the Northeast region. What should you do?
- A.
  
  Add the manager to the Bikes role.
- B.
  
  Add the manager to the Northeast Region role.
- C.
  
  Add the manager to a new role that has the following two components: ¡¤{[Sales Territry].[Sales Territory Region].&[Northeast]} as the allowed set. ¡¤{[Product].[Product Category].[Product Category].&[Bikes]} as the allowed set
- D.
  
  Add the manager to a new role that has the following four components: ¡¤{[Sales Territory].[Sales Territory Reion].AllMembers} as the denied set ¡¤{[Sales Territory].[Sales Territory Region].&[Northeast]} as the allowed set ¡¤{[Product].[Product Category].[Product Category].AllMembers} as the denied set. ¡¤{[Product].[Product Category].[Product Category].&[Bikes]}as the allowed set.
Correct Answer
C. Add the manager to a new role that has the following two components: ¡¤{[Sales Territry].[Sales Territory Region].&[Northeast]} as the allowed set. ¡¤{[Product].[Product Category].[Product Category].&[Bikes]} as the allowed set

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.

Rate this question:
44.

You update a role named Managers for the Human Resources cube in your SQL Server 2008 Analysis Services (SSAS) database. The database contains a dimension named Employee. You plan to configure the security for the Managers role. You need to allow the Managers role to access aggregate data based on only three or less employees. What should you do?
- A.
  
  Add members of the [Employee].[Employees] hierarchy that contain three or less children to the denied set for the Managers role.
- B.
  
  Add members of the [Employee].[Employees] hierarchy that contain more than three children to the allowed set for the Managers role.
- C.
  
  Add the following expression to the Allow Reading property in Cell Data in the Managers role. Descendants([Employee].[Employees].CurrentMember,,LEAVES).Count
- D.
  
  Add the following expression to the Allow Reading property in Cell Data in the Managers role. IIF(Descendants([Employee].[Employees].CurrentMember,,LEAVES).Count
Correct Answer
C. Add the following expression to the Allow Reading property in Cell Data in the Managers role. Descendants([Employee].[Employees].CurrentMember,,LEAVES).Count

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.

Rate this question:
45.

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?
- A.
  
  Add all the country members except those of France and Germany to the Allowed Set property.
- B.
  
  Add the following set to the Denied Set property. {[Customer].[Country].&[Germany] ,[Customer].[Country].&[France]}
- C.
  
  Add the following set to the Denied Set property. Except([Customer].[Country].[Country] ,{[Customer].[Country].&[Germany] ,[Customer].[Country].&[France]})
- D.
  
  Add the following set to the Allowed Set property. Extract({[Customer].[Country].&[Germany] ,[Customer].[Country].&[France]} ,[Customer].[Country])
Correct Answer
B. Add the following set to the Denied Set property. {[Customer].[Country].&[Germany] ,[Customer].[Country].&[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.

Rate this question:
46.

You maintain a SQL Server 2008 Analysis Services (SSAS) database on a production server. The database contains a cube. You add new functionality to the cube on a development server. You need to deploy the new functionality from the development server to the production server by ensuring that the effect on the users is minimized. What should you do?
- A.
  
  Use the Synchronize Database Wizard.
- B.
  
  Copy the appropriate files from the development server to the production server.
- C.
  
  Detach the production database. Copy the appropriate files to the production server, and then attach the database on the production server
- D.
  
  Detach the production database. Copy the appropriate files to the production server, and then attach the database on the production server
Correct Answer
A. Use the Synchronize Database Wizard.

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

Rate this question:
47.

You maintain a SQL Server 2008 Analysis Services (SSAS) database. You create a new measure group in one of the cubes. You have a program that maintains partitions on the production copy of the cube. You need to update the cube definition on the production server without overwriting any existing partitions. What should you do?
- A.
  
  Use the Deployment Wizard along with the appropriate options.
- B.
  
  Execute an UPDATE CUBE statement in Microsoft SQL Server Management Studio (SSMS).
- C.
  
  Use the Analysis Services Destination component in Microsoft SQL Server Integration Services (SSIS)
- D.
  
  Use the Business Intelligence Development Studio (BIDS) and set the processing option to Do Not Process.
Correct Answer
A. Use the Deployment Wizard along with the appropriate options.

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

Rate this question:
48.

You maintain a SQL Server 2008 Analysis Services (SSAS) instance. You need to configure the analysis services query log for the SSAS instance. Which tool should you use?
- A.
  
  SQL Server Configuration Manager
- B.
  
  SQL Server Management Studio
- C.
  
  SQL Server Error and Usage Reporting
- D.
  
  SQL Server Business Intelligence Development Studio
Correct Answer
B. SQL Server Management Studio

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

Rate this question:
49.

You maintain a SQL Server Analysis Services (SSAS) database. The database is configured by using multiple security roles. The database is accessed by a Microsoft ASP.NET application that runs on a remote computer. The application is configured to use Windows Authentication. You need to ensure that the users of the application can successfully access the SSAS database. You also need to ensure that security restrictions of the roles are applied. What should you do?
- A.
  
  Configure Kerberos authentication.
- B.
  
  Configure Analysis Services for HTTP authentication.
- C.
  
  Set the AnonymousConnectionsEnabled policy to True.
- D.
  
  Set the Security\RequireClientAuthentication property to True.
Correct Answer
A. Configure Kerberos authentication.

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

Rate this question:
50.

You maintain a SQL Server 2008 Analysis Services (SSAS) instance. The instance contains a database. You change the DataDir property of the instance to a new folder. You need to ensure that the instance uses the new folder in the minimum amount of downtime. What should you do?
- A.
  
  Process the database.
- B.
  
  Synchronize the database.
- C.
  
  Restart the Analysis Services service.
- D.
  
  Delete the files in the original folder, and then restart the Analysis Services service.
Correct Answer
A. Process the database.

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.

Rate this question:

Quiz Review Timeline +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

Current Version
Mar 22, 2023

Quiz Edited by
ProProfs Editorial Team
Nov 10, 2012

Quiz Created by
Ranaguha

MSBI 70-448

You build and deploy a SQL Server 2008 Integration Services (SSIS) package. During package development, the XML configuration is not specified. You need to apply the configuration to the deployed package. What should you do?

You plan to use SQL Server 2008 Integration Services (SSIS) on a SQL Server 2008 failover cluster. You have a complex package that takes a long time to run. You need to ensure that if the package executes during a failover, the package can be resumed at the point of failure. What should you do?

You 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?

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

You administer an instance of a SQL Server 2008 server. The server is used to execute SQL Server 2008 Integration Services (SSIS) packages. You need to ensure that the server executes only correctly signed packages. What should you do?

You 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?

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?

You create a SQL Server 2008 Analysis Services (SSAS) solution. You enable proactive caching for a partition. You need to ensure that SSAS can query relational data when multidimensional storage is being updated. What should you do?

You maintain a SQL Server 2008 Analysis Services (SSAS) instance. You need to configure the analysis services query log for the SSAS instance. Which tool should you use?

You 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?