Types of Fact Tables

An ETL developer must know the types of fact tables he/she will be using during dimensional modeling and ETL development.



Mid table or Factless table:

A table used to resolve many-to-many relationship between two dimension tables called a factless table, in Analysis Services these table are modeled as measure groups that bridges in different dimension tables.

Categorizing Fact Table:

The main type of fact table that provide better distinction to understand the fact table in detail

Transactional Fact Table:

As we have talk a lot about fact table all what we have explain so far about fact table is all belong to transactional fact table, normally a transaction fact table record event and against each event som sort of transaction that take place an any activity perform let say a single sale will populate a row in the fact table containing what product has been sale, what was the time of the sale etc.            

Snapshot Fact Table

In contrast with traditionally used transactional fact table the snapshot fact table records the state of something at different point likewise the total sales for each product for every month here we are not recording any event but a specific situation. The snapshot fact table is useful as they reduce the number of records and make the cube processing faster.

Structural Changes for Fact and Dimension tables:-

Some practitioners suggest that it is not a good idea to change in the warehouse once you have extracted and loaded the data from the OLTP databases because it represent the situation at the time of insertion but it is common observation for the data warehouse that it suffer with changes as we have discussed in SCDs, here we have discuss type of update that are require over the life cycle of data warehouse.

Configurationally updates:-

New measures and dimensions are needed to be added with the existing structure and this is the normal routine in the life cycle of a data warehouse.

Data Updates:-

In some cases as discussed above we may have inherently wrong that fetched from the OLTP databases, this data either deleted or cleaned and fix, there are many reason that how this bad comes to the warehouse.