Search code examples
timedata-warehousefact

Do i need a time dimension for my fact table to prevent duplication?

I am designing a Data Warehouse and need some help with my fact table.

My fact table is capturing the facts for aged debt, this table captures all transactions against bills.

The dimension keys i have are listed below:

  • dim_month_end_key
  • dim_customer_key
  • dim_billing_account_key
  • dim_property_key
  • dim_bill_key
  • dim_charge_key
  • dim_payment_plan_key
  • dim_income_type_key
  • dim_transaction_date_key
  • dim_bill_date_key

I am trying to work out what my level of granularity would be as all the keys together could be duplicated, let's say if a customer makes a payment twice in one day.

I am thinking to solve this i can add a time dimension as the time should always be different.

However the company do not need to report on time, do i add it to prevent duplication regardless?

Thanks

Cheryl

Solution

  • No you don't need a time dimension.

    there may be an apparent duplication in your fact, but it will actually reflect 2 deposits in one day - so two valid records. the fact that you might not be able to tell the two transactions apart is not (necessarily) a problem for the system

    the report will Sum all the deposits amounts, or count the number of deposits, along any dimension and the totals will still be fine.