70-463 | Accurate 70-463 Exam Questions 2021

NEW QUESTION 1
You are designing a data warehouse for a fresh food distribution business that stores sales by individual product. It stores sales targets by product category. Products are classified into subcategories and categories.
Each product is included in only a single product subcategory, and each subcategory is included in only a single category.
The data warehouse will be a data source for an Analysis Services cube. The data warehouse contains two fact tables:
• factSales, used to record daily sales by product
• factProductTarget, used to record the monthly sales targets by product category
Reports must be developed against the warehouse that reports product sales by product, category and subcategory, and product sales targets.
You need to design the product dimension. The solution should use as few tables as possible while supporting all the requirements.
What should you do?

• A. Create two product tables, dimProduct and dimProductCategor
• B. ConnectfactSales to dimProduct and factProductTarget to dimProductCategory with foreign key constraint
• C. Direct the cube developer to use key granularity attributes.
• D. Create one product table, dimProduct, which contains product detail, category, and subcategory column
• E. Connect factSales to dimProduct with a foreign key constrain
• F. Direct the cube developer to use a non-key granularity attribute for factProductTarget.
• G. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory, and a fourth bridge table that joins products to their appropriate category and subcategory table records with foreign key constraint
• H. Direct the cube developer to use key granularity attributes.
• I. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategor
• J. Connect factSales to all three product tables and connect factProductTarget to dimProductCategory with foreign key constraint
• K. Direct the cube developer to use key granularity attributes.

Answer: B

NEW QUESTION 2
You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data.
You need to use DQS profiling to obtain the required statistics. Which DQS activity should you use?

• A. Cleansing
• B. Matching
• C. Knowledge Discovery
• D. Matching Policy

Answer: A

Explanation: Ref: http://msdn.microsoft.com/en-gb/library/hh213055(v=sql.110).aspx

NEW QUESTION 3
DRAG DROP
You are designing a SQL Server Integration Services (SSIS) package. The package moves order-related
data to a staging table named Order. Every night the staging data is truncated and then all the recent orders from the online store database are inserted into the staging table.
Your package must meet the following requirements:
• If the truncate operation fails, the package execution must stop and report an error.
• If the Data Flow task that moves the data to the staging table fails, the entire refresh operation must be rolled back.
• For auditing purposes, a log entry must be entered in a SQL log table after each execution of the Data Flow task.
The TransactionOption property for the package is set to Required. You need to design the package to meet the requirements.
How should you design the control flow for the package? (To answer, drag the appropriate setting from the list of settings to the correct location or locations in the answer area.)

Answer:

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

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

• A. Natural
• B. Explicit
• C. Parent
• D. Recursive

Answer: D

Explanation: Reference: http://technet.microsoft.com/en-us/library/ff487006.aspx Reference: http://msdn.microsoft.com/en-us/library/ee633747.aspx Reference: http://technet.microsoft.com/en-us/library/ee633759.aspx

NEW QUESTION 5
You are reviewing the design of an existing fact table named factSales, which is loaded from a SQL Azure database by a SQL Server Integration Services (SSIS) package each day. The fact table has approximately 1 billion rows and is dimensioned by product, sales date, and sales time of day.

The database administrator is concerned about the growth of the database. Users report poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.
You need to reduce the report processing time and minimize the growth of the database. What should you do?

• A. Partition the table by product type.
• B. Create a view over the fact table to aggregate sales by month.
• C. Change the granularity of the fact table to month.
• D. Create an indexed view over the fact table to aggregate sales by mont

Answer: C

NEW QUESTION 6
You need to extract data from a table in a SQL Server 2012 database. What connection manager types can you use? (Choose all that apply.)

• A. An ODBC connection manager
• B. An OLE DB connection manager
• C. A File connection manager
• D. An ADO.NET connection manager

Answer: ABD

Explanation: References:
https://technet.microsoft.com/en-us/library/ms141013(v=sql.110).aspx https://docs.microsoft.com/en-us/sql/integration-services/data-flow/extract-data-by-using-theodbc- source
https://msdn.microsoft.com/en-us/library/ms254937(v=vs.100).aspx

NEW QUESTION 7
You are the administrator for a Master Data Services (MDS) environment.
A user who is not an administrator must be able to access Master Data Manager. You need to assign permissions to the user.
Which function should you use?

• A. Explorer
• B. System Administration
• C. Version Management
• D. User and Group Permissions
• E. Integration Management

Answer: D

Explanation: In the User and Group Permissions functional area, administrators can grant permission to functional areas, to attributes (on the Models tab), and to members (on the Hierarchy Members tab). Overlapping permissions are resolved to determine a user’s permission to each individual attribute. References:
https://docs.microsoft.com/en-us/sql/master-data-services/user-and-group-permissions-functionalarea- master-data-manager?view=sql-server-2021

NEW QUESTION 8
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package. An Execute SQL task in the package checks product stock levels and sets a package variable named InStock to TRUE or FALSE depending on the stock level found.
After the successful execution of the Execute SQL task, one of two data flow tasks must run, depending on the value of the InStock variable.
You need to set the precedence constraints.
Which value for the evaluation operation should you use? (To answer, select the appropriate option for the evaluation operation in the answer area.)

Answer:

Explanation:

NEW QUESTION 9
CORRECT TEXT
You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database. The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)

The Insert New Orders Data Flow task must meet the following requirements:
• Usage of the tempdb database should not be impacted.
• Concurrency should be maximized, while only reading committed transactions.
• If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements.
How should you configure the transaction properties? (To answer, select the appropriate setting or settings in the answer area.)

Answer:

Explanation: IsolationLevel = ReadCommited.
TransactionOption = Required
References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms137749.aspx http://msdn.microsoft.com/enus/ library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx http://msdn.microsoft.com/en-us/library/ms173763.aspx

NEW QUESTION 10
You are installing the Data Quality Server component of Data Quality Services.
You need to provision the hardware and install the software for the server that runs the Data Quality Server.
You must ensure that the minimum Data Quality Server prerequisites are met. What should you do?

• A. Install Microsoft Internet Explorer 6.0 SP1 or later.
• B. Install SQL Server 2012 Database Engine.
• C. Make sure the server has at least 4 GB of RAM.
• D. Install Microsoft SharePoint Server 2010 Standard Edition.

Answer: B

Explanation: Data Quality Server Minimum System Requirements
* SQL Server 2012 Database Engine.
* Memory (RAM): Minimum: 2 GB Recommended: 4 GB or more
Note: SQL Server Data Quality Services (DQS) is a new feature in SQL Server 2012 that contains the following two components: Data Quality Server and Data Quality Client.

NEW QUESTION 11
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 12
You have a server named SQL1 that has SQL Server Integration Services (SSIS) installed. SQL1 has eight CPUs, 8 GB of RAM, RAID-10 storage, and a 10-gigabit Ethernet connection.
A package named Package1 runs on SQL1. Package1 contains 10 data tasks. Package1 queries 3 GB of data from the source system.
You review the performance statistics on SQL1 and discover that Package1 executes slower than expected.
You need to identify the problem that is causing Package1 to execute slowly. Which performance monitor counter data should you review?

• A. Processor% Idle Time
• B. Processor% User Time
• C. MemoryPages/sec
• D. SQLServer:SSIS Pipeline 10.0:Buffers in use

Answer: B

Explanation: The Processor: % User Time value “Corresponds to the percentage of time that the processor spends on executing user processes such as SQL Server.”
In other words, this is the percentage of processor non-idle time spent on user processes
If the Processor:% User Time high values are caused by SQL Server, the reason can be non-optimal SQL Server configuration, excessive querying, schema issues, etc. Further troubleshooting requires finding the process or batch that uses so much processor time
References:
https://www.sqlshack.com/sql-server-processor-performance-metrics-part-2-processor-user-timeprocessor- privilege-time-total-times-thread-metrics/

NEW QUESTION 13
DRAG DROP
You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table.
You have the following requirements:
• The fact table must populate a column named TotalCost that computes the sum of the price and tax columns.
• Before the sum is calculated, any records that have a price of zero must be discarded.
You need to create the SSIS package in SQL Server Data Tools.
In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.)

Answer:

Explanation:

NEW QUESTION 14
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 15
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 answer represents a complete solution. Choose all that apply.)

• A. Slowly Changing Dimension
• B. pivot
• C. Lookup
• D. Union All
• E. Merge

Answer: DE

NEW QUESTION 16
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 changes with every package execution. Common language runtime (CLR) integration cannot be enabled on the SQL Server database where the reference table is located.
You need to configure the Fuzzy Lookup transformation in the most efficient manner. What should you do?

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

Answer: B

NEW QUESTION 17
Which of the following T-SQL functions is not very useful for capturing lineage information?

• A. APP_NAME()
• B. USER_NAME()
• C. DEVICE_STATUS()
• D. SUSER_SNAME()

Answer: C

NEW QUESTION 18
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.

Answer:

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