There are
basically three types of fact tables:
- Transaction Fact Table
- Periodic Snapshot Fact Table
- Accumulating Snapshot Fact Table
Transaction Fact Tables
A transactional table is the most basic and fundamental view of the business’s operations These fact tables represent an
event that occurred at an instantaneous point in time. A row exists in the fact
table for a given customer or product only if a transaction event occurred.
Conversely, a given customer or product likely is linked to multiple rows in
the fact table because hopefully the customer or product is involved in more
than one transaction. Transaction data often is structured quite easily into a
dimensional framework. The lowest-level data is the most naturally dimensional
data, supporting analyses that cannot be done on summarized data. Unfortunately,
even with transaction-level data, there is still a whole class of urgent
business questions that are impractical to answer using only transaction
detail.
Periodic Snapshot Fact Tables
Periodic
snapshots are needed to see the cumulative performance of the business at
regular, predictable time intervals. Unlike the transaction fact table, where
we load a row for each event occurrence, with the periodic snapshot, we take a
picture (hence the snapshot terminology) of the activity at the end of a day,
week, or month, then another picture at the end of the next period, and so on. Eg: A performance summary of a salesman over the previous month .
The periodic
snapshots are stacked consecutively into the fact table. The periodic snapshot
fact table often is the only place to easily retrieve a regular, predictable,
trendable view of the key business performance metrics.Periodic snapshots
typically are more complex than individual transactions.
Advantages:When transactions equate to
little pieces of revenue, we can move easily from individual transactions to a
daily snapshot merely by adding up the transactions, such as with the invoice
fact tables from this chapter. In this situation, the periodic snapshot
represents an aggregation of the transactional activity that
occurred
during a time period. We probably would build the daily snapshot only if we
needed a summary table for performance reasons.
Where to use Snapshots?
When you use
your credit card, you are generating transactions, but the credit card issuer’s
primary source of customer revenue occurs when fees or charges are assessed. In
this situation, we can’t rely on transactions alone to analyze revenue
performance. Not only would crawling through the transactions be time-consuming,
but also the logic required to interpret the effect of different kinds of
transactions on revenue or profit can be horrendously complicated. The periodic
snapshot again comes to the rescue to provide management
with a
quick, flexible view of revenue.
Accumulating Snapshot Fact Tables
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.
Accumulating
snapshots almost always have multiple date stamps, representing the predictable
major events or phases that take place during the course of a lifetime. Often
there’s an additional date column that indicates when the snapshot row was last
updated. Since many of these dates are not known when the fact row is first
loaded, we must use surrogate date keys to handle undefined dates.
In sharp
contrast to the other fact table types, we purposely revisit accumulating snapshot
fact table rows to update them. Unlike the periodic snapshot, where we hang
onto the prior snapshot, the accumulating snapshot merely reflects the
accumulated status and metrics. Sometimes accumulating and periodic snapshots
work in conjunction with one another
Also read about OBIA
Also read about OBIA
This is a good explanation of concepts. Can you please explain how we can define these fact tables in OBIEE. Perticularly if we donot have ETL team and OLTP tables are imported as is in physical layer.
ReplyDeleteVery nice explanation but had you added example with data, would have been more easier to understand for beginners.
ReplyDelete