Implementing a SQL Data Warehouse — Question 48

You have a data warehouse named DW1 that contains 20 years of data. DW1 contains a very large fact table. New data is loaded to the fact table monthly.
Many reports query DW1 for the past year of data.
Users frequently report that the reports are slow.
You need to modify the fact table to minimize the amount of time it takes to run the reports. The solution must ensure that other reports can continue to be generated from DW1.
What should you do?

Answer options

• A. Move the historical data to SAS disks and move the data from the past year to SSD disks. Run the ALTER TABLE statement.
• B. Move all the data to SSD disks. Load and archive the data by using partition switching.
• C. Move all the data to SAS disks. Load and archive the data by using partition switching.
• D. Move the historical data to SAS disks and move the data for the past year to SSD disks. Create a distributed partitioned view.

Correct answer: A

Explanation

The correct answer is A because moving historical data to SAS disks optimizes storage costs while using SSDs for recent data enhances performance for current queries. Option B would not address the performance issue effectively as it suggests moving all data to SSDs, which may not be necessary. Option C does not provide a performance enhancement strategy, and D introduces unnecessary complexity with a distributed partitioned view, which may not be needed.