Thursday, August 8, 2013

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

No comments:

Post a Comment