Informatica Certification Training Course
- 20k Enrolled Learners
- Weekend
- Live Class
(7850)
What is a Fact Table?
A Fact Table is a central table in a star schema of a data warehouse. It is an important concept required for Data Warehousing and BI Certification. A fact table stores quantitative information for analysis and is often denormalized. A fact table works with dimension tables and it holds the data to be analyzed and a dimension table stores data about the ways in which the data can be analyzed.
Thus, a fact table consists of two types of columns. The foreign keys column allows to join with dimension tables and the measure columns contain the data that is being analyzed.
Transaction Fact Tables
A Transaction table is the most basic and fundamental view of business 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 has occurred.
A given customer or product is likely linked to multiple rows in the fact table because 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 natural 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.
Snapshot Fact tables
This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table.
Example: Daily balances fact can be summed up through the customers’ dimension but not through the time dimension.
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 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. Example: A performance summary of a salesman over the previous month.
Accumulated Fact Table
This type of fact table is used to show the activity of a process that has a well-defined beginning and end. For example, 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 was first loaded, we must use surrogate date keys to handle undefined dates.
In sharp contrast to the other fact table types, we 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.
Edureka has a specially curated course on Data Warehousing that will help you master Fact Tables and other important concepts and their implementations. Click here to get started.
Got a question for us? Please mention them in the comments section and we will get back to you.
Related Posts:
