Databases Reference
In-Depth Information
deliveries, and confirmations. For a restaurant a Transaction table could
contain all the dining orders. For a retail company a Transaction table
could contain all the sales details that occurred. Granular detailed data
is a valuable asset to a data warehouse, unless you need a weekly sales
report. When you need to know how many pounds of potatoes sold
every week for the past two years, granular transaction data about the
individual sale of potatoes only causes the data warehouse RDBMS to
work harder to provide a weekly sales report. For that reason, the most
obvious performance optimization for a data warehouse is Summary
tables.
Summary tables hold the result set of an Insert/Select query that sums
the additive quantitative portions of a Transaction table. A summation
query also includes a GROUP BY statement. The GROUP BY statement
defines the level of granularity of the Summary table. If the summation
query groups retail locations by a geographic area (i.e., region), then the
geographic granularity of the Summary table will be the region, whereas
the geographic granularity of the Transaction table was the retail location.
If the summation query groups products by a hierarchy (i.e., department),
then the hierarchical granularity of the Summary table will be the depart-
ment, whereas the hierarchical granularity of the Transaction table was
the individual product.
Summary tables can, and often are, a summation of time. All the trans-
actions that occurred during a d ay can be summarized to the level of
granularity of a day (i.e., day-level summary), week (i.e., week-level sum-
mary), or any other time cycle within the enterprise. The time period of
a Summary table may, or may not, override the time variance of the data
included in the Summary table. If, for example, a Summary table is defined
as a week-level summary, that table would summarize all the transactions
that occurred from Sunday through Saturday. Suppose a product in those
transactions was blue on Sunday, green on Monday, red on Tuesday, yel-
low on Wednesday, black on Thursday, white on Friday, and mauve on
Saturday. So for the week-level summary of transactions including that
product, what is the color of that product for the week? Is it the first color?
Is it the last color?
The answer is based on the time variance of the Summary table. If the
Summary table is a Type 1 Summary table, then all dimensions will be
represented by their Entity Key. The product in question, which seems to
change color daily, will be represented by its Entity Key. What is its color?
It is the color it is right now. Because it is a Type 1 Summary table, by
