70-463 · Question #98
You are reviewing the design of an existing fact table named factSales, which is loaded from a SQL Azure database by a SQL Server Integration Services (SSIS) package each day. The fact table has…
The correct answer is C. Change the granularity of the fact table to month. When reporting requirements change so that only monthly aggregates are needed, reducing the granularity of the fact table from daily to monthly eliminates billions of unnecessary rows and directly addresses both performance and storage growth.
Question
You are reviewing the design of an existing fact table named factSales, which is loaded from a SQL Azure database by a SQL Server Integration Services (SSIS) package each day. The fact table has approximately 1 billion rows and is dimensioned by product, sales date, and sales time of day. The database administrator is concerned about the growth of the database. Users report 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
- APartition the table by product type.
- BCreate a view over the fact table to aggregate sales by month.
- CChange the granularity of the fact table to month.
- DCreate an indexed view over the fact table to aggregate sales by month.
How the community answered
(30 responses)- A7% (2)
- B23% (7)
- C63% (19)
- D7% (2)
Why each option
When reporting requirements change so that only monthly aggregates are needed, reducing the granularity of the fact table from daily to monthly eliminates billions of unnecessary rows and directly addresses both performance and storage growth.
Partitioning by product type reorganizes existing data but does not reduce the total number of rows or the overall size of the database.
A standard view is a virtual object that executes the underlying query at runtime; it does not persist aggregated results or reduce physical storage growth.
Changing the granularity of factSales from daily transactions to monthly aggregates physically reduces the number of rows from approximately 1 billion to a fraction of that, shrinking storage and dramatically improving query performance. Because the only remaining report groups by month and year, daily-level granularity provides no analytical value and storing it wastes resources. This also eliminates the need for the sales time-of-day dimension, further reducing table width.
An indexed view pre-materializes aggregates and can improve query speed, but the underlying billion-row fact table still exists and continues to grow, so database size is not reduced.
Concept tested: Fact table granularity design and storage optimization
Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models-olap-logical-dimension-objects/dimensions-introduction
Topics
Community Discussion
No community discussion yet for this question.