We will now look at the different types of fact tables used for building data warehouses . There are three major fact table types that we will discuss today: transaction snapshot, periodic snapshot, and accumulating snapshot fact table. Each of these three kinds of fact tables are used for different purposes. Generally more than one type of fact table is used in conjunction with another to obtain a "holistic business picture".
Transaction fact tables record information at the lowest possible grain level: the transaction. Transaction fact tables are therefore more densely populated than the other types. They also capture the most amount of information pertaining to a particular transaction. A date key is associated with a transaction fact table and it represents the specific date on which the transaction was "processed" or "completed". Given below is an example of a basic e-commerce order fact table.
Periodic snapshot fact tables record information over a period of time. They are one of the two kinds of time-based fact tables that can be implemented. Periodic snapshots could aggregate information over a day, week, month, or even year. Since data is aggregated over a period of time, this fact table is clearly of a higher grain level than the transaction fact table. Periodic snapshot is useful to obtain summary level performance of a business process over a period of time to drive decision making. But to get complete information at the transaction level we will still need to couple this data with a transaction fact table.
Periodic snapshots are also unique because they contain semi-additive facts. Semi-additive facts can be aggregated across some dimensions but not across others; the latter is most often a time dimension. An example of a semi additive fact would be the final state of inventory in a warehouse at the end of the week. It would not make sense to add the inventory levels across different weeks but it would make sense to add this fact across different warehouses at the end of a particular week.
Accumulating snapshot fact tables are the third type of fact tables. They can be described similar to transaction facts tables or periodic snapshot fact tables depending on the way we look at them. They are similar to transaction tables because every row contains information of a single event and are similar to periodic snapshot tables because they record information over the entire lifetime of the event. Accumulating snapshots require multiple updates on the same row for every "milestone" of the event. As a result, a single row has multiple date keys to capture the milestones for the event. The different stages of a particular customer order with dates corresponding to order entry, shipment processing, order execution and shipment completion could be an example for the e-commerce scenario.
Although accumulating snapshots have multiple date key facts on a single row, they still reference a single date dimension table which acts as a role-playing dimension.
Transaction fact tables record information at the lowest possible grain level: the transaction. Transaction fact tables are therefore more densely populated than the other types. They also capture the most amount of information pertaining to a particular transaction. A date key is associated with a transaction fact table and it represents the specific date on which the transaction was "processed" or "completed". Given below is an example of a basic e-commerce order fact table.
Periodic snapshots are also unique because they contain semi-additive facts. Semi-additive facts can be aggregated across some dimensions but not across others; the latter is most often a time dimension. An example of a semi additive fact would be the final state of inventory in a warehouse at the end of the week. It would not make sense to add the inventory levels across different weeks but it would make sense to add this fact across different warehouses at the end of a particular week.
Accumulating snapshot fact tables are the third type of fact tables. They can be described similar to transaction facts tables or periodic snapshot fact tables depending on the way we look at them. They are similar to transaction tables because every row contains information of a single event and are similar to periodic snapshot tables because they record information over the entire lifetime of the event. Accumulating snapshots require multiple updates on the same row for every "milestone" of the event. As a result, a single row has multiple date keys to capture the milestones for the event. The different stages of a particular customer order with dates corresponding to order entry, shipment processing, order execution and shipment completion could be an example for the e-commerce scenario.
Although accumulating snapshots have multiple date key facts on a single row, they still reference a single date dimension table which acts as a role-playing dimension.
With this blog piece hopefully we should have a better understanding of the different types of fact tables and their possible applications
HI you have a good blog i like your blog as it have all the good information. for more information Click on kurtis online
ReplyDelete