Measure or Fact Table
Fact tables contain measurements of individual business processes.
It contains two sections in the table
(a) Foreign Keys to Dimensions
(b) Facts or Measures
Fully Additive Facts:
It can be summarized across any and all dimensions information stored in Fact Table.
Example:
Revenue Quantity
Semi Additive Facts:
Semi Additive Facts are Facts that can be summed up for some of the dimensions in the Fact Table.
Example:
Inventory Quantities can be summed up through the model and Dealer dimension but not through the Time Dimension
Non Additive Facts:
Non Additive Facts are Facts that cannot be summed across any dimensions in the Fact Table.
All Ratios are Non Additive Facts.
Example:
In the below Diagram shows Margin rate is non additive.
Margin rate=Margin_Amount / Revenue
Fact less Fact Table:
A Fact Table contains no measures or facts that type of Facts are known as Fact less Fact Table. Sometimes store a “1” for convenience.
Example:
A fact table which has only key elements is a fact less fact. There are no measures in this table
Fact tables contain measurements of individual business processes.
It contains two sections in the table
(a) Foreign Keys to Dimensions
(b) Facts or Measures
Fully Additive Facts:
It can be summarized across any and all dimensions information stored in Fact Table.
Example:
Revenue Quantity
Semi Additive Facts:
Semi Additive Facts are Facts that can be summed up for some of the dimensions in the Fact Table.
Example:
Inventory Quantities can be summed up through the model and Dealer dimension but not through the Time Dimension
Non Additive Facts:
Non Additive Facts are Facts that cannot be summed across any dimensions in the Fact Table.
All Ratios are Non Additive Facts.
Example:
In the below Diagram shows Margin rate is non additive.
Margin rate=Margin_Amount / Revenue
Fact less Fact Table:
A Fact Table contains no measures or facts that type of Facts are known as Fact less Fact Table. Sometimes store a “1” for convenience.
Example:
A fact table which has only key elements is a fact less fact. There are no measures in this table
It contains two sections in the table
(a) Foreign Keys to Dimensions
(b) Facts or Measures
Fully Additive Facts:
It can be summarized across any and all dimensions information stored in Fact Table.
Example:
Revenue Quantity
Semi Additive Facts:
Semi Additive Facts are Facts that can be summed up for some of the dimensions in the Fact Table.
Example:
Inventory Quantities can be summed up through the model and Dealer dimension but not through the Time Dimension
Non Additive Facts are Facts that cannot be summed across any dimensions in the Fact Table.
All Ratios are Non Additive Facts.
Example:
In the below Diagram shows Margin rate is non additive.
Margin rate=Margin_Amount / Revenue
Fact less Fact Table:
A Fact Table contains no measures or facts that type of Facts are known as Fact less Fact Table. Sometimes store a “1” for convenience.
Example:
A fact table which has only key elements is a fact less fact. There are no measures in this table
FACT overview
What is fact?
- Stores a value which is measurable, indicates some numerical figure
- e.g. SalesFact, InvoiceFact, etc…
- Stores a value which is measurable, indicates some numerical figure
- e.g. SalesFact, InvoiceFact, etc…
Type of Columns
- Foreign Key
- Foreign keys to Dimension tables which make a sense to numerical figure
- e.g. CustomerKey, PurchaseDateKey, GeographyKey, etc…
- Measure
- Basic essence of a table contains a numerical value
- e.g. SalesAmount, InvoiceAmount, Profit, etc…
- Lineage Columns
- Used for Auditing and never exposed to end users
- CreatedOn, LastModifedOn, etc…
- Business key columns from primary source
- Source key columns used for reference
- e.g. TransactionID, CustomerID. CustomerID is the unique key coming from source where CustomerKey is the FK to CustomerDimension what we have created in our data warehouse.
- Foreign Key
- Foreign keys to Dimension tables which make a sense to numerical figure
- e.g. CustomerKey, PurchaseDateKey, GeographyKey, etc…
- Measure
- Basic essence of a table contains a numerical value
- e.g. SalesAmount, InvoiceAmount, Profit, etc…
- Lineage Columns
- Used for Auditing and never exposed to end users
- CreatedOn, LastModifedOn, etc…
- Business key columns from primary source
- Source key columns used for reference
- e.g. TransactionID, CustomerID. CustomerID is the unique key coming from source where CustomerKey is the FK to CustomerDimension what we have created in our data warehouse.
Measure Types
- Non Additive
- Which cannot be used inside any aggregate function
- e.g. Ratio, ProfitMarginPercentage… SUM(Ratio), MAX(Ratio) does not make sense
- Semi Additive
- Which can be used inside few aggregate functions and not all
- e.g. CurrencyRate …. SUM(CurrencyRate) does not make sense… but AVG(CurrencyRate) / MAX(CurrentRate) makes
- AccountBalance - SUM(AccountBalance) does not make sense… but MAX(AccountBalance) does
- Additive
- Which can be used inside all kind of aggregate function
- e.g. Quantity
- Non Additive
- Which cannot be used inside any aggregate function
- e.g. Ratio, ProfitMarginPercentage… SUM(Ratio), MAX(Ratio) does not make sense
- Semi Additive
- Which can be used inside few aggregate functions and not all
- e.g. CurrencyRate …. SUM(CurrencyRate) does not make sense… but AVG(CurrencyRate) / MAX(CurrentRate) makes
- AccountBalance - SUM(AccountBalance) does not make sense… but MAX(AccountBalance) does
- Additive
- Which can be used inside all kind of aggregate function
- e.g. Quantity
Types of FACT table
- Transactional FACT
- used to records one row per transaction with measures of particular business event.
- must be design with Additive facts
- takes more memory space in the database
- Contains additive Measures
- e.g
- used to records one row per transaction with measures of particular business event.
- must be design with Additive facts
- takes more memory space in the database
- Contains additive Measures
- e.g
- Periodic FACT
- used to records one row for a group of transactions with aggregated measures of business events that happen over a period of time.
- takes minimum memory space in the database when compare with Transaction fact table
- e.g.
- used to records one row for a group of transactions with aggregated measures of business events that happen over a period of time.
- takes minimum memory space in the database when compare with Transaction fact table
- e.g.
- Accumulating FACT
- used to records one row for the entire lifetime of transaction with measures of business events that happen over a period of time.
- must be design with combination of Additive facts with multiple date keys that helps to track the lifetime of business event
- takes minimum memory space in the database
- e.g.
- used to records one row for the entire lifetime of transaction with measures of business events that happen over a period of time.
- must be design with combination of Additive facts with multiple date keys that helps to track the lifetime of business event
- takes minimum memory space in the database
- e.g.
- Factless FACT
- A fact which does not contain any measure, called factless fact
- Used to maintain the mapping of different dimension keys
- e.g. in School database, we need to have a mapping which teacher is teaching which student, so when we create a table StudentTeacherMap, it will have just StudentKey and TeacherKey, so this table becomes factless fact
- A fact which does not contain any measure, called factless fact
- Used to maintain the mapping of different dimension keys
- e.g. in School database, we need to have a mapping which teacher is teaching which student, so when we create a table StudentTeacherMap, it will have just StudentKey and TeacherKey, so this table becomes factless fact
- Coverage FACT
- Factless fact can answer only optimistic queries, but cannot answer negative one
- If we talk about above example, then Factless Fact does NOT tell which teacher is NOT teaching which student
- So to cover all these scenarios, we need to introduce a flag in that table, which will have Y/N value based on their mapping and this becomes Coverage Fact
- Factless fact can answer only optimistic queries, but cannot answer negative one
- If we talk about above example, then Factless Fact does NOT tell which teacher is NOT teaching which student
- So to cover all these scenarios, we need to introduce a flag in that table, which will have Y/N value based on their mapping and this becomes Coverage Fact
