Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 201:

  You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  ABC.com has a SQL Server Integration Services (SSIS) package that is currently deployed on the file system. The package is configured to interact work together with an outside vendor data feed. It has been reported that the outside vendor

  data feed is erratic as a result of network failures and slow response times. You have been instructed to investigate the problem. Your solution must include information about the Start Time, End Time, Execution Result, and Execution

  Duration of the package.

  You start by creating a new project and adding the package to it. You then deployed the package to the msdb database.

  Which of the following actions should you take NEXT?

  A. You should consider querying the ExecutionlLog table.
  B. You should consider viewing the SSISDB catalog.
  C. You should consider viewing the SQL Agent Error Log.
  D. You should consider viewing the SQL Server Log.
  A. You should consider querying the ExecutionlLog table.

• Question 202:

  You are designing a data warehouse that contains a product dimension.

  The product dimension contains the original product attributes as well as the most recent set of updated attributes. When a product is updated, the original product attributes must be preserved.

  The product dimension design must:

  

  Support a Type 3 Slowly Changing Dimension (SCD) process

  

  Minimize the total storage consumed

  You need to design a solution that meets these requirements.

  What should you do?

  A. Design two tables. One table will hold the original product surrogate key and attributes. The other table will hold the most recent product surrogate key and attributes.
  B. Design a table as a Type 2 SCD dimension that holds the surrogate key for each product and its attributes. Limit the number of records per product within the table to two.
  C. Design a table to hold the surrogate key for each product and its attributes as well as additional columns to store the original attributes.
  D. Design a table to hold the surrogate key for each product and its attributes. Add a column to store an indicator that identifies the record as original data or recent data.
  C. Design a table to hold the surrogate key for each product and its attributes as well as additional columns to store the original attributes.

• Question 203:

  You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing the use of Recursive hierarchies in a SQL Server Master Data Services (MDS) model.

  Which of the following is TRUE with regards to Recursive hierarchies?

  A. Each leaf member can be included in the hierarchy only once.
  B. Consolidated members in the hierarchy tree do not have to contain leaf members underneath them.
  C. Recursive hierarchies cannot include circular relationships.
  D. All consolidated members must be included in a hierarchy.
  C. Recursive hierarchies cannot include circular relationships.

• Question 204:

  You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).

  The package includes custom scripts that must be upgraded.

  You need to upgrade the package to SQL Server 2012.

  Which tool should you use?

  A. SQL Server Configuration Manager
  B. SQL Server Agent
  C. SSIS Upgrade Wizard in SQL Server Management Studio
  D. SQL Server DTExecUI utility (dtexecui.exe)
  C. SSIS Upgrade Wizard in SQL Server Management Studio

  ---

  You can upgrade packages that were created in earlier versions of Integration Services to the Integration Services format that SQL Server 2012 uses. SQL Server provides the SSIS Package Upgrade Wizard to help in this process. Because

  you can configure the wizard to backup up your original packages, you can continue to use the original packages if you experience upgrade difficulties.

  You can run the SSIS Package Upgrade Wizard from SQL Server Data Tools (SSDT), from SQL Server Management Studio, or at the command prompt.

  Note:

  * When you upgrade an instance of SQL Server 2005 or SQL Server 2008 to the current release of SQL Server, your existing SQL Server 2008 Integration Services (SSIS) packages are not automatically upgraded to the package format that the current release SQL Server Integration Services uses. You will have to select an upgrade method and manually upgrade your packages.

• Question 205:

  You are developing a SQL Server Integration Services (SSIS) package.

  The package contains an ADO object source variable that holds a result set that was returned by a stored procedure execution.

  You need to add and configure a container that will execute several tasks for each row in the ADO object source variable.

  Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  

• Question 206:

  You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012.

  Package developers in your company must have permission to perform the following tasks only on their own projects:

  

  View projects and packages

  

  View Environments

  

  Validate packages

  

  Execute packages.

  View projects and packages, View Environments, Validate packages, Execute packages.

  You need to grant rights to the developers without assigning unnecessary privileges.

  What should you do? (Each correct answer presents part of a solution. Choose all that apply.)

  A. Add developer logins to the db_ssisltduser role in the msdb database.
  B. Add developer logins to the db_ssisoperator role in the msdb database.
  C. Grant Execute permission in the projects for the developer logins.
  D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments.
  E. Add developer logins to the ssis_admin role in the SSISDB database.
  F. Grant Modify permission in the projects for the developer logins.
  C. Grant Execute permission in the projects for the developer logins.
  D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments.

  ---

  References: https://www.mssqltips.com/sqlservertip/3153/managing-ssis-security-with-database-roles/ https://anothersqlgeek.wordpress.com/2013/07/20/sql-2012-ssis-catalog-permissions/

• Question 207:

  You are developing a SQL Server Integration Services (SSIS) package.

  You create a script task in the package.

  You need to dynamically set the file name of a connection object named File1 by using the script task.

  Which line of code should you add to the script task?

  A. Me.Dts.Connections("File1").Properties.Item("FileName").SetValue = "C:/temp/temp.txt"
  B. Me.Dts.Connections("File1").AcquireConnection("C:/temp/temp.txt")
  C. Me.Dts.Connections("File1").ConnectionString = "C:/temp/temp.txt"
  D. Me.Dts.Connections("File1").Variables.Item["FileName"].value = "C:/temp/temp.txt"
  D. Me.Dts.Connections("File1").Variables.Item["FileName"].value = "C:/temp/temp.txt"

  ---

  References: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/faeb29c7-6879-4d51-b94e-09404de02339/how-to-pass-the-existing-filename-into-a-variable-in-ssis-script-task?forum=sqlintegrationservices

• Question 208:

  You plan to deploy a package to a server that has SQL Server installed. The server contains a SQL Server Integration Services (SSIS) catalog.

  You need to ensure that the package credentials are encrypted.

  Which protection level should you set for the package?

  A. ServerStorage
  B. EncryptAllWithUserKey
  C. EncryptSensitivewithPassword
  D. EncryptSensitivewithUserKey
  D. EncryptSensitivewithUserKey

  ---

  The SSISDB catalog uses the ServerStorage protection level. When you deploy an Integration Services project to the Integration Services server, the catalog automatically encrypts the package data and sensitive values. The catalog also

  automatically decrypts the data when you retrieve it.

  If you export the project (.ispac file) from the Integration Services server to the file system, the system automatically changes the protection level to EncryptSensitiveWithUserKey.

  References: https://docs.microsoft.com/en-us/sql/integration-services/security/access-control-for-sensitive-data-in-packages?view=sql-server-2017

• Question 209:

  You are developing a SQL Server Integration Services (SSIS) package.

  The package control flow will contain many tasks. The tasks will execute consecutively and none will execute more than once. Certain groups of tasks will share variable and transaction scope.

  You need to group tasks together while enabling them to be collapsed and expanded as a group.

  Which item should you use from the SSIS Toolbox? (To answer, select the appropriate item in the answer area.)

  Hot Area:

  

  

• Question 210:

  You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.

  Referential integrity must be enforced declaratively.

  You need to design a solution that can join a single time dimension to both fact tables.

  What should you do?

  A. Create a time mapping table.
  B. Change the level of granularity in both fact tables to be the same.
  C. Merge the fact tables.
  D. Create a view on the sales table.
  B. Change the level of granularity in both fact tables to be the same.

  ---

  Microsoft SQL Server Analysis Services, a time dimension is a dimension type whose attributes represent time periods, such as years, semesters, quarters, months, and days. The periods in a time dimension provide time-based levels of granularity for analysis and reporting. The attributes are organized in hierarchies, and the granularity of the time dimension is determined largely by the business and reporting requirements for historical data

  References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/database-dimensions-create-a-date-type-dimension