By using this technique, your data

By using this technique, your data warehouse will be up all the time. Dropping and
re-creating a view takes only a few seconds. This technique is commonly used to increase the
data warehouse availability, both in packaged applications running on a normalized data
warehouse as well in custom-developed data warehouses.
Summary Tables
Out of the many things that can improve data warehouse performance, the summary table
is at the top of the list. (The second one is table partitioning, and the third one is indexing.)
This is because the data that the users need is already precalculated. We will discuss these
three things one by one in the next three main sections.
Say you have a report that displays a graph of the summary of weekly sales data for the
last 12 weeks by product group or by stores. Let’s say that within a week you have 1 million
rows. So the stored procedure behind that report must process 1 million rows and display the
data on the screen as a graph, while the user waits. Let’s say that this takes 45 seconds.
If you produce a table called weekly_sales_summary, which runs the same query, and put
the result in this summary table, when the user accesses the report, the stored procedure or
the SQL statement behind the report needs to access only this summary table. The response
time of the report is greatly improved; it’s probably only one or two seconds now.
The summary table needs to be at least 10 times smaller (in terms of the number of rows),
ideally 100 times smaller or more. Otherwise, there is no point creating a summary table. If
you wonder where 10 and 100 come from, the guideline to determine whether it is worth creating
the summary table is to compare the performance improvement with the time required
to build it. What I meant by “performance improvement” is how much faster querying the
summary table compared to querying the underlying table directly is. If the performance
improvement is insignificant (say less than 10 percent), if it takes a long time to build the table
(say one hour), and if the query is not used frequently, then it’s not worth building a summary
table. Remember that a summary table needs to be updated every time the underlying fact
table is updated. If the number of rows in the summary table is much less than the underlying
table, such as 10 or 100 times smaller, usually the performance improvement is significant.
The ideal time to refresh the summary tables is immediately after the population of the underlying
fact table.
As we discussed in Chapter 5, we have three types of fact table: the transactional fact
table, the periodic snapshot fact table, and the accumulative snapshot fact table. A summary
fact table is suitable for a transaction fact table. For a periodic snapshot fact table, we have the
“latest summary table.” A latest summary table contains only today’s version of the snapshot.
For example, the Subscription Sales fact table contains a daily snapshot of all the membership
subscriptions. If we have 1 million customer subscriptions, the table would contain 1 million
records every day. Over two years, there would be 730 million rows in the fact table. In this
case, we can create a table called fact_subscription_sales_latest that contains only today’s
rows so it contains only 1 million rows and is therefore faster. This latest summary table is useful
if you need information that depends on the today value only.