70-463 Exam Questions - Online Test

70-463 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Your success in Microsoft microsoft 70 463 is our sole target and we develop all our 70 463 exam braindumps in a way that facilitates the attainment of this target. Not only is our microsoft 70 463 study material the best you can find, it is also the most detailed and the most updated. 70 463 exam Practice Exams for Microsoft SQL Server microsoft 70 463 are written to the highest standards of technical accuracy.

Q1. 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 conformed dimension for the geography dimension. 

B. Implement change tracking. 

C. Create a degenerate dimension for the geography dimension. 

D. Create a Type 2 slowly changing dimension for the geography dimension. 

Answer: A 

Q2. 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 model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created. 

B. Use a project deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package. 

C. Use a package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime. 

D. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments. 

Answer: A 

Q3. 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 date. Insert new data directly into the fact table and delete old data directly from the fact table. 

B. Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data. 

C. Partition the destination fact table by customer. Use partition switching both to remove old data and to load new data into each partition. 

D. Partition the destination fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table. 

Answer: B 

Q4. 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. Create a time dimension that can join to both fact tables at their respective granularity. 

D. Create a surrogate key for the time dimension. 

Answer: C 

Explanation: References: http://msdn.microsoft.com/en-us/library/ms174537.aspx http://technet.microsoft.com/en-us/library/ms174832.aspx http://msdn.microsoft.com/en-us/library/ms174884.aspx http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vs-natural-keys-for-primary-key/ http://www.agiledata.org/essays/keys.html http://www.databasejournal.com/features/mssql/article.php/3922066/SQL-Server-Natural-Key-Verses-Surrogate-Key.htm http://www.jamesserra.com/archive/2012/01/surrogate-keys/ 

Q5. You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL Azure database. The project uses the Package Deployment 

Model. This project is deployed to SQL Server on a test server. 

You need to ensure that the project is deployed to the SSIS catalog on the production server. 

What should you do? 

A. Open a command prompt and run the dtexec /dumperror /conn command. 

B. Create a reusable custom logging component and use it in the SSIS project. 

C. Open a command prompt and run the gacutil command. 

D. Add an OnError event handler to the SSIS project. 

E. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility. 

F. Open a command prompt and run the dtexec /rep /conn command. 

G. Open a command prompt and run the dtutil /copy command. 

H. Use an msi file to deploy the package on the server. 

I. Configure the SSIS solution to use the Project Deployment Model. 

J. Configure the output of a component in the package data flow to use a data tap. 

K. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server. 

Answer: I 

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

http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx 

Q6. You are developing a SQL Server Integration Services (SSIS) package. The data source 

for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data. 

The CDC Source component has the following requirements: 

. The output must include metadata columns that indicate which source columns have changed. . The output must return only one change row per source row that is modified in the current CDC processing range. 

You need to configure the CDC Source component. 

Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.) 

Answer: 

Q7. Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and nothing is processed. 

You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts. 

What should you do? 

A. Create a reusable custom logging component. 

B. Use the gacutil command. 

C. Use the Project Deployment Wizard. 

D. Run the package by using the dtexec /rep /conn command. 

E. Add a data tap on the output of a component in the package data flow. 

F. Create an OnError event handler. 

G. Use the dtutil /copy command. 

H. Deploy the package by using an msi file. 

I. Run the package by using the dtexec /dumperror /conn command. 

J. Run the package by using the dtexecui.exe utility and the SQL Log provider. 

K. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration. 

Answer: J 

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

http://msdn.microsoft.com/en-us/library/hh231187.aspx 

Q8. 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. Enable logging to an XML file in the package control flow for the Onlnformation event. After 10 executions, view the XML file. 

B. After 10 executions, view the job history for the SQL Server Agent job. 

C. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package. 

D. Enable logging to an XML file in the package control flow for the OnPostExecute event. After 10 executions, view the XML file. 

Answer: C 

Q9. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. 

You are developing the part of the SSIS package that populates the ProjectDates dimension table. The business key of the ProjectDates table is the ProjectName column. 

The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table: 

ExpectedStartDate - New values should be tracked over time. 

ActualStartDate - New values should not be accepted. 

ExpectedEndDate - New values should replace existing values. 

ActualEndDate - New values should be tracked over time. 

You use the SSIS Slowly Changing Dimension Transformation. 

You must configure the Change Type value for each source column. 

Which Change Type values should you select? (To answer, drag the appropriate value from the list of values to the correct location or locations in the answer area.) 

Answer: 

Q10. You are designing a data warehouse that uses SQL Server 2012. You are preparing to update the contents of a fact table that contains a non-clustered columnstore index. 

You need to run an update statement against the table. 

What should you do first? 

A. Pause the columnstore index. 

B. Change the recovery model of the database to Bulk-logged. 

C. Change the non-clustered columnstore index to be a clustered columnstore index. 

D. Drop the columnstore index. 

Answer: A