Hello,
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?
Any thoughts?
