**This information is gathered from blogs of oaktonsoftware.It's purely for interesting reads and not of any commercial use.
Snapshot Fact Tables
A snapshot is a fact table that periodically samples a measurement. A snapshot always includes at least one measurement of status, such as a balance or level. A transaction fact table, in contrast, captures measurements as they occur.
The snapshot is useful for describing the effect of transactions, rather than transactions themselves. The best example is your bank account. Its all well and good if you have a record of each deposit, check, interest payment, fee, direct debit, and so forth. But what is your balance? Aggregating all your transactions is not the most expedient way to figure that out.
Sometimes Snapshots Summarize Transactions, but not Always
People sometimes think of snapshots as aggregates because they can be constructed from a table that contains transactions. That is certainly true in the case of account balances. If you have the transactions, you can compute the snapshots.
In this sense, you might be justified in thinking of a snapshot as an aggregate. But I don't. I'll explain why in just a second.
First, though, it is important to understand that not all snapshots are constructed from transactions. Sometimes, the number of transactions is too numerous to keep around. But in other cases,there simply aren't any. For example, we can't make use of transactions to describe the ambient temperature on the factory floor, or the level of pressure inside an industrial device. In these cases, the snapshot is clearly not a summary.
It really depends on how you define an aggregate.
The word aggregate is normally reserved for a summary table that does not transform the structure of its corresponding base table. For example, suppose account transactions are recorded in a fact table, with a fact called transaction_amount. A monthly aggregate would have exactly the same fact. Still called transaction_amount, is the same as the fact in the base table. The only difference is in level of detail: it represents a month's worth of transactions, rather than individual transactions. It is the same measurement.
Account balance (or other status measurements like levels) is a not the same as what the base fact table records. It is not an aggregation of transaction that occurred during the snapshot period; it is something more. It describes the net effect of the account transactions. It is a different measurement.
Conclusion
I use the term derived table to describe any table in a dimensional design uses another as its source. I save the word aggregate for non-transformative summaries.
To sum things up, then:
Yes, a snapshot sometimes summarizes a transaction fact table.
But if you accept these definitions for aggregate tables and derived tables, the answer is different:
A snapshot is is sometimes a derived table, but never an aggregate.
Snapshot Fact Tables
A snapshot is a fact table that periodically samples a measurement. A snapshot always includes at least one measurement of status, such as a balance or level. A transaction fact table, in contrast, captures measurements as they occur.
The snapshot is useful for describing the effect of transactions, rather than transactions themselves. The best example is your bank account. Its all well and good if you have a record of each deposit, check, interest payment, fee, direct debit, and so forth. But what is your balance? Aggregating all your transactions is not the most expedient way to figure that out.
Sometimes Snapshots Summarize Transactions, but not Always
People sometimes think of snapshots as aggregates because they can be constructed from a table that contains transactions. That is certainly true in the case of account balances. If you have the transactions, you can compute the snapshots.
In this sense, you might be justified in thinking of a snapshot as an aggregate. But I don't. I'll explain why in just a second.
First, though, it is important to understand that not all snapshots are constructed from transactions. Sometimes, the number of transactions is too numerous to keep around. But in other cases,there simply aren't any. For example, we can't make use of transactions to describe the ambient temperature on the factory floor, or the level of pressure inside an industrial device. In these cases, the snapshot is clearly not a summary.
It really depends on how you define an aggregate.
The word aggregate is normally reserved for a summary table that does not transform the structure of its corresponding base table. For example, suppose account transactions are recorded in a fact table, with a fact called transaction_amount. A monthly aggregate would have exactly the same fact. Still called transaction_amount, is the same as the fact in the base table. The only difference is in level of detail: it represents a month's worth of transactions, rather than individual transactions. It is the same measurement.
Account balance (or other status measurements like levels) is a not the same as what the base fact table records. It is not an aggregation of transaction that occurred during the snapshot period; it is something more. It describes the net effect of the account transactions. It is a different measurement.
Conclusion
I use the term derived table to describe any table in a dimensional design uses another as its source. I save the word aggregate for non-transformative summaries.
To sum things up, then:
Yes, a snapshot sometimes summarizes a transaction fact table.
But if you accept these definitions for aggregate tables and derived tables, the answer is different:
A snapshot is is sometimes a derived table, but never an aggregate.
No comments:
Post a Comment