Free Microsoft 70-463 Exam Dumps Questions & Answers

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.  Create a surrogate key for the time dimension.
• C.  Create a time dimension that can join to both fact tables at their respective granularity.
• D.  Merge the fact tables.

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.)

Correct Answer:

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/en-us/library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx
http://msdn.microsoft.com/en-us/library/ms173763.aspx

Your company has several line-of-business applications. The applications use a server that has SQL Server installed and contains several databases.
You need that the business applications can access curated and validated data from the databases.
Which features should you deploy to the server?

• A.  SQL Server Analysis Services (SSAS) dimension hierarchies.
• B.  Master Data Services (MDS) subscriptions.
• C.  Data Quality Services (DQS)
• D.  SQL Server Integration Services (SSIS)

You have a SQL Server Integration Services (SSIS) package that uses an Execute SQL task to load data to a data warehouse.
You need to log all of the statements that execute for the data warehouse. The solution must store the log on a server that has SQL Server installed.
Which SSIS log provider and event type should you use? To answer, select the appropriate options in the answer area.

Correct Answer:

Explanation

SSIS log provider: SQL Server
Event type: ExecutesSQLExecutingQuery
The following describes the custom log entry for the Execute SQL task:
ExecuteSQLExecutingQuery provides information about the execution phases of the SQL statement. Log entries are written when the task acquires connection to the database, when the task starts to prepare the SQL statement, and after the execution of the SQL statement is completed. The log entry for the prepare phase includes the SQL statement that the task uses.
Note: You can log the calls that the Execute SQL task makes to external data providers. You can use this logging capability to troubleshoot the SQL commands that the Execute SQL task runs. To log the calls that the Execute SQL task makes to external data providers, enable package logging and select the Diagnostic event at the package level.

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 Agent
• B.  SSIS Upgrade Wizard in SQL Server Management Studio
• C.  SQL Server Configuration Manager
• D.  SQL Server DTExecUI utility (dtexecui.exe)