Fact tables are not always based on transactions alone. Transactional fact tables
are the most commonly used type, but there are a couple of variations you
need to be aware of. The first one is the accumulating periodic snapshot table.
This sounds more complicated than it actually is; the only difference between a regular fact table and this one are the date fields tied to a specific process that
need to be updated when new events occur. Take, for instance, World Class
Movie’s rental process where DVDs are ordered on an order date, shipped
on a ship date, and returned on a return date. Because we don’t want to wait
until the process is completed to begin entering the data, we start by adding
the fact row to the data warehouse when the order is placed and subsequently
accumulate this row with the correct dates as they become known.
A rather different type of fact table is the periodic snapshot, which is like a picture taken every day, week or month. Periodic snapshots are used when you need to freeze data that are only indirectly linked to transactions. A good example in the WCM data warehouse is the DVD inventory. Inventory levels change constantly and there is no way to keep track of these over time by using a transactional fact table. The only option here is to periodically take a snapshot that enables you to report on inventory level increase or decrease over time.
Another way of looking at transaction and snapshot fact tables is by the types of measures that they contain. Measures in a regular transaction table, like sales revenue, can be summarized across all dimensions, including time. These are referred to as additive facts or measures. The measures in a periodic snapshot table cannot be easily summarized, at least not when multiple periods are involved. For example, summarizing the weekly SKU articles stock levels does not give you a total yearly stock! These measures are called semi-additive, meaning that you can add them together but need to include a time or period filter to slice the data.
The last type of measure is the non-additive fact. A good example of this
is room temperature. It doesn’t make sense to summarize the temperature
of different rooms or different periods, although you can always calculate
averages.
A rather different type of fact table is the periodic snapshot, which is like a picture taken every day, week or month. Periodic snapshots are used when you need to freeze data that are only indirectly linked to transactions. A good example in the WCM data warehouse is the DVD inventory. Inventory levels change constantly and there is no way to keep track of these over time by using a transactional fact table. The only option here is to periodically take a snapshot that enables you to report on inventory level increase or decrease over time.
Another way of looking at transaction and snapshot fact tables is by the types of measures that they contain. Measures in a regular transaction table, like sales revenue, can be summarized across all dimensions, including time. These are referred to as additive facts or measures. The measures in a periodic snapshot table cannot be easily summarized, at least not when multiple periods are involved. For example, summarizing the weekly SKU articles stock levels does not give you a total yearly stock! These measures are called semi-additive, meaning that you can add them together but need to include a time or period filter to slice the data.
