Changing measures!

View previous topic View next topic Go down

Changing measures!

Post  zaci on Wed Sep 16, 2015 11:35 am

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

zaci

Posts : 11
Join date : 2015-09-16

View user profile

Back to top Go down

Re: Changing measures!

Post  ngalemmo on Wed Sep 16, 2015 12:08 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Changing measures!

Post  zoom on Thu Sep 17, 2015 7:22 am

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

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Changing measures!

Post  zaci on Thu Sep 17, 2015 10:24 am

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

zaci

Posts : 11
Join date : 2015-09-16

View user profile

Back to top Go down

Re: Changing measures!

Post  zoom on Thu Sep 17, 2015 12:52 pm

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Changing measures!

Post  ngalemmo on Thu Sep 17, 2015 1:41 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Changing measures!

Post  zaci on Mon Sep 21, 2015 7:22 am

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

zaci

Posts : 11
Join date : 2015-09-16

View user profile

Back to top Go down

Re: Changing measures!

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum