Exam 70-767 topic 1 question 82 discussion

Actual exam question from Microsoft's 70-767

Question #: 82
Topic #: 1

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?

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.

Show Suggested Answer

Suggested Answer: A 🗳️
We use ALTER TABLE to partition the table.
Incorrect Answers:
D: A Distributed Partitioned View contains participating tables from multiple SQL Server instances, which can be used to distribute the data processing load across multiple servers. Another advantage for the SQL Server Partitioned Views is that the underlying tables can participate in more than one Partitioned View, which could be helpful in some implementations.

by Vlad0 at July 21, 2020, 7:09 a.m.

Comments

Submit Cancel

Vlad0

4 years, 11 months ago

Not sure to understand why it's not D.

upvoted 2 times

Cococo

4 years, 9 months ago

One table only, making it a partition table is enough. Other reports must be able to use the table. If you create a view and optimise it somehow reports will use the same table anyway, which you didn't "improve".

upvoted 1 times

...

Slava_bcd81

4 years, 8 months ago

it's harder to manage data using partitioned views, for example move partitions boundaries

upvoted 1 times

...