There are basically three fundamental measurement
events, which characterizes all fact tables.
·
Transactional
A transactional table is
the most basic and fundamental. The grain associated with a transactional fact
table is usually specified as "one row per line in a transaction",
e.g., every line on a receipt. Typically a transactional fact table holds data
of the most detailed level, causing it to have a great number of dimensions associated with it.
§ Contains
the actual occurrence of an event, such as a transaction
§ The facts
are at their most atomic level
§ No
aggregation built in
§ Supports
ad hoc analysis
§ Important
for scalability/flexibility
·
Periodic snapshots
The periodic snapshot, as
the name implies, takes a "picture of the moment", where the moment
could be any defined period of time, e.g. a performance summary of a salesman
over the previous month. A periodic snapshot table is dependent on the
transactional table, as it needs the detailed data held in the transactional
fact table in order to deliver the chosen performance output.
§ A record
of a set of values taken at a predictable interval, such as daily or monthly.
§ Semi-additive
facts such as balances are common types of facts in such fact tables.
§ Other
facts might include the total amount and number of transactions over the period
·
Accumulating snapshots
This type of fact table is
used to show the activity of a process that has a well-defined beginning and
end, e.g., the processing of an order. An order moves through specific steps
until it is fully processed. As steps towards fulfilling the order are
completed, the associated row in the fact table is updated. An accumulating
snapshot table often has multiple date columns, each representing a milestone
in the process. Therefore, it's important to have an entry in the associated
date dimension that represents an unknown date, as many of the milestone dates are
unknown at the time of the creation of the row.
Good one.
ReplyDelete