You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millionsof new
records are loaded into the data warehouse weekly, outside of business hours. Most queries are generated by
reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.
You need to partition the table to maximize the performance of queries. What should you do? (More thanone
answer choice may achieve the goal. Select the BESTanswer.)
A.
Partition the fact table by month, and compress each partition.
B.
Partition the fact table by week.
C.
Partition the fact table by year.
D.
Partition the fact table by day, and compress each partition.
D.
Partition the fact table by day, and compress each partition.
http://technet.microsoft.com/en-us/library/dd578580(v=sql.100).aspx
Specifically, the linked whitepaper seems to point to “by day” with the following three recommendations:
• Ensure that indexes are aligned with the partitioned table, and that indexed views are partition-aligned.
• Ensure that queries against the partitioned tables have filters based on the partition column.
• On data warehouse joins, keep the join column simple (such as an integer or date) and explicit, so as to take advantage of bitmap filtering for star joins.