1

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

cherylberyl
  • 13
  • 2
  • 1
    Since a data warehouse exists to aggregate rows along a dimension, duplicate values in an index aren't a problem. You're not trying to retrieve one row at a time. – Gilbert Le Blanc Aug 13 '20 at 09:49

1 Answers1

0

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.

Trubs
  • 2,829
  • 1
  • 24
  • 33