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.

5 Responses to “Types of Fact Tables”

  1. Lesha Galleta Says:

    Good information on fact tables :)

  2. Wonderful guide ,Make loved for it, It is finicky to enjoy a book and invaluable with respect to rewrite,I really bookmark this, for improve go through. Credit ranking on the part of posting.I’m keen the idea.

  3. I Too Like the Blog here. Keep up all the work. I too love to blog. This is great everyone sharing opinions :)

  4. I think you made some good points in Options also. Hold working ,nice job!

  5. Your grasp of this topic is incredible. It really touched base to me and I’m glad I located this material. Thank you very significantly.

Leave a Reply

Spam Protection by WP-SpamFree

Einfobuzz Site Map