Microsoft 70-448 Online Practice Questions and Exam Preparation

Microsoft 70-448 Online Questions & Answers

• Question 201:

  You are creating SQL Server 2008 Integration Services (SSIS) packages which is stored in a folder on the network for Company.com.

  All members of the team can view the packages but not the attribute values. Your task is to make the attribute values be protected by password.

  Which is the correct answer below?

  A. You should Use a batch file that runs the dtutil program to set the PackageProtectionLevel property to EncryptSensitiveWithPassword on each of the SSIS packages you have
  B. You should Use a batch file that runs the dtutil program to set the PackageProtectionLevel property to ServerStorage on each of the SSIS packages you have created.
  C. You should Use the SSIS package designer to set the PackageProtectionLevel property to EncryptAllWithUserKey on each of the previously saved packages.
  D. You should Use the SSIS package designer to set the PackageProtectionLevel property to EncryptSensitiveWithUserKey on each of the previously saved packages.
  A. You should Use a batch file that runs the dtutil program to set the PackageProtectionLevel property to EncryptSensitiveWithPassword on each of the SSIS packages you have

  ---

  You can deploy single SSIS packages manually, but a better choice is to use the DTUtil command- line tool, which lets you write a command for the deployment and then automate the process. You could deploy the package to the local SQL Server using Windows Authentication by including a simple command, such as this: dtutil.exe / FILE c:\IntegrateCRM.dtsx / COPY SQL;IntegrateCRM Other command-line switches let you specify the destination SQL Server and SQL Server user name and password as needed. Table 4-2 ProtectionLevel Options

  Option Description DontSaveSensitive Sensitive information is not saved in the package at all. Each time the package is reopened in BIDS, the connection passwords must be reentered. For package execution, the password needs to be stored in a package configuration or passed to the package through the command-line setting. This is the recommended setting if you are exclusively using Windows Authentication for your connections. EncryptAllWithPassword The entire package is encrypted with a password that is set in the PackagePassword property. To open the package in BIDS, you need to enter the correct package password, and the package cannot be executed without passing the password to the package at execution EncryptAllWithUserKey The entire package is encrypted based on the current user and computer. Only the user who last designed or exported the package can design or execute the package. A package cannot be opened in BIDS unless it is opened by the user who developed it, on the computer on which it was developed. The package can be executed only through the same user account on the same computer. EncryptSensitiveWithPassword The sensitive information in the package is encrypted based on a package password, which is set in the PackagePassword property. Each time the package is reopened, a password must be provided. If the password is not provided, the package will open, but all sensitive data will be replaced with blanks. If a user attempts to execute the package without a password,the execution will fail. EncryptSensitiveWithUserKey The sensitive information (connection passwords) in the package is encrypted based on the current user and computer. If the same user reopens the package, nothing is changed. If a different user opens the package, all the sensitive information is cleared out. Other users can still design or execute the package, but they need to enter the password or pass the password into the package through the command line or SSIS configurations. ServerStorage This setting can be used only when a package is stored in a SQL Server database. Server storage means that nothing in the package definition is encrypted. Instead, the entire package is protected by using a SQL Server database role. This option is not supported when a package is saved to the file system. If encryption is enabled in the package by using any of the ProtectionLevel options except ServerStorage and DontSaveSensitive, the encrypted data is saved along with the rest of the package's information in the .dtsx file. If the entire package is encrypted, all the .dtsx file's XML code is encrypted, and the XML cannot be read by a text editor. SSIS encrypts the data by using the Microsoft Data Protection Application Programming Interface (DPAPI).

• Question 202:

  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 the technical support for the company which uses SQL Server2008 Analysis Services (SSAS).

  To meet the business development, (SSAS) is used to create a data mining model and the Data Mining Extensions (DMX) code is used to create a mining structure.

  The Data Mining Extensions (DMX) code is shown as the following:

  Now you are assigned the following tasks:

  Make sure that the Microsoft Decision Trees algorithm is used by the model. Have a mining model that can be processed by this mining structure created.

  So of the following DMX statements, which one should be used to accomplish the tasks above?

  A. INSERT INTO [Decision Tree] ( [Customer Key], [Age], [Bike Buyer], [Commute Distance], [Yearly Income]) OPENQUERY([Bike Buyer], 'Select [Customer Key], [Age], [Bike Buyer] , [Commute Distance],[Yearly Income]')
  B. SELECT * INTO [Decision Tree]USING [Microsoft_Decision_Trees]FROM [Bike Buyer]
  C. CREATE MINING MODEL [Decision Tree] ( [Customer Key] LONG KEY, [Age] LONG DISCRETIZED (Automatic, 10), [Bike Buyer] LONG DISCRETE, [Commute Distance] TEXT DISCRETE, [Yearly Income] DOUBLE CONTINUOUS) USING Microsoft_Decision_Trees
  D. ALTER MINING STRUCTURE [Bike Buyer]ADD MINING MODEL [Decision Tree] ( [Customer Key], [Age], [Bike Buyer] PREDICT, [Commute Distance], [Yearly Income]) USING Microsoft_Decision_TreesWITH DRILLTHROUGH
  D. ALTER MINING STRUCTURE [Bike Buyer]ADD MINING MODEL [Decision Tree] ( [Customer Key], [Age], [Bike Buyer] PREDICT, [Commute Distance], [Yearly Income]) USING Microsoft_Decision_TreesWITH DRILLTHROUGH

• Question 203:

  You develop a SQL Server 2008 Analysis Services (SSAS) database. Your database includes several measure groups.

  A measure group named Sales Data requires frequent updates than other measure groups.

  You need to ensure that the Sales Data measure group will automatically process on a schedule managed by SSAS.

  What should you do?

  A. Modify the ProactiveCaching property.
  B. Modify the ProcessingPriority property.
  C. Modify the ProcessingMode property.
  D. Modify the DataAggregation property.
  A. Modify the ProactiveCaching property.

• Question 204:

  You are developing a SQL Server 2008 Integration Services (SSIS) package. The package extracts data from text files and loads the data to a SQL Server 2008 database.

  You configure the data flows to fail on a single error.

  The control flow package is as shown in the following exhibit. (Click the Exhibit button.)

  

  The package extracts data from files that are stored in two folders. Each folder can store a maximum of 10 files.

  You need to ensure that the package runs and loads additional files even if an error occurs during the loading of a file.

  What should you do?

  A. Configure the MaximumErrorCount property for the package.
  B. Configure the FailPackageOnFailure property for each Foreach Loop container.
  C. Configure the FailPackageOnFailure property for each Data Flow task.
  D. Configure an OnError event handler for each Foreach Loop container.
  A. Configure the MaximumErrorCount property for the package.

  ---

  MaximumErrorCount is the maximum number of errors that can occur before a container stops running. The default value of this property is 1

• Question 205:

  You maintain a SQL Server 2008 Analysis Services (SSAS) database in a test environment.

  You plan to deploy changes from an SSAS project in a development environment to the test environment.

  You need to ensure that the roles and partitions previously deployed to the test environment are unaffected by the deployment.

  What should you do?

  A. Update the database by using the Deployment Wizard.
  B. Update the database by using the Synchronize Database Wizard.
  C. Back up the development database and restore it to the test environment.
  D. Process the database by using the Business Intelligence Design Studio (BIDS).
  A. Update the database by using the Deployment Wizard.

• Question 206:

  You are creating a SQL Server 2008 Integration Services (SSIS) package which gets information from a FTP server through utilizing many FTP tasks and operate information through utilizing many Execute SQL tasks for Company.com.

  After the package downloads the files, you should make sure that you could set the package to restart.

  In order to solve the problem, which is the correct answer?

  A. You should set the HTTP tasks to hold transactions Log.
  B. You should set the Execute SQL store procedure to hold transactions Log.
  C. You should transfer the half HTTP job to a sequence container.
  D. You should set the package to utilize a checkpoint.
  D. You should set the package to utilize a checkpoint.

• Question 207:

  You create a SQL Server 2008 Analysis Services (SSAS) solution.

  You add a data source view (DSV) to the solution. The DSV has a table named Employee that includes the following columns:

  

  EmployeeKey

  

  ManagerKey

  The ManagerKey column references the manager of the employees.

  You need to define the relationship between the employees and their manager in the DSV.

  What should you do?

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

• Question 208:

  You are developing a report which is difficult and cost a lot of time to produce. You use the technology of SQL Server 2008 Reporting Services (SSRS). You should make sure that you could set the report server to cache the report for 40

  minutes at the beginning of the request.

  Which is the correct answer?

  A. Report snapshots should be run for report.
  B. You should run report execution caching for report.
  C. You should set the data source to utilize a DBCC INDEXDEFRAG.
  D. You should set the data source to utilize a DBCC SHRINKFILE. INDEXDEFRAG
  B. You should run report execution caching for report.

  ---

  By default, a report is rendered using the most recent data. This means that every execution starts by reading the database data. This could be a large processing burden on a production server. To reduce the load on your server, you can cache a temporary copy of a report. SSRS caches the report in the ReportServer database with all the data but without any of the rendering information. If a report exists in the cache, when the next user runs the report, the user gets the data from the cache, and there is no need to reread data from the database. With report caching, Reporting Services can create and store a copy of the intermediate format report in a temporary SQL Server database named ReportServerTempDB in the Execution- Cache table. Even when the first request from a user follows the schema presented earlier, the processing of subsequent requests is altered as follows:

  1.

  A request is sent to Report Server.

  2.

  Report Server validates the properties associated with the object requested. If report execution is set to use the cache, and an item already exists in the ExecutionCache table of the ReportServerTempDB database for the ReportID of the requested report and the parameters passed are the same, Report Server renders the report from the stored intermediate format. Uusing Report Snapshots for Report Execution If you cache a report, the fi rst user who runs the report after the cache has expired will have to wait longer to view the report than the users who follow, because the fi rst user triggers the data being read from the database. You can prevent this problem by creating a snapshot of the data in advance of the fi rst report being run. To do this, select the Render This Report From An Execution Snapshot option in the execution properties of a report. You can create a snapshot based on a shared or a report-specifi c schedule. In addition, you can create the first snapshot immediately after you save the execution settings. Another caching consideration is determining whether previous versions of the cache should be persisted. Persistence of previous values in reports might be required by business users for comparison purposes or to be able to refer to those numbers later. Using snapshots, you can create persistent copies of a report and store those copies in the report history.

• Question 209:

  You are managing a SQL Server 2008 server instance which is use for running SQL Server 2008 Integration Services (SSIS) packages for Company.com.

  You should make sure that the server runs normally.

  Which is the correct answer?

  A. You should configure the package protection level to AdministrativeRestriction.
  B. You should configure the package protection level to SaveSensitive.
  C. You should configure the package protection level to EncryptSensitiveWithPassword.
  D. You should configure the BlockedSignatureStates registry enters to Block unsafe and untrusted signs and unsigned instances.
  D. You should configure the BlockedSignatureStates registry enters to Block unsafe and untrusted signs and unsigned instances.

  ---

  Signing Packages with Certificates A SQL Server 2005 Integration Services (SSIS) package can be signed with a certificate and configured to require the runtime to check the signature before loading the package. The properties of the package, CheckSignatureOnLoad and CertificateObject, indicate whether a certificate must be checked, and specify the certificate that was used to sign the package. The certificate used to sign the package must be enabled for code signing. Integration Services provides a registry value that you can use to manage an organization's policy for loading signed and unsigned packages. The registry value can also manage untrusted signatures of signed packages. With regard to the status of signatures used to sign packages, the BlockedSignatureStates registry value uses the following definitions:

  A valid signature is one that can be read successfully. An invalid signature is one for which the decrypted checksum (the one-way hash of the package code encrypted by a private key) does not match the decrypted checksum that is calculated as part of the process of loading Integration Services packages. A trusted signature is one that is created by using a digital certificate signed by a Trusted Root Certification Authority. This setting does not require the signer to be found in the user's list of Trusted Publishers. An untrusted signature is one that cannot be verified as issued by a Trusted Root Certification Authority, or a signature that is not current. To use the registry value to prevent packages from loading if the packages are unsigned, or have invalid or untrusted signatures, you must add the BlockedSignatureStates DWORD value to the HKEY_LOCAL_MACHINE\SOFTWARE \Microsoft\MSDTS registry key and specify the value 0, 1, 2, or 3. The following table lists the valid values of the DWORD data and their associated policies.

• Question 210:

  You are creating a SQL Server 2008 Reporting Services (SSRS) report which utilizes a Multidimensional Expressions (MDX) query including a parameter to show products information for various product categories for Company.com.

  The codes listed below:

  SELECT NON EMPTY {[Measures].[Reseller Sales-Order Quantity] } ON COLUMNS, NON EMPTY {([Month.[Calendar Year].[Calendar Year].ALLMEMBERS * [Products].

  [Category]. [Category].ALLMEMBERS ) } ON CHAPTERS

  FROM (SELECT (STRTOSET(@Category,CONSTRAINED)) ON COLUMNS FROM [Adventure Works Cube])

  You find out that the search produces an error when running.

  You should make sure that the query runs normally. Which is the correct answer?

  A. You should delete the NON EMPTY clauses.
  B. You should select the ROWS axis
  C. You should select the CHAPTERS axis
  D. You should select the SECTIONS axis
  B. You should select the ROWS axis