70-463 · Question #75
You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The…
The correct answer is C. Change the granularity of the fact table to month. The only remaining report requires monthly aggregation (sale month, sale year) by product name. The current fact table records at daily granularity with a time-of-day dimension, resulting in ~4 billion rows. Changing the granularity to month means pre-aggregating rows to…
Question
You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day. The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table. You need to reduce the report processing time and minimize the growth of the database. What should you do?
Exhibit
Options
- ACreate an indexed view over the fact table to sum orderTotal by month.
- BCreate a view over the fact table to sum orderTotal by month.
- CChange the granularity of the fact table to month.
- DPartition the fact table by productKey.
How the community answered
(28 responses)- A7% (2)
- B25% (7)
- C57% (16)
- D11% (3)
Explanation
The only remaining report requires monthly aggregation (sale month, sale year) by product name. The current fact table records at daily granularity with a time-of-day dimension, resulting in ~4 billion rows. Changing the granularity to month means pre-aggregating rows to month-level, which could reduce the row count by a factor of 20–31 (days per month). This directly minimizes database growth and dramatically reduces query scan time - both stated goals. Option A (indexed view) improves query speed but does not reduce storage growth. Option B (non-materialized view) provides no physical performance benefit on a 4-billion-row scan. Option D (partitioning by productKey) aids maintenance and can improve targeted queries but does not reduce total row count or storage size.
Topics
Community Discussion
No community discussion yet for this question.