Types of Facts in Data warehouse
What is Fact Table?
Fact Table consists of Measures/Metrics/Facts of a business process. Typically it contains two types of data. Foreign keys which are holding the primary key of Dimension Tables and the second type is the sales,revenue, unit of product etc. measures.
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
Measures Types:
Additive: The measures which can be added across all the dimensions is known as Additive Measures.
In the above table , you can add the sales amount with respect to Product,Customer and also by Date.
Semi-Additive: The measures which can be added across some of the dimensions is known as Semi-Additive.
Department wise we can calculate the total number of employees in that organization. But as per the Date you cannot say total employees in Sales department is 29+35=64 which is a wrong data. You can calculate total number of employees on 20150901 as 35+150=185. So the measures are possible only for some of the dimensions.
Non-Additive: The measures which cannot be added across any dimension is known as Non-Additive.
We cannot calculate total profit margin with any of the dimension as shown in above table.
Fact less fact: This is not another type. It is like it may happen in real time that the fact will not store any measure kind of data. It is like set of events information. This is known as Fact less fact.
In the above table, there is no fact, All the three columns derived from dimensions only. There is no facts in this scenario but we can retrieve the information how many students attended Java course on particular time etc.
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
Fact Types:
1)Transactional: The most common type of fact table, and each record is an event of transaction which will involve most of the dimensions. So it contains robust data, which enables maximum slicing and dicing of the data.
2)Periodic snapshots: The measurements occurring over a standard period, such as a day, a week, or a month. These fact tables are uniformly dense in their foreign keys because even if no activity takes place during the period, a row is typically inserted in the fact table containing a zero or null for each fact.
3)Accumulating snapshots : The measurement events occurring at predictable steps between the beginning and the end of a process. e.g., 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. An accumulating snapshot table often has multiple date columns, each representing a milestone in the process.
Thank You
Lalitha
Best Open Source Business Intelligence Software Helical Insight is Here
