Aggregated Fact Table

Aggregated Fact Table is the summarized table (or materialized view) propagated from the atomic transaction fact table. The aggregated fact table contains fewer rows from the summarization. Query performance gain can be obtained through it when less details are required in some user analysis. We may build multiple aggregated fact tables in different levels (Daily and Monthly).The aggregated fact tables and their atomic transaction fact table can be co-exists and accessible by users. Some aggregate navigation technologies are available in some DB optimizers / BI Tools (e.g. aggregate aware in BO) to pick the best table for user query.

Consolidated Fact Table

Transactional Fact and Snapshot Fact tables are usually designed from the corresponding individual business process. Consolidated Fact table is usually designed from particular analysis (report) which required consolidation of data (derive) from multiple business processes, which they can be presented in a same grain.

Factless Fact Table

Factless fact table is the fact table which log an business event but does not contain any measure fields. Typical use cases are the attendance and event log. Their row count (log count) is usually the key measurement in analysis. 

We recommend deriving some measures from them to avoid factless if possible. For example, the measure "number of days to close" may be derived from an Issue Log Fact table. 

Accumulating Snapshot Fact Table

This is the fact table to capture stepwise (fixed steps) business processes. It captures the life cycle of a well-defined process. So, the fact table record is first inserted in the first step of the business process and is updated later after completion of the next step(s). (e.g. from ordering to shipping). Typically, there are multiple date dimension fields (e.g. ordering date and shipping date). The number of days between them could be derived as its measure (e.g. days to close).

Periodic Snapshot Fact Table

This kind of fact table represents the snapshot status (i.e. the measures of the state at some point-in-time) periodically (e.g. daily, weekly, monthly). Typically, it is useful for recording account/stock balance. It is the preferred way to capture balance figures because the balance can't be aggregated from the transactions from an individual single period but from the very beginning.

Transactional Fact Table

This is the most common form of fact table. It represents an business process event happened in a particular time and space. Sales fact table is a classic example. Typically, we insert records into the transactional fact table without updating since the transactions never changed after happened. Sometimes, we may also allow updating in the ETL programs for enabling data fixing and reloading.

This kind of table also usually acts as the atomic grain fact table.

Grain of the Fact Table

The primary key of fact table can simply be all its dimension keys (including degenerated dimensions) which are all columns except measures. Sometimes, it doesn't necessary to be all dimension keys since there may be some redundant information in dimensions. We usually don't need to generate a surrogate key for the fact table but simply use the composite primary key from those dimension keys columns. This primary key is also aligned to the granularity of the fact table.