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?
A.
Partition the table by product type.
B.
Create a view over the fact table to aggregate sales by month.
C.
Change the granularity of the fact table to month.
D.
Create an indexed view over the fact table to aggregate sales by month.
Explanation:
Shouldn’t the answer be either B or D..?
It is a great idea, but the subject here is that you should optimize growth. The solutions you indicated are focused on query execution speed using views.
Answer C is actually better because you decrease rows in the table as you aggregate them before inserting them to the table (no need to aggregate each time) since you need monthly reports only. Less rows mean less time reading/filtering the table.
C