Revenue Fact and Account Balance Fact
2 posters
Page 1 of 1
Revenue Fact and Account Balance Fact
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.
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
Re: Revenue Fact and Account Balance Fact
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.
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.
Similar topics
» 2 facts or 1 fact - Revenue and time billed
» Product and account dim FK in fact table
» Account Numbers in a Fact Table
» Card data - a Dimension or Fact and relationship among Card, Account and Client
» Calculated measure value in aggregate fact table
» Product and account dim FK in fact table
» Account Numbers in a Fact Table
» Card data - a Dimension or Fact and relationship among Card, Account and Client
» Calculated measure value in aggregate fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum