70-463 | Downloadable 70-463 Study Guides 2021

NEW QUESTION 1
You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.
You have the following requirements:
• Ensure that the deployment model supports changing the content of connection
strings by using parameters at execution time.
• Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database.
• Maximize performance at execution time.
• Minimize development effort.
You need to design a solution that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Use a project deployment mode
• B. Modify connection manager properties to use project parameter
• C. Ensure that the SSISDB database is created.
• D. Use a project deployment mode
• E. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
• F. Use a package deployment mode
• G. Use a SQL Server package configuration with a common filte
• H. Change the contents of the SSIS Configurations table at runtime.
• I. Use a package deployment mode
• J. Save each SSIS package to a file share that can be accessed from all environments.

Answer: A

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

• A. Recursive
• B. Explicit
• C. Domain-based
• D. User-defined
• E. Derived
• F. Parent

Answer: C

Explanation: References:
http://msdn.microsoft.com/en-us/library/bb190163.aspx http://msdn.microsoft.com/en-us/library/ee633737.aspx http://msdn.microsoft.com/en-us/library/ee633759.aspx http://msdn.microsoft.com/en-us/library/ee633745.aspx http://msdn.microsoft.com/en-us/library/ee633724.aspx http://msdn.microsoft.com/en-us/library/ee633733.aspx http://msdn.microsoft.com/en-us/library/ff487058.aspx

NEW QUESTION 3
You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job.
You make changes to the package that should improve performance.
You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal.
What should you do?

• A. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.
• B. Configure the package to send you an email upon completion that includes information about the duration of the packag
• C. After 10 executions, view the emails.
• D. Enable logging to the Application Event Log in the package control flow for the OnInformation even
• E. After 10 executions, view the Application Event Log.
• F. Enable logging to the Application Event Log in the package control flow for the OnPostExecute even
• G. After 10 executions, view the Application Event Log.

Answer: A

Explanation: The All Executions Report displays a summary of all Integration Services executions that have been performed on the server. There can be multiple executions of the sample package. Unlike the Integration Services Dashboard report, you can configure the All Executions report to show executions that have started during a range of dates. The dates can span multiple days, months, or years.
The report displays the following sections of information.
* Filter
Shows the current filter applied to the report, such as the Start time range.
* Execution Information
Shows the start time, end time, and duration for each package execution. You can view a list of the parameter values that were used with a package execution, such as values that were passed to a child package using the Execute Package task.

NEW QUESTION 4
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. Partition the fact tables by day.
• C. Create a time dimension that can join to both fact tables at their respective granularity.
• D. Join the two fact table

Answer: C

NEW QUESTION 5
HOTSPOT
You plan to design an SQL Server Integration Services (SSIS) package to move data from a source system to an SQL Server database named Warehouse.
You need to implement a data loading strategy for the package. The solution must ensure that if a failure occurs, the connection automatically resumes from the point of failure.
How should you configure each package property? To answer, select the appropriate options in the answer area.

Answer:

Explanation: Box 1 CheckPointUsage: Always
CheckpointUsage specifies whether checkpoints are used.
Always specifies that the checkpoint file is always used and that the package restarts from the point of the previous execution failure. If the checkpoint file is not found, the package fails.
Box 2: FailPackageOnFailure: False
You must set the FailPackageOnFailure property to true for all the containers in the package that you want to identify as restart points.
Box 3: SaveCheckPoints: Yes
SaveCheckpoints indicates whether the package saves checkpoints. This property must be set to True to restart a package from a point of failure.
Box 4: TransactionOption: Supported
TransactionOption in SSIS is used to start transactions, add executables to transactions or keep them out of transactions. It has 3 values; namely
-> Required :- The container which has the transactionoption set to this, joins any available transaction or else starts a new transaction .
-> Supported -> Joins any available transaction (started by its parent container) but if not available, does not start one.
-> Not Supported -> Does not support transactions. The transactions follow all the
References:
https://docs.microsoft.com/en-us/sql/integration-services/packages/restart-packages-by-usingcheckpoints? view=sql-server-2021

NEW QUESTION 6
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 7
You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Windows Azure SQL Database database. The package has been deployed to a production server that runs Windows Server 2008 R2 and SQL Server 2012.
The package fails when executed on the production server.
You need to ensure that the package can load the Excel workbook data without errors. You need to use the least amount of administrative effort to achieve this goal.
What should you do?

• A. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
• B. Enable Address Windowing Extensions (AWE) for the local SQL Server instance.
• C. Replace the SSIS Excel source with a SSIS Flat File source.
• D. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.

Answer: D

Explanation: XLSX files, the new file type in Excel 2007/2010 cannot be opened with Excel Source/Destination in SSIS. To use XLSX in SSIS we need to install ACE driver and use OLE DB Source/Destination to read read/write data in XLSX files.
ACE is a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office Excel 7 (*.xls, *.xlsx, and *.xlsb) files to Microsoft SQL Server. If the SQL Server or your development box does not have ACE driver then install that 1st.
Note:
* The most efficient way to link to SQL Server is using an OLEDB connection manager. Incorrect:
Not B:
* Address Windowing Extensions (AWE) is a Microsoft Windows application programming interface that allows a 32-bit software application to access more physical memory than it has virtual address space.
* Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical memory greater than 4GB. Certain data-intensive applications, such as database management systems and scientific and engineering software, need access to very large caches of dat
A. In the case of very large data sets, restricting the cache to fit within an application's 2GB of user address space is a severe restriction.

NEW QUESTION 8
DRAG DROP
You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist and Song columns.
The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base. The values in the Song column in the dataset can be a close match with the values in the Song domain.
You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates.
How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.)

Answer:

Explanation:

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

• A. INSERT INTO dbo.Table (variablevalue) VALUES ($StringVar)
• B. INSERT INTO dbo.Table (variablevalue) VALUES (0)
• C. INSERT INTO dbo.Table (variablevalue) VALUES (@0)
• D. INSERT INTO dbo.Table (variablevalue) VALUES (?)

Answer: D

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141003.aspx http://msdn.microsoft.com/en-us/library/ms140355.aspx http://msdn.microsoft.com/en-us/library/cc280502.aspx

NEW QUESTION 10
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 11
You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)


Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1?

• A. DateHired
• B. DepartmentID
• C. EmployeeID
• D. EmployeeNum
• E. FirstName
• F. JobTitle
• G. LastName
• H. MiddleName
• I. ReportsToID

Answer: C

NEW QUESTION 12
You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort. What should you do?

• A. Create an Integration Services package and use a Fuzzy Lookup transform.
• B. Create a Master Data Services (MDS) model.
• C. Create a Data Quality Project.
• D. Create an Integration Services package and use a DQS Cleansing transform.
• E. Create an Integration Services package and use a Fuzzy Grouping transform.

Answer: C

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

NEW QUESTION 13
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 an OLE DB connection string that sets the Provider property to MDS.
• C. Use transactional replication for data synchronization.
• D. Create a Subscription View in MD

Answer: D

NEW QUESTION 14
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 15
You are designing an extract, transform, load (ETL) process for loading data from a SQL Server database into a large fact table in a data warehouse each day with the prior day's sales data.
The ETL process for the fact table must meet the following requirements: Load new data in the shortest possible time.
Remove data that is more than 36 months old. Ensure that data loads correctly.
Minimize record locking.
Minimize impact on the transaction log.
You need to design an ETL process that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Partition the destination fact table by dat
• B. Insert new data directly into the fact table and delete old data directly from the fact table.
• C. Partition the destination fact table by dat
• D. Use partition switching and staging tables both to remove old data and to load new data.
• E. Partition the destination fact table by custome
• F. Use partition switching both to remove old data and to load new data into each partition.
• G. Partition the destination fact table by dat
• H. Use partition switching and a staging table to remove old dat
• I. Insert new data directly into the fact tabl

Answer: B

NEW QUESTION 16
HOTSPOT
You are the Master Data Services (MDS) administrator at your company.
An existing user must be denied access to a certain hierarchy node for an existing model. You need to configure the user's permissions.
Which user management menu item should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Answer:

Explanation:

NEW QUESTION 17
You have a SQL server integration Services (SSIS) package named package1. You discover that the Data Flow task for Package1 runs slower than expected. You need to reduce the amount of time required to run the Data Flow task. Which two actions should you perform? (Select Two)

• A. Configure the package to run by using the CallerInfo option.
• B. Modify the DefaultBufferSize package setting.
• C. Modify the ForceExecutionResult Package setting.
• D. Modify the DefaultBufferMaxRows package setting.
• E. Configure the package to run by using MaxConcurrent optio

Answer: DE

NEW QUESTION 18
You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and
then CustomerID.
Which Transact- SQL statement should you use?

• A. CREATE TABLE Customer (SourceID int NOT NULL IDENTITY,CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL);
• B. CREATE TABLE Customer (SourceID int NOT NULL,CustomerID int NOT NULL PRIMARY KEY CLUSTERED,CustomerName varchar(255) NOT NULL);
• C. CREATE TABLE Customer(SourceID int NOT NULL PRIMARY KEY CLUSTERED,CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);
• D. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED(SourceID, CustomerID));

Answer: D