SQL 70-448 certification Prep

Card Set Information

Author:
donniekjv1611
ID:
225804
Filename:
SQL 70-448 certification Prep
Updated:
2013-08-08 22:42:01
Tags:
Microsoft sql certification test prep
Folders:

Description:
This is sample questions and answer for preparation to be certified for 70-448 Microsoft SQL Server 2008: Business Intelligence Development and Maintenance
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user donniekjv1611 on FreezingBlue Flashcards. What would you like to do?


  1. You are managing several SQL Server Integration Services (SSIS) packages.

    You want to deploy your project, named SalesETL, by using the package deployment utility. You have configured the project for the DeploymentOutputPath to be binDeployment and set the CreateDeploymentUtilty property to True.

    After you build the project, which file under the binDeployment folder will you use to deploy your packages?

    1. SalesETL.dtsConfig

    2. SalesETL.dtsx

    3. SalesETL.SSISDeploymentManifest 

    4. SalesETL.dtsxDeploy
    Answer: 

    3. SalesETL.SSISDeploymentManifest 

    Explanation:


    The package deployment utility will create an SSISDeploymentManifest file under the DeploymentOutputPath folder. This file is the Installer Kit used to deploy your packages. After you move the installer kit to a server with SQL Server SSIS installed, you can run the .SSISDeploymentManifest file, and it will launch the package installation wizard.The SSIS packages are also included in the Installer kit with the .dtsx extension, and any XML configuration files with the .dtsConfig extension (the default extension) are also included, but these files are not used to launch the deployment of your SSIS project.
  2. You are designing a SQL Server Integration Services (SSIS) package. You have created a Master package named Master.dtsxthat uses an Execute Package task to call a child package named Child.dtsx. In Master.dtsx, you have created a FilePath variable.

    You would like child.dtsx to use the value of the FilePath variable that is stored in Master.dtsx.

    How would you accomplish this? (Each correct answer presents part of the solution. Choose two.)

    1. In Child.dtsx, create a Parent Package Variable configuration. Set the Parent variable for the configuration to FilePath.

    2. In Child.dtsx, create a variable named FilePath. Set the scope of the variable to Child. 

    3. In Parent.dtsx, create a Parent Package Variable configuration. Set the Parent variable for the configuration to FilePath. InMaster.dtsx, edit the Set Values property of the Execute Package task to package.variables[FilePath].

    4. In Child.dtsx, create a variable named FilePath. Set the scope of the variable to Master
    Answer:

    1. In Child.dtsx, create a Parent Package Variable configuration. Set the Parent variable for the configuration to FilePath.<Correct>

    2. In Child.dtsx, create a variable named FilePath. Set the scope of the variable to Child. <Correct>

    Explanation:

    The Parent Package Variable configuration pulls the value of a variable from a parent package. Set the scope of the variable to Child because you are working within the Child package.

    • The scope of the variable in the child package cannot be set to Master because Master is not part of the Child.dtsx package.
  3. While working on a report, a user asks if the report can be automatically run once a week and delivered to the users. You are evaluating the use of a subscription to meet this requirement.What delivery method is NOT supported by subscriptions?

    1. Shared folder

    2. E-mail

    3. SMS 

    4. SharePoint document library
    3. SMS <Correct>

    Explanation:

    SMS is not a supported delivery method.

    SSRS supports e-mail, shared folder, and SharePoint document library delivery methods by default.
  4. You are managing several SQL Server Integration Services (SSIS) packages.You wish to allow only the set of SSIS Development managers to execute SSIS packages on the production server. The SSIS Development managers have been added to the SSISDevMgr Windows group. How do you ensure that the SSIS Development managers can execute the SSIS packages?

    1. Deploy the packages to the production SQL server. Add the SSISDevMgr group to the role db_ssisoperator.

    2. Update the package's ProtectionLevel to Encrypt All With Password. Give the SSIS Development managers the package password. Deploy the packages to the production server's file system.

    3. Update the package's ProtectionLevel to Encrypt All With User Key. Set the User key to SSISDevMgr. Deploy the packages tothe production server's file system.

    4. Deploy the packages to the production SQL server. Add the SSISDevMgr group to the role db_datareader.
    1. Deploy the packages to the production SQL server. Add the SSISDevMgr group to the role db_ssisoperator.<Correct>

    Explanation:

    Setting the package protection level to Encrypt All With User Key or Encrypt All With Password will protect the package from beingopened unless the package password is provided or the same user opens the package on the same machine. To secure packagesthat are deployed to SQL Server, use the SSIS-related mdsb roles and assign the correct Windows users or groups to the role.The following roles are available:The db_ssisltduser user can view, execute, and import only the packages created by the user.The db_ssisoperator user can view and execute any packages on the server but cannot import a package.The db_ssisadmin user can view, execute, and import any packages on the server.The role db_datareader role is related only to data in the defined database, not to viewing, executing, or importing packages.
  5. You are developing a SQL Server Integration Services (SSIS) package. The package contains a variable named ErrorRows.
    ErrorRows is updated by a Row Count transformation placed on a data flow before lookup errors are placed into a SQL table. You need to configure the package to send an e-mail message, with the number of rows being sent, to the error table.How would you configure the package to use a Send Mail task? (Each correct answer presents part of the solution. Choose two.)

    1. Create an OnError event handler.

    2. Set the Raise Change Event values for the variable to False.

    3. Create an OnTaskFailed event handler.

    4. Set the Raise Change Event values for the variable to True. 

    5. Create an OnVariableValueChanged event handler.
    4. Set the Raise Change Event values for the variable to True. <Correct>

    5. Create an OnVariableValueChanged event handler. <Correct>

    Explanation:

    Setting the Raise Change Event value to True for the variable allows the OnVariableValueChanged event to be raised. Send Mail can then be used inside the OnVariableValueChanged event handler.Setting OnError and OnTaskFailed would not help in this scenario because you want to determine when the value of the variable changes.



  6. You are a Report Server administrator at Contoso Pharmaceuticals, and you want to enable the user contosoKen to manage allthe server's content without modifying security for the items. You decide to create a new role.Of the items listed, which item would you NOT enable for the new role?

    1. Manage data sources

    2. Manage reports

    3. Manage resources

    4. Set security for individual items 

    5. Create linked reports

    6. Manage folders

    7. Manage individual subscriptions

    8. View reports
    4. Set security for individual items <Correct>

    Explanation:

    The only permission you need NOT enable for the new item role is Set Security For Individual Items.

    To give contosoKen the right access based on the requirement, you would enable this full list of permissions except the item above:

    • Consume reports
    • Create linked reports
    • Manage all subscriptions
    • Manage data sources
    • Manage folders
    • Manage individual subscriptions
    • Manage models
    • Manage report history
    • Manage reports
    • Manage resources
    • View data sources
    • View folders
  7. You are managing several SQL Server Integration Services (SSIS) packages. You are deploying your packages by using a deployment utility.Which of the following items contained in your project will NOT be deployed with the deployment utility?

    1. XML configuration files

    2. .dtsx files

    3. Miscellaneous project files

    4. Custom SSIS components
    4. Custom SSIS components <Correct>

    Explanation:

    The package deployment utility will deploy all packages in the project, the miscellaneous files, and any package XML configuration files. When you run through the Package Installation Wizard, you are prompted to choose the file system location to which thesefiles will be deployed, and any package references to these files will be updated.

    If you are using any custom SSIS components, such as a custom task or custom transformation, these components are not moved over with the deployment utility. Instead, you must manually move these files to the server where the packages will execute and register the components on the server to enable the SSIS packages to access them.
  8. You are building an Analysis Services cube that analyzes sales for your stores. One of the analytic requirements is that you understand the number of customers you serve. You have a measure called CustomerSalesCount that counts the number of sales transactions across your stores and products and the sales dates. However, several customers shop at your stores frequently, and you want to count each customer only once.This new measure, called CustomerCount, should return the number of unique customers no matter whether someone is browsing by store, product, or date (including weeks, months, or years). How would you implement this requirement to achieve this functionality and the best performance?

    1. Create a new Customer Sales Count fact table in the relational database that has only one record per customer per product purchased per day. Create a new measure group called Customer Sales Count in the cube based on this new fact table. Create a CustomerCount measure that uses the Count aggregation function.

    2. In the existing Sales measure group, add a new measure called CustomerCount that uses the DistinctCount aggregation function based on the Customer Key column.

    3. Create a new measure group called Customer Sales Counts that is based on the same fact table as the Sales measure group.In the new measure group, add a new measure called CustomerCount that uses the DistinctCount aggregation function based on the Customer Key column. 

    4. In the existing Sales measure group, add a new measure called CustomerCount that uses the Count aggregation function.
    3. Create a new measure group called Customer Sales Counts that is based on the same fact table as the Sales measure group.In the new measure group, add a new measure called CustomerCount that uses the DistinctCount aggregation function based on the Customer Key column. <Correct>

    Explanation:

    To achieve a unique count of customers regardless of whether someone is displaying the count by store, product, category, year,month, day, or any combination of attributes, you must use the DistinctCount aggregation function on the Customer Key column.The best way to implement this is to create a new measure group on the same fact table as the sales to ensure that the distinctcount is optimized for processing.The Count aggregation function will not return the correct unique count of customers, even if a new fact table is created. This is because the distinct count requirements need to work at higher levels in the dimension hierarchies, and only the DistinctCount aggregation function can achieve that
  9. You are developing a SQL Server Integration Services (SSIS) package to create a dimension table. You are using an OLE DBsource to pull in the source data and would like to check whether the records already exist in the dimension table.
    Besides Lookup Transformation, what is another method you can use to look up the dimension records?

    1. Add a second OLE DB source and join the data, using the Slowly Changing Dimension Wizard.

    2. Add a second OLE DB source and join the data, using a Merge transformation.

    3. Add a second OLE DB source and join the data, using a Merge Join transformation.

    4. Add a second OLE DB source and join the data, using a Union All transformation.
    3. Add a second OLE DB source and join the data, using a Merge Join transformation. <Correct>

    Explanation:

    The Merge Join transformation takes the output of two sorted datasets and uses the INNER, LEFT or OUTER join to join the data together.

    The Union All transformation creates a new row from each data source rather than matching data.

    A Merge transformation behaves the same as a Union All transformation except that it merges only two data sources, and both data sources must be sorted in the same way.

    The Slowly Changing Dimension Wizard determines whether new data in the data flow matches the existing data in a table.
  10. You are implementing a SQL Server Integration Services (SSIS) package. You are populating a sales database. The Sales table has a numeric column called Units Sold. You would like to validate that the Units Sold value is properly distributed before finishing the package.

    What type of data viewer would you add to this package?

    1. Grid

    2. Scatter Plot

    3. Histogram 

    4. Column Chart
    3. Histogram <Correct>

    Explanation:

    The Histogram data viewer displays the values from one numeric column as a distribution of columns.

    The Grid data viewer shows you the actual data values.

    The Scatter Plot setting compares numeric values.

    The Column Chart setting breaks down discrete data values and shows them as columns.
  11. You have a report that you have made interactive by toggling hidden regions. This report will be viewed through Report Manager but will also be rendered in different formats.

    Which rendering formats support hidden regions? (Each correct answer presents part of the solution. Choose two.)

    1. Word

    2. Excel 

    3. HTML 

    4. PDF
    2. Excel <Correct>

    3. HTML <Correct>

    Explanation:

    HTML and Excel rendering formats support hidden regions.

    The PDF and Word renderers do not support interactive features such as hidden regions.
  12. You are developing a SQL Server Analysis Services (SSAS) cube. You have a data mining structure that must forecast the day on which a set of products will sell.How would you configure the Date column?

    1. Set the Content property as Cyclical and the column as Predictable.

    2. Set the Content property as Discrete and the column as Predictable. 

    3. Set the Content property as Continuous and the column as Predictable.

    4. Set the Content property as Discretized and the column as Predictable.
    2. Set the Content property as Discrete and the column as Predictable. <Correct>

    Explanation:

    Because the date column has a distinct list of values at the day level, the Content property of the data should be marked as Discrete. Continuous data has values that are numerical in nature with a high level of uniqueness at a low granularity, which does not fit the Date column values. Continuous data can also be grouped into buckets or ranges, which would require the Content property to be set to Discretized. Cyclical data contains values in a recurring ordered pattern such as numbers of days in a week and, therefore, is not the right choice for the Date column.

    Therefore, the best choice is setting the Content property to Discrete to match the type of data contained in the Date column.
  13. You are developing a SQL Server Integration Services (SSIS) package. In one of your packages, you are performing a Fuzzy Lookup transformation to help clean up the StudentName column. You have noticed that not enough student names are being detected by the transformation.

    What adjustment should you make to the transformation?

    1. Increase the Similarity Threshold value.

    2. Decrease Maximum Number Of Matches to Output Per Line.

    3. Increase Maximum Number Of Matches to Output Per Line.

    4. Decrease the Similarity Threshold value.
    4. Decrease the Similarity Threshold value. <Correct>

    Explanation:

    The Similarity Threshold value sets the resemblance factor of the StudentName column to the lookup value. The closer the value is to 1, the more similar the values must be.

    You want to decrease the Similarity Threshold value to increase the number of fuzzy matches.

    If you change Maximum Number Of Matches, more rows might be added to the output.
  14. You are creating a cube in a SQL Server Analysis Services (SSAS) database that has a dimension named DimDate. The fact table, FactSales, has two relationships to the date dimension, OrderDateKey and ShipDateKey.

    How would you configure the cube so that the measure group for the Fact Sales table can join the Date dimension twice so that the measures in FactSales can be viewed for both the OrderDateKey and ShipDateKey relationships?

    1. Create one dimension usage relationship between DimDate and FactSales by using a many-to-many relationship to associate both the OrderDateKey and ShipDateKey relationships.

    2. Create one dimension usage relationship between DimDate and FactSales by using a regular relationship.

    3. Create two dimension usage relationships between DimDate and FactSales by using a many-to-many relationship for both.

    4. Add the Date dimension twice in the cube. For one of the usage relationships in the cube, use OrderDateKey and, for the other,use ShipDateKey.
    4. Add the Date dimension twice in the cube. For one of the usage relationships in the cube, use OrderDateKey and, for the other,use ShipDateKey. <Correct>

    Explanation:

    To relate the Fact Sales measure group to the date dimension for two date keys in the fact table, you need to include the date dimension in the cube twice. For each use of the date dimension in the cube, define the dimension usage relationship to use one of the keys.

    A single dimension used in a cube cannot be associated with more than one dimension key in the fact table.

    Use a many-to-many relationship when more than one dimension attribute member relates to a single record in the underlying fact table and it requires an intermediate measure group to resolve the relationship.
  15. You've developed a report, but users are complaining that when the report is saved as a PDF file, an extra blank page is included for every page in the report. Which properties need to be reviewed to resolve odd blank pages when a report is rendered to PDF? (Each correct answer presents a complete solution. Choose three.)

    1. InteractiveSize Height

    2. Margins Top

    3. PageSize Height

    4. PageSize Width 

    5. Margins Right

    6. Margins Left 

    7. InteractiveSize Width

    8. Margins Bottom
    4. PageSize Width <Correct>

    5. Margins Right <Correct>

    6. Margins Left <Correct>

    Explanation:

    This is caused when the report content is wider than the usable page area. The usable page area is the physical page that remains after space is allocated for margins, column spacing, and so on. Decreasing the MarginLeft or MarginRight properties will increase the usable page area. You could also change the PageSize Width property to be sure that the width of the page is sufficient for the report rendering.
  16. You are designing a SQL Server Integration Services (SSIS) package. You currently have three Data Flow tasks: tasks A, B, and C. You have tasks A and B running concurrently and task C running after both complete successfully. You want to set up a constraint so that task C will run if task A succeeds, regardless of whether task B succeeds or fails. How would you accomplish this?

    1. Add a precedence constraint between task A and task B. Under Multiple Constraints, select Logical OR.

    2. Change the precedence constraint between task B and C to On Completion. 

    3. Add a precedence constraint between task B and task C. Under Multiple Constraints, select Logical OR.

    4. Add a precedence constraint between task A and task C. Set Evaluation Operation to Expression or Constraint.
    2. Change the precedence constraint between task B and C to On Completion. <Correct>

    Explanation:

    An expression or constraint would not evaluate the status of task B. The precedence constraint needs to be between "A and C"and "B and C" with the constraint between tasks B and C set to On Completion.

    A precedence constraint between task A and task B would not dictate when C would execute. An expression between task A and C would not take into account what happens between tasks B and C.

    The Logical OR constraint allows a constraint to execute regardless of the outcome of the other constraints in the task.
  17. You are managing a SQL Server Analysis Services (SSAS) cube. The cube contains a Total Sales measure that is joined to the Store dimension. Analysts report that the queries are slow when the Store Name is added to the query. After reviewing several of the queries, you see that the Store Name attribute is used in almost every query to isolate or compare the approximately 150 stores. You decide to review the aggregation usage to see whether you can improve the time it takes to retrieve the Sales Total information. In the Aggregation Design Wizard, what Aggregation Usage value should you select for the Store Name attribute in the Store dimension?

    1. None

    2. Unrestricted

    3. Full 

    4. Default
    3. Full <Correct>

    Explanation:

    Because the Store Name attribute is often used in queries, you can force the attribute to be part of every aggregation by setting its Aggregation Usage property to Full. The Aggregation Usage of Full should be avoided when the attributes contain large amounts of data, but in this case, there are a limited number of stores.

    Setting the Aggregation Usage to None would prevent the attribute from being included in any aggregations.

    A setting of Unrestricted enables the wizard to choose attributes to use in aggregations based on counts and aggregationpercentages and does not ensure that the Store Name attribute will be used in an aggregation.

    The Default depends on the type of dimension and attribute. In this case, the default is not set to Full aggregations
  18. Unfortunately, your Reporting Services server had an internal hard drive failure. The networking team has reinstalled the operating system and reinstalled Reporting Services. You have restored the backup of the ReportServer database and now need to restore the encryption key. Which operation restores encryption keys from a backup? (Each correct answer presents a complete solution. Choose two.)

    1. Restore the ReportServerTemp database.

    2. Use the Reporting Services Configuration tool. 

    3. Execute "rskeymgmt -e -f c:rskey.snk -p<password>".

    4. Execute "rskeymgmt -a -f c:rskey.snk -p<password>".
    2. Use the Reporting Services Configuration tool. <Correct>

    4. Execute "rskeymgmt -a -f c:rskey.snk -p<password>". <Correct>

    Explanation:

    The correct command to restore the encryption keys is rskeymgmt -a -f c:rskey.snk -p<password>. The -a switch replaces an existing key with a copy. The -e switch extracts the key so that it can be copied to a file. You can also restore encryption keys in the Reporting Services Configuration tool by using the Encryption Keys pane.

    The encryption keys are saved as a file when a backup is performed. The ReportServerTemp database is not related to the encryption keys.
  19. You have a dataset in a report that needs to be filtered after the data is received by Reporting Services.

    What does a dataset filter require? (Each correct answer presents part of the solution. Choose two.)

    1. The dataset requires one or more filter equations. 

    2. The data types of the filtered data and the value must match. 

    3. Filtering must be based on aggregated values for the dataset.

    4. The filters can be applied only at design time.
    1. The dataset requires one or more filter equations. <Correct>

    2. The data types of the filtered data and the value must match. <Correct>

    Explanation:

    The data types of the filtered data and the value must match.

    A filter equation is the action applied by the filter. A filter must contain at least one filter equation.

    Filtering on aggregate values for a dataset and data regions is not supported.

    Dataset filters are applied at run time. The order of application is dataset, data region, group.
  20. You would like users of a report to select the value of a parameter from a list of predefined values. The values are stored in the database.

    Which of the following steps will help you configure the report? (Each correct answer presents part of the solution. Choose two.)

    1. Add a new Table report item.

    2. Create a new dataset to query the list of predefined values. 

    3. Assign the dataset and field on the Available Values tab of the Report Parameter Properties dialog box for the parameter.

    4. Add a new dataset parameter.
    2. Create a new dataset to query the list of predefined values. <Correct>

    3. Assign the dataset and field on the Available Values tab of the Report Parameter Properties dialog box for the parameter.<Correct>

    Explanation:

    You must create a new dataset to query the list of predefined values and assign this dataset to the parameter, using the Available Values tab of the Report Parameter Properties dialog box.

    Creating a dataset parameter enables the user to filter the data displayed in the report, but it does not provide the ability to select from a list of available values.

    A table report item displays data related to the rendered report, not the parameters used to query the data source.
  21. You are deploying a SQL Server Integration Services (SSIS) package.

    The package contains a direct XML configuration that sets the connection string of the SQL connection. The XML file resides on C:SSIS. You have deployed the package and XML file to the D:SSIS folder of the production server.

    When you execute the package, you receive an error stating Cannot Acquire Connection from Connection Manager.

    Which steps should you take to resolve this error?

    1. Ensure that the XML configuration file exists on the C:SSIS drive of the production server. 

    2. Ensure that the SSIS package file exists on the C:SSIS drive of the production server.

    3. Open the package in BIDS and configure Connection Manager to point to the production server.

    4. Open the package in BIDS and set the IgnoreErrors package property to True.
    1. Ensure that the XML configuration file exists on the C:SSIS drive of the production server. <Correct>

    Explanation:

    A direct XML configuration will look at a specific location for the XML file. Therefore, when the package runs and the file is not there, the XML file cannot be loaded. This will either cause a connection failure or the wrong connection to be used. To resolve this, the XML file needs to be in the same location as on the development server. Simply moving the package to the same location will not resolve the problem.

    Opening the package in BIDS after it has been deployed requires first that a project is created; changing the connection will defeat the purpose of using an XML file configuration.

    If the folder path changes on each server, an indirect configuration should be used.
  22. You are developing a SQL Server Integration Services (SSIS) package. You have attempted to use a Merge Join transformation to join two sorted SQL statements, using OLE DB data sources. You receive an error stating that the IsSorted property must be true for both sources of the transformation.

    How would you correct the error while using minimal resources? (Each correct answer presents part of the solution. Choose two.)

    1. Edit the properties of each OLE DB source and set the IsSorted property to True.

    2. Open Editor in the Merge Join transformation and set DataSourceIsSorted to True.

    3. Open Advanced Editor for each OLE DB source and set the IsSorted property to True. 

    4. Set SortKeyPosition in each OLE DB source for each column that is sorted in the SQL statement. 

    5. Add a Sort transformation after each of the OLE DB sources.
    3. Open Advanced Editor for each OLE DB source and set the IsSorted property to True. <Correct>

    4. Set SortKeyPosition in each OLE DB source for each column that is sorted in the SQL statement. <Correct>

    Explanation:

    Because the data coming from the SQL statements for the two OLE DB sources is already sorted, adding a Sort transformation would add additional processing to the package. The IsSorted property is found by opening Advanced Editor for the OLE DBsources.

    When working with Advanced Editor, ensure that SortKeyPosition matches the sorted columns in the SQL statement.

    The Merge Join requires that the sources are pre-sorted and the inputs therefore need to be marked as sorted before they reach the Merge Join.
  23. You are designing a SQL Server Integration Services (SSIS) package and currently have a Control Flow task that has two SQLServer tasks. If one of the SQL Server tasks fails, you want to roll back the transaction for both of the SQL Server tasks.

    How would you set this up?

    1. Add two Sequence Containers to the package. Place each SQL Server task in a sequence container. Set TransactionOption to Supported.

    2. Add one Sequence Container to the package and place both SQL Server tasks in the sequence container. Set TransactionOption to Required. 

    3. Add one Sequence Container to the package and place both SQL Server tasks in the sequence container. Set TransactionOption to Supported.

    4. Add two Sequence Containers to the package. Place each SQL Server task in a sequence container. Set TransactionOption to Required.
    2. Add one Sequence Container to the package and place both SQL Server tasks in the sequence container. Set TransactionOption to Required. <Correct>

    Explanation:

    The Required option indicates that SSIS uses a single transaction for the sequence container. All items in the container are held in a transaction.

    A single container allows all values in the transaction to be rolled back together. If these values were in two sequence containers,the first container would not roll back if the second failed.

    The Supported option indicates that a transaction will not begin but only participate in the transaction of a parent container or the package as a whole. Adding two sequence containers causes the transaction to roll back only a single task.
  24. As part of your recovery strategy for SQL Services Reporting Services (SSRS), you need to back up your encryption keys and store them in a secure location in case the server needs to be rebuilt.

    Which methods can be used to both back up and restore your SSRS encryption keys? (Each correct answer presents a completesolution. Choose two.)

    1. Reporting Services Configuration Manager tool 

    2. Rsconfig command-line tool

    3. Rskeymgmt command-line tool 

    4. Report Manager connected through HTTP or SSMS
    1. Reporting Services Configuration Manager tool <Correct>

    3. Rskeymgmt command-line tool <Correct>

    Explanation:

    The rskeymgmt tool can both back up and restore the SSRS symmetric encryption key. In addition, the Reporting Services Configuration Manager tool can also back up and restore the keys.

    The RSConfig utility encrypts and stores connection and account values in the RSReportServer.config file.

    Report Manager and SSMS cannot back up and restore encryption keys.
  25. You are deploying a SQL Server Integration Services (SSIS) package. You have added a digital signature to the package. Every time the package is loaded in BIDS, you would like to verify that the digital signature on the package is checked.

    Which of the following properties would you set to True?

    1. ProtectionLevel (package property)

    2. Show Warning If Package Is Unsigned (Visual Studio 2008 Options)

    3. DelayValidation

    4. Check Digital Signature When Loading A Package (Visual Studio 2008 Options)
    4. Check Digital Signature When Loading A Package (Visual Studio 2008 Options) <Correct>

    Explanation:

    The Check Digital Signature When Loading A Package (CheckSignatureOnLoad application class property) indicates whether a certificate must be checked when loading the package. This property is set in BIDS by navigating from the Options menu to the Integration Services Designers options.

    The Show Warning If Package Is Unsigned will display only a warning, but the package will still load and execute.

    The ProtectionLevel and DelayValidation package level properties do not relate to digital signatures.
  26. You are deploying the packages in a SQL Server Integration Services (SSIS) project by using a deployment utility.

    You have specified a SQL Server deployment. The deployment utility has deployed the XML configuration file with the packages.

    Where is the configuration file saved?

    1. In the location specified on the Installation Folder page of the Package Installation Wizard 

    2. Same location as the dtsx files

    3. msdb database

    4. master database
    1. In the location specified on the Installation Folder page of the Package Installation Wizard <Correct>

    Explanation:

    You have specified a SQL Server deployment, so the packages will be stored in the msdb database. The configuration file is installed on the location specified on the Installation Folder page of the Package Installation Wizard when the wizard asks for the file system location of dependent files.

    After you specify the location, the package installation wizard will also update any packages in the project that reference the XML configuration file so that when the packages are executed, they will have the correct path to the configuration files.
  27. You install SQL Server Analysis Services (SSAS) on a production server. You need to disallow the local Windows administrators (members of the Windows Administrators group) administrative access to the SSAS server and its objects.

    Which server property must you change to deny local Windows administrators administrative access to the Analysis Services server?

    1. SecurityServiceAccountIsServerAdmin

    2. SecurityRequireClientAuthentication

    3. SecurityBuiltinAdminsAreServerAdmins 

    4. To prevent server lockdown, SSAS doesn't enable you to revoke administrative rights for Windows administrators.
    3. SecurityBuiltinAdminsAreServerAdmins <Correct>

    Explanation:

    You can set the SecurityBuiltinAdminsAreServerAdmins server property to False to deny administrative rights to Windows local administrators.

    The SecurityRequireClientAuthentication property indicates whether client authentication is required.

    The SecurityServiceAccountIsServerAdmin property indicates whether the account the SSAS Windows services runs under has administrative privileges
  28. You are developing a SQL Server Integration Services (SSIS) package. You are currently pulling 100 rows of data from an OLE DB source, 20 rows from a Flat File source, and 50 rows of data from an Excel source. You want to merge these rows together to get 170 rows of data.

    Which Data Flow task would you use?

    1. Union All 

    2. Lookup

    3. Merge Join

    4. Multicast
    1. Union All <Correct>

    Explanation:

    A Union All task combines the data from multiple inputs into one output, thus merging the 100 rows from the OLE DB source with the 20 rows from the Flat File source and the 50 rows from Excel.

    The Merge Join task joins two sorted datasets, using the FULL, LEFT, or OUTER JOIN clause.

    The Lookup task pulls all the rows from one source and matches them to another data source, resulting in a row-per-row display in the original source.

    The Multicast task splits the data out rather than merging them.
  29. You have created a Customer Profile data mining structure that contains two data mining models. You fully process the structure to train the mining models. You need to load the structure with new training data without affecting the mining models.

    Which processing option do you need to use to process a structure without affecting the mining models in the structure?

    1. Process Clear Structure

    2. Unprocess

    3. Process Full

    4. Process Structure
    4. Process Structure <Correct>

    Explanation:

    Process Structure loads the structure with data without affecting the models in the structure.

    Process Clear Structure removes the training cases from the structure.

    The Unprocess option removes data from the structure and mining models.

    Finally, Process Full processes both the structure and the mining models.
  30. You are managing a SQL Server Analysis Services (SSAS) cube. You want to back up an SSAS database in SQL Server Management Studio (SSMS) and the C:Temp folder to appear in the list of backup locations in SSMS when you perform a backup of a database.

    What must you do for the C:Temp folder to appear in the backup folder list?

    1. Add the C:Temp folder to the DataDir server property.

    2. Add the C:Temp folder to the BackupDir server property.

    3. Add the C:Temp folder to the AllowedBrowsingFolders server property. 

    4. You cannot save backup files to any location other than the default backup folder.
    3. Add the C:Temp folder to the AllowedBrowsingFolders server property. <Correct>

    Explanation:

    The allowed backup folders are listed in the AllowedBrowsingFolders server property. The default backup folder is the location of the installation folder in Program Files as specified by the BackupDir server property. If you want to save the backup file to a different folder, add that folder to the AllowedBrowsingFolders property. The DataDir property defines the default location for the MOLAP and HOLAP data for the cubes and dimensions within an SSAS installation.
  31. You have a large partition that is configured for proactive caching. You don't want end users to experience performance degradation while the Multidimensional OLAP (MOLAP) cache is being rebuilt.

    How do you configure the proactive caching to ensure that the server never switches to Relational OLAP (ROLAP) while the MOLAP cache is being rebuilt?

    1. Clear the Bring Online Immediately check box in the proactive caching storage options. 

    2. Select the Bring Online Immediately check box in the proactive caching storage options.

    3. Select the Update The Cache Periodically check box.

    4. Select the Enable ROLAP Aggregations check box
    1. Clear the Bring Online Immediately check box in the proactive caching storage options. <Correct>

    Explanation:

    When you clear the Bring Online Immediately check box, the server never reverts to ROLAP. Instead, it will wait until the new MOLAP cache is ready before switching to it.

    The Update The Cache Periodically option schedules the new cache to be built automatically, irrespective of the data activity.

    The Enable ROLAP Aggregations option controls whether the server uses materialized views for aggregations
  32. You want to present report information filtered by category.

    What is the difference between using parameters in the report query and filtering the information in the dataset? (Each correct answer presents part of the solution. Choose two.)

    1. Filtering the dataset might be slower than using parameters when you're not using cached reports. 

    2. Filtering the dataset is always faster than using parameters.

    3. Filtering the dataset enables you to use a cached report that reuses the same data. 

    4. Filtering the dataset is always slower than using parameters.
    1. Filtering the dataset might be slower than using parameters when you're not using cached reports. <Correct>

    3. Filtering the dataset enables you to use a cached report that reuses the same data. <Correct>

    Explanation:

    Filtering the dataset enables SQL Server Reporting Services to query all the data and later filter and cache the information. The advantage of filtering the dataset is that other reports can use the same data when using cached reports.

    Filtering a dataset for a cached report can increase the performance over using parameters, but this is not always true.

    If the report is not configured as cached, filtering the dataset will likely be slower than using parameters.
  33. You need to integrate Reporting Services into a custom line of business application in your company.

    Select the options for integrating SSRS reports into the application. (Each correct answer presents part of the solution. Choose three.)

    1. Report Server Web Service (SOAP API) 

    2. ReportViewer controls 

    3. Embedded resource

    4. URL access
    1. Report Server Web Service (SOAP API) <Correct>

    2. ReportViewer controls <Correct>

    4. URL access <Correct>

    Explanation:

    The three options are the Report Server Web service, also known as the Report Services SOAP API; the ReportViewer controls; and URL access.
  34. You are developing a SQL Server Integration Services (SSIS) package. You configured a package to write logs to a log file, Package.log. You would like to configure the log file so that it can be written in a different location, depending on which server it is located on. In your package, you have created a variable named LogFilePath that stores the folder path for the log file.

    How would you configure the path of the log file?

    1. In the Configure SSIS Logs dialog box, add an expression to the SSIS log provider.

    2. In the Connection Manager dialog box, add an expression to the Name property of Package.log.

    3. In the Connection Manager dialog box, add an expression to the ConnectionString property of Package.log.

    4. In the Configure SSIS Logs dialog box, add an expression to the Log File connection.
    3. In the Connection Manager dialog box, add an expression to the ConnectionString property of Package.log.<Correct>

    Explanation:

    The Configure SSIS Logs dialog box enables you to set logging to go to an OLE DB connection manager or File connection manager. These are stored in the package connection manager. The ConnectionString property of File Connection Manager sets the path to the file that contains the file connection properties.

    The Name property is only the visual name given to the connection and does not contain the path and file name of the log file.
  35. Why would you use a cube instead of a relational database for your mining models?

    1. Because you can easily modify data in cubes

    2. Because you get additional algorithms when you mine a cube

    3. Because you get additional algorithm parameters when you mine a cube

    4. Because you have cleansed data in your cube
    4. Because you have cleansed data in your cube <Correct>

    Explanation:

    Typically, you mine a cube because the data in the cube is already cleansed; however, you do not get any additional functionality when you mine a cube. In addition, you cannot modify data in a cube.

    It does not matter whether the data mining is based on a relational source or a cube; the same algorithms are available
  36. You are setting up a SQL Server Analysis Services (SSAS) cube to use Kerberos authentication. You need to configure an application's ActiveX Data Objects Multidimensional (ADOMD) connection to delegate the credentials to the Analysis Services server.

    Which Impersonation Level do you need to set to enable Kerberos?

    1. Anonymous

    2. Impersonate

    3. Delegate 

    4. Identify
    3. Delegate <Correct>

    Explanation:

    To enable Kerberos authentication, you must set the application's connection to Delegate. Delegation is required for any authentication that requires two or more hops from where the user is authenticated to where the underlying data is.

    Anonymous access will not allow the user to authenticate on SSAS.

    Neither Identify nor Impersonate relate to delegating a user's credentials.
  37. You are developing a new report and need to call a method through custom code.

    Choose the correct syntax for calling custom code methods. (Each correct answer presents a complete solution. Choose two.)

    1. Static Method Call - <Namespace>.<ClassName>.<Method>() 

    2. Static Method Call - Code.<Instance>.<Method>()

    3. Instance Method Call - Code.<Instance>.<Method>() 

    4. Instance Method Call - <Namespace>.<ClassName>.<Method>()
    1. Static Method Call - <Namespace>.<ClassName>.<Method>() <Correct>

    3. Instance Method Call - Code.<Instance>.<Method>() <Correct>

    Explanation:

    Reporting Services can call static and instance methods in externally referenced code.

    Static methods can be called directly from the class in the form <Namespace>.<ClassName>.<Method>().

    Instance methods require an instance name to be defined in the References tab of the Report Properties dialog box. The format ofan instance method is Code.<Instance>.<Method>().

    Static methods cannot be called using an instance name.

    Instance methods cannot be called directly from the class.
  38. You have a list of Windows users and groups that need access to Reporting Services. You have identified the roles for each group in the list and are now working on role assignments.

    Which of the following components comprise a role assignment? (Each correct answer presents part of the solution. Choose three.)

    1. A user or group account 

    2. A role definition 

    3. A securable item such as a folder, report, and so on 

    4. The Report Server name
    1. A user or group account <Correct>

    2. A role definition <Correct>

    3. A securable item such as a folder, report, and so on <Correct>

    Explanation:

    A role assignment is composed of:

    • * A securable item for which you want to control access. Examples of securable items include folders, reports, and resources.
    • * A user or group account that can be authenticated by Windows security or another authentication mechanism.
    • * Role definitions that define a set of tasks. Examples of role definitions include System Administrator, Content Manager, and Publisher.

    The report server name is not part of a role assignment.
  39. The marketing department in your firm has asked you to create a report with several gauges that show the status of their marketing goals and campaigns.

    Which is NOT a type of Gauge region that you can use?

    1. Linear

    2. Pie chart

    3. Traffic light 

    4. Radial
    3. Traffic light <Correct>

    Explanation:

    The Gauge region is new in SSRS 2008. It is based on the Dundas Gauges. The gauge displays a single value from a dataset. It is commonly used to display values that are categorized as Good, Medium, and Bad. Key performance indicators (KPI) can be displayed using the gauge region. The traffic light is a popular visualization for KPIs, but it is not supported in SSRS.

    Radial and linear gauges are supported. The pie chart is a type of chart region
  40. You are developing a SQL Server Analysis Services (SSAS) cube that is tracking daily account balances for your customers and bank branches. You have a fact table that stores the end-of-day balance for each customer account; therefore, each account has only one record per day in the fact table. During testing, you notice that when you look at the data from a week level, the end-of-day account balances are being added together for every day in each week. You want to change this so that it shows the final available balance for the week, but you still want the balances to add up for your higher-level customer and account attributes.

    How can you accomplish this?

    1. Change the aggregation function for the Balance measure to LastNonEmpty.

    2. Change the date dimension to measure group relationship Cardinality property to One.

    3. This requirement cannot be handled in Analysis Services.

    4. Create a calculated member on the Date dimension to return the last day in the week.
    1. Change the aggregation function for the Balance measure to LastNonEmpty. <Correct>

    Explanation:

    The account balances requirement is based on a fact table called a snap shot fact table. This table is semi-additive and should not sum up along the date dimension. To handle this requirement, the LastNonEmpty aggregation usage type always displays the lastnon-NULL value for the measure in the date dimension, while still summing up across all the other dimensions.

    The Cardinality property defines how attributes relate to one another and is not related to handling a semi-additive requirement. Although a calculated member can be created in the date dimension, it cannot search the date dimension hierarchy efficiently to achieve the goal.
  41. When creating a new report with the Report Wizard, you have to choose whether the report's data source should be shared or just internal to the report.

    Which are benefits of using a shared data source? (Each correct answer presents part of the solution. Choose three.)

    1. Securable 

    2. Centralized connection information 

    3. Can be used in multiple reports 

    4. Creates a connection for each report
    1. Securable <Correct>

    2. Centralized connection information <Correct>

    3. Can be used in multiple reports <Correct>

    Explanation:

    Database connections are expensive. Using shared data sources minimizes the number of open connections to a database. There are two types of data sources, embedded and shared. A shared data source can be used in multiple reports. The connection information is stored in a single location, so modifications can be made once and yet affect multiple reports. A shared data source can be secured, whereas an embedded data source cannot be.
  42. You are designing a SQL Server Integration Services (SSIS) package and want to perform a Send Mail task if the Execute SQLtask fails and the user package variable SendErrorMailFlag is set to True.

    How would you achieve this?

    1. Add a Send Mail task after the Execute SQL task. Configure the precedence constraint and set the Evaluation Operation optionto Expression And Constraint. Set the Value option to Failure. Set the Expression option to @[User::SendErrorMailFlag] == "True".

    2. Add a Send Mail task after the Execute SQL task. On the Expression tab of the Send Mail task, add the @[User::SendErrorMailFlag] == "True" && @[System::Constraint] == "Failure" expression.

    3. Add a Send Mail task after the Execute SQL task. Set the Evaluation Operation option for the precedence constraint toExpression And Constraint. Set the Value option to Failure. Set the Expression option to SendErrorMailFlag = "True".

    4. Add a Send Mail task after the Execute SQL task. Configure the precedence constraint and set the Evaluation Operation option to Constraint. Set the Value option to Failure. On the Expression tab of the Send Mail task, add the SendErrorMailFlag = "True" expression.
    1. Add a Send Mail task after the Execute SQL task. Configure the precedence constraint and set the Evaluation Operation optionto Expression And Constraint. Set the Value option to Failure. Set the Expression option to @[User::SendErrorMailFlag] == "True".<Correct>

    Explanation:

    The Precedence Constraint Editor dialog box enables you to configure the constraint for a task to follow (Success, Failure, or Completion). An expression can be used in place of or in conjunction with the constraint.

    Setting the Evaluation Operation option to Expression And Constraint allows the package to use both an expression (SendErrorMailFlag) and a constraint (Failure).

    The proper syntax for checking a variable value in an expression is @[User::SendErrorMailFlag] == "True".
  43. You have a cube that has a Reseller Sales measure group. You add the Product dimension to the cube. When you browse the cube by the Product dimension, you see the same total for all products.

    What is the most likely cause of this problem?

    1. The AttributeHierarchyEnabled property of the dimension key attribute in the Product dimension is set to False.

    2. The AllMemberAggregationUsage of the Product cube dimension is set to None.

    3. The Product dimension doesn't have a dimension key.

    4. There are no dimension usage relationships between the Product dimension and the Reseller Sales measure group.
    4. There are no dimension usage relationships between the Product dimension and the Reseller Sales measure group.<Correct>

    Explanation:

    Your first stop for troubleshooting this problem should be checking the Dimension Usage tab in Cube Designer. The most likely reason is that no relationship is defined between the Product dimension and the Reseller Sales measure group. When you add a dimension to the cube, always check its dimension usage to ensure that the dimension is properly joined to the required measure groups.
  44. You are working with a report that has a table displaying multiple groups of data. You want to hide some of the rows to and toggle the visibility.

    Which step is NOT required to hide a row in a table?

    1. Select the table.

    2. Click Hide in the When The Report Is Initially Run property.

    3. Right-click the table and then select Row Visibility. 

    4. Right-click the row handle and then select Row Visibility.
    3. Right-click the table and then select Row Visibility. <Correct>

    Explanation:

    Row Visibility is defined for each row in the table and not at the table level.

    To hide a row in a table, you must first select the item. Next, you must right-click the row and choose Row Visibility. When the Visibility dialog appears, choose Hide in the When The Report Is Initially Run property.
  45. You are creating a report that uses a data region. The data region uses a dataset with groups of related records.

    How do you force a page break, keeping the related records together within the grouping?

    1. Set the Add A Page Break Before property in the Tablix Properties dialog box.

    2. Use the Fit Contents To One Page If Possible property in the Table Properties dialog box.

    3. Create a row group. Set the Between Each Instance Of A Group property in the Page Breaks pane of the Group Properties dialog box. 

    4. Set the Add A Page Break After property in the Tablix Properties dialog box.
    3. Create a row group. Set the Between Each Instance Of A Group property in the Page Breaks pane of the Group Properties dialog box. <Correct>

    Explanation:

    The related rows in the dataset should be grouped by a common field so they are seen as a common set of data. The Between Each Instance Of A Group property creates a page break after each group defined in the row groups is printed This property can be set in the Page Breaks pane of the Group Properties dialog box.

    The Fit Contents To One Page If Possible property attempts to keep all the rows of a table on the same page.

    The Add A Page Break After property moves the entire table to a new page.

    The Add A Page Break Before property sets a page break before the table.
  46. You want to create a revenue Key Performance Indicator (KPI) that shows how the overall company revenue relates to the prior year revenue for the same date period. One of the requirements given to you is that the KPI must show not just that the revenue target was met but also whether the revenue value is getting better or worse over the time period.

    Which KPI property can you use to show whether the revenue value is getting better or worse?

    1. You can use the KPI Acceleration property to show whether the revenue is getting better or worse.

    2. You can use the KPI Trend property to show whether the revenue is getting better or worse. 

    3. You can use the KPI Status property to show whether the revenue is getting better or worse.

    4. KPIs can show only the goal and the status of the target value.
    2. You can use the KPI Trend property to show whether the revenue is getting better or worse. <Correct>

    Explanation:

    The KPI Trend property enables you to define an MDX expression that indicates whether the target value is increasing or decreasing even if the goal has been met. The Trend property requires an output of 0, 1, or -1 to show the trend indicator. The KPI Status defines how the current value of the KPI is performing against the goal but does not indicate whether the value is going up or down.
  47. You are designing a SQL Server Integration Services (SSIS) package, which you plan to deploy to multiple server environments. Each server might have a different file directory structure. You wish to configure the SQL Server connection in the package.

    How would you configure the package with the least amount of effort?

    1. Create an environment variable on each server. Set the environment variable to the name of the SQL Server connection. Open the package in BIDS. Create a SQL Server configuration and point the configuration to the environment variable.

    2. Use the Package Installation Wizard to move the packages to each server. When deploying, set the SQL connection to thecorrect SQL Server instance.

    3. Create a SQL Server configuration in the package.

    4. Create an environment variable on each server. Set the environment variable to point to the location of an XML configuration.Open the package in BIDS. Create an XML configuration and point the configuration to the environment variable.
    4. Create an environment variable on each server. Set the environment variable to point to the location of an XML configuration.Open the package in BIDS. Create an XML configuration and point the configuration to the environment variable.<Correct>

    Explanation:

    The XML configuration can use an indirect configuration to locate the XML file. This allows an XML configuration file to exist in different locations on different servers.

    If a SQL Server configuration is used to point to an environment variable, the connection string of the SQL Server connection, not the name of the SQL Server connection, must be stored.

    The SQL Server configuration is not the best option because each server has a different SQL Server instance. The package mustbe edited to point to the correct instance.

    Changing the connection during deployment does not resolve the goal of updating the XML file configuration's location.
  48. You are deploying a set of SQL Server Integration Services (SSIS) packages to multiple servers.

    The packages will be executed on the different servers, but you would like to centralize the configuration so that you can share package properties such as connection strings and variable values. You do not want to manage multiple copies of the configuration.

    Which type of SSIS configuration should you use?

    1. SQL Server configuration 

    2. XML File configuration

    3. Environment Variable configuration

    4. Parent Package Variable configuration
    1. SQL Server configuration <Correct>

    Explanation:

    The SQL Server configuration would be effective because it would allow a central database table where configuration entries could be stored and shared across servers and packages.

    The XML File configuration and Environment Variable configuration options are machine dependent, so you would have multiple copies of the configuration, one for each machine, and this configuration would not be centralized.

    The Parent Package Variable Configuration option is used when a package executes another package by using the Execute Package task and would not apply to centralizing configuration properties.
  49. You have deployed a set of SQL Server Integration Services (SSIS) packages to SQL Server. As part of your disaster recovery plan, you want to back up the packages.

    How should you back up the deployed packages?

    1. Back up the SQL Server msdb system database. 

    2. Back up the SQL Server tempdb system database.

    3. Back up the SQL Server master system database.

    4. Back up the file system folder, {SQL Server Install}100Files.

    5. Back up the entire file system folder where SQL Server has been installed.
    1. Back up the SQL Server msdb system database. <Correct>

    Explanation:

    When packages are deployed to SQL Server, they are saved in the msdb database under the sysssis packages table. Therefore, if you back up the msdb system database on a recurring basis, you are able to recover the packages in the event of a failure when msdb needs to be restored.

    The SQL Server tempdb and master databases do not include SSIS package information.

    SSIS Packages can also be deployed to the file system but, in this case, the packages are deployed to SQL Server.
  50. You are managing several SQL Server Integration Services (SSIS) packages.Using a command-line utility, you want to encrypt the entire MyPackage.dtsx package with the password abc123.

    What would the command-line syntax be to encrypt MyPackage.dtsx with a package password?

    1. DTutil /file MyPackage.dtsx /encrypt package;MyPackage.dtsx;3; abc123

    2. DTexec /encrypt package;MyPackage.dtsx;EncryptSensitiveWithPassword; abc123

    3. DTutil /file MyPackage.dtsx /encrypt file;MyPackage.dtsx;3; abc123 

    4. DTexec /file MyPackage.dtsx /encrypt file;MyPackage.dtsx;3; abc12
    3. DTutil /file MyPackage.dtsx /encrypt file;MyPackage.dtsx;3; abc123 <Correct>

    Explanation:

    The DTUtil command-line utility generally allows you to copy, move, and delete packages, but you can encrypt and decryptpackages as well.

    The syntax for the DTUtil utility to encrypt a package is as follows:

    DTutil /file {package Name} /encrypt file; {package Name};{EncryptionLevel}; {Password}

    The encryption level is a numeric indicator related to the EncryptionLevel property:

    • 0 = Don'tSaveSensitive
    • 1 = EncryptSensitiveWithUserKey
    • 2 = EncryptSensitiveWithPassword
    • 3 = EncryptAllWithPassword
    • 4 = EncryptAllWithUserKey
    • 5 = SQLServerStorage
  51. You are developing a SQL Server Analysis Services (SSAS) cube. You have a fact table named FactSalesTargets that stores the saleAmount for each transaction. You would like to add a measure to the cube, [Total Sales], which represents the total saleAmount.

    How would you create the measure [Total Sales]?

    1. Create a new measure. Select saleAmount as the measure source. Set the calculation value to SUM.

    2. Create a new measure. Select FactSalesTargets as the source table. Select saleAmount as the source column. Set the usage to Count of Rows.

    3. Create a new measure. Select FactSalesTargets as the source table. Select saleAmount as the source column. Set the usage to Max.

    4. Create a new measure. Select FactSalesTargets as the source table. Select saleAmount as the source column. Set the usage to SUM.
    4. Create a new measure. Select FactSalesTargets as the source table. Select saleAmount as the source column. Set the usage to SUM. <Correct>

    Explanation:

    The Create New Measure pop-up gives you three values to configure, Source Table, Source Column, and Usage. The SUM usage will add up the value of the source column.

    The Count of Rows is used to count the number of rows returned, and the Max setting will display the greatest value within the dimensionality selected.
  52. You are developing a package that contains a Foreach Loop container. The container is looping over a set of files on a folder.When the package is deployed to another server, the folder in which the files are contained changes.

    How can you set up the package so that the folder can point to the right folder? (Each correct answer presents part of the solution.Choose two.)

    1. Use an SSIS configuration and capture the Directory property of the Foreach Loop container enumerator.

    2. Use an SSIS expression in the Foreach Loop container in the enumerator and update the Directory property.

    3. In the Foreach Loop container, change the Folder text box to use an Environment Variable path.

    4. On the Variable Mapping tab of the Foreach Loop container, map the Directory property to a variable that contains the path to the folder.
    1. Use an SSIS configuration and capture the Directory property of the Foreach Loop container enumerator.<Correct>

    2. Use an SSIS expression in the Foreach Loop container in the enumerator and update the Directory property.<Correct>

    Explanation:

    The folder the Foreach Loop container iterates over when using the Foreach File enumerator is the Directory property. This can be updated by using either an SSIS configuration or an SSIS expression on the property.

    The Folder property does not accept an Environment Variable entry, and the Variable Mapping tab of the Foreach Loop container does not take variable values and update properties; rather, it takes the iteration value and updates the variable.
  53. You have configured the following settings in the project properties:
    * TargetDataSourceFolder: DataSources
    * TargetReportFolder: RSReports
    * TargetServerURL: http://reports.contoso.com/ReportServer

    What will be the outcome of deploying this project?

    1. The project will be deployed to http://reports.contoso.com.The reports will be copied to the RSReports folder.The shared data sources will be copied to the DataSources folder.

    2. The project will be deployed to http://reports.contoso.com/ReportServer.All of the reports and shared data sources will be copied to the same folder.

    3. The project will be deployed to http://localhost/ReportServer.All the reports and shared data sources will be copied to the same folder.

    4. The project will be deployed to http://reports.contoso.com/ReportServer.The reports will be copied to the RSReports folder.The shared data sources will be copied to the DataSources folder.
    4. The project will be deployed to http://reports.contoso.com/ReportServer.The reports will be copied to the RSReports folder.The shared data sources will be copied to the DataSources folder. <Correct>

    Explanation:

    The project will be deployed to http://reports.contoso.com/ReportServer. The reports will be copied to the RSReports folder, and shared data sources will be copied to the DataSources folder.

    The project will not be deployed to http://localhost/ReportServer because the TargetServerURL property ishttp://reports.contoso.com/ReportServer. The reports and shared data sources will not be copied to the same folder because the TargetReportfolder and TargetDataSourceFolder properties are not equal.

    The project will be deployed to http://reports.contoso.com/ReportServer, but the reports and shared data sources will not be copied to the same folder because the TargetReportfolder and TargetDataSourceFolder properties are not equal.

    The project will not be deployed to http://reports.contoso.com because the TargetServerURL property ishttp://reports.contoso.com/ReportServer. The reports will be copied to the RSReports folder, and shared data sources will be copied to the DataSources folder.
  54. You are designing an SSIS package. The control flow of the package contains two sequence containers. Inside sequence container A is an Execute SQL task and a Data Flow task. Inside sequence container B are two File System tasks.You need to create a variable that can be seen by the Execute SQL task and the Data Flow task but not by the File System tasks.

    Which kind of variable do you create?

    1. Create a variable and set the scope of the variable to Sequence Container A. 

    2. Create a variable and set the Namespace property to Sequence Container A.

    3. Create two variables with the same name. Set the scope for the two variables as Execute SQL Task and Data Flow Task.

    4. Create a variable and set the scope of the variable to Sequence Container B.
    1. Create a variable and set the scope of the variable to Sequence Container A. <Correct>

    Explanation:

    The scope of a variable defines when the variable can be seen. If Scope is set to Sequence Container A, any object outside of Sequence Container A cannot see the variable.

    There is no reason to create two variables in this scenario.

    The Namespace property is set to either System or User. Setting this to another value would not change its permissions.

    Setting an expression of the variable would not change whether the variable can be seen.
  55. You are managing a SQL Server Analysis Services (SSAS) cube. Currently, the cubes are stored on the G: volume, which is connected to external drives. A new volume H: has been added that is connected to a second set of external drives. System monitoring on drive space and IO throughput alerted the IT department to add the additional volume.

    How can some of the cube's MOLAP data and aggregations be directed to the new drive?

    1. Change the StorageLocation property for some of the partitions to use a folder on the H: drive. 

    2. Change the StorageLocation property for some of the measure groups to use a folder on the H: drive.

    3. Change the StorageLocation for the aggregations to use a folder on the H: drive.

    4. Change the StorageLocation for the dimensions to use a folder on the H: drive.
    1. Change the StorageLocation property for some of the partitions to use a folder on the H: drive. <Correct>

    Explanation:

    Data and aggregations for measure groups are stored at the partition level. Therefore, the StorageLocation setting for each partition directs where the data will reside.

    A StorageLocation property exists at the measure group level; however, this is only a default value.

    The StorageLocation property does not exist for aggregations or dimensions. Aggregations for partitions can reside only in the same directory as the partition's data. Dimension data resides in the StorageLocation directory set at the SSAS database level. For any StorageLocation property that is blank, the default server storage location is used, which is defined in the DataDir server property.
  56. To simplify the Reporting Services environment within your organization, you have decided not to create any new custom roles, but rather to use the existing predefined roles within Reporting Services. Which of the roles below is NOT a predefined role?

    1. Publisher
    2. Content Manager
    3. Report Builder
    4. System Users
    5. Report Developer 
    6. My Reports
    7. System Administrator
    8. Browser
    5. Report Developer <Correct>

    Explanation:

    Report Developer is not a predefined role.

    The predefined system roles are System Administrator and System Users. The predefined item-level roles are Browser, Content Manager, Publisher, My Reports and Report Builder.
  57. You need to customize the rendering properties for rendering reports to Excel.

    Which Reporting Services configuration file controls the Excel rendering extension at run time?

    1. RSWebApplication.config
    2. RSReportDesigner.config
    3. ReportingServicesService.exe.config
    4. RSReportServer.config
    4. RSReportServer.config <Correct>

    Explanation:

    All the rendering extension run time properties are stored in RSReportServer.config.

    Previous versions of SQL Server Integration Services used RSWebApplication.config. This file is no longer used in SSRS 2008.

    ReportingServicesService.exe.config stores tracing and logging options for the Report Server service.

    RSReportDesigner.config stores the configuration for the Report Designer. Design time properties of the rendering extensions are stored in this file.
  58. You are managing several SQL Server Integration Services (SSIS) packages.  You would like to schedule a package to run in SQL Server Agent.

    Which of the following is NOT a scheduling option in SQL Server Agent?

    1. When SQL Server Agent starts

    2. On a recurring schedule

    3. Whenever the CPU usage becomes idle

    4. When the package is modified
    4. When the package is modified <Correct>

    Explanation:

    A SQL Server Agent job can be scheduled to run on the following scheduling options: On a recurring schedule, one time, when SQL Server Agent starts, and whenever the CPU usage becomes idle.
  59. You are deploying a SQL Server Integration Services (SSIS) package.

    You currently have a package in an SSIS BIDS project. You built the project with CreateDeploymentUtility set to true.

    By default, where will you find the packages created by the deployment utility?

    1. In the root folder of the project

    2. In the binDeployment folder of the project 

    3. In the msdb database

    4. In the Deployment folder of the project
    2. In the binDeployment folder of the project <Correct>

    Explanation:

    If you set the CreateDeploymentUtility property of the project to true, then when you build the SSIS project, the packages and manifest file are placed (by default) in the binDeployment folder of the project along with the SSIS packages in the project. You can also change the DeploymentOutputPath property to any file system path and, when the project is built, the files would be placed in that alternate location.

    The msdb database stores packages when they have been deployed to SQL Server and is not related to building an SSIS project.
  60. You have a Product dimension that has a Price Range attribute. You don't want the users to be able to browse data by the Price Range attribute. The Price Range attribute participates in a Product Model hierarchy with two levels, formed by the Product Category and Price Range attributes.

    What property of the Price Range attribute do you need to change to make the attribute unavailable to end users without affecting the Product Model hierarchy?

    1. AttributeHierarchyOrdered

    2. AttributeHierarchyOptimizedState

    3. AttributeHierarchyEnabled

    4. AttributeHierarchyVisible <Correct>
    4. AttributeHierarchyVisible <Correct>

    Explanation:

    You can hide an attribute from end users by setting its AttributeHeirarchyVisible property to False. A hidden attribute can still participate in hierarchies and can be used in MDX expressions.

    The members on an attributed hierarchy can be sorted by using the AttributeHierarchyOrdered property. TheAttributeHierarchyOptimizedState property creates an index for each attribute hierarchy during processing. Neither of these properties disables the Price Range attribute as a stand-alone attribute hierarchy. If the AttributeHierarchyEnabled property is set to false, the attribute cannot be used as either a stand-alone attribute hierarchy or part of a user hierarchy.
  61. You are deploying several SQL Server Integration Services (SSIS) packages. You are using the deployment utility to deploy the packages to the production SQL server in the msdb database.

    What do you need to do to ensure that the package developers can see the packages?

    1. Add the package developers' msdb role, db_ssisltduser, in the msdb database. 

    2. In the deployment utility, verify that the Rely On Server Storage For Encryption check box is selected.

    3. Add the package developers to the Windows group db_ssisadmin.

    4. Add the package developers to the Windows role db_ssisoperator.
    1. Add the package developers' msdb role, db_ssisltduser, in the msdb database. <Correct>

    Explanation:


    When SSIS packages are deployed to SQL Server and stored in the msdb database, the security access for the packages is managed through SQL Server logins and roles. For users to have view rights on the package, they must be added to the db_ssisltduser role in msdb.

    When running the SSIS deployment utility, the Rely On Server Storage For Encryption check box will ensure that the security features of the database engine are used to secure the packages, but this property is unrelated to package accessibility in msdb.

    The SSIS roles are not defined in Windows, but the SSIS roles are defined in the msdb database when connected to SQL Server 2008.
  62. You are managing a SQL Server Analysis Services (SSAS) cube. During performance analysis, you realize that every time anMDX query is sent to SSAS from the user tool, it generates a SQL query against the underlying database. You have a partition set to ROLAP storage, but it is only for your current day data, and most of the queries are historical in nature.

    How can you prevent the ROLAP partition from being queried if the query is not referencing the current day's data?

    1. Create a different measure group and measures for the current day data.

    2. Set the Source property on the ROLAP partition to the current day.

    3. Create a different cube for the current day data.

    4. Set the Slice property on the ROLAP partition to the current day.
    4. Set the Slice property on the ROLAP partition to the current day. <Correct>

    Explanation:

    The partition Slice property tells the query engine which subset of data is in the underlying database. When this property is not set, SSAS has to query the partition for any query that references a measure from the same measure group.

    The Source property defines where the data comes from and filters the underlying data for the partition, but SSAS does not know what data is in the partition. Creating different cubes or measure groups only makes the solution more complicated and doesn't allow the inclusion of the current day's data in the cube.
  63. Which Configuration Manager property settings deploy reports in a BIDS project? (Each correct answer presents a complete solution. Choose two.)

    1. Build is not selected and Deploy is not selected.

    2. Build is selected and Deploy is selected. 

    3. Build is not selected and Deploy is selected. 

    4. Build is selected and Deploy is not selected.
    2. Build is selected and Deploy is selected. <Correct>

    3. Build is not selected and Deploy is selected. <Correct>

    Explanation:

    The Deploy property in the Configuration Manager must be selected to deploy reports to the server. If Build is selected and Deploy is selected, the project will build and deploy the reports to the server. If Build is not selected and Deploy is selected, the project will not build but will still deploy the reports to the server.

    If Build is selected and Deploy is not selected, the project will build, but the reports will not be deployed to the server.

    If Build is not selected and Deploy is not selected, the project will not build, and the reports will not be deployed to the server.
  64. A report designer has asked you where the best place to filter data is, in the dataset query, the dataset filter, or the data region filter.

    Which scenario is best solved by filtering a data region?

    1. A report that contains a chart for which the data is a subset of the complete dataset

    2. A report that has a table data region to display the top ten stores for sales and a different table data region to display the bottom ten stores for sales 

    3. A report with multiple groupings in which each detail group should display a different set of data

    4. A report that contains a table that displays sales data and a chart that displays the same data
    2. A report that has a table data region to display the top ten stores for sales and a different table data region to display the bottom ten stores for sales <Correct>

    Explanation:

    The report processor applies filters in the following order: on the dataset, on the data region, and then on groups from the top down in each group hierarchy. Filter on the data region when you want one or more data regions that are bound to a single dataset to provide a different view of the dataset. Tables and charts displaying the same set of data are better suited to use a dataset filter. Multiple groupings should use a filter on the detail group. Charts with a subset of the dataset should use a Series or Category group.
  65. You need to define an MDX expression that returns the top 10 most profitable customers. You want the results of this expression to be readily available to any client application that connects to the cube.

    Which implementation approach would you use?

    1. Named set 

    2. Cell calculation

    3. Calculated member

    4. Query set
    1. Named set <Correct>

    Explanation:

    Named sets enable you to define an MDX set that becomes part of the cube definition and is available to all client applications. Named sets generate a result of multiple members of the same dimension attributes and can be used to filter lists in most client tools or add to the rows, columns, or page level.  

    Calculated Member returns only one member, usually a measure value, and, therefore, would not be able to identify the top 10 customers. Because the requirement specified that the tool must immediately expose the properties, adding these rights into the queries is the right solution.
  66. You are deploying a SQL Server Integration Services (SSIS) package. You have saved the package to a file system.

    What is the file extension of the SSIS package that has been saved?

    1. .dts

    2. .ssis

    3. .dtx

    4. .dtsx
    4. .dtsx <Correct>

    Explanation:

    SQL Server Integration Services (SSIS) packages are saved to the file system as .dtsx files. Packages can also be deployed to the SQL Server msdb database, but when packages are developed in BIDS, an XML file is added to the Visual Studio project files witha .dtsx extension.
  67. You are managing several SQL Server Integration Services (SSIS) packages.

    You have opened the DTExecUI utility and configured all the settings you need for a particular package. You would like to copy thecommand line generated by DTExecUI.

    Which property tab would you use to get the command line generated by DTExecUI?

    1. Command Line 

    2. DTExec

    3. General

    4. Output
    1. Command Line <Correct>

    Explanation:

    The command line generated by the DTExecUI utility can be copied from the Command Line properties tab in the DTExecUI window. To see the command line generated by DTExecUI (and used when a package is executed in DTExecUI), select a package to be executed on the General tab of the utility. Packages can be referenced that are stored in the file system, SQLServer, or the Package Store.

    After you copy the command line, you can use the command-line switches that are referenced in conjunction with the DTExec command-line utility, and you can schedule the executable with any command-line execution utility or embed the command in a BAT or CMD file that is executed.
  68. You are developing the incremental deployment scripts to change an SSAS cube in production. Your requirement is to keep the security and the partitions intact but still modify the cube structure to add some calculated members and change some dimension attributes.

    What is the easiest way to create the script?

    1. Use SQL Server Management Studio; script out the new version of the SSAS database and delete the partition and security elements in the XMLA code.

    2. Use the SSAS Deployment Wizard and choose the option to retain roles and members and partitions so that the roles,members, and partitions are not overwritten. 

    3. In Business Intelligence Development Studio, point the project properties to the production computer and deploy the development database to the production computer.

    4. Use the Synchronize Database Wizard to synchronize the data and structures between your development and production servers. Choose the option to retain roles and members and partitions so that the roles, members, and partitions are not overwritten.
    2. Use the SSAS Deployment Wizard and choose the option to retain roles and members and partitions so that the roles,members, and partitions are not overwritten. <Correct>

    Explanation:

    The SSAS Deployment Wizard enables you to generate an incremental deployment script that updates the cube and dimension structures. You can also customize how roles and partitions are handled.

    The Synchronize Database Wizard can synchronize metadata between servers and enable you to maintain roles, but production data is often different from development data, and synchronizing the data moves development data to the production server.
  69. You are designing a SQL Server Integration Services (SSIS) package.

    At which of the following places can you NOT place a breakpoint? (Each correct answer presents part of the solution. Choose two.)

    1. Foreach Loop container

    2. Line of code of a Script task: on a Control Flow Task

    3. Line of code of a Script component: in a Data Flow Task 

    4. Lookup transformation 

    5. FTP task6. Sequence container
    3. Line of code of a Script component: in a Data Flow Task <Correct>

    4. Lookup transformation <Correct>

    Explanation:

    Breakpoints cannot be applied within the data flow on any component such as the Lookup transformation or in the code within a Script component.

    A breakpoint can be set on any control flow object and within the script of a Script task in the control flow.

    To debug data flow tasks, you can use data viewers.
  70. You are executing several SQL Server Integration Services (SSIS) packages, using the DTExec command-line tool.

    You have been experiencing package failures and want to create a debug dump file when the package fails so that you can troubleshoot the failure with a debugging tool.

    Which DTExec command-line switches generate debug dumpfiles? (Each correct answer presents a complete solution. Choosetwo.)

    1. /VLog

    2. /LOGGER

    3. /DumpOnError 

    4. /Reporting

    5. /Dump (error codes)
    3. /DumpOnError <Correct>

    5. /Dump (error codes) <Correct>

    Explanation:

    The /DumpOnError switch, by itself, generates a debug dump file, .mdmp, that can be used for troubleshooting package failures.

    You can also use the /Dump switch, which requires you to specify a semicolon-separated list of error codes that should generatethe .mdmp file.

    The /LOGGING, /Reporting, and /VLog switches relate to error reporting but do not generate the debug dump file. The /LOGGING switch sends the errors to a specified location. The /Reporting switch returns the errors at a specified level back to the command line output, and the /VLog reports errors to the Windows event log.
  71. You are developing a SQL Server Analysis Services (SSAS) cube. You currently have a cube with a measure group called Customer Count that uses a Distinct Count aggregation function on the CustomerKey column of the Sales fact table. You want to create a calculated member called [Growth in Customer Base] that shows the percentage of new customers in the period based on the previous customer count (at any level of the Calendar hierarchy).

    What is the proper Multidimensional Expressions (MDX) syntax that shows this?

    1. (( [Measures].[Customer Count].PrevMember)-([Measures].[Customer Count].CurrentMember))/ ( [Measures].[Customer Count].CurrentMember)

    2. (([Date].[Calendar].CurrentMember, [Measures].[Customer Count])-([Date].[Calendar].PrevMember, [Measures].[Customer Count]))/([Date].[Calendar].PrevMember,[Measures].[Customer Count])

    3. ([Date].[Calendar].PrevMember, [Measures].[Customer Count])/ ([Date].[Calendar].CurrnetMember,[Measures].[Customer Count])

    4. (([Date].[Calendar].CurrentMember, [Measures].[Customer Count])-([Date].[Calendar].CurrentMember.Lag(-1), [Measures].[Customer Count]))/([Date].[Calendar].CurrentMember,[Measures].[Customer Count])
    2. (([Date].[Calendar].CurrentMember, [Measures].[Customer Count])-([Date].[Calendar].PrevMember, [Measures].[Customer Count]))/([Date].[Calendar].PrevMember,[Measures].[Customer Count])<Correct>

    Explanation:

    The MDX PrevMember function references the immediately prior member of the referenced attribute based on the current member. To show the growth in customers, subtract the previous customer count from the current customer count and then divide by the previous customer count.
  72. You are managing a SQL Server Analysis Services (SSAS) cube. You would like to process the Sales cube. You know that some customers have been deleted from the Customer dimension. Furthermore, historical data has not been removed from the Salesfact table, and there might be a CustomerKey in the fact table that no longer exists in the Customer dimension.

    You perform a full process on the Customer dimension and are now configuring the Sales partition processing. The cube should discard records for missing customer keys.

    Which settings in the cube processing options would you change? (Each correct answer presents part of the solution. Choose two.)

    1. Key Not Found = Ignore Error

    2. Key Error Action = Discard Record 

    3. On Error Action = Ignore Error

    4. Processing Error Limit = 10
    1. Key Not Found = Ignore Error <Correct>

    2. Key Error Action = Discard Record <Correct>

    Explanation:

    The Key Not Found property can be set to Ignore Error. This enables the process to continue if an attribute key is found in a partition but is missing from the dimension. To discard records that do not have a key, set Key Error Action to Discard Record.

    The possible values for On Error Action are Stop Processing and Stop Logging.
  73. You are designing a SQL Server Integration Services (SSIS) package. You are using an Execute SQL task to call a stored procedure that returns the single column named EmployeeName. Only one row is returned with the stored procedure. You need to store the employee name in a package variable named EmployeeName.

    On the Execute SQL task, how do you set the value of the EmployeeName variable? (Each correct answer presents part of the solution. Choose two.)

    1. On the Parameter Mapping tab, set the variable EmployeeName to the EmployeeName value.

    2. On the General tab, set the ResultSet value to Full Result Set.

    3. On the Result Set tab, set the variable EmployeeName to the EmployeeName value. 

    4. On the Variable Mapping tab, set the variable EmployeeName to the EmployeeName value.

    5. On the General tab, set the ResultSet value to Single Row.
    3. On the Result Set tab, set the variable EmployeeName to the EmployeeName value. <Correct>

    5. On the General tab, set the ResultSet value to Single Row. <Correct>

    Explanation:

    The ResultSet values should be set to Single Row to capture the single record from the stored procedure. The Result Set tab captures the values returned in the Execute SQL task.

    You use Full Result Set only if you want to capture multiple rows and store them in an object.

    Use the Parameter Mapping tab to set variables that are passed to a SQL statement.

    There is no Variable Mapping tab under an Execute SQL task.
  74. You are developing a SQL Server Analysis Services (SSAS) cube. You currently have a dimension named Customer. The dimension contains an attribute named Parent Company.

    When browsing the hierarchy, there are several missing customers and when the Parent Company is displayed, a member of Unknown is displayed.

    How do you ensure that only known customers with a parent company are displayed?

    1. In the hierarchy properties, set the AttributeHierarchyVisible to False.

    2. In the dimension properties, set the UnknownMember property to Hidden. 

    3. In the dimension properties, set the UnknownMember property to None.

    4. In the hierarchy properties, on the Customer level, set the HideMemberIf property to OnlyChildWithNoName.
    2. In the dimension properties, set the UnknownMember property to Hidden. <Correct>

    Explanation:

    If the UnknownMember property of a dimension is set to Hidden, any unrelated attributes will hide the Unknown Customer values.

    When the UnknownMember property is set to None, the missing members will not be displayed.

    The HideMemberIf property is used for ragged hierarchies when a member should be hidden within a hierarchy, not for unknown members.

    The AttriuteHierarchyVisible property will prevent the attribute from being queried as an attribute hierarchy.
  75. You are developing multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed on multiple production servers. Each package contains a SQL Server connection. Each production server has the same file directory structure. Environment variables are not allowed on the production servers.

    What would be the best way to configure the packages to configure the SQL connection when moved to a new server?

    1. Add a Parent Variable configuration to each package. On each server, copy the packages to the same file path location.

    2. Add an XML configuration to each package. On each server, copy the XML file to the same file path. Update the XML file to point to the correct SQL Server instance. 

    3. Use the SQL Server Deployment Wizard to move the packages to each server. When using the SQL Server DeploymentWizard, set the SQL connection to the correct SQL Server instance.

    4. Add a SQL Server configuration to each package. On each server, configure the SQL Server configuration to point to thecorrect SQL Server instance.
    2. Add an XML configuration to each package. On each server, copy the XML file to the same file path. Update the XML file to point to the correct SQL Server instance. <Correct>

    Explanation:

    Each server has the same file directory structure, so an XML configuration file can be placed in the same location on each server.

    The SQL Server configuration is not the best option because each server has a different SQL Server instance. The package needs to be edited to point to the correct instance.

    The Parent Variable configuration is used only when the Execute Package task runs a child package.

    The SQL Server Deployment Wizard does not have an option to set the SQL connections. The SQL Server Deployment Wizard enables you to choose a configuration to be moved with the packages.
  76. There are two types of report processing modes, local and remote.

    What are the rendering extensions supported by local mode? (Each correct answer presents part of the solution. Choose three.)

    1. Image 

    2. Excel 

    3. Word

    4. PDF 

    5. HTML
    1. Image <Correct>

    2. Excel <Correct>

    4. PDF <Correct>

    Explanation:

    Only PDF, Excel, and Image rendering extensions are available. Local processing mode allows reports to be rendered when noreport server is present. Only a subset of the functionality provided by the report server is available. Report rendering is handled bythe control.

    Reports run in remote processing mode use the full functionality of the report server, including all rendering extensions
  77. You are developing a SQL Server Analysis Services (SSAS) cube. You currently have a measure named Currency Rate, which displays the end of the day's exchange rate for a given currency. In the cube, you would like the Currency Rate measure to display the most recent value stored at a given date aggregation.

    Which aggregation function would you assign to the measure?

    1. FirstNonEmpty

    2. Sum

    3. Max

    4. LastNonEmpty
    4. LastNonEmpty <Correct>

    Explanation:

    The LastNonEmpty aggregation function returns the value of the last non-empty child member, which would be the most recent value.

    The FirstNonEmpty aggregation function would return the first non-empty child member, which would be the oldest value in the measure.

    The Max aggregation function would return the highest value of the measure group, not necessarily the most recent.

    The Sum aggregation function will add all the values for the measure
  78. You want to make your reports more interactive and usable by implementing drill through in your reports.

    Which options of the Enable As Hyperlink property in the Action pane of the Text Box Properties dialog box enable you to implement the drill through capability? (Each correct answer presents part of the solution. Choose two.)

    1. None

    2. Go To Bookmark

    3. Go To URL 

    4. Go To Report
    3. Go To URL <Correct>

    4. Go To Report <Correct>

    Explanation:

    A drillthrough action link can be created by using Go To Report if the drillthrough report exists on the same server as the main report.

    Go To URL creates a drillthrough report if the drillthrough report exists on a different server or if the report server is configured for SharePoint integrated mode.

    The None option will not create any type of action.

    The Go To Bookmark option creates internal navigation in the report.
  79. You need to add default values to the report parameters so the report runs without user input.

    How are default values added to a report parameter? (Each correct answer presents a complete solution. Choose two.)

    1. Select the Default Values pane in the Report Parameter Properties dialog box, select Get Values From A Query, and enter the default value.

    2. Select the Default Values pane in the Report Parameter Properties dialog box, select Specify Values, and enter the default value. 

    3. The default value is automatically added when you create the parameter.

    4. Select the Default Values pane in the Report Parameter Properties dialog box, select Get Values From A Query, and then select the dataset and field from the Value drop-down list.
    2. Select the Default Values pane in the Report Parameter Properties dialog box, select Specify Values, and enter the default value. <Correct>

    4. Select the Default Values pane in the Report Parameter Properties dialog box, select Get Values From A Query, and then select the dataset and field from the Value drop-down list. <Correct>

    Explanation:

    There are two methods of assigning a default value to a report parameter. A static value can be assigned by entering a value after selecting Specify Values in the Default Values pane of the Report Parameters Properties dialog box.

    A default value can also be assigned from a query. Select Get Values From A Query. Select the dataset and field from the Default Values pane of the Report Parameters Properties dialog box.

    A default value is not automatically created when a report parameter is created.

    You cannot enter a default value when the Get Values From A Query option is selected.
  80. You have a Product dimension that has a Product attribute as the dimension key. The Product dimension also has a ProductSort attribute. You need to sort the members of the Product attribute on the ProductSort attribute.

    What changes must you make to the Product attribute? (Each answer is part of a complete solution. Choose two.)

    1. Change the OrderBy property to AttributeKey. 

    2. Change the KeyColumns property to the ProductSort attribute.

    3. Change the NameColumn property to the ProductSort attribute.

    4. Set the OrderByAttribute property to the ProductSort attribute.
    1. Change the OrderBy property to AttributeKey. <Correct>

    4. Set the OrderByAttribute property to the ProductSort attribute. <Correct>

    Explanation:

    By default, the members of the attribute are sorted by the attribute key. You can sort the attribute members by a secondary attribute. To do so, change the OrderBy property to AttributeKey and set the OrderByAttribute property to the sorting attribute.

    KeyColumns represents the column or columns that uniquely identify a member of the dimension, not a different sort attribute. NameColumn displays the member name.
  81. You are setting up the Report Designer environment for a couple of new report developers on your team. They will be using BIDS for their report development and you want to set up multiple configurations to handle different deployment scenarios.

    Which configuration is NOT predefined in BIDS?

    1. Debug

    2. Production

    3. Test 

    4. DebugLocal
    3. Test <Correct>

    Explanation:

    Test is not a configuration by default. This configuration would have to be created by the developer.

    Report Designer supports multiple project configurations and provides three configurations by default: DebugLocal, Debug, and Production. You can use the DebugLocal configuration to view reports in a local preview window, the Debug configuration to publish reports to a test server, and the Production configuration to publish reports to a production server.
  82. You are deploying a SQL Server Integration Services (SSIS) package.You have deployed Package1.dtsx to the production server. The package has been set up with EncryptSensitiveWithPassword. The password is set to password@1. The package contains an OLE DB connection, named SalesDB, that is connecting to a SQLServer database using a SQL Server user.

    You have moved the package to the production server.

    How would you execute the package so that the SalesDB connection has the proper user name and password? (Each correct answer presents part of the solution. Choose two.)

    1. Create a SQL Agent job. Create a step to execute Package1.dtsx. On the Configurations tab, set the package password.

    2. Run DTExec.exe /FILE "C:SSISPackage1.dtsx" /DECRYPT password@1. 

    3. Run DTExec.exe /FILE "C:SSISPackage1.dtsx" /PASSWORD password@1.

    4. Run DTExec.exe /FILE "C:SSISPackage1.dtsx" /Validate password@1.

    5. Create a SQL Agent job. Create a step to execute Package1.dtsx. SQL Agent will prompt you for a user name and password.
    2. Run DTExec.exe /FILE "C:SSISPackage1.dtsx" /DECRYPT password@1. <Correct>

    5. Create a SQL Agent job. Create a step to execute Package1.dtsx. SQL Agent will prompt you for a user name and password.<Correct>

    Explanation:

    Use the DTExec /DECRYPT password command-line parameter to set the decryption password when a package is saved with password encryption. When saving a package in SQL Agent, if there is a package password, SQL Agent will prompt you to enter the password.

    The /PASSWORD command-line parameter connects to SQL Server and does not apply to decrypting a package with a password.

    The /Validate command-line parameter is used only to validate a package without executing it and does not relate to package encryption.
  83. You are managing a set of SQL Server Integration Services (SSIS) packages. You have restartability turned on for one of the packages. You are now designing a new package that will run immediately after the first package. Its purpose it to check to see if the first package completed successfully (by checking for the existence of the checkpoint file), and if not, it should run a set of cleanup operations.

    How can you set up the new package so that it will run only if it finds a checkpoint file?

    1. Add a File System task. Set Operation to Check File. Set Source Connection to the name of the checkpoint file.

    2. Add the @[System::CheckpointUsage] == True expression to the package.

    3. Add a sequence container to the package. Place all the tasks in Sequence Container. Right-click and select Add A PrecedenceConstraint From Checkpoint File.

    4. Set the CheckpointUsage package property to Always and CheckpointFileName to the name of the checkpoint file.
    4. Set the CheckpointUsage package property to Always and CheckpointFileName to the name of the checkpoint file.<Correct>

    Explanation:

    The CheckpointUsage value of Always tells the package to execute only if a checkpoint file exists. Therefore, if you set CheckpointUsage to Always, the cleanup package runs only if it finds the checkpoint file.

    The restartability operations are handled through the Checkpoint properties only, not through a File System task or sequence container
  84. You are managing some SQL Server Integration Services (SSIS) packages. You currently have 15 packages in a project using a shared data source that points to the development database server. You do not have package configurations set up. You have updated the shared data source to point to the production database server.

    You want to run one of the packages by using the execute package utility, dtexecui.

    What must you do to set the connection string in the package to the production server?

    1. Do nothing. The package is using a shared data connection.

    2. Open dtexecui. On the Connection Managers page, set the connection to point to the production server. Click Save to save the package.

    3. Open the package in BIDS. Delete the existing Connection Manager setting pointing to the development database and add anew Connection Manager setting that points to the production database.

    4. Open the packages in BIDS. You will be prompted to update the connection in the package to the shared data connection. Save the packages.
    4. Open the packages in BIDS. You will be prompted to update the connection in the package to the shared data connection. Save the packages. <Correct>

    Explanation:

    Because you wish to change the connections to point to the new server and you do not have configurations set up, you must update the packages. Connection Manager stores the connection information at the package level. Changes to connections that are based on shared data sources will occur only when the package is opened in BIDS.

    You would not need to delete each connection because each package prompts you to make the change when the package is opened.
  85. You open an Analysis Services project in BIDS. You attempt to deploy the project by right-clicking the project node in Solution Explorer and selecting Deploy. However, BIDS interrupts the deployment process with the following error message:

    The project could not be deployed to the 'SSASTEST1' server because of the following connectivity problems: A connection cannotbe made. Ensure that the server is running.

    To resolve the deployment error, you must change the deployment server from 'SSASTEST1' to 'SSASTEST2'.

    Which project property do you need to change?

    1. Database

    2. Server 

    3. Processing Option

    4. Deployment Mode
    2. Server <Correct>

    Explanation:

    The Server property is the correct choice because it specifies the target server to which BIDS will deploy the project.

    The Database property specifies the name of the target database.

    The Deployment Mode property specifies what will be deployed.

    The Processing Option property specifies how the SSAS objects should be processed when the project is deployed.
  86. You are developing a SQL Server Integration Services (SSIS) package. You are working with a package that was developed to pull data from the Customer table, using an OLE DB data source. The Customer table no longer exists in the source database.

    You want to continue editing the package without receiving a package error.

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

    1. Set the ValidateExternalMetadata property to = True for the Customer OLE DB data source.

    2. Set the SSIS package property to DisableEventHandlers = True.

    3. Choose Work Offline from the SSIS menu. 

    4. Set the SSIS package property to DisableEventHandlers = False.

    5. Set the ValidateExternalMetadata property to = False for the Customer OLE DB data source.
    3. Choose Work Offline from the SSIS menu. <Correct>

    5. Set the ValidateExternalMetadata property to = False for the Customer OLE DB data source. <Correct>

    Explanation:

    The ValidateExternalMetadata property specifies whether the task should be validated at design time. If set to False, the task is validated only at run time. The Work Offline property tells the entire package not to validate.

    The DisableEventHandlers property prevents event handlers from firing during execution and is not related to disabling validation during package development.
  87. You are developing a SQL Server Analysis Services (SSAS) cube.

    Your current fact table does not have a value populated for the product dimension key. Currently, the Product dimension is showing the Product Name as blank for these records. You would like the Product Name to display as N/A in these instances.

    How would you configure the Product dimension?

    1. In the Product dimension, set UnknownMember property to Visible, the UnknownMemberName property to N/A, and the NullProcessing property to UnknownMember for the Product Name attribute.

    2. In the dimension properties of the Product dimension, set UnknownMember property to Visible and the UnknownMemberNameproperty to N/A. Set the NullProcessing property to UnknownMember for the Product Key attribute. 

    3. In the Product dimension, set the NullProcessing property to N/A for the Product Key attribute.

    4. In the Product dimension, set UnknownMember property to Visible and the UnknownMemberName property to N/A for the Product Name attribute. Set the NullProcessing property to UnknownMember for the Product Key attribute.
    2. In the dimension properties of the Product dimension, set UnknownMember property to Visible and the UnknownMemberNameproperty to N/A. Set the NullProcessing property to UnknownMember for the Product Key attribute. <Correct>

    Explanation:

    The UnknownMember and UnknownMemberName properties are set at the dimension level, not at the attribute level within a dimension. Therefore you would set these properties at the Product dimension level, not at the Product Name attribute level.

    The UnknownMemberName is used to set the caption if a dimension value is not found. The UnknownMember property needs to be set to true if the Unknown values are to be displayed.

    In addition, the NullProcessing property is defined for the key attribute, the Product Key, and not at the attribute level, like the Product Name attribute. It should be set to UnknownMember to pull the dimension's UnknownMemberName.
  88. You are managing several SQL Server Integration Services (SSIS) packages.

    You would like to execute a package by using the DTExec utility. When running the package, you would like to set the user variable package Path to the "C:SSIS" value.

    Which is the proper syntax for setting the variable packagePath when running DTExec?

    1. /set Variables[user::packagePath].Value="C:SSIS"

    2. /set Package.Variables[user::packagePath].Value="C:SSIS"s

    3. /va [user::packagePath].Value;"C:SSIS"

    4. /set Package.Variables[user::packagePath].Value;"C:SSIS"
    4. /set Package.Variables[user::packagePath].Value;"C:SSIS" <Correct>

    Explanation:

    The proper syntax for running DTExec and setting a variable is as follows:

    DTExec /f MyPackage /set Package.Variables[user::packagePath].Value;"C:SSIS"

    The /set switch can update any property in a package, using the path to the property. The value is specified after the path with a semicolon (;), and quotes (") can be used for text qualification.

    The /va switch is not used with the DTExec command line to update variables; rather, it is used to validate the package referenced in the command line.
  89. You have deployed an Analysis Services cube that contains several roles. For testing, you want to modify the connection string from the tool to simulate connecting as a specific role.

    Which property should you add to the connection string to achieve this?

    1. User=[Domain][User]

    2. Roles=[Role1],[Role2],etc 

    3. Roles cannot be simulated through the SSAS connection string.

    4. EffectiveRoles=[Role1],[Role2],etc
    2. Roles=[Role1],[Role2],etc <Correct>

    Explanation:

    Using the Roles property of the connection string, a user can connect as a certain role or multiple roles. The property accepts acomma-separated list of roles. The user must be a member of the role or roles in the list.

    The User property for connection strings does not filter the data or objects related to a specific role in SSAS, and theEffectiveRoles property is reserved for future use. In addition, the authenticated user must be a member of the role or roles for the role to apply
  90. You are developing a SQL Server Analysis Services (SSAS) cube. You currently have a cube with a calculated measure called [Growth in Customer Base] that is based on the previous member of the Calendar hierarchy of the Date dimension. You have created a Key Performance Indicator (KPI) that uses this calculated member as the KPI value. The KPI goal is defined as:

    Case
        When [Date].[Calendar].CurrentMember.Level Is [Date].[Calendar].[Calendar Year]
        Then .30
        When [Date].[Calendar].CurrentMember.Level Is [Date].[Calendar].[Calendar Semester]
        Then .15
        When [Date].[Calendar].CurrentMember.Level Is [Date].[Calendar].[Calendar Quarter]
        Then .075
        When [Date].[Calendar].CurrentMember.Level Is [Date].[Calendar].[Month]
        Then .025
        Else "NA"
    End

    You want to create a KPI status expression that returns a positive growth when the value is greater than the goal, a flat growth when the goal is within 90 percent of the goal, and a negative growth when the goal is less than 90 percent of the goal.

    What is the proper Multidimensional Expressions (MDX) syntax for the KPI status?

    1. CASE
     WHEN KpiTrend ("Growth in Customer Base") >=KpiGoal ("Growth in Customer Base") THEN 1
     WHEN KpiTrend ("Growth in Customer Base") >=.90 * KpiGoal ("Growth in Customer Base")AND KpiTrend ("Growth in Customer Base") <KpiGoal ("Growth in Customer Base") THEN 0
    ELSE -1
    END

    2. CASE
      WHEN KpiValue("Growth in Customer Base") >=KpiTrend ("Growth in Customer Base") THEN 1
      WHEN KpiValue("Growth in Customer Base") >=.90 * KpiTrend ("Growth in Customer Base")AND KpiValue("Growth in Customer Base") <KpiTrend ("Growth in Customer Base") THEN 0
      ELSE -1
    END

    3. CASE
      WHEN KpiValue("Growth in Customer Base") >=KpiGoal ("Growth in Customer Base") THEN 1
      WHEN KpiValue("Growth in Customer Base") >=.90 * KpiGoal ("Growth in Customer Base") AND KpiValue("Growth in Customer Base") <KpiGoal ("Growth in Customer Base") THEN 0
      ELSE -1
    END

    4. CASE
      WHEN KpiValue("Growth in Customer Base") >=KpiStatus ("Growth in Customer Base") THEN 1
      WHEN KpiValue("Growth in Customer Base") >=.90 * KpiStatus ("Growth in Customer Base") AND KpiValue("Growth in Customer Base") < KpiStatus ("Growth in Customer Base") THEN 0
      ELSE -1
    END
    • 3. CASE 
    •   WHEN KpiValue("Growth in Customer Base") >=KpiGoal ("Growth in Customer Base") THEN 1 
    •   WHEN KpiValue("Growth in Customer Base") >=.90 * KpiGoal ("Growth in Customer Base") AND KpiValue("Growth in Customer Base") <KpiGoal ("Growth in Customer Base") THEN 0
    •   ELSE -1
    • END<Correct>

    Explanation:

    The status expression compares the value expression to the goal expression. The value expression is calculated by using the MDX function, KpiValue(). The goal expression is calculated by using the MDX function, KpiGoal().

    When comparing the two values and defining the status output, the output must return a 1, 0, or -1 based on whether the status is good, neutral, or bad, respectively. If the KPI value is greater than the KPI goal, the status would be good. If the KPI value is greater than 90 percent (.90) of the KPI goal and less than the KPI goal, the status is neutral (0). Otherwise, the status is bad (-1).The answer needs to compare the right KPI references, the KPIValue, and the KPIGoal.
  91. For your SQL Server Reporting Services (SSRS) project, you have your staging report server installed locally, using the default installation. You are using the Debug deployment configuration.

    Which value should you use for the TargetServerURL property?

    1. MyReportServerMyFolder

    2. http://localhost/OLAP

    3. Servernameshare

    4. http://localhost/ReportServer
    4. http://localhost/ReportServer <Correct>

    Explanation:

    A default installation of Reporting Services creates a virtual directory named ReportServer, which stores the reports.

    A UNC path is not a valid value for the TargetServerUrl property. The http protocol must be used.

    MyReportServerMyFolder is a relative path that does not provide the deployment server.

    The OLAP virtual directory is not the default Report Server virtual directory.
  92. You have a Customer dimension. One of the attributes is Phone Number, which displays the customer phone number. The users will not browse data by this attribute. To optimize the dimension design, you want to prevent the server from creating an attribute hierarchy for the Phone Number attribute.

    Which property must you change?

    1. AttributeHeirarchyVisible

    2. AttributeHierarchyOptimizedState

    3. AttributeHierarchyEnabled 

    4. AttributeHierarchyOrdered
    3. AttributeHierarchyEnabled <Correct>

    Explanation:

    When AttributeHierarchyEnabled is False, the server will not create an attribute hierarchy. However, the attribute can still be used for display purposes (that is, as a member property of the Customer attribute).

    The members on an attributed hierarchy can be sorted by using the AttributeHierarchyOrdered. The AttributeHierarchyOptimizedState will create an index for each attribute hierarchy during processing.
  93. The accounting group has requested that all reports that show data in tables highlight every other row.

    Which expression correctly simulates this green bar effect in a table?

    1. =IIF(Fields!Profit.Value >= 0, "PaleGreen", "White")

    2. =IIF(RowNumber(Nothing) MOD 2, "PaleGreen", "White") 

    3. =IIF(Fields!Profit.Value < 0, "PaleGreen", "White")

    4. =IIF(RowNumber(Nothing) MOD 3, "PaleGreen", "White")
    2. =IIF(RowNumber(Nothing) MOD 2, "PaleGreen", "White") <Correct>

    Explanation:

    The BackgroundColor property of the table should contain the =IIF(RowNumber(Nothing) MOD 2, "PaleGreen", "White")expression to simulate a green bar effect. If the RowNumber value is an odd number, the background color is white; otherwise, it isPaleGreen.

    The =IIF(Fields!Profit.Value < 0, "PaleGreen", "White") and =IIF(Fields!Profit.Value >= 0, "PaleGreen", "White") expressions change the background color based on the contents of the Profit field.

    The =IIF(RowNumber(Nothing) MOD 3, "PaleGreen", "White") expression changes the background color to white for every third row.
  94. You have a Sales cube that uses a Product dimension. You need to process the Product dimension fully. You are concerned that the processing operation might affect other objects.

    How can you determine which objects will be affected by processing a given object before starting the processing operation?

    1. Use the Impact Analysis feature of the Process Object dialog box. <Correct>

    2. Script the Product dimension and review the related objects.

    3. Use the Process Progress window.

    4. Click the Process Affected Objects check box.
    1. Use the Impact Analysis feature of the Process Object dialog box. <Correct>

    Explanation:

    The Process Object dialog box displays the objects that are affected by the processing operation.

    The Process Progress window displays which objects are being processed, but this happens after you initiate the processing operation.

    The Process Affected Objects check box in the processing settings dialog box processes related measure groups that need updating.

    Scripting the dimension in XMLA does not reveal which objects are affected.
  95. You need to make a change to the MDX script of a cube deployed to a production server. You don't have the Analysis Servicesproject or the source files, but you must perform the change as soon as possible.

    Which is the best option to change the MDX script as quickly as possible?

    1. BIDS in connected (online) mode 

    2. Analysis Services Deployment Wizard

    3. BIDS in project mode

    4. XMLA script
    1. BIDS in connected (online) mode <Correct>

    Explanation:

    The fastest way to make changes to the cube metadata is to use BIDS in connected mode. In this mode, BIDS connects directly to the SQL Server Analysis Services (SSAS) 2008 database, and the changes are applied immediately after the object is saved.

    The project mode in BIDS requires generating the project source files and deploying the changes back to the server.

    The Analysis Services Deployment Wizard approach is similar to the project mode but supports more granular deployment.

    Finally, XMLA scripting will take more time because you must script the cube definition, make the change, and execute the script to apply the change.
  96. You have a Reporting Services environment. Report Manager has been installed and configured in native mode (as opposed to SharePoint integrated mode). A report you are developing needs to reference a subreport.

    Select the INCORRECT way of specifying a subreport in the Use This Report As A Subreport property.

    1. Select the report name from a drop-down list if the subreport is in the same project.

    2. Use a fully qualified URL to the report with the extension.

    3. Use a fully qualified URL to the report without the extension. 

    4. Use the full or relative path to the report without the extension.
    3. Use a fully qualified URL to the report without the extension. <Correct>

    Explanation:

    In native mode, you do not have to use a fully qualified URL to specify a subreport if it is in the same project.

    For a subreport in this project, select the name of the report from the drop-down list.

    For a report published to a report server configured for native mode, use a full or relative path to the report without the file name extension. If the report is in the same folder as the current report, use the name of the report only. If the report is in a different folder, use a relative path to the report (for example, /Reports/DetailReport).

    For a report published to a report server configured in SharePoint integrated mode, use a fully qualified URL including the file name extension (for example, http://TestServer/TestSite/Reports/Doc/DetailReport.rdl). Relative paths are not supported.
  97. You are managing several SQL Server Integration Services (SSIS) packages. You have deployed several of the packages to aSQL Server package store.

    Which database on the SQL Server instance does SQL Server use to store the SSIS packages?

    1. msdb 

    2. master

    3. SSIS

    4. tempdb
    1. msdb <Correct>

    Explanation:

    When packages are stored in a SQL Server database, they are physically stored in the msdb database in a table called dbo.sysssispackages. Packages deployed to msdb can be managed through SQL Server 2008 Management Studio (SSMS).When the msdb database is backed up, all the packages deployed to msdb are also backed up.

    The SSIS database is not a Microsoft created database. The master and tempdb are SQL Server system databases and are not used to store SSIS packages.
  98. You have implemented a data source view (DSV) that includes a table called FactSales. In the underlying database, you have created a new view called vwFactSales_SSAS on the FactSales table, which filters out some records that should not be included in the cube.

    What is the best way to replace the FactSales table in the DSV with the vwFactSales_SSAS?

    1. In the properties of the FactSales table in the DSV, change the source property to vwFactSales_SSAS.

    2. You cannot replace tables with other tables or views in the DSV.

    3. Remove the FactSales table from the DSV and add the vwFactSales_SSAS view to the DSV. Re-create the relationships to the vwFactSales_SSAS and then modify all the objects based on the FactSales table to use the new vwFactSales_SSAS object.

    4. Right-click on the FactSales table in the DSV. Choose Replace Table and then choose With Other Table. Select the vwFactSales_SSAS table from the list.
    4. Right-click on the FactSales table in the DSV. Choose Replace Table and then choose With Other Table. Select the vwFactSales_SSAS table from the list. <Correct>

    Explanation:

    Entities in the DSV can be replaced with other tables or views from the underlying database. To accomplish this, right-click the entity, choose Replace Table, and then select With Other Table. In addition, you can replace an entity in the DSV with a namedquery.

    If you remove a table in the DSV, all the objects based on the table will be invalid, and it would be difficult to reset all the objects and properties.
  99. You want to create subscriptions for a set of reports, and need to determine which subscriptions should be data-driven subscriptions.

    Choose the scenarios that are better suited for a data-driven subscription. (Each correct answer presents a complete solution. Choose three.)

    1. Users will be allowed to create subscriptions to reports.

    2. The list of subscribers changes regularly. 

    3. The output format will vary by user. 

    4. The report data needs to be filtered at run time.
    2. The list of subscribers changes regularly. <Correct>

    3. The output format will vary by user. <Correct>

    4. The report data needs to be filtered at run time. <Correct>

    Explanation:

    A data-driven subscription is preferred when the list of subscribers is dynamic because the subscriber data is retrieved from a query at run time. Filtering report data at run time is supported by only a data-driven subscription. Standard subscriptions require static data. The output format is stored as part of the subscriber data retrieved from a query at run time.

    Individual users cannot subscribe to a data-driven subscription, which is normally managed by a report administrator.
  100. You are managing a SQL Server Analysis Services (SSAS) cube. You are looking for some performance measures based on queries that access the cube. You are planning to set up query logging and would like the log to pick up 1 out of every 10 queries executed.

    Which QueryLog property would you set and what value would you set it to?

    1. QueryLogSampleSize = 0.1

    2. QueryLogSampling = 10 

    3. QueryLogSampling = 0.1

    4. LogDurationSec = 10
    2. QueryLogSampling = 10 <Correct>

    Explanation:

    The QueryLogSampling property of the QueryLog states the sample rate at which queries are to be collected. If the value is set to 10, 1 out of every 10 queries will be logged.

    The LogDurationSec property relates to the Flight recorder and does not set the number of queries to capture for Usage-Based Optimization logging.
  101. You are configuring a parameter to filter departmental information. You want users to select the department name, but you want the parameter to use the department ID to filter the information.

    Which columns should you use to bind the parameter? (Each correct answer presents part of the solution. Choose two.)

    1. Bind Department Name to the Value field.

    2. Bind DepartmentID to the Label field.

    3. Bind DepartmentID to the Value field. 

    4. Bind Department Name to the Label field.
    3. Bind DepartmentID to the Value field. <Correct>

    4. Bind Department Name to the Label field. <Correct>

    Explanation:

    The column bound to the Label field is what users use to select the parameter, and the associated value in the Value field is used internally to filter the information. Thus, you must assign Department Name to the Label field and DepartmentID to the Value field.Binding Department Name to the Value field and DepartmentID to the Label field will not provide adequate information to the user or the query. The user would not be able to identify a department by its ID.
  102. You are designing a set of SQL Server Integration Services (SSIS) packages. Several other developers will be working on thesame packages you are currently developing. All the SQL Server connections in the package require a user name and a password.

    While working on the package, you do not want to have to reenter the user name and password for each of the SQL Server connections after you have entered them once. When a new developer opens the package, you would like the developer to enter the password for each SQL Server connection.

    Which protection level would you set for the package?

    1. Server Storage

    2. Encrypt Sensitive With Password

    3. Encrypt Sensitive With User Key 

    4. Do Not Save Sensitive
    3. Encrypt Sensitive With User Key <Correct>

    Explanation:

    The Encrypt Sensitive With User Key protection level encrypts the sensitive information in the package by using keys based on the current user. When a new user opens the package, he or she must reenter sensitive data, such as a SQL Server password, and then save the package.

    The Encrypt Sensitive With Password protection level relies on a package password to encrypt the connection passwords and prompts developers to enter the package password. However, after they enter the package password, they do not need to enter the connection passwords.

    Do Not Save Sensitive does not allow the package to execute unless it is used in conjunction with an SSIS configuration that stores the package.

    Rely On Server Storage For Encryption will save the package to the msdb database and rely on SQL Server security to protect the packages.
  103. You are managing several SQL Server Integration Services (SSIS) packages. You have deployed five SSIS packages to a SQLServer database.

    Which SQL Server tool would you use to view the list of five packages that are now stored in the SQL Server database?

    1. SQL Server Configuration Manager (SSCM)

    2. SQL Server DTUtil Command Line Tool

    3. Business Intelligence Development Studio (BIDS)

    4. SQL Server Management Studio (SSMS)
    4. SQL Server Management Studio (SSMS) <Correct>

    Explanation:

    After packages are stored to a SQL Server database, they are managed by using SQL Server Management Studio (SSMS). In SSMS, you can connect directly to the instance of SSIS to view the packages saved to the package store.

    SQL Server Configuration Manager helps to manage the services and features related to a SQL Server 2008 installation but does not manage down to the package level for SSIS.

    BIDS cannot connect to SSIS to see packages that are deployed. DTUtil is a command-line utility only; it can handle deployment but cannot show deployed packages.
  104. You are designing a report with a table, and want to group the data.

    What types of groups are supported by the table? (Each correct answer presents part of the solution. Choose four.)

    1. Column groups

    2. Row groups 

    3. Data groups

    4. List group

    5. Report group

    6. Recursive hierarchy groups 

    7. Details group
    1. Column groups <Correct>

    2. Row groups <Correct>

    6. Recursive hierarchy groups <Correct>

    7. Details group <Correct>

    Explanation:

    The table data region is based on the Tablix data region, so the table inherits the group types supported by the Tablix. The Tablix data region supports details, row, column, and hierarchy groups.
  105. You have created a report that references an Analysis Services data source. You have some complicated query requests and are considering overriding the MDX generated by the Query Designer in Design mode.

    What is a disadvantage of switching to Query mode to edit the MDX query?

    1. No drag and drop functionality exists on measures.

    2. The MDX Query Designer does not provide a list of built-in functions.

    3. After making a change in Design mode, if you switch back to Design mode, you will lose the changes.

    4. The query cannot be executed in Query mode.
    3. After making a change in Design mode, if you switch back to Design mode, you will lose the changes. <Correct>

    Explanation:

    Switching to Design mode after you make changes in Query mode reverts to the original query, thereby losing the changes.

    Query mode provides drag-and-drop functionality.

    The query can be executed in both Query and Design modes.

    The designer adds a new Functions tab in Query mode, providing a list of the built-in functions.
  106. You are developing a SQL Server Analysis Services (SSAS) cube that is tracking system alerts in your network center for different equipment types. One of the measures is tracking the number of events. Your Excel users are asking you to modify the events measure so that when they connect to the cube in a new pivot table, no decimal places are shown and so that commas are placed to separate hundreds from thousands in the numbers.

    What needs to be changed to achieve the correct display?

    1. In Analysis Services, change the DisplayFolder property of the measure to Standard.

    2. In Analysis Services, change the DataType property to Integer.

    3. In Excel, go to the Options page on the Pivot table tab and clear the Display Decimals check box.

    4. In Analysis Services, change the FormatString property of the measure to #,###.
    4. In Analysis Services, change the FormatString property of the measure to #,###. <Correct>

    Explanation:

    The FormatString property of a measure tells the client tool how to format the display. This property accepts currency symbols, percent characters, commas, and decimal places that control how the number is shown. Setting the property to #,### removes decimals and inserts commas between the hundreds, thousands, and so on.

    The DataType property of a measure is related to the underlying column and will not affect the display.

    The DisplayFolder property allows measures to be grouped in a subfolder during user browsing but does not relate to how the measure is formatted.

    The decimal places can be changed in Excel for a highlighted set of cells; however, new queries against the cube will not use the updated formatting, so the formatting needs to be set in SSAS.
  107. When creating a dataset that connects to Analysis Services, which are valid methods of entering a query for a dataset created from an SSAS data source? (Each correct answer presents a complete solution. Choose three.)

    1. Build the query through the graphical Query Designer. 

    2. Paste a query into the Query pane. 

    3. Import an MDX query from another report. 

    4. Import a query from an .mdx file.
    1. Build the query through the graphical Query Designer. <Correct>

    2. Paste a query into the Query pane. <Correct>

    3. Import an MDX query from another report. <Correct>

    Explanation:

    The MDX queries are added on the Query page of the Dataset Properties dialog box. Query Type must be set to Text. MDXqueries can be built through the graphical Query Designer by clicking the Query Designer button.

    A query can be pasted into the Query pane of the Dataset Properties dialog box.An MDX query can be imported from another report by clicking the Import button in the Dataset Properties dialog box.

    Select the .rdl file of the report that contains the query.

    Queries saved as .mdx files cannot be imported.
  108. You are evaluating the reporting requirement for a new Business Intelligence project.

    Which scenarios would allow you to use a cached snapshot to meet the requirement? (Each correct answer presents a complete solution. Choose two.)

    1. The report requires row-level security.

    2. The report data changes infrequently. 

    3. Previous versions of the report need to be archived. 

    4. The data source must use Windows authentication.
    2. The report data changes infrequently. <Correct>

    3. Previous versions of the report need to be archived. <Correct>

    Explanation:

    Reports with static or infrequently changing data are good candidates for snapshot use. Historical versions of reports can be created by creating a snapshot every time the report runs.

    Reports using row-level security cannot be used as snapshots because the report data will be different for each user requestingthe report.

    One of the limitations of snapshots is that they cannot be used with reports connecting to a data source through Windows authentication
  109. You are designing a SQL Server Integration Services (SSIS) package. You currently have a Foreach Loop container with a Script task inside. Foreach Loop is looping through a set of text files. The Script task creates a new variable for the name of the text file plus the current time.

    You would like to verify that you have set up the variable properly.

    How can you view the value of the variable in the Script task?

    1. Add a breakpoint to the Foreach Loop container.

    2. Add a data viewer after the Script task.

    3. Add a data viewer before the Script task.

    4. Add a breakpoint to the Script task.
    4. Add a breakpoint to the Script task. <Correct>

    Explanation:

    Breakpoints can be defined on a line of code inside a Control Flow Script task. Data viewers are used only for data flow development and troubleshooting. A breakpoint in the Foreach Loop container does not stop each time the variable changes; it just breaks at the Foreach Loop container.
  110. You need to add an image to a report that displays the company logo. The graphics department has given you an image and you would like to include the image in the report directly without having to specify the file system location or storing the image in a database table.

    Which source type of the image report item ensures that the specified image is always available to the report? (Each correct answer presents a complete solution. Choose two.)

    1. External

    2. Background 

    3. Embedded

    4. Data-bound
    2. Background <Correct>

    3. Embedded <Correct>

    Explanation:

    An embedded image ensures that the image is always displayed in the report. When an image is embedded in a report, the Report Designer MIME encodes the image and stores it in the report definition.

    A background image stores a URL for accessing an image, an image from a database, or an embedded image. If you configure a background image as embedded, the Report Designer stores the image as part of the report itself. However, if you configure the report to retrieve the background image from either a URL to an image or a location in a database, it's possible that Reporting Services won't be able to retrieve the image if the Web server that corresponds to the URL is unavailable or the database becomes corrupt.

    An external image stores the URL for the image. When the report is run on the report server, the unattended execution account is used to retrieve the image. The account having insufficient rights to access the image or the location having been changed are situations that would prevent the image from being displayed on the report.

    A data-bound image refers to an image that is stored in a database. When the report is rendered, Reporting Services must retrieve the image in order to display it in the report. Reporting Services cannot display a data-bound image if the database that stores the associated image becomes corrupt or is deleted.
  111. You implement a SQL Server Analysis Services (SSAS) project. Most of the dimensions will use tables from SQL Server 2008. One of the dimensions will be loaded from a Microsoft Access database. You don't have permissions to create objects in the SQLServer database.

    How would you implement the solution? (Each correct answer presents part of the solution. Choose two.)

    1. In SQL Server 2008, create a server linked to the Access database, link the tables, and then create a view in the database to reference the Access tables.

    2. Create a data source view with SQL Server as a primary data source and Access as a secondary data source.

    3. Create a data source view with Access as a primary data source and SQL Server as a secondary data source.

    4. Create one data source that points to the SQL Server database and a second data source that points to the Access database.
    2. Create a data source view with SQL Server as a primary data source and Access as a secondary data source.<Correct>

    4. Create one data source that points to the SQL Server database and a second data source that points to the Access database.<Correct>

    Explanation:

    A data source view (DSV) can combine tables from multiple data sources, but the primary data source must support remote queries with OPENROWSET. First, you must create two data sources in the Analysis Services project that point to the two databases. Then, you must add the required tables from the SQL Server database so that SQL Server becomes the primary data source in the DSV. A primary data source is the first data source from which you add objects to the DSV. The Access database must be the secondary data source because it relies on the SQL Server 2008 database for the OPENROWSET requirement.

    This solution can also be handled by using a linked server in SQL Server to Access with database views to the Access databases; however, because you do not have security rights to create the objects, you cannot use this solution.
  112. You open an Analysis Services project in BIDS and make changes to the cube source file in project mode. You need to deploy the changes to a test server for QA testing. The cube on the test server already has measure group partitions.

    You want the deployment process to preserve the partition design of the test cube.

    Which deployment option would you use?

    1. Analysis Services Deployment Wizard 

    2. Backup and restore

    3. Synchronize Databases Wizard

    4. BIDS deployment
    1. Analysis Services Deployment Wizard <Correct>

    Explanation:

    The Analysis Services Deployment Wizard enables you to retain existing security and partition settings.

    BIDS deployment overwrites the management settings of the target server.

    Backup and restore replaces the database.

    The Synchronize Databases Wizard clones an SSAS database from one server to another.
  113. You are responsible for implementing a new SSAS application for your company. The solution is highly visible and therefore requires maximum uptime. One of the high-availability choices is to install SSAS in an existing Microsoft Clustering Service(MSCS) installation.

    What advantages does this provide? (Each correct answer presents part of the solution. Choose two.)

    A) Your SSAS solution stays online even with an operating system (OS) corruption. 
    B) Clustering tolerates a server hardware failure. 
    C) You are able to load balance queries between multiple servers.
    D) Data corruption does not affect the solution's availability.
    A) Your SSAS solution stays online even with an operating system (OS) corruption. <Correct>

    C) Clustering tolerates a server hardware failure. <Correct>

    Explanation:

    MSCS uses a shared-drive model for high availability with multiple servers. Therefore, if a server OS crashes or a hardware failure occurs in the server, the SSAS instance fails over to another node in the cluster.

    Load balancing is not possible because the SSAS instance runs on only one server at a time.

    Data corruption is not protected by clustering.
    (this multiple choice question has been scrambled)
  114. A cube has Internet Sales Amount and Reseller Sales Amount measures. You have defined a calculated member, Sales Amount,as follows:

    [Sales Amount] = [Internet Sales Amount] + [Reseller Sales Amount]You need to prevent a role from seeing the Sales Amount calculated member if the role does not have access to either the Internet Sales Amount measure or the Reseller Sales Amount measure.

    Which cell-level security changes must you make to the role?

    1. You need to enable read contingent permissions on the Sales Amount measure. 

    2. You need to enable read contingent permissions on Internet Sales Amount and Reseller Sales Amount measures.

    3. You need to enable read cascading permissions on the Sales Amount measure.

    4. You need to enable read permissions on Sales Amount.
    1. You need to enable read contingent permissions on the Sales Amount measure. <Correct>

    Explanation:

    To deny access to a calculated member if one or more of the underlying base measures is denied, you need to enable read contingent permissions on the calculated measure. If you enable read permission on the calculated member, the users can still see the calculated measure even if they do not have access to one of the underlying base measures
  115. You have created a Customer Profile data mining structure that contains two data mining models. You fully process the structure to train the mining models. You need to load the structure with new training data without affecting the mining models.

    Which processing option must you use to process a structure without affecting the mining models in the structure?

    1. Process Clear Structure

    2. Process Full

    3. Process Structure

    4. Unprocess
    3. Process Structure <Correct>

    Explanation:

    Process Structure loads the structure with data without affecting the models in the structure.

    Process Clear Structure removes the training cases from the structure.

    The Unprocess option removes data from the structure and mining models.

    Finally, Process Full processes both the structure and the mining models.
  116. You are designing a SQL Server Integration Services (SSIS) package. You are transferring data from one SQL Server table toanother inside a Data Flow task. You want to log the number of rows that have been processed to the Windows Event log. You have enabled package logging.

    Which event should you log to ensure that the number of rows transferred is logged every time the package is run?

    1. OnPipelineRowsSent 

    2. OnInformation

    3. OnPostExecute

    4. OnPipelinePostPrimeOutput
    1. OnPipelineRowsSent <Correct>

    Explanation:

    The OnPipelineRowsSent event reports the number of rows provided to a task's input.

    The OnPipelinePostPrimeOutput event indicates that a task has completed its last call to the PrimeOutput method.

    The OnPostExecute event writes to a log after an executable has completed.

    The OnInformation event writes to a log during the validation and execution of a task.
  117. You are managing several SQL Server Integration Services (SSIS) packages. You try to open one of the packages in Business Intelligence Development Studio (BIDS), but it requires you to enter a package password.

    You do not know the package password, so you click the Cancel button. The package still opens, and you can still view all the tasks on the Control Flow.

    To which current protection level is the package set?

    1. Rely On Server Storage For Encryption

    2. Do Not Save Sensitive

    3. Encrypt All With Password

    4. Encrypt Sensitive With Password
    4. Encrypt Sensitive With Password <Correct>

    Explanation:

    The Encrypt Sensitive With Password protection level encrypts all sensitive data in the package. If the password is not provided,the package will still open but without the sensitive data.

    If no password is provided to a package saved with Encrypt All With Password, nothing in the package will be loaded.

    When a package is saved with Do Not Save Sensitive, no package password is required, but any connection passwords will not be saved.

    Rely On Server Storage For Encryption saves the package to the msdb database and relies on SQL Server security to protect the packages.
  118. A cube has two measure groups: Internet Sales and Currency Rates. The Internet Sales measure group has a Sales Amount measure, which stores the sales order amount in foreign currencies. You need to define a measure expression for the Internet Sales measure to convert it to USD.

    Which of the following is true about measure expressions? (Each correct answer presents part of the solution. Choose two.)

    1. One referenced measure must be in a different measure group. 

    2. A measure group can use only multiplication (*) and division (/) operators. 

    3. A measure expression can have an addition (+) operator.

    4. A measure expression can have more than two operands.
    1. One referenced measure must be in a different measure group. <Correct>

    2. A measure group can use only multiplication (*) and division (/) operators. <Correct>

    Explanation:

    A measure expression supports multiplication and division operators only and with only two operands. One of the operands must be in a different measure group.
  119. Which tasks CANNOT be performed using the Reporting Services Configuration Manager tool?

    1. Configure the unattended execution account.

    2. Configure report server e-mail settings.

    3. Upload reports to the report server database. 

    4. Configure Report Server service account.

    5. Back up, restore, or replace the encryption key.

    6. Configure a scale-out deployment.

    7. Create and configure URLs.
    3. Upload reports to the report server database. <Correct>

    Explanation:

    The Reporting Services Configuration Manager tool is used to configure a Reporting Services installation. This tool must be used to configure the server if you used a files-only install option. The configuration can be modified if you used the default installation.

    The Report Server service account can be configured in the Service Account pane.

    A scale-out deployment can be configured in the Scale-Out Deployment pane.

    Encryption key management can be performed in the Encryption Keys pane.

    The unattended execution account can be configured in the Execution Account pane.

    The Report Server e-mail settings can be configured in the E-mail Settings pane.
  120. You are designing a SQL Server Integration Services (SSIS) package.

    Your current package has an Execute SQL Task that is critical for the package. Occasionally, this Execute SQL Task fails. If the task fails, you want the rest of the package to stop running.

    What property can you change on the task to prevent the rest of the package from executing if the Execute SQL Task fails?

    1. MaximumErrorCount = 0

    2. MaximumErrorCount = 1

    3. FailPackageOnFailure = True 

    4. FailParentOnError = True
    3. FailPackageOnFailure = True <Correct>

    Explanation:

    If you set the FailPackageOnFailure property of the Execute SQL Task to True, and if the Execute SQL Task fails, any remaining tasks or containers within the package will not execute, and the package will fail.The MaximumErrorCount property identifies the maximum number of errors that can occur in a task before a package stops running. However, even if this property is set to 1 or 0, the rest of the package will continue to execute even if the task fails, assuming that the precedence constraints are configured to allow additional tasks to execute. FailParentOnError specifies whether the parent container should fail if the there is an error in the child. Even though the package is considered the parent to the task, the package will continue to run even if this property is set to True.
  121. You are managing several SQL Server Integration Services (SSIS) packages.

    You have deployed several packages to a SQL Server instance. You open SQL Server Management Studio (SSMS), and you want to execute one of the packages stored in the package store.

    Which utility will SSMS use to execute the package?

    1. Business Intelligence Development Studio (BIDS)

    2. DTExec

    3. DTExecUI 

    4. SQL Server Agent
    3. DTExecUI <Correct>

    Explanation:

    SSMS will use DTExecUI to execute packages stored in a package store. To perform this operation, the SSIS service must be started. When you right-click on a package and choose Run Package in SSMS, DTExecUI opens and is configured with the package you choose.

    DTExec is the command-line tool to execute SSIS packages but is not directly used by the SSMS to run packages.

    BIDS is used to develop SSIS packages before they are deployed to SQL Server or the file system but is not used to execute packages after they are deployed.

    SQL Server Agent can be used to execute an SSIS package on a scheduled basis but, when connected to SSIS through SSMS, SQL Server Agent is not used to execute a package.
  122. You are developing an SSAS cube to identify potential customers for a new mail marketing campaign. To begin, you want to create a mining model structure that includes the customer key, a gender attribute, a number-of-cars-owned attribute, and a column to predict whether the customer is a good candidate to purchase a bicycle.

    What is the right Data Mining Extensions (DMX) query syntax to create this structure?

    1. CREATE MINING STRUCTURE [New Mailing]
    (
       CustomerKey LONG KEY,
       Gender TEXT DISCRETE,
       [Number Cars Owned] LONG DISCRETE,
       [Bike Buyer] LONG DISCRETE
    )

    2. CREATE MINING STRUCTURE [New Mailing]
    (
      CustomerKey LONG KEY,
      Gender TEXT DISCRETE,
      [Number Cars Owned] LONG DISCRETE,
      [Bike Buyer] PREDICT
    )

    3. CREATE MINING MODEL [New Mailing]
    (
       CustomerKey LONG KEY,
       Gender TEXT DISCRETE,
       [Number Cars Owned] LONG DISCRETE,
       [Bike Buyer] PREDICT
    )

    4. CREATE MINING MODEL [New Mailing]
    (
      CustomerKey LONG KEY,
      Gender TEXT DISCRETE,
      [Number Cars Owned] LONG DISCRETE,
      [Bike Buyer] LONG DISCRETE
    )
    • 1. CREATE MINING STRUCTURE [New Mailing]
    • (   
    •    CustomerKey LONG KEY,   
    •    Gender TEXT DISCRETE,   
    •    [Number Cars Owned] LONG DISCRETE,
    •    [Bike Buyer] LONG DISCRETE
    • )<Correct>

    Explanation:

    The correct DMX statement to create a new data mining structure is CREATE MINING STRUCTURE. After the structure is created, different data mining models can be applied to the mining structure

    .Even though Bike Buyer will predict whether the customer is a good candidate for a mailing, the mining models define whether the column is used as an input or a prediction. The mining structure just defines the column list and data types.
  123. You are developing a SQL Server Analysis Services (SSAS) cube. You have several SSAS load-balanced production servers,each with an identical copy of an SSAS database. The SSAS database takes a very long time to process. You need to avoid processing the database on each server. Instead, you decide to process the database once on a dedicated staging server and update the production databases from the staging database.

    Which deployment option would you use?

    1. Analysis Services Deployment Wizard

    2. Synchronize Databases Wizard 

    3. Business Intelligence Deployment Studio (BIDS) deployment

    4. File copy
    2. Synchronize Databases Wizard <Correct>

    Explanation:

    The Synchronize Databases Wizard is designed to handle exactly this scenario because it synchronizes databases efficiently.

    • BIDS deployment processes each database. Similarly, the Analysis Services Deployment Wizard processes the databases
    • individually.

    SSAS 2008 doesn't support copying databases with file copy.
  124. You are managing a SQL Server Analysis Services (SSAS) cube, and query performance issues have been reported at peak times of the day.
    Which tools can you use to help identify and resolve performance issues? (Choose three.)

    1. Performance Monitor 

    2. Analysis Services Deployment Wizard

    3. SQL Server Profiler 

    4. SQL Server Management Studio Query Designer

    5. Usage-Based Optimization Wizard
    1. Performance Monitor <Correct>

    3. SQL Server Profiler <Correct>

    5. Usage-Based Optimization Wizard <Correct>

    Explanation:

    SQL Server Profiler can trace Analysis Services to view user and query activity. Profiler can show MDX queries, caching, query durations, and so on. The Usage-Based Optimization Wizard can tune aggregations on a partition by reviewing user queries and creating aggregations that relate directly to users' queries. Performance Monitor is a Windows tool that shows counters for applications and processes. Hundreds of counters exist for SSAS to see aggregation hit cache ratios, cell calculation counts, and so on.

    The Analysis Services Deployment Wizard assists in moving SSAS databases from one server to the next but will not help with performance. Furthermore, the Management Studio Query Designer can run MDX queries but cannot assist in optimization of them.
  125. You are developing a SQL Server Analysis Services (SSAS) cube. You have a cube in the SSAS database that contains a measure group called Sales with a measure called Units Sold. The measure group is connected to a dimension called Purchase Date that contains a hierarchy called Calendar, using the Year, Quarter, Month, and Day attributes.

    You want to create a calculated member that shows the year-to-date value of the Units Sold measure at any level in the Purchase Date hierarchy.

    What is the proper Multidimensional Expressions (MDX) syntax to show this?

    1. SUM(PeriodsToDate([Purchase Date].[Calendar].CurrentMember.Parent.Level),[Sales].[Units Sold])

    2. SUM([Purchase Date].[Calendar].CurrentMember.Parent.Level),SUM([Sales].[Units Sold])

    3. SUM(PeriodsToDate([Purchase Date].[Calendar].[Year],[Date].[ Purchase Calendar].CurrentMember), [Measures].[Units Sold])

    4. SUM(PeriodsToDate([Purchase Date].[Calendar].[Year],[Date].[ Purchase Calendar].CurrentMember), [Sales].[Units Sold])
    3. SUM(PeriodsToDate([Purchase Date].[Calendar].[Year],[Date].[ Purchase Calendar].CurrentMember), [Measures].[Units Sold])<Correct>

    Explanation:

    The PeriodsToDate MDX function returns the set of date dimension members from the beginning of the defined level to the current member of the date dimension. The function takes a level reference and, to ensure that the function returns the year to date value for any member of the date dimension, the Calendar Year level must be referenced directly. The correct reference to any measure in the cube, no matter which measure group it belongs is to, uses the [Measures] reference. In this case, [Measures].[Units Sold]references the Units Sold measure in the Sales measure group.
  126. You are building a scale-out implementation of SQL Server Reporting Services (SSRS) to include several Web servers in a Web farm.

    To add more servers to the Web farm, which of the following steps are required to set up new SSRS servers to join a Web farm?(Each correct answer presents part of the solution. Choose two.)

    1. Use the Reporting Services Configuration Manager tool and set the Initialized check box for the existing SSRS server(s) in the Web farm. 

    2. Configure the new SSRS instance that needs to join the Web farm by using the Reporting Services Configuration Manager tooland pointing the database connection to the existing repository database for the Web farm. 

    3. Back up the encryption key on the first installation of SSRS and restore it on the new instance of SSRS that will join the Web farm.

    4. Stop the SSRS service on the existing server(s) in the Web farm.
    1. Use the Reporting Services Configuration Manager tool and set the Initialized check box for the existing SSRS server(s) in the Web farm. <Correct>

    2. Configure the new SSRS instance that needs to join the Web farm by using the Reporting Services Configuration Manager tooland pointing the database connection to the existing repository database for the Web farm. <Correct>

    Explanation:

    The new report server instance must be added to the scale-out deployment. This is done by initializing the report server. All the report servers in a scale-out deployment must connect to the same repository database.

    The SSRS services are not required to be stopped. In addition, the encryption keys are automatically synched up after adding anew SSRS instance to the Web farm. Therefore, the encryption keys do not need to be backed up and restored.
  127. You are managing several SQL Server Integration Services (SSIS) packages.

    A user has told you that he cannot view the SSIS packages stored in the SQL Server database. You want to add the user to the appropriate database role in the msdb database so that he can view the SSIS packages.

    Which of the following database roles give permissions to view SSIS packages stored in the msdb database? (Each correctanswer presents a complete solution. Choose three.)

    1. db_ssisoperator 

    2. db_datareader

    3. db_ssisadmin 

    4. db_ssisltduser
    1. db_ssisoperator <Correct>

    3. db_ssisadmin <Correct>

    4. db_ssisltduser <Correct>

    Explanation:

    SSIS uses three fixed database-level roles: db_ssisadmin, db_ssisltduser, and db_ssisoperator. They are implemented within the msdb database and enable an SSIS administrator to define who can view, execute, and import packages. The db_ssisltduser user can view, execute, and import only the packages created by the user. The db_ssisoperator user can view and execute any packages on the server but cannot import a package. The db_ssisadmin user can view, execute, and import any packages on the server.

    The role db_datareader role is related only to data in the defined database, not to viewing, executing, or importing packages.
  128. You are designing a SQL Server Integration Services (SSIS) package. On the Control Flow tab in SSIS Designer, you have a File System task. You would like to write to a SQL log table after each time the File System task completes.

    Which event handler would you use?

    1. OnPostValidate

    2. OnInformation

    3. OnProgress

    4. OnPostExecute
    4. OnPostExecute <Correct>

    Explanation:

    The event handlers fire based on various occurrences when a package is running. For example, when a package first starts up, the package validates the structures and data and, after the validation of each task or container is complete, the OnPostValidateexecutes. OnInformation and OnProgress run during the execution of a task, but only OnPostExecute runs after a task is complete.
  129. You are developing a report that will contain several pages and you want to simplify the report navigation.

    Which interactive reporting feature enables a user to navigate a large report easily, without searching the entire report?

    1. Hyperlink action

    2. Report toolbar

    3. Document map 

    4. Table of contents
    3. Document map <Correct>

    Explanation:

    A document map in a report provides users with a way to navigate to certain areas of the report. When you view a report that includes a document map, a separate side pane appears next to the report. Report sections and groups are arranged in a hierarchy of links. Clicking items in the document map refreshes the report and displays the area of the report that corresponds to the item in the document map.

    The hyperlink action is used to navigate from one report to another and the report toolbar shows the report options, but neither allow navigating easily within a report. The Document Map acts like a table of contents for the report, but the report feature is called the Document Map.
  130. You have a cube that uses Reseller and Geography dimensions and has a Reseller Sales measure group. The Reseller dimension has a Regular relationship with the Reseller Sales measure group. The Geography dimension cannot be joined directly to the Reseller Sales measure group. Both the Geography dimension and the Reseller dimension have a Geography Key attribute.

    You need to enable end users to browse the reseller sales data by Geography.

    What type of dimension usage relationship do you need to join the Geography dimension to the Reseller Sales measure group?

    1. Fact

    2. Regular

    3. Many-to-many

    4. Referenced
    4. Referenced <Correct>

    Explanation:

    You can use a referenced relationship to join the Geography dimension to the Reseller Sales measure group. In this case, the Geography dimension will use the Reseller dimension as a bridge dimension.

    The regular relationship joins a dimension to a fact table.

    There is no many-to-many relationship in the instance.

    The fact relationship is used if a dimension is created from a fact table.
  131. Which MDX query will return the July 2003 status of the Revenue KPI in the Adventure Works cube for each product category?(Each correct answer presents a complete solution. Choose two.)

    1. SELECT ([Date].[Calendar].[Month].[July 2003],KPIStatus("Revenue")) ON COLUMNS, [Product].[Subcategory].Members ON ROWSFROM [Adventure Works]

    2. SELECT KPIStatus("Revenue") ON COLUMNS, [Product].[Subcategory].Members ON ROWS, [Date].[Calendar].[Month].[July 2003] ON AXIS (3)FROM [Adventure Works]

    3. SELECT KPIStatus("Revenue") ON COLUMN, [Product].[Subcategory].Members ON ROWSFROM [Adventure Works]WHERE [Date].[Calendar].[Month].CurrentMember = [July 2003]

    4. SELECT KPIStatus("Revenue") ON COLUMNS, [Product].[Subcategory].Members ON ROWSFROM [Adventure Works]WHERE [Date].[Calendar].[Month].[July 2003]
    1. SELECT ([Date].[Calendar].[Month].[July 2003],KPIStatus("Revenue")) ON COLUMNS, [Product].[Subcategory].Members ON ROWSFROM [Adventure Works]<Correct>

    4. SELECT KPIStatus("Revenue") ON COLUMNS, [Product].[Subcategory].Members ON ROWSFROM [Adventure Works]WHERE [Date].[Calendar].[Month].[July 2003]<Correct>

    Explanation:

    The KPIStatus("Revenue") function will reference the KPI status correctly. To filter the query by July 2003, the [Date].[Calendar].[Month].[July 2003] member must be referenced either in the WHERE clause or on the columns axis as part of the KPIStatus in a tuple.
  132. Your security requirements dictate that you enforce secure traffic between the server and the clients. In fact, all Web service calls to the SSRS server require a secure SSL connection.

    How can you define that level of SSL security?

    1. Modify the web.config file and change the defaultProxy setting to Disabled.

    2. Edit the rsreportserver.config file and change the SecureConnectionLevel property to 1.

    3. Modify the web.config file and change the defaultProxy setting to Enabled.

    4. Edit the rsreportserver.config file and change the SecureConnectionLevel property to 3.
    4. Edit the rsreportserver.config file and change the SecureConnectionLevel property to 3. <Correct>

    Explanation:

    The SecureConnectionLevel property supports values from 0 to 3, where 0 is the least secure and 3 is the most secure. By setting the property to 3, you require all server access to use SSL to communicate.

    The defaultProxy setting in the web.config file defines how the Report Manager should access the Report Server, whether through a proxy or directly.
  133. You have an Internet Sales measure group that is based on a FactInternetSales fact table. Each fact record in the fact tablerepresents a line order number. The FactInternetSales fact table has a SalesOrderNumber column. The end users have requested the ability to browse the cube data by sales orders. You decide to create a SalesOrder dimension from the SalesOrderNumbercolumn.

    What type of a dimension usage relationship do you need to use to join the SalesOrder dimension to the Internet Sales measure group?

    1. Fact 

    2. Regular

    3. Referenced

    4. Many-to-many
    1. Fact <Correct>

    Explanation:

    Because the scenario mentions that the SalesOrder dimension is generated from the fact table, the SalesOrder dimension is a fact dimension. You need to use a fact dimension usage relationship to join a fact dimension to a measure group.

    The regular relationship type is for SSAS dimensions that are based on underlying dimension tables that relate directly to the fact table on which the measure group is based.

    Similarly, the referenced relationship also requires an underlying dimension table that is related to another dimension table.

    Many-to-many relationships require an intermediate measure group to enable multiple dimension records or members to relate to the base fact table.
  134. You have a measure group that contains several partitions. One of the partitions stores historical data for several years and is rarely queried. You need to select a storage mode that saves storage space on the SSAS server, but you still want to take advantage of aggregations.

    How should you configure these partitions?

    1. Set the partition's StorageMode property to ROLAP, set the Slice property to the date dimension member that defines the underlying historical data, and define aggregations on the partition.

    2. Set the partition's StorageMode property to HOLAP and define aggregations on the partition. 

    3. Use the Automatic MOLAP Proactive Caching setting.

    4. Set the partition's StorageMode property to MOLAP.
    2. Set the partition's StorageMode property to HOLAP and define aggregations on the partition. <Correct>

    Explanation:

    With HOLAP, SSAS stores aggregations in SSAS, but the underlying data remains in the relational database.

    ROLAP, which contains no aggregations, consumes the least storage space because the all the data is left in the relational database. With ROLAP, aggregations are not stored in SSAS.

    When you define a partition with a MOLAP storage mode, both the data and the aggregations are stored in SSAS.
  135. Applying the following properties to the Interactive Sort pane of the Text Box Properties dialog box of a column header will result in which type of sort action?

    Enable Interactive Sort On This Text Box Property: Select The Check Box.Sort Property: Select Detail Rows.Sort By Property: Select The OrderQty Field To Sort On.

    1. Sort rows based on a complex group expression.

    2. Sort a child-level group of a table.

    3. Sort a top-level parent group of a table.

    4. Sort detail rows of a table with no groups.
    4. Sort detail rows of a table with no groups. <Correct>

    Explanation:

    The property values create a sorting of detail rows for a table with no groups.

    To sort a top-level parent group of a table, the Sort property should be changed to Groups and a parent group selected.

    To sort a child-level group of a table, the Sort property should be changed to Groups and a child group selected.

    To sort rows based on a complex group expression, the Sort property should be changed to Groups and a group expression created containing multiple groups.
  136. You have been given a set of reports to administer that contain embedded code.

    Which statement about embedded code is NOT true?

    1. Methods are available through the global Code member.

    2. The code must be instance based.

    3. The code must be written in VB.NET.

    4. Methods can be called from multiple reports.
    4. Methods can be called from multiple reports. <Correct>

    Explanation:

    Embedded code methods cannot be used in multiple reports. The methods are stored in the report definition, so they are available in only the report that contains the methods.

    Embedded code must be written in VB.NET. No other language is currently supported.

    All methods must be instance based.

    Each report contains a global code member that provides access to the methods.
  137. You are developing a SQL Server Analysis Services (SSAS) cube. You currently have two tables, DimDate and FactSalesTargets. The FactSalesTargets table joins DimDate with the CalendarMonth and CalendarYear columns, even though the DimDate table'sgrain is the day level. You would like to create a measure group based on the FactSalesTargets table that joins your date dimension at the month level.

    How would you configure the Define Relationship dialog box?

    1. Set the Relationship type to Referenced. Set the Granularity attribute to Calendar Month. Match the Dimension columns to the measure group columns of Calendar Year and Calendar Month, respectively.

    2. Set the Relationship type to Referenced. Set the Granularity attribute to Calendar Month. Match the dimension columns to the measure group columns on the Calendar Month.

    3. Set the Relationship type to Regular. Set the Granularity attribute to Calendar Month. Match the Dimension columns to the measure group columns of Calendar Year and Calendar Month, respectively. 

    4. Set the Relationship type to Month. Set the Granularity attribute to Day. Match the Dimension columns to the measure groupcolumns on DateKey
    3. Set the Relationship type to Regular. Set the Granularity attribute to Calendar Month. Match the Dimension columns to the measure group columns of Calendar Year and Calendar Month, respectively. <Correct>

    Explanation:

    You can join measure groups to dimensions at different grains by using the Define Relationship dialog box and setting the Relationship type to Regular. The Granularity attribute needs to be set to the grain of the relationship, and the unique attribute keys need to be matched between the dimension columns and the measure group columns.
  138. You have a Product dimension and a Sales cube that uses the Product dimension. You want to process the Product dimension to reflect the data changes committed to the dimension table. Dimension data changes include adding, deleting, and updating dimension members. You want to process only the Product dimension and not the Sales cube.

    Which dimension processing option would you use to update the Product dimension?

    1. Process Update 

    2. Process Add

    3. Process Incremental

    4. Process Full
    1. Process Update <Correct>

    Explanation:

    The Process Update option is a more lightweight processing option that applies member changes (inserts, deletes, updates) without invalidating the cubes that use the dimension.

    Process Incremental is used only for partitions to load new data into a partition.

    Process Add detects member additions only.

    The Process Full option requires processing the cube that uses the dimension
  139. You have a SQL Server Integration Services (SSIS) package stored in a SQL Server msdb database.

    You notice that the SSIS Service is disabled on the server.

    How can you execute the package? (Each correct answer presents a complete solution. Choose three.)

    1. SQL Server Agent 

    2. DTExecUI command prompt utility 

    3. Execute Package Utility 

    4. SQL Server Management Studio (SSMS)
    1. SQL Server Agent <Correct>

    2. DTExecUI command prompt utility <Correct>

    3. Execute Package Utility <Correct>

    Explanation:

    Even though the SSIS service is stopped, you can still execute a package that is deployed to the msdb database in SQL Server2008 with the following methods: SQL Server Agent, the Execute Package Utility (DTExecUI), and the dtexec command prompt utility.

    When a package is deployed to the package store, the service needs to be running to execute a package. Packages deployed to the package store are not stored in the msdb database but to a system folder in the SSIS installation files. The package store is managed by the SSIS Service, so to execute a package deployed to the package store, SSIS needs to be running.
  140. You have a SQL Server Integration Services (SSIS) package stored on a 64-bit server.

    Which of the following tools does NOT have a 64-bit version of the tool?

    1. dtexec utility

    2. DTExecUI utility 

    3. SQL Server Import and Export Wizard

    4. dtutil utility
    2. DTExecUI utility <Correct>

    Explanation:

    The DTExecUI utility is available only as a 32-bit application. Therefore, if you are working on a 64-bit server and run a package through the DTExecUI (Execute Package Utility), the package will run in 32-bit emulation mode. Furthermore, the package might fail if the connection managers used are not 32-bit compliant.

    Dtexec, dtutil, and the SQL Server Import and Export Wizard have both a 64-bit and a 32-bit application. Be sure to note that if you develop a package in a 32-bit environment and want to run the package in a 64-bit environment, the connection managers need to be 64-bit compliant. Some connection managers such as Excel work in a 32-bit environment only.
  141. You have created an XMLA script to process the objects in your SQL Server Analysis Services (SSAS) cube. You currently are performing a full process of the dimensions and measure groups. The XMLA includes the following line for each object:

    <Type>ProcessFull</Type>

    Over time, the processing has taken longer, and you want to change the processing type for the dimensions and process only the partitions affected by data changes.

    What is the right XMLA setting for each object type?

    1. Dimensions:<Type>ProcessUpdate</Type>
    Partitions:
    <Type>ProcessUpdate</Type>

    2. Dimensions:<Type>ProcessFull</Type>
    Partitions:
    <Type>ProcessFull</Type>

    3. Dimensions:<Type>ProcessUpdate</Type>
    Partitions:
    <Type>ProcessAdd</Type>

    4. Dimensions:<Type>ProcessUpdate</Type>
    Partitions:
    <Type>ProcessFull</Type>
    • 4. Dimensions:
    • <Type>ProcessUpdate</Type>
    • Partitions:
    • <Type>ProcessFull</Type> 
    • <Correct>

    Explanation:

    To process only some of the partitions, the dimension processing must be configured to use the ProcessUpdate type.

    A ProcessFull setting on the dimensions invalidates all the partition data, and every partition then needs to be processed. The ProcessFull operation for partitions rebuilds the partition with the new data.

    The ProcessUpdate setting does not exist for the partitions. The ProcessFull setting should be used for any partition that has changed data. ProcessAdd is a valid setting for partitions, but it adds data only to an existing partition with a defined source query; it does not handle changes. ProcessAdd creates duplicate records if a filter is not applied to process only new data.
  142. You have a sales summary report that needs to be emailed out regularly to the business development team for each member of the team. Therefore you want to set up the data-driven subscription to make that an automated process.

    Which of the following conditions have to be met to create the data-driven subscription for the sales summary report? (Each correct answer presents part of the solution. Choose three.)

    1. An antivirus program must be running.

    2. You must use the My Reports folder.

    3. You have to store the credentials for accessing source data on the report server. 

    4. You have to collect data for subscription properties with a single query. 

    5. SQL Server Agent must be running.
    3. You have to store the credentials for accessing source data on the report server. <Correct>

    4. You have to collect data for subscription properties with a single query. <Correct>

    5. SQL Server Agent must be running. <Correct>

    Explanation:

    The report must use stored credentials or no credentials to retrieve data at run time. You cannot subscribe to a report that is configured to use the impersonated or delegated credentials of the current user to connect to an external data source. The SQLServer Agent must be running because the query to retrieve the subscribers must be validated. If the query cannot be validated,the subscription task cannot be completed. A data-driven subscription must specify a query or command that retrieves subscriberdata. The query should produce one row for each subscriber.

    Although running an antivirus program is always a good practice, it is not necessary for data-driven subscriptions.

    Usage of the My Report folder is not necessary for data-driven subscriptions
  143. You are executing a SQL Server Integration Services (SSIS) package. The package is named BuildFact.dtsx and has a variable named MaxRowID. You need to set the value of MaxRowID to 78. You are using DTExec to run the package.

    Which syntax would you use to execute DTExec?

    1. Execute dtexec /FILE "C:BuildFact.dtsx" /Variable MaxRowID=78

    2. Execute dtexec /FILE "C:BuildFact.dtsx" /SET package.variables[MaxRowID].78

    3. Execute dtexec /FILE "C:BuildFact.dtsx" /SET package.variables[MaxRowID].Value;78 

    4. Execute dtexec /FILE "C:BuildFact.dtsx" /Variables [MaxRowID].Value;78
    3. Execute dtexec /FILE "C:BuildFact.dtsx" /SET package.variables[MaxRowID].Value;78 <Correct>

    Explanation:

    The SET command changes the value of a variable when executing DTExec. The syntax for setting a variable value is /SETpackage.variables[variableName].Values;{value}.

    Using the package.variables command must be preceded by /SET. The .Value must be used to set the value of the variable.
  144. You deploy a cube to production. An end user reports that the server has not responded for the past 15 minutes and it appears that the service has hung. You need to identify exactly the last activity that happened on the SSAS server before the service hung.

    Which log activity would you review?

    1. SQL Server Logs

    2. Flight recorder

    3. Windows Performance Monitor Logs

    4. Windows Event Log
    2. Flight recorder <Correct>

    Explanation:

    The SSAS flight recorder records the server activity for the past hour. You can open the flight recorder trace file in SQL ServerProfiler and replay it. If you have configured Profiler to monitor the server events, you can use this option, too. However, chances are that in real life, to avoid performance degradation, you won't run Profiler on a production server for long periods of time.

    The Windows Event Log and Windows Performance Monitor won't capture the query activity.
  145. You are designing a SQL Server Integration Services (SSIS) package and have five tasks contained in a sequence container. The sequence container is set up with transactions so that if any of the tasks fail, the transaction will roll back. You want to add an Execute SQL task for logging in between each Data Flow task. You do not want the logging data to roll back.

    How must you configure each of the SQL Server logging tasks?

    1. Add a sequence container for each Execute SQL task and place each Execute SQL task in a sequence container. SetTransactionOption to Supported.

    2. Set TransactionOption in each Execute SQL task to NotSupported. 


    3. Set TransactionOption in each Execute SQL task to False.

    4. Set TransactionOption in each Execute SQL task to Required.
    2. Set TransactionOption in each Execute SQL task to NotSupported. <Correct>

    Explanation:

    You can force a task not to participate in a transaction by setting its TransactionOption setting to NotSupported.

    The Required option would set the transaction to roll back on failure.

    If you choose the Supported option, each task joins the transaction and inherits the properties of the parent.

    False is not an option under TransactionOption.
  146. You are developing a SQL Server Analysis Services (SSAS) cube.

    You need to use the Usage-Based Optimization Wizard to optimize the cube aggregation design.

    In the list on the right, select the steps required to use the Usage-Based Optimization Wizard. Place your selections in the list on the left in the order in which the steps should be performed. Place your selections in the list on the left by clicking the items in the list on the right and clicking the arrow. You can also use the Up and Down buttons to rearrange items in the list on the left.
    Answer: no given

    Explanation:

    First, open the server properties and enable the query log. Next, capture enough query statistics. Finally, run the Usage-Based Optimization Wizard to create the aggregation design.
  147. You are designing a SQL Server Integration Services (SSIS) package. You have created two variables to set the value of a file connection dynamically. The variable values are set as follows:

    FilePath = C:Temp
    FileName = Test.txt

    How would you update the file connection, using these variables?

    1. Set the ServerName expression to @[User::FilePath] + "" + @[User::FileName]

    2. Set the ServerName expression to @FilePath + "" + @FileName

    3. Set the ConnectionString expression to @[User::FilePath] + "" + @[User::FileName] 

    4. Set the ConnectionString expression to @FilePath + "" + @FileName
    3. Set the ConnectionString expression to @[User::FilePath] + "" + @[User::FileName] <Correct>

    Explanation:

    The ConnectionString expression needs to be set to update the file path. When writing an expression, the "" is an escape character and must be written as "".

    The ServerName property for a connection sets the name of the server for a database connection.
  148. You are developing a SQL Server Analysis Services (SSAS) cube. You currently have a table named DimCustomer in a datasource view (DSV). You have created an SSAS Customer dimension that uses the DimCustomer table.

    You would like to add a new column to the Customer dimension named Full Name. Full Name will contain the contents of the FirstName and Last Name columns in the DimCustomer table.

    What would be the best way to add the new column to the DSV?

    1. Add a new named calculation to the DimCustomer dimension table in the DSV. Name the column Full Name and use theexpression, FirstName + ' ' + LastName. 

    2. Use the Replace Table option and select With Other Table for the Replace Table option. The other table should include acolumn named FirstName + ' ' + LastName AS FullName.

    3. Add a new named query to the DimCustomer dimension table in the DSV. Name the column Full Name and use the expression,FirstName + ' ' + LastName.

    4. Use the Replace Table option and replace the table with a named query. The named query should include a column namedFirstName + ' ' + LastName AS FullName
    1. Add a new named calculation to the DimCustomer dimension table in the DSV. Name the column Full Name and use theexpression, FirstName + ' ' + LastName. <Correct>

    Explanation:

    A named calculation in a data source view is a calculated column added to a table using an expression. The expression uses the syntax of the underlying database. Named calculations are the easiest way to add a calculation to a table.

    A named query is an entity in the DSV that is built on a query rather than on an underlying table or view.
  149. You are building a set of reports for the accounting group in your organization. The auditors have asked you what level of security access the server administrators have.

    A local Windows administrator account is given which rights in SQL Server Reporting Services by default? (Each correct answer presents part of the solution. Choose two.)

    1. Browser

    2. Content Manager 

    3. Report Builder

    4. System Users

    5. System Administrator 

    6. Publisher
    2. Content Manager <Correct>

    5. System Administrator <Correct>

    Explanation:

    A local Windows administrator account is assigned the System Administrator and Content Manager roles. These roles give the administrator unrestricted access to the report server and report catalog.

    The roles that are not assigned to the local Windows administrator account by default are:

    * System Users: enables users to view server properties, select shared schedules, and run Report Builder reports

    * Browser: enables users to view reports and create individual subscriptions

    * Report Builder: enables users to perform all the tasks allowed by the Browser role and to create reports in Report Builder

    * Publisher: enables users to perform all the tasks allowed by the Browser and Report Builder roles and grants additional permissions for publishing reports and models from Business Intelligence Development Studio
  150. You have implemented a data source view (DSV) that uses tables from a SQL Server database. A new column has been added to one of the tables.

    What's the easiest way to update the DSV definition to include the new column?

    1. Drop the affected table in the DSV and then add it back to the DSV.

    2. Manually add the new column.

    3. Click the Refresh Data Source View toolbar button. 

    4. Open the DSV in Business Intelligence Development Studio (BIDS) DSV Designer. DSV Designer will automatically detect the new column and update the DSV.
    3. Click the Refresh Data Source View toolbar button. <Correct>

    Explanation:

    The easiest way to synchronize the DSV with underlying data schema changes is to refresh the DSV. DSV Designer doesn't automatically reflect data schema changes.

    Dropping the affected DSV takes more effort.

    You cannot manually add a new column to a table.
  151. One of the responsibilities you have as an administrator for SSAS is to monitor SSAS querying so that you can optimize aggregation of the measure group partitions by using the Usage-Based Optimization Wizard.

    What must you do to capture the queries for the Usage-Based Optimization Wizard?

    1. Turn on the QueryLog settings in the SSAS server properties. 

    2. Use Windows Performance Monitor to capture SSAS:Query counters.

    3. Turn on the ProactiveCaching setting in the Measure Group partition properties.

    4. Run SQL Server Profiler and capture the SSAS MDX queries.
    1. Turn on the QueryLog settings in the SSAS server properties. <Correct>

    Explanation:

    The Usage-Based Optimization Wizard uses the query logs captured through the SSAS server properties. After the queries are captured to a table or file, you can use them to perform usage-based optimization for the aggregates in the measure group partitions.

    Although SQL Server Profiler can capture MDX queries in SSAS, you cannot use the Profiler output with the Usage-Based Optimization Wizard. The ProactiveCaching setting for partitions defines how the data is stored and when it is processed.
  152. You need to write a data mining model query that uses a defined data mining model called [Buyer Decision Model] to predict whether a set of customers is likely to buy a bicycle.

    What is the general data mining query syntax that enables you to return this result?

    1. SELECT
     Customer.CustomerNumber,
     [Buyer Decision Model].[Bike Buyer]
    FROM
     [Buyer Decision Model], (<source data query>) AS Customer
    PREDICTION FILTER <join mapping list>

    2. SELECT
      Customer.CustomerNumber,
      [Buyer Decision Model].[Bike Buyer]
    FROM
      [Buyer Decision Model]CROSS JOIN
    (<source data query>) AS Customer
    ON <join mapping list>

    3. SELECT
      Customer.CustomerNumber,
      [Buyer Decision Model].[Bike Buyer]
    FROM
      [Buyer Decision Model]
    PREDICTION JOIN
    (<source data query>) AS Customer
    ON <join mapping list>


    4. SELECT
      Customer.CustomerNumber,
      [Buyer Decision Model].[Bike Buyer]
    FROM
      [Buyer Decision Model]
    PREDICTION JOIN
    (<source data query>) AS Customer
    • 3. SELECT 
    •   Customer.CustomerNumber, 
    •   [Buyer Decision Model].[Bike Buyer]
    • FROM 
    •   [Buyer Decision Model]
    • PREDICTION JOIN
    • (<source data query>) AS Customer
    • ON <join mapping list>
    • <Correct>

    Explanation:

    Querying a data mining model to predict the results from a given input requires using the PREDICTION JOIN syntax in a Data Mining Extensions (DMX) query. The PREDICTION JOIN requires the mining model to be mapped to the source through a join mapping list in the ON clause. A NATURAL PREDICTION JOIN could also be used if the column names from the model match the column names in the source query.

    Although a CROSS JOIN is valid SQL syntax, it would not return the correct results for this requirement.
  153. You are evaluating the reporting requirement for a new Business Intelligence project.

    The requirements list several reports to be created.

    Which of the following reports would be a candidate for report caching?

    1. A report that prompts for user credentials

    2. A summary report for which the data changes infrequently 

    3. A report that uses Windows authentication

    4. A report based on user-dependent data
    2. A summary report for which the data changes infrequently <Correct>

    Explanation:

    A report that is derived from infrequently changing data is a good candidate for report caching.

    Caching does not support Windows authentication as an authentication method.

    Data that would change based on the user requesting the report and a report that prompts for credentials would not be the same for different users; they are not candidates for report caching.
  154. You must select a data notification option for a partition that is configured for proactive caching. The cube loads data from an Oracle database. The data notification strategy should require minimum implementation effort.

    Which data notification strategy should you select?

    1. You cannot use proactive caching with non-SQL Server data sources.

    2. Scheduled Polling 

    3. SQL Server

    4. Client Initiated
    2. Scheduled Polling <Correct>

    Explanation:

    You can use the Scheduled Polling option with a polling query with non-SQL Server data sources. SQL Server notifications are available with SQL Server data sources only. Client-initiated notifications require additional programming effort to send the NotifyTableChange XMLA command when the data is changed.

  155. Which configuration property must be added to the Report Manager Web.config file to configure it for an Internet-facing site when the Report Manager and report server are installed on the same server?

    1. Remove the <defaultProxy> element.

    2. <defaultProxy><proxyusesystemdefault = "false"proxyaddress = "http://proxyserver:80"bypassonlocal = "false"/><bypasslist>

    3. <defaultProxy enabled="false" />

    4. <defaultProxy enabled="true" />
    • 3. <defaultProxy enabled="false" />
    • <Correct>

    Explanation:

    If you are using Report Manager as a front-end application for Internet deployment and the report server is also installed on the same computer, you must specify a Web.config setting that allows Report Manager to bypass the proxy server when sending requests to a local report server that is installed on the same computer.

    The Web.config setting is the System.NET defaultProxy network setting. By default, defaultProxy is disabled in the Web.config file for Report Manager. This is the recommended configuration when Report Manager and the report server are deployed together on the same computer.

    If you upgraded from an earlier version of Reporting Services, the Report Manager Web.config file does not include the default Proxy configuration setting. You can add and set the defaultProxy setting to bypass the proxy server for installations in which Report Manager and the report server are running on the same computer.

    Enabling the defaultProxy is not recommended when Report Manager and Report Server are installed on the same computer.

    • Including a proxy element in the defaultProxy element sets the proxy settings to the server settings specified in the proxy element.
    • This is not recommended because it enables the default proxy.

    The default proxy is enabled by default. Removing the defaultProxy element prevents a way to disable the setting.
  156. You are processing a partition in BIDS. You want BIDS to ignore errors caused by missing dimension keys. You don't have rights to change the object source files, but you do have rights to process objects.

    How could you instruct the server to ignore Key Not Found errors during the partition processing?

    1. Don't do anything; the default error configuration ignores Key Not Found processing errors.

    2. Change the Partition Error configuration.

    3. Click the Change Settings button in the Process dialog box. 

    4. Change the Dimension Key Errors configuration.
    3. Click the Change Settings button in the Process dialog box. <Correct>

    Explanation:

    The Process dialog box enables you to change the processing options, including how the server reacts to dimension key errors(Dimension Key Errors tab). These settings overwrite the error configuration settings at the object level. The default error configuration uses the Report and Continue option for Key Not Found errors instead of ignoring them.

    The Partition Error configuration and Dimension Key Errors configuration properties define default error handling during processing. The default value for the Key Not Found error condition is Report and Continue. If you want to use a different setting, you must explicitly set the Key Not Found errors by clicking Change Settings in the Process dialog box.
  157. You have installed a second instance of SQL Server Reporting Services (SSRS) on the same server. Because the first instance was installed with the default configuration, the new instance is installed but not configured. After the install, you need to configure the new instance of SSRS, which involves creating the virtual directories for Report Manager and the Reporting Services Web service to distinguish the new instance from the first instance.

    What method can you use to create the virtual directories?

    1. Open IIS and create the new virtual directories on the default Web site for both the Report Server Virtual Directory and the Report Manager Virtual Directory.

    2. Use the Reporting Services Configuration Manager to create the virtual directories for both the Report Server Virtual Directory and the Report Manager Virtual Directory. 

    3. Connect to the SSRS instance through SQL Server Management Studio (SSMS) and right-click the server to create the virtual directories.

    4. Use the RSConfig command-line tool.
    2. Use the Reporting Services Configuration Manager to create the virtual directories for both the Report Server Virtual Directory and the Report Manager Virtual Directory. <Correct>

    Explanation:

    You must create the virtual directories through the Reporting Services Configuration Manager tool, which can create custom named directories with the necessary configuration and code.

    Creating the virtual directories in IIS Manager will not add the necessary configuration elements.

    RSConfig encrypts and stores connection and account values in the RSReportServer.config file.

    SSMS does not provide the ability to create the virtual directories
  158. One of the Reporting Services applications in your enterprise has become mission critical and you are now adding multiple Reporting Services servers in a scale-out deployment and using a Network Load Balancing (NLB) cluster.

    Which step is NOT part of a reporting services installation on an NLB?

    1. Verify that the servers are accessible through the host name you specified.

    2. Install Reporting Services in files-only mode on nodes that are already part of an NLB cluster and configure the report server instances for scale-out deployment.

    3. Configure HostName and UrlRoot to use the virtual server IP of the NLB cluster.

    4. Configure view state validation.

    5. Install Reporting Services in default mode on nodes that are already part of an NLB cluster and configure the report server instances for scale-out deployment.
    • 5. Install Reporting Services in default mode on nodes that are already part of an NLB cluster and configure the report server instances for scale-out deployment.
    • <Correct>

    Explanation:

    The Reporting Services installation cannot be installed using the default mode.

    The Reporting Services installation must be installed in files-only mode. Report Server must be configured manually.

    View state validation must be configured in a load-balanced scenario to prevent failed view state validation errors.

    HostName and URLRoot must be assigned a virtual server that provides a single access point.

    Ensure that users can access reports by verifying that the reports are available through the virtual server's host name.
  159. You are defining a list of the security groups needed for your Reporting Services environment.

    Which role is required to enable a user to grant access to report objects?

    1. Publisher

    2. System Administrator

    3. System Users

    4. Report Builder

    5. Browser

    6. Content Manager
    6. Content Manager <Correct>

    Explanation:

    The Content Manager role is required to authorize object access. The local Administrator's group on the report server computer has this authorization. You can delegate that responsibility by assigning other users to the Content Manager role.

    The System Administrator role is required for a report server administrator who has overall responsibility for a report server but not necessarily for the content within it.

    The Browser role controls who can view reports, but it does not necessarily write or manage them.

    The Publisher role is required for users who add content to a report server.

    The Report Builder role is required for users who create and edit reports in Report Builder.
  160. You are designing a SQL Server Integration Services (SSIS) package. You have created a Script task that modifies the value of a taskStatus variable.

    What do you need to do in the Script task to make sure that you can edit the value of taskStatus?

    1. Replace the Script task with a SQL task.

    2. In the Script task, add the Dts.Variables("taskStatus").Write = True line of code.

    3. Add taskStatus to the ReadOnlyVariables property in the Script Task editor.

    4. Add taskStatus to the ReadWriteVariables property in the Script Task editor.
    4. Add taskStatus to the ReadWriteVariables property in the Script Task editor. <Correct>

    Explanation:

    For a variable to be edited in a Script task, the variable must be added to the ReadWriteVariables property.

    The ReadOnlyVariables property can be used for access variable values, but for the variables in the ReadOnlyVariables list, the variable value cannot be updated.
  161. You have a FactResellerSales measure group that has OrderDateKey, ShipDateKey, and DueDateKey columns that reference the DimDate dimension table. The users need to browse the cube by Order Date, Ship Date, and Due Date dimensions. Each date dimension should use the same regular and fiscal calendar hierarchies defined to let the users browse data by regular or fiscal years, respectively.

    How could you implement this requirement? (Each correct answer presents part of the solution. Choose two.)

    1. Add the Date dimension to the cube three times.

    2. Add the Order Date, Ship Date, and Due Date dimensions to the cube.

    3. Create three SSAS date dimensions, Order Date, Ship Date, and Due Date.

    4. Create one SSAS date dimension.
    1. Add the Date dimension to the cube three times. <Correct>

    4. Create one SSAS date dimension. <Correct>

    Explanation:

    The recommended way to implement this scenario is to use role-playing dimensions. With this approach, you define one Date dimension in SSAS and add this dimension to the cube as many times as needed. This simplifies the dimension maintenance because all role-playing dimensions share the same dimension definition.
  162. You have a shared data source that is connecting to Analysis Services.

    Which authentication method is supported by the Analysis Services provider?

    1. Basic authentication

    2. Windows authentication 

    3. Forms authentication

    4. Anonymous authentication
    2. Windows authentication <Correct>

    Explanation:

    Reporting Services supports all the provided authentication methods; however, the Analysis Services data provider supports only Windows authentication.
  163. There are many command line utilities included in SSRS.

    Which utility can be used to deploy reports and administer a server through the use of scripts?

    1. Rsconfig utility

    2. Rs utility 

    3. Rskeymgmt utility

    4. SQLCMD utility
    2. Rs utility <Correct>

    Explanation:

    The rs utility is used to automate deployment and administration tasks through script files. You can define scripts to set system properties, publish reports, and so on. The script must be written in Visual Basic .NET.

    The rsconfig utility is used to configure and manage a report server connection to the report server database and specify a user account to use for unattended report processing.

    The rskeymgmt utility is an encryption key management tool. It is used to back up, apply, re-create, and delete symmetric keys.

    The SQLCMD utility is a query execution tool.
  164. The security model for Reporting Services includes a list of Tasks that can be performed in Reporting Services and a set of roles that have a list of tasks pre-defined for access.

    Which statement about a Reporting Services server's role-based model is NOT true?

    1. Task permissions cannot be changed.

    2. Tasks can be created and modified. 

    3. Operations cannot be modified without writing custom security extensions.

    4. Roles can be created and modified.
    2. Tasks can be created and modified. <Correct>

    Explanation:

    Tasks cannot be created or modified.

    Roles comprise a collection of tasks and can be created and modified.

    Operations are the most granular permission and cannot be modified without writing a custom security extension.

    Tasks comprise a set of predefined permissions, which cannot be changed.
  165. You are designing a SQL Server Integration Services (SSIS) package. In your package, you have an OLE DB connection that is logging into SQL Server, using SQL Server authentication. Using BIDS, you add an XML configuration to update the connection string of the OLE DB connection.

    You copy the package and the XML file to the Production server. When you attempt to run the package, you receive an error when attempting to acquire a connection.

    What should you do to fix the connection error?

    1. Update the package properties and set ProtectionLevel to EncryptSensitiveWithUserKey.

    2. Update the package properties and set ProtectionLevel to EncryptSensitiveWithPassword.

    3. Edit the XML configuration on the production server and add a user name and password. 

    4. Edit the XML configuration on the production server and set ProtectionLevel to EncryptSensitiveWithUserKey.
    3. Edit the XML configuration on the production server and add a user name and password. <Correct>

    Explanation:

    When an XML configuration is created, sensitive data is removed from the XML configuration file. This information must be added back to the configuration file.

    The protection level of the package does not matter in this case because the connection value is being overwritten with the XML configuration
  166. You are developing a SQL Server Analysis Services (SSAS) cube. You are attempting to connect to a cube by using OLE DB.

    When viewing the cube, you would like also to view extended properties such as format and color.

    What must you add to the connection string to view the extended properties?

    1. Extended Properties="true"

    2. Format="true"

    3. Extended Properties="ReturnCellProperties=true" 

    4. Format="ReturnCellProperties=true"
    3. Extended Properties="ReturnCellProperties=true" <Correct>

    Explanation:

    The extended properties are returned by setting the Extended Properties property. The ReturnCellProperties property returns the extended properties with the cube. The default is set to False.

    Format determines the format in which the result set is returned but does not relate to returning the cell's extended properties
  167. You have a financial cube that has Customer and Account dimensions. The cube has a measure group that has only non-additive measures such as rates and percentages. The lowest grain of the measure group is the Account dimension. The non-additive measures cannot be aggregated at a level higher than the individual customer account. The Account dimension joins the measure group with a regular relationship.

    Which relationship type must you use for the Customer dimension if you want to prevent the end users from browsing the non-additive measures by the Customer dimension?

    1. Reference

    2. Regular

    3. No Relationship 

    4. Fact
    3. No Relationship <Correct>

    Explanation:

    When a dimension is not joined to a measure group, the end users cannot browse the measure group by that dimension. To dissociate a dimension from a measure group, choose No Relationship as a Relationship type on the Dimension Usage tab.
  168. You are designing a SQL Server Integration Services (SSIS) package. You add a Lookup task to the package. The SQL query the Lookup task is running might return no rows. You do not want the package to fail if the lookup returns 0 records. If 0 records are returned, you would like the data flow to continue as designed, with NULL for the columns returned by the lookup.

    How would you configure this?

    1. In the Configure Error Output dialog box, set the Error drop-down list value to Ignore Failure.

    2. Add an OLE DB Destination task. Link the error output of Lookup to the OLE DB destination. In the Configure Error Outputdialog box, set the Error drop-down list value to Redirect Row.

    3. In the Configure Error Output dialog box, set the Truncate drop-down list value to Ignore Failure.

    4. Do nothing. A Lookup task returning 0 records does not return an error.
    1. In the Configure Error Output dialog box, set the Error drop-down list value to Ignore Failure. <Correct>

    Explanation:

    If the task is left as is, it will fail. Selecting Ignore Failure for Error Output ensures that SSIS will ignore any error in the task.

    You would like to ignore the failure of the task; you do not want to ignore the Truncation event.

    Adding an OLE DB Destination task causes all the rows to be redirected to the OLE DB destination. The data flow would not continue.
  169. You are designing a SQL Server Integration Services (SSIS) package. You have a created a master package to call several child packages. All the packages are saved in the same folder location. You have developed the package on your local computer,pointing to the package path for your computer. You would like to make the child package paths dynamic so that they can be configured using variables.

    How would you configure File Connections for the package with the minimal amount of effort? (Each correct answer presents part of the solution. Choose two.)

    1. Add an expression to each file connection. Set the ConnectionString property to the @[User::PackagePath] + [Package Name]expression. 

    2. Create a variable named PackagePath. Set the value of PackagePath to the package folder path on your computer.

    3. Create a variable for each child package called by the master package.

    4. Add an expression to each file connection. Set the ConnectionString property to an expression, using the package variable name.

    5. Add an expression to each file connection. Set ServerName to @[User::PackagePath].
    1. Add an expression to each file connection. Set the ConnectionString property to the @[User::PackagePath] + [Package Name]expression. <Correct>

    2. Create a variable named PackagePath. Set the value of PackagePath to the package folder path on your computer.<Correct>

    Explanation:

    An expression can be used to update the Connection String property of a file connection. A variable can be used to set the folder path for each of the packages. You must use both the PackagePath variable and the Package Name attribute to move the given file connection dynamically to a given path.

    The ServerName property is not relevant for a file connection.
  170. You are creating a report that requires users to be able to create a subscription.

    What type of data source credentials must be used to enable users to subscribe to the report? (Each correct answer presents a complete solution. Choose two.)

    1. Stored credentials 

    2. No credentials 

    3. Windows Authentication (integrated security)

    4. Prompted credentials
    1. Stored credentials <Correct>

    2. No credentials <Correct>

    Explanation:

    Report subscriptions require stored credentials or no credentials if the database does not require authentication. The subscription will run as a background process. The Report Server agent must retrieve the credentials from the report database.

    Prompted credentials cannot be used because the report execution is not an interactive process. Windows Authentication is not applicable because there is no user context at the time of report execution.
  171. You can use a document map in a report to provide users with a way to navigate to certain areas of the report.

    Which rendering extensions support the document map? (Each correct answer presents part of the solution. Choose three.)

    1. PDF 

    2. Word 

    3. HTML 

    4. XML
    1. PDF <Correct>

    2. Word <Correct>

    3. HTML <Correct>

    Explanation:

    The document map is intended for use in the HTML rendering extension.

    The PDF rendering extension creates a Bookmarks pane based on the document map.

    The Word rendering extension creates a table of contents based on the document map.

    The XML rendering extension does not support the document map.
  172. You are managing a SQL Server Integration Services (SSIS) package.

    On the Production server, you are required to use the command line to copy or move packages. You wish to copy a package, PackageA.dtsx, from the C:SSIS folder to the D:SSIS folder.

    Which command will you run?

    1. Dtutil /FILE C:SSIS PackageA.dtsx /C FILE; D:SSISPackageA.dtsx 

    2. DTExec / FILE C:SSIS PackageA.dtsx /C FILE; D:SSISPackageA.dtsx

    3. DTExec /COPY C:SSIS PackageA.dtsx; D:SSISPackageA.dtsx

    4. Dtutil /COPY C:SSIS PackageA.dtsx; D:SSISPackageA.dtsx
    1. Dtutil /FILE C:SSIS PackageA.dtsx /C FILE; D:SSISPackageA.dtsx <Correct>

    Explanation:

    Use the DTUtil command to copy, move, or delete packages in the file system or that are deployed to the SQL Server msdb database or the Package Store.

    When running DTUtil, specify the storage location of the package, a Microsoft SQL Server database, the SSIS Package Store, and the file system. The storage type of the package is identified by the /SQL, /FILE, and /DTS parameters. Use the copy command to copy a package. The syntax is/C[opy] storeageLocation;destinationPathandPackageName.

    You can use the DTExec command-line utility to execute packages, but it does not have any functionality related to copying, moving, or deleting packages.
  173. You have a Product dimension with several attributes. One of the attributes, Product Size, is the measurement of the units of weight or length based on the product type. The values for this attribute are highly unique, but there are still some reasons to include it as an attribute hierarchy. However, you want to limit the processing and query overhead for this attribute so that other more relevant attributes will be optimized.

    Which attribute property must you change to limit the overhead of the Product Size attribute?

    1. Change the AttributeHierarchyOptimizedState property to NotOptimized. 

    2. Change the AttributeHierarchyEnabled property to False.

    3. Set the GroupingBehavior property to False.

    4. Set the IsAggregatable property to False.
    1. Change the AttributeHierarchyOptimizedState property to NotOptimized. <Correct>

    Explanation:

    Setting the AttributeHierarchyOptimizedState property to NotOptimized (from FullyOptimized) will prevent an index from being created. The AttributeHierarchyOptimizedState property affects whether an index is created for the attribute to improve query performance. For attributes that have a high cardinality, this adds to the overhead of the dimension and attribute with little performance improvements.

    The IsAggregatable property determines whether the attribute hierarchy can have an All level and is not related to performance.

    The AttributeHierarchyEnabled property must remain True for the Product Size attribute to retain browsing capability as an attribute hierarchy. The GroupingBehavior relates to how a client tool will display the attribute but is unrelated to the storage.
  174. You are designing a SQL Server Integration Services (SSIS) package. You currently have two variables, FileName and FilePath.You need to update the FullFileName variable by combining the FileName and FilePath variables.

    How would you accomplish this?

    1. Create a SQL task. Set the SQLCommand property to @FullFileName = @FileName & @FilePath.

    2. Create a SQL task. Set the SQLCommand property to Set @[user:FullFileName] = @[user:FileName] & @[user:FilePath].

    3. Create a Script task. Inside the task, set Dts.Variables("FullFileName").Value = Dts.Variables("FilePath").Value & Dts.Variables("FileName").Value. 

    4. Create a Script component inside a data flow. Inside the component, set Dts.Variables("FullFileName").Value = Dts.Variables("FilePath").Value & Dts.Variables("FileName").Value.
    3. Create a Script task. Inside the task, set Dts.Variables("FullFileName").Value = Dts.Variables("FilePath").Value & Dts.Variables("FileName").Value. <Correct>

    Explanation:

    The Script task on the Control Flow design surface can be used to set the value of a variable.

    You do not need to go inside a data flow to change the values of a variable by using a Script component.

    The SQL task can update variables based on SQL logic, but the task is not used to update variables based on the value of other variables.
  175. You are troubleshooting a report that contains embedded custom code and you need to review the code and check for errors.

    Which tools can be used to debug the custom code?

    1. Visual Studio 2008

    2. Report Builder

    3. SQL Server Management Studio

    4. BIDS Report Designer
    1. Visual Studio 2008 <Correct>

    Explanation:

    A custom assembly must be debugged in Visual Studio 2008. BIDS Report Designer, Report Builder, and SQL Server Management Studio do not provide the capability to debug custom code.
  176. You are developing a moderately large-sized SSAS solution, with the underlying database containing approximately a billion rows of data (representing four years of data) in one of the main fact tables. You use the Cube wizard to create an initial cube and dimensions. You create some aggregations and deploy the solution to your development server and process it, which takes several hours. All your initial queries take a couple of minutes to run, which is longer than you expect. From your discussion with the users, you expect them to target queries based on fiscal weeks and fiscal months but rarely to look at data across all four years.

    Which is the first priority to improve query performance?

    1. Create a summary cube that rolls up the data to the fiscal week level.

    2. Add more memory to the server to increase the SSAS cache.

    3. Create partitions at the fiscal week level so that queries are not scanning all data for every query. 

    4. Include the Fiscal Week and Fiscal Month attribute in every aggregation.
    3. Create partitions at the fiscal week level so that queries are not scanning all data for every query. <Correct>

    Explanation:

    With the Enterprise edition of SQL Server 2008 Analysis Services, partitions are the first step in improving query performance for medium to large solutions. In this situation, creating a couple hundred partitions at the fiscal week level would improve query performance tremendously because the amount of data and aggregations necessary in the query would be a small percentage of the overall data volumes.

    Although aggregations and memory would help, their initial impact would be limited if a partitioning strategy is not implemented first.
  177. You have a SQL Server Analysis Service (SSAS) database that contains several dimensions and two cubes. You must process the database periodically to bring it up to date with the data changes in the data source. You want to process all database objects with minimum effort.

    Which database processing option would you use?

    1. Process Full 

    2. Process Default

    3. Unprocess

    4. Process Update
    1. Process Full <Correct>

    Explanation:

    The easiest way to process a database is to use the Process Full option.

    Process Default doesn't detect data changes; it processes only partitions that have not been processed.

    The Unprocess option deletes the object data.

    The Process Update option doesn't apply to measure groups or partitions.
  178. You need to load test a SQL Server Analysis Services (SSAS) server as part of a capacity planning effort. You use Visual Studio 2008 Test Edition to prepare test scripts. You need to monitor the server usage, such as server CPU and memory usage.

    Which tool would you use to capture this activity?

    1. Windows Performance Monitor 

    2. Flight recorder

    3. Windows Event Log

    4. SQL Server Logs
    1. Windows Performance Monitor <Correct>

    Explanation:

    For performance testing, you would typically use the Windows Performance Monitor to track the SSAS performance counters. The rest of the tools mentioned don't record the server usage; they just record the activity at the server level (Windows Event Log), the database and SQL instance level (SQL Server Logs), and the Analysis Services activity (flight recorder).
  179. You have developed an advanced report that uses custom code to reference an external assembly.

    Choose the correct methods of adding a reference to custom code for use in a report. (Each correct answer presents part of the solution. Choose three.)

    1. Embedded code is the only option by which to add custom code to a report.

    2. Add the assembly to the References tab of the Report Properties dialog box in the SSRS project.

    3. Add CodeModule and Class elements to the CodeModules and Classes elements in the RDL file, respectively.

    4. Add the Class Name and Instance Name to the References tab of the Report Properties dialog box in the SSRS project.
    2. Add the assembly to the References tab of the Report Properties dialog box in the SSRS project. <Correct>

    3. Add CodeModule and Class elements to the CodeModules and Classes elements in the RDL file, respectively.<Correct>

    4. Add the Class Name and Instance Name to the References tab of the Report Properties dialog box in the SSRS project.<Correct>

    Explanation:

    In BIDS, you add external references to assemblies in the Report Properties dialog box on the References tab. Select the assembly to be added or type the class and instance name to reference the code. The .rdl file can be edited directly to include an assembly reference. Use the CodeModules and Classes elements to add the reference.

    Embedded code is not the only option by which you can add custom code. The supported options are embedded code and external references.
  180. You are developing a SQL Server Analysis Services (SSAS) cube. You have created a Date dimension and Sales measure group. The Sales table contains an OrderDateKey column. The Primary key of the Date dimension is DateKey. You would like to join the Date dimension to the Sales measure group.

    Which steps would you perform to accomplish this?

    1. On the Dimension Usage tab in Cube Designer, define a relationship between the Date dimension and the Sales measure group. Set the Relationship type to Regular. Set the Granularity attribute to Date. Under Relationship, set the Dimension columns to DateKey and the measure group columns to OrderDateKey. 

    2. On the Dimension Usage tab of Cube Designer, define a relationship between the Date dimension and the Sales measure group. Set the Relationship type to Many-To-Many. Set the Granularity attribute to Date. Under Relationship, set the Dimension columns to DateKey and the measure group columns to OrderDateKey.

    3. On the Dimension Usage tab of Cube Designer, define a relationship between the Date dimension and the Sales measure group. Set the Relationship type to Fact. Set the Granularity attribute to Date. Under Relationship, set the Dimension columns to DateKey and the measure group columns to OrderDateKey.

    4. On the Dimension Usage tab of Cube Designer, define a relationship between the Date dimension and the Sales measure group. Set the Relationship type to Fact. Set the Granularity attribute to Date. Under Relationship, set the Dimension columns to DateKey and the measure group columns to DateKey.
    1. On the Dimension Usage tab in Cube Designer, define a relationship between the Date dimension and the Sales measure group. Set the Relationship type to Regular. Set the Granularity attribute to Date. Under Relationship, set the Dimension columns to DateKey and the measure group columns to OrderDateKey. <Correct>

    Explanation:

    The Regular dimension relationship is used for a regular dimension-to-fact-table join. The Dimension table's key is joined to the foreign key in the fact table. Therefore, after you add the dimension to the cube, you must define the relationship on the Dimension Usage tab as regular and join across the DateKey to the OrderDateKey with the Date attribute as the granularity attribute.

    The Fact relationship creates a dimension from a fact table.

    Use the many-to-many relationship when creating a many-to-many join. An intermediate measure must be created before you can use the many-to-many relationship. Therefore, the other choices would incorrectly set the relationship type.
  181. Your cube has a Geography dimension that contains a Country attribute. The Country attribute contains all countries in the world.
    You need to create a role that can see all countries except France.

    What's the easiest way to configure the Country dimension data security to meet this requirement?

    1. Create a denied set that has France in the list of denied members. 

    2. Use an MDX expression that filters the members of the Country attribute.

    3. Enable Visual Totals.

    4. Create an allowed set that has all the countries except France in the allowed members.
    1. Create a denied set that has France in the list of denied members. <Correct>

    Explanation:

    Because you need the role to see all countries except France, the easiest way is to deny only France by setting up a denied set.

    If you define an allowed set, the set must list all countries except France. This is more involved to set up and might present maintenance challenges as new members are added to the Country attribute.

    The Visual Totals property controls whether the denied members contribute to the aggregated totals.You can use an MDX expression to filter the Country members, but this approach is more involved, too.
  182. You have a new report that uses a list box data region and contains a matrix data region embedded within the list box.

    How do the data in the list box and the matrix relate?

    1. Each data region can use the same or different data source/dataset.

    2. Each data region must use the same data source/dataset. 

    3. Each data region must use a different data source/dataset.

    4. Each data region must be based on a shared data source/dataset.
    2. Each data region must use the same data source/dataset. <Correct>

    Explanation:

    Nested data regions must use the same data source/dataset. By nesting the matrix inside a list, the matrix can show only a different view of the same data.

    A nested data region cannot use a different data source. The nested data region is constrained by its parent data region. A data region can be associated with a shared or embedded data source.
  183. You have added a Lookup Transformation. Some rows might not be found during the lookup. You would like these rows to be sent to a text file.

    How would you configure this?

    1. Configure Error Output and set the Error drop-down list value to Fail Component. Enable logging for the package. Configure the error log to capture OnError events.

    2. Configure Error Output and set the Error drop-down list value to Redirect Row. Direct Error Output to a Flat File destination.

    3. Connect the green data path from the Lookup transformation to Flat File Destination. When prompted to choose an output, select the Lookup No Match Output option. 

    4. Connect the green data path from the Lookup transformation to the Flat File destination. When prompted to choose an output, select the Lookup Match Output option.
    3. Connect the green data path from the Lookup transformation to Flat File Destination. When prompted to choose an output, select the Lookup No Match Output option. <Correct>

    Explanation:

    In SQL Server 2008 Integration Services, the Lookup transformation can send unmatched rows through the data path output, using Lookup No Match Output.

    Setting Error Output to Redirect Row sends other errors out the red output path; this option does not send the unmatched rows to a text file. This feature is changed in SQL Server 2008 Integration Services from how it functioned in SQL Server 2005 Integration Services.
  184. You have a cube that loads data from a database. The data in the database is updated throughout the day and at different intervals. The cube needs to update its data as soon as the data changes in the data source.

    You must select a standard storage mode that provides a low data latency and the best query performance.

    Which storage mode should you select?

    1. Scheduled MOLAP

    2. Automatic MOLAP 

    3. Scheduled ROLAP

    4. Realtime ROLAP
    2. Automatic MOLAP <Correct>

    Explanation:

    • When proactive caching is enabled, the server automatically updates the data when it receives data-change notifications.
    • Automatic MOLAP enables proactive caching and gives you the best query performance because it stores data efficiently in multidimensional stores.
  185. If you create a mining structure from a relational source, which objects must you create in your Analysis Services projects first?(Each correct answer presents part of the solution. Choose two.)

    1. Role

    2. Data Source 

    3. Data Source View 

    4. UDM Cube
    2. Data Source <Correct>

    3. Data Source View <Correct>

    Explanation:

    You create a relational mining structure from a data source view (DSV). A DSV requires a data source.

    The data mining features in SSAS do not require a cube to build mining structures. Cubes can be used, but so can relational tables and views. A Role relates to the security of the cube or mining structure but is not a prerequisite for creating mining structures.
  186. You have created an application that uses the WinForms ReportViewer control in local processing mode.

    Which export formats are supported in local processing mode? (Each correct answer presents part of the solution. Choose two.)

    1. Word

    2. PDF 

    3. XML

    4. Excel
    2. PDF <Correct>

    4. Excel <Correct>

    Explanation:

    Local processing mode supports only a subset of the export formats available in Reporting Services. The supported formats are Excel and PDF.

    Word and XML formats are not supported in local processing mode. Remote processing mode would have to be used to enable these formats.
  187. You are developing a SQL Server Analysis Services (SSAS) cube. Because you are creating a date dimension, you want to ensure that the hierarchies are optimized for query performance. Your Calendar and Fiscal hierarchies both display a warning of decreased query performance.

    What needs to be done in the dimension to optimize the hierarchies?

    1. On the Attribute Relationships tab, make sure each attribute relates to the key attribute directly.

    2. For each attribute in a hierarchy, change the AttributeHierarchyOptimizedState to FullyOptimized.

    3. On the Attribute Relationships tab, create attribute relationships that are not connected to the key attribute.

    4. On the Attribute Relationships tab, create relationships from the lower to the higher levels in the hierarchies.
    4. On the Attribute Relationships tab, create relationships from the lower to the higher levels in the hierarchies.<Correct>

    Explanation:

    Attribute relationships define natural hierarchies based on how the members of the attributes relate to one another. For example,July 2008 relates to Q1 2008, which relates to 2008 in a natural hierarchy.

    When these attribute relationships are created, the query engine can use related aggregations. For example, if a user queries 2008 sales for a store, but an aggregation doesn't exist at the year level or the store, the engine can check for aggregations at a quarter level. If attribute relationships did not exist, the query engine would have to go to the lowest level, day in this example, and aggregate all the values. Therefore, attribute relationships should be created from the key attribute all the way up the hierarchy to the top level. Having attribute relationships defined only at the attribute key will degrade performance.

    The AttributeHierarchyOptimizedState relates to the indexing applied to the individual attributes but does not help the performance of the hierarchy overall.
  188. Within a Reporting Services report, a dataset contains information related to the data used in the report.

    Which of the following items are stored in the dataset definition? (Each correct answer presents part of the solution. Choose three.)

    1. Report name

    2. Data source reference 

    3. Report Server name

    4. Query details 

    5. Collection of fields
    2. Data source reference <Correct>

    4. Query details <Correct>

    5. Collection of fields <Correct>

    Explanation:

    A report dataset contains the query, a reference to the data source used, and a list of the fields used in the report. Additional options such as character sensitivity and collation information are also stored in the definition.

    The report name and Report Server name are not included in the dataset definition.
  189. You have a measure group that has several partitions. One of the partitions contains data for the last three months. The end users query this partition frequently. You need to select a storage mode that gives the best query performance.

    Which storage mode should you select?

    1. Relational OLAP (ROLAP)

    2. Hybrid OLAP (HOLAP)

    3. Let the server decide

    4. Multidimensional OLAP (MOLAP)
    4. Multidimensional OLAP (MOLAP) <Correct>

    Explanation:

    The MOLAP storage mode gives the best query performance because the data is stored efficiently in multidimensional file stores.

    The ROLAP setting would not store any data within SSAS, neither aggregates nor granular data, and all MDX queries would be sent to the underlying database as SQL. This has performance overhead.

    HOLAP is a hybrid approach by which the aggregates are stored in SSAS, but the granular data is kept in the database. Although this reduces the storage requirement, it also has performance impact when the aggregates do not contain values for a specific query and the underlying database would then need to be queried.

    "Let the server decide" is a property used when defining the parallelization during object processing.
  190. You are designing a SQL Server Integration Services (SSIS) package. You have two Execute SQL tasks contained in a sequence container. TransactionOption for the sequence container is set to Supported. When the package runs, one Execute SQL task fails and the other Execute SQL task succeeds.

    What is the outcome of the package?

    1. The package fails. The data in the successful task remains. 

    2. The package succeeds. The data in the successful task remains.

    3. The package succeeds. The data in both tasks rolls back.

    4. Both of the Execute SQL tasks roll back.
    1. The package fails. The data in the successful task remains. <Correct>

    Explanation:

    The TransactionOption setting of Supported indicates that the container does not start a transaction but inherits a transaction if there is a transaction in a parent container. If the TransactionOption setting is set to Required, both tasks need to succeed for the sequence container to succeed and not roll back.

    Because one of the tasks failed, the entire package fails.
  191. You have a complex cube that contains many dimensions and measure groups. You need to configure the cube so that specific groups can see only the data relevant to their group. For example, the HR department would be allowed to see only HR-related objects.

    How would you implement logical subviews of the cube?

    1. Create a separate cube for each logical view.

    2. Create a cube perspective for each logical view. 

    3. Create a separate data source view for each logical view.

    4. Let the client application filter the cube metadata.
    2. Create a cube perspective for each logical view. <Correct>

    Explanation:

    Cube perspectives enable you to define logical subviews of a larger cube. A perspective is not a data filter, and roles cannot be assigned to perspectives; however, you can use perspectives to limit the list of attributes and measures to simplify querying for a user.

    A new cube would add storage, processing, and management overhead.

    Data source views are required for SSAS but are not visible to the end user and do not filter out the objects for a user.
  192. You are designing several SQL Server Integration Services (SSIS) packages.

    You need to create a deployment utility to move the packages to the production server.

    Which step must you perform to create the deployment utility?

    1. Add the SSIS Import and Export Wizard to the SSIS project.

    2. Open the package properties and set the UpdateObjects property to True. Build the SSIS project.

    3. Select Create Deployment Utility from the Build menu of the SSIS project.

    4. Open the SSIS project properties and set CreateDeploymentUtility to True. Build the SSIS project.
    4. Open the SSIS project properties and set CreateDeploymentUtility to True. Build the SSIS project. <Correct>

    Explanation:

    If the SSIS project property CreateDeploymentUtility is set to True, when the project is built a deployment utility will be built under the project path binDeployment directory. The deployment utility includes the project packages, dependent files (XMLConfiguration Files), and an SSIS Manifest file. After the deployment utility is deployed, the manifest file can be executed, and the package installation wizard will execute.

    The UpdateObjects property for the package does not relate to deployment and is used to determine whether a new version of acomponent should be used. The SSIS Import and Export Wizard will generate a new package, but that does not relate to package deployment
  193. You are designing a SQL Server Integration Services (SSIS) package. You have implemented logging in a package, added an SSIS log provider for SQL Server, and set the configuration of the log provider to point to the local database.

    Which table will the SSIS log provider for SQL Server write to?

    1. dbo.sysssislog 

    2. dbo.sysmaintplan_log

    3. This is defined in the SQL Server connection.

    4. dbo.[SSIS Configuration]
    1. dbo.sysssislog <Correct>

    Explanation:

    The SQL Server log provider will write to the dbo.sysssislog table in the database where the log provider is specified.

    The dbo.[SSIS Configuration] table is the default name for a SQL Server configuration but does not contain log information.

    The dbo.sysmintplan_log is reserved for logging of maintenance plan activity and is stored in the msdb database.
  194. You are creating a report that has a report parameter. You want to assign the default value for a parameter dynamically without hard coding an entry or requiring the user to enter a value.

    How can you achieve this?

    1. Sort the data source by value and assign the lowest value to the All element.

    2. Expand the Parameters folder in the Report Data window. Right-click the parameter and select Parameter Properties. In the Default Values section, select Specify Values and assign the All value to the parameter.

    3. Add a new data source; expand the Parameters folder in the Report Data window. Right-click the parameter and select Parameter Properties. In the Default Values section, select Get Values from a Query and bind the new dataset to the default value.

    4. Expand the Parameters folder in the Report Data window. Right-click the parameter and select Parameter Properties. In the Default Values section, select Specify Values and type in the value.
    3. Add a new data source; expand the Parameters folder in the Report Data window. Right-click the parameter and select Parameter Properties. In the Default Values section, select Get Values from a Query and bind the new dataset to the default value.<Correct>

    Explanation:

    Using the Get Values From a Query section of the parameter properties window, you can change the value dynamically based on the result of a query. You must define the new data source to provide the value and then configure the default value to use the new data set.

    The lowest value of a data source cannot be assigned as a default value.

    Assigning the default value to <All> or a specific value using the Specify Values option defines a static value that cannot be changed without modifying the report parameters.

    Non-queried parameters are not dynamic parameters. The value is stored in the report definition.
  195. You are managing a SQL Server Analysis Services (SSAS) cube. You need to configure the security in the Sales cube to deny a database role access to the Salary measure.

    What Multidimensional Expressions (MDX) expression would you use to accomplish this?

    1. Measures.CurrentMember <> Measures.Salary

    2. Measures.CurrentMember IS Measures.Salary

    3. Object.CurrentMember <> Measures.Salary

    4. Not Measures.CurrentMember IS Measures.Salary
    4. Not Measures.CurrentMember IS Measures.Salary <Correct>

    Explanation:

    The NOT expression, along with the IS comparison, denies a role access to a measure. The CurrentMember function checks the measures being requested in the query and evaluates whether the measure can be shown.

    Because a member is being checked against another member, the <> or != symbols will not evaluate whether the members are equivalent.
  196. You have just set up a new development server for Reporting Services. A set of developers are developing reports on their local computers and now need access to deploy their reports to the new server.

    Which role enables a developer to deploy reports to a report server?

    1. Publisher 

    2. Content Manager

    3. Browser

    4. System User
    1. Publisher <Correct>

    Explanation:

    The Publisher role is a built-in role definition that includes tasks that enable users to add content to a report server. It is not used until you create role assignments that include it. This role is intended for users who author reports or models in Report Designer or Model Designer and then publish those items to a report server. The Publisher role is required for users to add content to a report server.The Content Manager role enables users to authorize object access.

    The Browser role enables users to view reports.

    The System User role enables users to view basic information about the report server such as the schedule information in a shared schedule.
  197. You are creating a report that connects to an Analysis Services data source.

    What type of format does the Analysis Services provider support in Reporting Services?

    1. Data region

    2. Rowset 

    3. Cellset

    4. Dataset
    2. Rowset <Correct>

    Explanation:

    Analysis Services query results can be returned in two formats, cellset and rowset. The cellset format retains the hierarchical structure of the multidimensional data. The rowset format flattens the structures to a tabular layout.

    For Reporting Services, the Analysis Services data source supports only the rowset format. The cellset format does not support hierarchical structures.

    The dataset and data region items are report objects and not related to the data coming from Analysis Services.
  198. Which kinds of synchronization does the SSAS Synchronization Wizard perform when the destination SSAS database already exists? (Each correct answer presents part of the solution. Choose two.)

    1. Metadata synchronization 

    2. ROLAP data synchronization

    3. Full data synchronization

    4. Incremental data synchronization
    1. Metadata synchronization <Correct>

    4. Incremental data synchronization <Correct>

    Explanation:

    The Synchronize Database Wizard performs a metadata synchronization and an incremental data synchronization when the destination database already exists. When the destination database does not exist, a full deployment and data synchronization is done.

    ROLAP structures in SSAS do not retain data, just metadata; therefore, there is no data to synchronize.
  199. You’ve created a new report and need to turn on report caching so that the report will be cached at a certain time every day. When reviewing the shared schedules already on the report server, there currently is not a schedule that meets the need. You need to determine whether the report should use a shared schedule or you should create a private schedule for the report.

    What are the benefits of using a shared schedule? (Choose two.)

    1. The schedule is permanent and cannot expire.

    2. It is included in the report definition.

    3. All dependent items pick up the new properties of a shared schedule. 

    4. It can be managed separately from the items that use the schedule.
    3. All dependent items pick up the new properties of a shared schedule. <Correct>

    4. It can be managed separately from the items that use the schedule. <Correct>

    Explanation:

    Shared subscriptions are similar to shared data sources in that they can be used by multiple reports. After you change a shared schedule, all dependent items pick up the new properties.

    The shared schedule is not permanent. It can be configured to expire like any other schedule.

    Because the schedule is shared, it is not associated with a report definition.
  200. You are developing a SQL Server Integration Services (SSIS) package. In the data flow, you are using an OLE DB source to access the Sales table on the production server. At times, you are working in an environment that does not have access to the production server. In these cases, you receive errors on the OLE DB task.

    Which property must you set in the Data Flow task to avoid seeing errors during development?

    1. Set the DisableEventHandlers property to True.

    2. Set the DelayValidation property to True. 

    3. Set the DelayValidation property to False.

    4. Set the FailPackageOnFailure property to False.
    2. Set the DelayValidation property to True. <Correct>

    Explanation:

    The DelayValidation property stops package validation until the package is run.

    DisableEventHandlers tells the package whether to run package event handlers.

    FailPackageOnFailure tells the package to fail if an error occurs in a package component.
  201. You want to create a newspaper style report with two columns and a half-inch space between them.

    Which setting is correct?

    1. ColumnSpacing = 0.5in

    2. Columns = 2

    3. Columns = 2
    ColumnSpacing = 0.5in
    <Correct>

    4. Create a Custom Property (Name="Columns", Value="2")
    • 3. Columns = 2
    • ColumnSpacing = 0.5in <Correct>

    Explanation:

    Columns = 2 creates two columns, and ColumnSpacing = 0.5 in creates a half-inch margin between the columns.

    Setting only the Columns property creates two columns but solves only half the requirement.

    Setting only the ColumnSpacing property creates the spacing between columns but solves only half the requirement.

    There are no Custom Properties that modify the report layout.

What would you like to do?

Home > Flashcards > Print Preview