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?
A.
Create an indexed view over the fact table to sum orderTotal by month.
B.
Create a view over the fact table to sum orderTotal by month.
C.
Change the granularity of the fact table to month.
D.
Partition the fact table by productKey.
C
C is absolutely right. We need to reduce the size/growth of the fact table…
“You need to reduce the report processing time and minimize the growth of the database”
While creating a view will improve the creation of reports, it will have little effect if the size of the fact table still continues to grow unabated; so, the most effective action to take is to drastically reduce the growth of the table by changing the GRANULARITY factor (which changes the captured data from ‘daily’ to ‘monthly’).