Revenue Fact and Account Balance Fact

View previous topic View next topic Go down

Revenue Fact and Account Balance Fact

Post  dw_user on Thu Aug 02, 2012 12:33 am

Hello,
I am designing couple of dimensional models for a bank. The requirements are to be able to slide-n-dice bank revenue and account balance. Bank has a G/L code that identifies income/revenue type such as interest, fees, commissions etc. This is an intelligent code where the first two numbers signify whether it is an income or expense etc. In the kimball book, he mentions to create a revenue fact alone.
I also have to design a fact for each transaction that occurs at the bank for account balance. I am thinking of combining the two fact tables into one. Knowing that only some transactions will generate revenue for a bank, I will put all the transactions into one fact table and this fact table will have 3 metrics: transaction amount, ending balance, and revenue amount. I will also add a G/L code dimension which will tell me what kind of revenue it is for only those transactions that generate revenue.

Is this a good idea to combine Revenue fact and transaction fact into one? Please suggest.

dw_user

Posts : 8
Join date : 2011-05-11

View user profile

Back to top Go down

Re: Revenue Fact and Account Balance Fact

Post  ngalemmo on Thu Aug 02, 2012 1:29 am

The problem with balance is it is semi-additive while transactional measures are fully additive. Care should be taken when combining them in the same table. The BI layer you use should be able to distinguish between fully additive and semi-additive measures and prevent certain aggregations of semi-additive measures.

The other thing is stuff like balances don't really mean much in real time, from an analysis standpoint. Usually an end-of-day type balance, and maybe an average and min/max, overdraw counts and some other things are more useful. Such a daily snapshot would go into a different fact table.
avatar
ngalemmo

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

View user profile http://aginity.com

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