Microsoft 70-463 Practice Questions 4

Create an image of a person studying SQL Server integration concepts, surrounded by digital screens displaying data flow diagrams and database management icons, with books on data processing and analytics in the foreground.

Microsoft 70-463 Practice Quiz

Welcome to the Microsoft 70-463 Practice Quiz! This engaging quiz is designed for individuals looking to sharpen their knowledge and skills in SQL Server Integration Services (SSIS) and Master Data Services (MDS). Whether you are preparing for certification or simply want to test your knowledge, this quiz has it all!

Featuring:

  • 31 targeted questions on SSIS and MDS
  • Multiple choice and multi-select formats
  • Instant feedback on your answers
31 Questions8 MinutesCreated by CodingGuru257
Each question is a total of 10 points.  There are 60 total questions.
For multichoice questions the points are divided among the correct answers.
 
It has a limitation in that for multi-choice questions if you choose every answer I think it will give you all the points, even though Microsoft would not allow the same.  So you do have to use this tool wisely.
 
Microsoft has their own practice test which you can purchase as well.
Each question is a total of 10 points.  There are 60 total questions.
For multichoice questions the points are divided among the correct answers.
 
It has a limitation in that for multi-choice questions if you choose every answer I think it will give you all the points, even though Microsoft would not allow the same.  So you do have to use this tool wisely.
 
Microsoft has their own practice test which you can purchase as well.
You are developing a SQL Server Integration Services (SSIS) package that imports data from a relational database to a data warehouse. You are importing data from a relational table named Projects. The table has change data capture enabled on all columns. You need to process only the most recent values from rows that have been inserted or updated since the previous execution of the package. Which query should you use as the data source?
SELECT * FROM cdc.fn_cdc_get_all_changes_Projects (@from_lsn, @to_lsn, N’ all update old’)
SELECT * FROM cdc.fn_cdc_get_all_changes_Projects (@from_lsn, @to_lsn, N’ all’)
SELECT * FROM cdc.fn_cdc_get_net_changes_Projects (@from_lsn, @to_lsn)
SELECT * FROM cdc.Projects_CT WHERE @from_lsn >= _$start_lsn AND @to_lsn < _$start_lsn
You are creating a SQL Server Master Data Services (MDS) model for a company. The source data for the company is stored in a single table that contains the manager-to-subordinate relationships. You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?
Natural
Explicit
Parent
Recursive
You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on SQL Azure. One of the columns in the data source is named ProductCode. Some of the data to be loaded will reference products that need special processing logic in the data flow. You need to enable separate processing streams for a subset of rows based on the source product code. Which data flow transformation should you use?
Source Assistant
Audit
Destination Assistant
Script Component
You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation. The reference data to be used in the transformation does not change. You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance. What should you do?
Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.
Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors. Products for each distributor source must be combined for insertion into a single product table destination. You need to select the appropriate data flow transformation to meet this requirement. Which transformation types should you use? (Each correct answer presents a complete solution. Choose all that apply.)
Multicast
Merge Join
Term Extraction
Union All
Merge

You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named Quotes. The table has no indexes and consists of seven columns:

  • [ID]
  • [QuoteDate]
  • [Open]
  • [Close]
  • [High]
  • [Low]
  • [Volume]

Each of the following queries must be able to use a columnstore index:

SELECT AVG ([Close]) AS [AverageClose]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.

SELECT AVG([High] – [Low]) AS [AverageRange]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.

SELECT SUM([Volume]) AS [SumVolume]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’

You need to ensure that the indexing strategy meets the requirements. The strategy must also minimize the number and size of the indexes. What should you do?

Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].
Create three coiumnstore indexes: Ÿ One containing [QuoteDate] and [Close] Ÿ One containing [QuoteDate], [High], and [Low] Ÿ One containing [QuoteDate] and [Volume]
Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].
Create two columnstore indexes: Ÿ One containing [ID], [QuoteDate], [Volume], and [Close] Ÿ One containing [ID], [QuoteDate], [High], and [Low]
You are creating a SQL Server Master Data Services (MDS) model. This model is used to store a master list of products. An attribute must be added to the Product entity to define the sales manager responsible for each product. You need to create an attribute in the Product entity that prevents users from entering invalid sales manager values. Which type of attribute should you create?
Derived
Domain-based
User-defined
Parent
Explicit
Recursive
You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure. Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart. You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented. What should you do?
Create a conformed dimension for the geography dimension.
Implement change tracking.
Create a degenerate dimension for the geography dimension.
Create a Type 2 slowly changing dimension for the geography dimension.
You are reviewing the design of a student dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to student attributes such as ParentOccupation. You need to redesign the dimension to enable the full historical reporting of changes to multiple student attributes including ParentOccupation. What should you do?
Add CurrentValue and PreviousValue columns to the student dimension.
Enable Snapshot Isolation on the data warehouse.
Add an IsCurrent column to the student dimension.
Add StartDate and EndDate columns to the student dimension.
You are maintaining a Data Quality Services (DQS) environment. The production server failed and a new server has been set up. The DQS databases are restored to a new server. All the appropriate permissions are granted. DQS users are experiencing issues connecting to the new Data Quality Server. You need to enable users to connect to the new server. Which Surface Area Configuration property should you enable?
AdHocRemoteQueriesEnabled
SoapEndpointsEnabled
ClrlntegrationEnabled
RemoteDacEnabled
OleAutomationEnabled
XpCmdShellEnabled
1. DimCurrency, DimScenario, DimAccount in parallel 2. DimOrganization 3. FactFinance
1. DimCurrency, DimOrganization in parallel 2. DimScenario, DimAccount in parallel 3. FactFinance
1. DimCurrency, FactFinance in parallel 2. DimOrganization, DimScenario, DimAccount in parallel
1. FactFinance 2. DimOrganization, DimScenario, DimAccount in parallel 3. DimCurrency
1. DimCurrency 2. DimOrganization 3. DimScenario, DimAccount in parallel 4. FactFinance
You are developing a SQL Server Integration Services (SSIS) package. You need to design a package to change a variable value during package execution by using the least amount of development effort. What should you use?
Expression task
Script task
Execute SQL task
Execute Process task
Term Extraction transformation
You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed. You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables. You need to configure the SQL Server environment to support these packages. What should you do?
Create SSIS configuration files for the packages.
Create an Integration Services catalog.
Install Data Quality Services.
Install Master Data services.

You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You add an Execute SQL task to the control flow. The task must execute a simple INSERT statement. The task has the following requirements:

  • The INSERT statement must use the value of a string package variable. The variable name is StringVar.
  • The Execute SQL task must use an OLE DB Connection Manager.

 In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter. You must configure the SQLStatement property of the Execute SQL task. Which SQL statement should you use?

INSERT INTO dbo.Table (variablevalue) VALUES ($Project::StringVar)
INSERT INTO dbo.Table (variablevalue) VALUES (@StringVar)
INSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar)
INSERT INTO dbo.Table (variablevalue) VALUES (?)

You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on Server A. The package includes a data flow and is executed on ServerB. The destination table has its own identity column. The destination data load has the following requirements:

  • The identity values from the source table must be used.
  • Default constraints on the destination table must be ignored.
  • Batch size must be 100,000 rows.

You need to add a destination and configure it to meet the requirements. Which destination should you use?

ADO NET Destination with Bulk Insert
SQL Server Destination
OLE DB Destination with Fast Load
ADO NET Destination without Bulk Insert
OLE DB Destination without Fast Load
You are installing the Data Quality Client on user desktops. You need to ensure that the prerequisite software components are installed. Which components must be present to meet this goal? (Each correct answer presents part of the solution. Choose all that apply.)
SQL Server Management Studio
Internet Explorer 6.0 SP1 or later
Microsoft Silverlight 5
.NET Framework 3.5 SP1
.NET Framework 4.0
Microsoft Silverlight 4
SQL Server Data Tools
You manage a SQL Server Master Data Services (MDS) environment. A new application requires access to the product data that is available in the MDS repository. You need to design a solution that gives the application access to the product data with the least amount of development effort. What should you do?
Create a Subscription View in MDS.
Access the product entity tables in the MDS database directly.
Use SQL Server Integration Services (SSIS) to extract the data and put it in a staging database.
Use change data capture on the product entity tables.
You manage a SQL Server Master Data Services (MDS) environment. A new application requires access to the product data that is available in the MDS repository. You need to design a solution that gives the application access to the product data with the least amount of development effort. What should you do?
Use sp_addlinkedserver to add a linked server to access the MDS database tables directly.
Create an OLE DB connection string that sets the Provider property to MDS.
Use transactional replication for data synchronization.
Create a Subscription View in MDS.
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure. The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model. Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule. Each job passes a different folder path to the package. You need to configure the package to accept the folder path from each job. Which package configuration should you use?
Parent Package Variable
XML Configuration File
Environment Variable
.dtsConfig file
Registry Entry
You are installing SQL Server Data Quality Services (DQS). You need to give users belonging to a specific Active Directory group access to the Data Quality Server. Which SQL Server application should you use?
Data Quality Client with administrative credentials
SQL Server Configuration Manager with local administrative credentials
SQL Server Data Tools with local administrative permissions
SQL Server Management Studio with administrative credentials
You are installing SQL Server Data Quality Services (DQS). You need to give specific user’s access to the Data Quality Server. Which SQL Server application should you use?
SQL Server Configuration Manager
SQL Server Data Tools
SQL Server Management Studio
Data Quality Client
You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project. The first package that you create in this project contains a package connection that accesses a flat file. Additional packages in the project must also access this file. You need to define and reuse the flat file connection in all project packages. What should you do?
Convert the package Connection Manager in the first package to a project Connection Manager.
Copy the package Connection Manager and paste it into the second package.
Convert the project to the Package Deployment model.
Set the ProtectionLevel property of the package Connection Manager to DontSaveSensitive to reuse the flat file connection.
You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog. You need to add the Environment to the project. Which stored procedure should you use?
The catalog. create_execution stored procedure.
The catalog. create_environment_variable stored procedure.
The catalog. create_environment_reference stored procedure.
The catalog. add_data_tap stored procedure.
You develop and deploy a SQL Server Integration Services (SSIS) package. The package is stored in the file system. You need to execute the package without importing it to the SSIS server. What should you use to execute the package? (Each correct answer presents a complete solution. Choose all that apply.)
Catalog.start_package
Dtexec
SQL Server Management Studio
SQL Server Agent
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. You are running a training exercise for Microsoft SQL Server 2012 junior administrators. You are discussing the use of Slowly Changing Dimension Transformation Outputs. One of the output options causes Derived Column transformations to create columns for the expired row and the current row indicators. Which option is the output that causes this?
Unchanged Output
Inferred Member Updates Output
Historical Attributes Inserts Output
Fixed Attribute Output
Changing Attributes Updates Output
You work as a 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. You have recently created an SQL Server Integration Services (SSIS) project catalog that contains two Environments. A single Environment Variable, named ConnectionString, of type string is specified by each environment. The project also has a project Connection Manager configured to link up with the data warehouse. You have been instructed to make sure that deployed packages are executed using any of the specified Environments. Which of the following suitably describes an Environment Variable?
An environment variable specifies the data that will be used by a package.
An environment variable can be used to manage Integration Services objects in the catalog.
An environment variable defines a literal value that can be assigned to a parameter during package execution.
An environment variable allows you to use folders to organize your projects and environments.
An environment variable is a container of variables that can be referenced by Integration Services projects.
You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse. The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis. You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum. What should you do?
Add an OnError event handler to the SSIS project.
Use an msi file to deploy the package on the server.
Open a command prompt and run the gacutil command.
Open a command prompt and run the dtutil /copy command.
Open a command prompt and run the dtexec /rep /conn command.
Open a command prompt and run the dtexec /dumperror /conn command.
Run the package by using the dtexecui.exe utility and the SQL Log provider.
Create a reusable custom logging component and use it in the SSIS project.
Configure the SSIS solution to use the Project Deployment Model.
Configure the output of a component in the package data flow to use a data tap.
Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.

To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place. The methodology has the following requirements:

  • The deployment process must be simplified.
  • All the logs must be centralized in SQL Server.
  • Log data must be available via reports or T-SQL.
  • Log archival must be automated.

You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort. What should you do?

Open a command prompt and run the gacutil command.
Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
Add an OnError event handler to the SSIS project.
Use an msi file to deploy the package on the server.
Configure the output of a component in the package data flow to use a data tap.
Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
Open a command prompt and run the dtexec /rep /conn command.
Open a command prompt and run the dtutil /copy command.
Open a command prompt and run the dtexec /dumperror /conn command.
Configure the SSIS solution to use the Project Deployment Model.
Create a reusable custom logging component and use it in the SSIS project.
You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure. The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode. You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode. What should you do?
Add StartDate and EndDate columns to the customer dimension.
Add an IsCurrent column to the customer dimension.
Enable Snapshot Isolation on the data warehouse.
Add CurrentValue and PreviousValue columns to the customer dimension.
You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure. Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart. You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented. What should you do?
Create a junk dimension for the geography dimension.
Implement change data capture.
Create a conformed dimension for the geography dimension.
Create three geography dimensions.
{"name":"Microsoft 70-463 Practice Questions 4", "url":"https://www.quiz-maker.com/QPREVIEW","txt":"Welcome to the Microsoft 70-463 Practice Quiz! This engaging quiz is designed for individuals looking to sharpen their knowledge and skills in SQL Server Integration Services (SSIS) and Master Data Services (MDS). Whether you are preparing for certification or simply want to test your knowledge, this quiz has it all!Featuring:31 targeted questions on SSIS and MDSMultiple choice and multi-select formatsInstant feedback on your answers","img":"https:/images/course8.png"}
Powered by: Quiz Maker