Am designing a data model and am torn between two approaches for the fact table. Am wondering if anyone has any advice or preferences (or even an alternate suggestion).
Essentially I want to measure the balance of a client account on a daily basis, and also look at the number and value of orders by type (deposits, withdrawls) made each day.
Is my best approach to have:
1. a single row per account per day in the fact table, with a number of different measures on each row
e.g.
Date
Account
Balance
Deposit #
Deposit Value
Withdrawal #
Withdrawal Value
03/07/2016
1
$100
2
$5
1
$10
03/07/2016
2
$300
3
$23
2
$5
or
2. Multiple fact rows per account each day, containing the result for each particular measure
e.g.
Date
Account
Fact Type
Fact Value
03/07/2016
1
Balance
100
03/07/2016
1
Deposit #
2
03/07/2016
1
Deposit Value
5
03/07/2016
1
Withdrawal #
1
03/07/2016
1
Withdrawal Value
10
Bearing in mind the number of facts per account per day could grow and grow?
