Lets start with the word called ‘granularity’ or ‘the grain’ first and then we understand the Fact. Later we will proceed with the types of Facts.
The lowest level of detail for a particular business purpose or for an attribute is called the grain or granularity.
For example, if we consider the date/time dimension, it could start at the year (top most), month, quarter, period, week, day, hour, minute, second, hundredth’s of seconds (lowest) level of granularity. Most data warehouses do not go to the second or hundredths of seconds level, but it could be possible based on the business processes and the requirements.
A Fact table contains the Fact’s data, which are also termed as measures at a detailed level or aggregated (grouped, based on a key value) level. The fact attributes are usually numbers which can be used for calculating and grouping to address the business questions. A simple example of facts are the measures/numbers like the sales, cost, profit, loss etc.
Technically, a Fact table has two types of columns, foreign keys to dimension tables and measures those that contain numeric Facts. Based on the business need and necessity there can be more attributes (technical/business related attributes) apart form the dimension keys and the numeric attributes.
We can divide the Facts in to these three types.
Certain measures/numbers are completely non-additive, such as ratios. Non-additive Facts are Facts that cannot be summed up for any of the dimensions present in the Fact table.
Eg: Facts which have percentages, Ratios calculated.
Semi-additive Facts are Facts that can be summed up for some of the dimensions in the Fact table, but not the others. One of the usual examples for this is are the current account or savings account balance amounts are common semi-additive facts. We can get/generate a balance amount from the overall transactions , but it doesn’t make any sense to add (group) the balance amounts from different dates or months or across the time dimension.
Additive Facts are Facts that can be summed up through all of the dimensions in the Fact table.
For example, if there is a retail store and if we want to identify the total sales which have happened in the last six months, we can group the records of the last six months and get the summed up (aggregated) value.
In this case, Sales or the Sales amount is an additive fact, because we can sum up (group) the fact records with other related dimensions (product, customer, supplier etc) present in the fact table.
Now, based on the types of facts discussed, lets classify these in to different types of fact tables.
Transaction Fact Tables:
This is the most common type of fact table. The grain of this type is one row per transaction, or one row per line on a transaction. The grain of a transaction fact table is a point in time. These have the lowest level of content per record.
As a transaction happens, extensive context about it is captured. This leads to create a lot of detailed dimension tables, which is good in a lot of ways for data insights.
Transactional data is usually not updated as it’s captured at a lowest granular level. There can be business cases where updates are possible but that is not usual.
This kind of Fact data will help in answering the questions like, How many transactions are done per day by a specific business process? OR
What is the average amount withdrawn in one day per customer?
Periodic Snapshot Fact Tables:
Periodic snapshot tables record the cumulative performance of the business at predefined periods of time. A predetermined interval for taking snapshots is the key: daily, weekly, monthly, etc. The results are saved in the periodic snapshot fact table. This can be sometimes in the same fact table or this also be moved into different tables based on the volumes and the design for the reporting purposes.
In this type of Fact tables, we can incorporate any information that describes activity over a period of time like daily, weekly, monthly, etc.
Just as transaction fact tables have many dimensions, the snapshot usually has fewer dimensions which leads it not to have the lower grain of data.
This kind of Fact data will help in answering the questions like, How many days does a customer carry a positive account balance in one month? OR What is the average balance of a specific type of customer each month? etc.
Cumulative Fact Tables:
This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative fact table.
Eg: Sales fact
Factless Fact Tables:
In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called “Factless Fact tables”.
Eg: A fact table which has only product key and date key is a factless fact. There are no measures in this table. But still you can get the number products sold over a period of time.