70-463 Free Practice Test

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?

1. A. Join the two fact tables.
2. B. Merge the fact tables.
3. C. Create a time dimension that can join to both fact tables at their respective granularity.
4. D. Create a surrogate key for the time dimension.

Correct Answer: C
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/

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?

1. A. Design two table
2. B. One table will hold the original customer surrogate key and attribute
3. C. The other table will hold the most recent customer surrogate key and attributes.
4. D. Design a table as a Type 2 SCD dimension that holds the surrogate key for each customer and its attribute
5. E. Limit the number of records per customer within the table to two.
6. F. Design a table to hold the surrogate key for each customer and its attribute
7. G. Add a column to store an indicator that identifies the record as original data or recent data.
8. 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.

Correct Answer: D

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)

1. A. Configure the package to run by using the CallerInfo option.
2. B. Modify the DefaultBufferSize package setting.
3. C. Modify the ForceExecutionResult Package setting.
4. D. Modify the DefaultBufferMaxRows package setting.
5. E. Configure the package to run by using MaxConcurrent optio

Correct Answer: DE

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?

1. A. Create a time mapping table.
2. B. Partition the fact tables by day.
3. C. Create a time dimension that can join to both fact tables at their respective granularity.
4. D. Join the two fact table

Correct Answer: C

You develop a SQL Server Integration Services (SSIS) project by using the Package Deployment Model. A package in the project extracts data from a Windows Azure SQL Database database. The package is deployed to SQL Server.
The package is not producing the desired results.
You need to generate the .mdmp and .tmp debug files in order to troubleshoot the issues. What should you do?

1. A. Execute the catalog.add_data_tap stored procedure with the package execution_id.
2. B. Execute the catalog.create_execution_dump stored procedure with the package execution_id.
3. C. Run the DTEXEC utility with the /Logger option.
4. D. Run the DTEXEC utility with the /Reporting V option.

Correct Answer: C