Microsoft 70-463 Pdf 2021

NEW QUESTION 1
You are designing a data warehouse that contains a customer dimension.
The customer dimension contains the original customer attributes as well as the most recent set of updated attributes. When a customer is updated, the original customer attributes must be preserved.
The customer 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 table
• B. One table will hold the original customer surrogate key and attribute
• C. The other table will hold the most recent customer surrogate key and attributes.
• D. Design a table as a Type 2 SCD dimension that holds the surrogate key for each customer and its attribute
• E. Limit the number of records per customer within the table to two.
• F. Design a table to hold the surrogate key for each customer and its attribute
• G. Add a column to store an indicator that identifies the record as original data or recent data.
• H. Design a table to hold the surrogate key for each customer and its attributes as well as additional columns to store the original attributes.

Answer: D

NEW QUESTION 2
You are developing a SQL Server Integration Services (SSIS) project to read and write data from a Windows Azure SQL Database database to a server that runs SQL Server 2012.
The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter.
You need to create a solution to meet the requirements by using the least amount of administrative effort.
What should you do?

• A. Add a SQLMOBILE connection manager to each package.
• B. Add an ADO.NET project connection manager.
• C. Add a SQLMOBILE project connection manager.
• D. Add an ADO.NET connection manager to each data flow task.
• E. Add a SQLMOBILE connection manager to each data flow task.
• F. Add an ADO.NET connection manager to each package.

Answer: B

Explanation:
Ref: http://www.databasejournal.com/features/mssql/windows-azure-sql-database-uploading-databy- using-sql-server-integration-services.html

NEW QUESTION 3
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 view on the sales table.
• B. Partition the fact tables by day.
• C. Create a surrogate key for the time dimension.
• D. Change the level of granularity in both fact tables to be the sam

Answer: D

NEW QUESTION 4
DRAG DROP
You have a SQL Server data warehouse that contains all of the customer data for your company. You plan to deploy an archiving solution.
You need to move a table named OLDCUSTOMERS to a new storage device. OLDCUSTOMERS contains a clustered index.
Which four 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.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 5
A part of your data consolidation process involves extracting data from Excel workbooks. Occasionally, the data contains errors that cannot be corrected automatically.
How can you handle this problem by using SSIS?

• A. Redirect the failed data flow task to an External Process task, open the problematic Excel file in Excel, and prompt the user to correct the file before continuing the data consolidation process.
• B. Redirect the failed data flow task to a File System task that moves the erroneous file to a dedicated location where an information worker can correct it later.
• C. If the error cannot be corrected automatically, there is no way for SSIS to continue with the automated data consolidation process.
• D. None of the answers above are correc
• E. Due to Excel's strict data validation rules, an Excel file cannot ever contain erroneous data.

Answer: B

Explanation:
References:
http://www.techbrothersit.com/2014/09/ssis-what-is-delay-validation-property.html

NEW QUESTION 6
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?

• A. Data Quality Client with administrative credentials
• B. SQL Server Configuration Manager with local administrative credentials
• C. SQL Server Data Tools with local administrative permissions
• D. SQL Server Management Studio with administrative credentials

Answer: D

NEW QUESTION 7
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?

• A. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
• B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
• C. Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.
• D. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
• E. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.

Answer: A

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms137786.aspx

NEW QUESTION 8
DRAG DROP
You are editing a SQL Server Integration Services (SSIS) package that contains a task with a sensitive property.
You need to create a project parameter and configure it so that its value is encrypted when it is deployed to the SSIS catalog.
Which three steps 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.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 9
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. Join the two fact tables.
• B. Merge the fact tables.
• C. Change the level of granularity in both fact tables to be the same.
• D. Partition the fact tables by da

Answer: D

NEW QUESTION 10
DRAG DROP
You deploy a server that has SQL Server installed.
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package fails to execute.
In which order should you perform all the actions? To answer, move all of the actions to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

Here are the steps: References:
http://www.sherweb.com/blog/how-to-set-up-database-mail-for-sql-server-job-failures/

NEW QUESTION 11
HOTSPOT
A SQL Server Integration Services (SSIS) package is designed to download data from a financial database hosted in SQL Azure.
The connection string to the financial database is defined as a project parameter named FinConStr. The parameter value must be stored securely and must be set explicitly every time the package is executed.
You need to configure the parameter to meet the requirements.
What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 12
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?

• A. Create a junk dimension for the geography dimension.
• B. Implement change data capture.
• C. Create a conformed dimension for the geography dimension.
• D. Create three geography dimension

Answer: C

NEW QUESTION 13
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 Integration Services Deployment Wizard
• B. SQL Server Configuration Manager
• C. SSIS Upgrade Wizard in SQL Server Management Studio
• D. SSIS Upgrade Wizard in SQL Server 2008 BIDS

Answer: C

Explanation:
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.

NEW QUESTION 14
HOTSPOT
You are the data steward at your company.
Duplicate customers exist in a Microsoft Excel workbook. You create a Data Quality Services (DQS) knowledge base and matching policy to identify these duplicate customers.
You need to identify the duplicate customers.
Which option should you use? (To answer, select the appropriate option in the answer area.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 15
You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system.
Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes.
You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.
What should you do?

• A. Add a data tap on the output of a component in the package data flow.
• B. Deploy the package by using an msi file.
• C. Run the package by using the dtexecui.exe utility and the SQL Log provider.
• D. uses the dtutil /copy command.
• E. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
• F. Create an OnError event handler.
• G. uses the Project Deployment Wizard.
• H. Use the gacutil command.
• I. Create a reusable custom logging component.
• J. Run the package by using the dtexec /rep /conn command.
• K. Run the package by using the dtexec /dumperror /conn command.

Answer: A

Explanation:
Reference:
http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/hh230989.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx

NEW QUESTION 16
You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012.
Quality Assurance (QA) testers in the company must have permission to perform the following tasks on specific projects:
View and validate projects and packages
View Environments and Environment variables Execute packages
You need to grant the minimum possible privileges to the QA testers.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. In the SSISDB database, add QA Tester logons to the ssis_admin role.
• B. In the msdb database, add QA Tester logons to the db_ssisoperator role.
• C. Grant Modify permission in the projects to the QA Tester logons.
• D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments to the QA Tester logons.
• E. Grant Execute permission in the projects to the QA Tester logons.
• F. In the msdb database, add QA Tester logons to the db_ssisItduser rol

Answer: BD

NEW QUESTION 17
You have a SQL Server Integration Services (SSIS) package that is configured as shown in the exhibit. (Click the Exhibit button.)

You need to recommend changes to the package to reduce load times.
Which two changes should you recommend? Each correct answer presents part of the solution.

• A. Remove the OLE DB command and use a Script task.
• B. Add an Execute SQL task that batch-updates changed rows.
• C. Set the ValidateExternalMetadata property to False.
• D. Add an Execute SQL task that truncates and reloads the table.
• E. Remove the OLE DB command and use an OLE DB destinatio

Answer: BE

Explanation:
If the OLE DB destination uses a fast-load data access mode, you can specify the following fast load options in the user interface, OLE DB Destination Editor, for the destination:
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/ole-db-destination?view=sqlserver- 2021#fast-load-options

NEW QUESTION 18
You are developing a SQL Server Integration Services (SSIS) package.
The package sources data from an HTML web page that lists product stock levels.
You need to implement a data flow task that reads the product stock levels from the HTML web page. Which data flow source should you use?

• A. Raw File source
• B. XML source
• C. Custom source component
• D. Flat File source

Answer: C

NEW QUESTION 19
DRAG DROP
A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse.
The SSIS catalog includes two Environments:
Test
Production
Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the test or production data warehouses. You need to execute deployed packages by using either of the defined Environments.
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.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
We need to add references to the Test and Production environments to the project. Then we can map the variables in the project to the environment variables defined in the environments.
When you execute a package in a project that references multiple environments (Test and Production in this case), we can select which environment the package runs under.

NEW QUESTION 20
DRAG DROP
You need to design a data load strategy for a data warehouse fact table. The solution must use an ordered data load.
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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 21
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?

• A. Use sp_addlinkedserver to add a linked server to access the MDS database tables directly.
• B. Create a Subscription View in MDS.
• C. Use transactional replication for data synchronization.
• D. Create an OLE DB connection string that sets the Provider property to MD

Answer: B

Explanation:
You create a subscription view to export Master Data Services data to a subscribing system such as SQL Server. You use the subscribing system to view the data in the Master Data Services database. References:
https://docs.microsoft.com/en-us/sql/master-data-services/overview-exporting-data-master-dataservices? view=sql-server-2021

NEW QUESTION 22
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. SSIS Upgrade Wizard in SQL Server 2008 BIDS
• B. SSIS Upgrade Wizard in SQL Server Data Tools
• C. SQL Server DTExecUI utility (dtexecui.exe)
• D. SQL Server dtexec utility (dtexec.exe)

Answer: B

NEW QUESTION 23
You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The three Execute SQL tasks modify products in staging tables in preparation for a data warehouse load.
The package and all three Execute SQL product tasks have their TransactionOption property set to Supported.
You need to ensure that if any of the three Execute SQL product tasks fail, all three tasks will roll back their changes.
What should you do?

• A. Change the TransactionOption property of the package to Required.
• B. Change the TransactionOption property of all three Execute SQL product tasks to Required.
• C. Move the three Execute SQL product tasks into a Foreach Loop container.
• D. Move the three Execute SQL product tasks into a Sequence containe

Answer: A

Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms141144.aspx

NEW QUESTION 24
DRAG DROP
You are building a fact table in a data warehouse.
The table must have a columnstore index. The table cannot be partitioned. You need to design the fact table and load it with data.
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.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Only nonclustered columnstore indexes are available in SQL Server 2012 so when the index is created, you cannot update the table (without using partition switching). Therefore you have to load the data BEFORE creating the index.
In SQL Server 2014, you could create a clustered columnstore index which would allow updates to the table after the index is created.

NEW QUESTION 25
You are designing an Extract, Transform and Load (ETL) solution that loads data into dimension tables. The ETL process involves many transformation steps.
You need to ensure that the design can provide:
Auditing information for compliance and business user acceptance
Tracking and unique identification of records for troubleshooting and error correction What should you do?

• A. Develop a Master Data Services (MDS) solution.
• B. Develop a Data Quality Services (DQS) solution.
• C. Create a version control repository for the ETL solution.
• D. Develop a custom data lineage solutio

Answer: D

NEW QUESTION 26
You are installing SQL Server Data Quality Services (DQS).
You need to give specific users access to the Data Quality Server. Which SQL Server application should you use?

• A. SQL Server Configuration Manager
• B. SQL Server Data Tools
• C. SQL Server Management Studio
• D. Data Quality Client

Answer: C

Explanation:
Ref: http://msdn.microsoft.com/en-us/library/hh213045.aspx

NEW QUESTION 27
DRAG DROP
You use SQL Server Integration Services (SSIS) for extract, transformation, load (ETL) processing. Issues concerning addresses are discovered in the data warehouse that you manage. Validation must separate the addresses into three categories:
Valid addresses Autocorrected addresses Invalid addresses
You need to enhance the SSIS packages to perform address validation by using an external service. Which transformation should you use? (To answer, drag the appropriate transformation from the list of options to the correct location or locations in the answer area.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 28
To support the implementation of new reports, Active Directory data will be downloaded to a SQL Server database by using a SQL Server Integration Services (SSIS) 2012 package.
The following requirements must be met:
All the user information for a given Active Directory group must be downloaded to a SQL Server table.
The download process must traverse the Active Directory hierarchy recursively.
You need to configure the package to meet the requirements by using the least development effort. Which item should you use?

• A. Script task
• B. Script component configured as a transformation
• C. Script component configured as a source
• D. Script component configured as a destination

Answer: C

NEW QUESTION 29
You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours.
Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.
You need to partition the table to maximize the performance of queries. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Partition the fact table by month, and compress each partition.
• B. Partition the fact table by week.
• C. Partition the fact table by year.
• D. Partition the fact table by day, and compress each partitio

Answer: D

NEW QUESTION 30
......