FACT overview

What is fact?

• 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.

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

Type of FACTs

ClickHere