Microsoft 70-448 Online Practice Questions and Exam Preparation

Microsoft 70-448 Online Questions & Answers

• Question 11:

  You are working as an ETL developer for a payroll company. You configure a SQL Server 2008 Integration Services (SSIS) package to use checkpoints.

  The SSIS package truncates the dbo.Employee_Attendance table, loads the new employees in the dbo.Employee table, and then loads dbo.Employee_Attendance for a particular month.

  The package fails at the Derived Column transformation within the data flow task.

  You need to determine where the package execution will begin after you correct the errors and run the package again.

  Where does the package execution begin? (To answer, select the appropriate task in the control flow.)

  Hot Area:

  

  

• Question 12:

  You are managing the security in SQL Server 2008 Reporting Services (SSRS).

  You assign a new user to a role. You need to grant the permissions to the user to publish reports and manage content only in a private folder.

  What should you do?

  A. Assign the user to the Publisher role.
  B. Assign the user to the My Reports role.
  C. Assign the user to the Content Manager and the Browser roles.
  D. Assign the user to the Content Manager and the Report Builder roles.
  B. Assign the user to the My Reports role.

• Question 13:

  You are managing a SQL Server 2008 Reporting Services (SSRS) sample for Company.com. The company network security type regarding the domain which masters the sample alterations. You should make sure that the sample would help Kerberos authentication.

  Which is the correct answer?

  A. You should change the RSReportServer.config file for authentication.
  B. Through using Microsoft SQL Server Management Studio (SSMS), you should change the Server Settings
  C. Through using Transactions Log, you should change the Site Settings of the instance.
  D. You should delete RSReportServer Configuration File
  A. You should change the RSReportServer.config file for authentication.

• Question 14:

  You are a server administrator of a SQL Server 2008 Analysis Services (SSAS) instance. The instance contains a database that is used by the members of the Sales group.

  You configure a new role named Northern Region by using the "{[Customers].[Region].

  [Region].[Northern]}' allowed permission set.

  You assign the Sales group to the Northern Region role.

  You need to verify that users in the Northern Region role can view data only for their region. What should you do?

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

• Question 15:

  You are developing a SQL Server 2008 Analysis Services (SSAS) method for Company.com. There is a dimension called Product and a time dimension called TestTimer in your solution. You own a fact table which includes a column called ProductBalance which includes the current account balance.

  You should make sure that you develop a measure called Test ProductBalance from the TestBalance column to show the closing account balance for some times.

  Which is the correct answer?

  A. You should configure the AggregationFunction property of ProductBalance to Total.
  B. You should configure the AggregationFunction property of ProductBalance to ByProduct.
  C. You should configure the AggregationFunction property of ProductBalance to LastNonEmpty.
  D. You should configure the AggregationFunction property of ProductBalance to FirstEmpty.
  C. You should configure the AggregationFunction property of ProductBalance to LastNonEmpty.

  ---

  EXAM TIP Development and Enterprise editions of SSAS provide several functions--such as LastChild, LastNonEmpty, and AverageOfChildren--that help you handle semiadditive measures. for example, LastNonEmpty will let you get the closing inventory balance for a given time period that is not empty. Although you can use custom MDX expressions to aggregate semiadditive measures, you will get better performance if you use these standard functions. for the complete list of standard aggregation functions, see the topic "Confi guring Measure Properties" in SQL Server 2008 Books online (see References).

• Question 16:

  You are a database developer and you have about two years experience in creating business Intelligence (BI) by using SQL Server2008.

  Now you are employed in a company named NaproStar which uses SQL Server2008. You work as the technical support.

  Now you get an order from your company CIO, you're asked to develop a SQL Server 2008 Integration Services (SSIS) data flow.

  The data flow loads data to a SQL Server 2008 database.

  Using data from the rows in your data flow, you must make sure that the existing records in the SQL Server database is updated by the data flow.

  Of the following data flow components, which one should be used?

  A. SQL Server Destination should be used
  B. OLE DB Destination should be used
  C. OLE DB Command Transformation should be used
  D. Data Conversion Transformation should be used.
  C. OLE DB Command Transformation should be used

  ---

  Msdn OLE DB Command Transformation The OLE DB Command transformation runs an SQL statement for each row in a data flow. For example, you can run an SQL statement that inserts, updates, or deletes rows in a database table. You can configure the OLE DB Command Transformation in the following ways:

  *

  Provide the SQL statement that the transformation runs for each row.

  *

  Specify the number of seconds before the SQL statement times out.

  *

  Specify the default code page.

  Typically, the SQL statement includes parameters. The parameter values are stored in external columns in the transformation input, and mapping an input column to an external column maps an input column to a parameter. For example, to locate rows in the DimProduct table by the value in their ProductKey column and then delete them, you can map the external column named Param_0 to the input column named ProductKey, and then run the SQL statement DELETE FROM DimProduct WHERE ProductKey = ?.. The OLE DB Command transformation provides the parameter names and you cannot modify them. The parameter names are Param_0, Param_1, and so on. If you configure the OLE DB Command transformation by using the Advanced Editor dialog box, the parameters in the SQL statement may be mapped automatically to external columns in the transformation input, and the characteristics of each parameter defined, by clicking the Refresh button. However, if the OLE DB provider that the OLE DB Command transformation uses does not support deriving parameter information from the parameter, you must configure the external columns manually. This means that you must add a column for each parameter to the external input to the transformation, update the column names to use names like Param_0, specify the value of the DBParamInfoFlags property, and map the input columns that contain parameter values to the external columns. The value of DBParamInfoFlags represents the characteristics of the parameter. For example, the value 1 specifies that the parameter is an input parameter, and the value 65 specifies that the parameter is an input parameter and may contain a null value. The values must match the values in the OLE DB DBPARAMFLAGSENUM enumeration. For more information, see the OLE DB reference documentation. The OLE DB Command transformation includes the SQLCommand custom property. This property can be updated by a property expression when the package is loaded. For more information, see Integration Services Expression Reference, Using Property Expressions in Packages, and Transformation Custom Properties. This transformation has one input, one regular output, and one error output.

• Question 17:

  You develop a package by using SQL Server 2008 Integration Services (SSIS). You test the package by using your local development environment.

  The package is deployed to a production server.

  You need to configure the protection level of the package to ensure that only the user who deployed the package can execute it.

  Which protection level should you use?

  A. EncryptAllWithPassword
  B. EncryptSensitiveWithPassword
  C. DontSaveSensitive
  D. EncryptAllWithUserKey
  D. EncryptAllWithUserKey

• Question 18:

  You are a database developer and you have about two years experience in creating business Intelligence (BI) by using SQL Server2008.

  Now you are employed in a company named NaproStar which uses SQL Server2008. You work as the technical support. According to the requirement of the company, you are developing a SQL Server 2008 Integration Services (SSIS) data

  flow.

  The data flow extracts data from two sources. Each source database is located on a different server. Each source is based on a SELECT statement that uses an ORDER BY clause. Each source is based on a SELECT statement. An ORDER

  BY clause is used by the statement.

  The following diagram presents the data flow.

  The following error message pops up when the paths from the two sources are connected to a Merge Join component. "Data Flow Task: The input is not sorted.

  The 'input "Merge Join Left Input" (80)' must be sorted."

  You must make sure that data from each query is included in each row in the data flow. You must achieve this goal by using as little execution time as possible.

  So what action should you perform?

  

  A. For each data flow a Sort transformation should be added.
  B. In each source component, the sorting properties should be updated
  C. For each data flow, an OLE DB Command transformationshould be added
  D. You should replace the Merge Join transformation with a Union All transformation
  B. In each source component, the sorting properties should be updated

• Question 19:

  You are a database developer and you have about two years experience in creating business intelligence (BI) solutions by using SQL Server2008.

  Now you are employed in a company which is named NaproStar and work as a developer of a SQL Server 2008 Reporting Services (SSRS) for the company. To meet the business requirements, you use SSRS to develop a production report

  with a table contained.

  Now you get an order from your company CIO, according to his requirement, when users click on a field in the table, a SSRS technology should be executed to open a new report for them.

  Which SSRS technology should be used to achieve this goal?

  A. Tablix Data Region should be used to achieve this goal
  B. Drillthrough action should be used to achieve this goal
  C. Interactive Sorting should be used to achieve this goal
  D. Interactivereport Data should be used to achieve this goal
  E. Subreport should be used to achieve this goal
  B. Drillthrough action should be used to achieve this goal

• Question 20:

  You maintain a SQL Server 2008 Integration Services (SSIS) instance.

  You use the Execute SQL task to create a table in a Control Flow. Following the Execute SQL task is a Data Flow task in which an OLE DB source component uses this table.

  The package fails during runtime.

  You need to delay the validation of a task in the Control Flow and the OLE DB source in the data flow.

  Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

  A. Set the DelayValidation property for the Control Flow task to True.
  B. Set the ValidateExternalMetaData property for the OLE DB source to False.
  C. Set the ValidateExternalMetaData property for the OLE DB source to True.
  D. Set the DelayValidation property for the Control Flow task to False.
  A. Set the DelayValidation property for the Control Flow task to True.
  B. Set the ValidateExternalMetaData property for the OLE DB source to False.

  ---

  The DelayValidation property is available only in executables (e.g., Control Flow tasks, the package itself). The property isn't available in data flow components. If you want to change the behavior of a specific data flow component, you must set its ValidateExternalMetadata property to False.