Does current balance belong in a fact or a dimension table?

1
votes

I'm starting out building a data warehouse and I'm struggling to get it straight in my head whether a balance amount is a fact or a dimension.

I have a 'Customer' table, which I believe should be a dimension. It has attributes such as the customer's name.

It seems to make sense to me to create a fact table called something like 'Customer Account' that contains information such as the account balance, and link the 'Customer' dimension to that.

However, 'Customer' will be a conformed dimension, as pretty much every fact table will be linked to it. And I know that one common way that people want to slice and dice their data is by current balance (e.g. Show me how many X where the customers balance is over £500 in arrears). So that suggests that the balance should be stored in the dimension. But that 'feels' wrong to me.

I guess that if anyone wants a report that is filtered or grouped by balance then they could drill-across to the 'Customer Account' fact table - but that doesn't seem very user-friendly.

Perhaps I need to create a 'Customer Account' fact table, and also add the balance to the 'Customer' dimension.

Suggestions welcome!

1
data-warehousebusiness-intelligence

1 Answers

1
votes

You pretty much answered your own question: use both.

  • In the dimension, to allow filtering based on current balance; balance must be updated for every transaction or at least every day;
  • As a snapshot fact table, using the semi-aditive measure balance, to allow time series analysis on account balances, for example.