Apologize in advance for a lengthy but general query related to data warehousing - I hope there is an expert who has done this many times that can help me.
We need to build a periodic snapshot table in our data warehouse. This is an example of a bank that needs to store account balances for every account for each of its millions of customers. On a daily basis, only about 10% of the accounts have any activity (transactions) on them, for the rest, the balance remains unchanged. In the data feed from the source system, we will only get updated balances for accounts that have had any activity on them. The challenge is to get the account balance for every account for any given day.
Tables:
FACT TABLE:
FACT_ACCOUNT_BALANCE (DATE_KEY, ACCT_KEY, CUST_KEY, ACCOUNT_BALANCE)
DIMENSION TABLES:
DIM_TIME (DATE_KEY, ...)
DIM_ACCOUNT (ACCT_KEY, ACCOUNT_NUM,...)
DIM_CUSTOMER (CUST_KEY, CUSTOMER_NAME, ...)
Question: What is the best way to store the data in the fact table?*
<ul><li>1) Should we record rows for an account for days in which there are no transactions?
</li>
</ul>
Data for FACT_ACCOUNT_BALANCE:
CUS_KEY ACCT_KEY DATE_KEY ACCOUNT_BALANCE
777 123 20090301 2000.00
777 123 20090302 1980.00
777 123 20090303 1980.00 -- no transactions
777 123 20090304 1980.00 -- no transactions
777 123 20090305 1927.48
777 123 20090306 1927.48 -- no transactions
777 123 20090308 2000.41
... ... so on.
What is the best way to implement this logic where a balance is carried forward if there are no changes?
Should we record rows only for changed balances?
Data for FACT_ACCOUNT_BALANCE:
CUS_KEY ACCT_KEY DATE_KEY ACCOUNT_BALANCE
777 123 20090301 2000.00
777 123 20090302 1980.00
777 123 20090305 1927.48
777 123 20090308 2000.41
... ... so on.
What is the best way to query for a balance on a particular day e.g. What was the balance on 2009-03-04 for this account?