Changing measures!

Hello:

I am working in a University where I am trying to create a dimensional model.

One of the facts I identified so far is, research money that's granted. Grain in this fact table is every expense made against a research award (account).

Here, the awarded research amount can change overtime - positively or negatively. My question is, where should the awarded amount be stored?
1. A measure in a dimension? That should definitely be an SCD-2 field (if it ends up in a dim) Or
2. Make that awarded money a separate fact (Budget Fact?). Since it can change over a period of time, should that be an SCD-2 field in a fact?
Or any other suggestions will be highly appreciated.

Thanks in advance,
-Zaci

It should be a fact. You can implement it as a snapshot, accumulating snapshot or a transactional fact. Transactional facts (where you insert net change information and never update a row) tend to be more flexible to report and easiest to maintain. An accumulating snapshot stores current balance over time (sort of like a type 2 dimension). A snapshot stores only current data, no history.

You need a transaction fact table where you track of all research money amount. That fact table is not a SCD type 2. Bases on the account activity, you have grain on the fact table by day or time in a day. Make sure you get expense money as negative or if you know it is an expense then store it is negative. The reason to store expense money as negative because when you sum all expense money with remaining money you get the reaming balance amount.

Acct# Day Balance Transaction type
A1 1/1/2015 $100 Grant money
A1 2/1/2015 -30 pay Electric bill
A1 2/5/2015 -10 pay water bill

If you sum Balance amount and group by Acct# you get the ending balance of $60.00

Thank you ngalemmo & zoom.

I was actually thinking of having two separate fact tables TRANSACTION_FACT (that has all expense amounts) & BUDGET_FACT (that has actual budget amount awarded).

However, zoom, in your example you suggested to have only one fact table for both types of transactions. While it definitely gets easier, is that the right practice? Being a newbie, I am trying to get the basics right.

Secondly, what are we gaining from having both types of amounts in the same table? Again, I am only trying to understand your thought process... If we have two different tables (TRANSACTION & BUDGET), it gets easier to write reports that don't need both types of amounts. Where as, if you had only one... every single report goes after the same table.

Please advise.

Thanks,
zaci

A Fact table to another fact join is not recommended. If you keep 2 fact tables as you described, then SQL will run slow. IF you think that number of transactions are going to be in millions, then I recommend have a transactions table and have another Fact table that shows an account balance. It is up to the business user If they want to see account balance by day, week, or by month. If they want to see balance by day then that fact table is a daily snap shot of account balance.

The choice of one or two facts is primarily an issue of grain. A secondary consideration is the summable nature of the measures.

If the grain is the same you could store both in the same table with mutually exclusive measures (budget amt, spend amt). Amounts would need to be net change amounts so they are fully summable. Otherwise use two facts.

zoom - I wasn't thinking about joining two fact tables to get the balance, you are right... it is not a good practice to perform that. I was thinking about one of the techniques mentioned in the DW Toolkit for Dimensional modelling. A two-step process, first step to get the actual amount awarded to the researcher and the second step to deduct the amount spent so far from the actual amount to current balance. Is this the best approach? I don't know... but this can be taken care of in the reports and users don't even realize that there are two steps involved to get the balance.

ngalemmo - That's correct. My understanding is the grain is different in both the fact tables, one gives you the expense amount in every transaction the researcher makes and the other should give you the original amount awarded for the research. To me, they both are completely different and can't be in the same fact table.

-zaci