Where to store Fee breakup facts?

View previous topic View next topic Go down

Where to store Fee breakup facts?

Post  riderkings on Fri Sep 30, 2011 12:30 pm

I'm working on creating the data warehouse for the bank. I'm planning to store the total of all fees charged to an account in a month in the core fact table. How should I store the breakup of the fees? Should I create a separate fact table for it with the link to the core fact table?

riderkings

Posts : 3
Join date : 2011-09-30

View user profile

Back to top Go down

Re: Where to store Fee breakup facts?

Post  omm on Fri Sep 30, 2011 3:21 pm

riderkings wrote:I'm working on creating the data warehouse for the bank. I'm planning to store the total of all fees charged to an account in a month in the core fact table. How should I store the breakup of the fees? Should I create a separate fact table for it with the link to the core fact table?

If breakup of the fees can be maintained in separate columns and can be used as aggregate or for some functionality, I guess, you can keep them in the same fact table. May be if the attributes involved are textual or slowly changing (in this case, it is not), then you have a separate dimension.
Can you elaborate on your fees like what all fees make up the total fees and whether you use them for any reporting?
[Please correct me if I am wrong]

omm

Posts : 11
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Where to store Fee breakup facts?

Post  ngalemmo on Fri Sep 30, 2011 3:48 pm

Can you elaborate on your fees like what all fees make up the total fees and whether you use them for any reporting?

C'mon... its a bank. There is a fee for everything... using an ATM, bouncing a check, talking to someone, and now... using your debit card.

Do you have an atomic activty fact table? Fee charges should go in there as separate rows along with deposits and withdrawls.

In terms of the monthly aggregate, if you need to break it out by type of fee, then it needs its own fact table because the grain would be different than a month-end balance fact table. If all you need is a total charged during the month, you can include that measure with your month-end balance aggregate.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Where to store Fee breakup facts?

Post  riderkings on Fri Sep 30, 2011 3:55 pm

Thanks for the reply.

I do need to break out fees by type. I'm going to show the monthly aggregate fees along with month-end balance in the main fact table. Wasn't sure about storing the break up fee facts. Would I be able to tie them up easily in the cube?

riderkings

Posts : 3
Join date : 2011-09-30

View user profile

Back to top Go down

Re: Where to store Fee breakup facts?

Post  ngalemmo on Fri Sep 30, 2011 4:07 pm

I'm not much of a 'cube' guy, but if you are talking about putting month-end account balances and fees by type into the same cube, probably not, because of the mixed grain... most cubes would inflate the balance by the number of fee types charged that month.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Where to store Fee breakup facts?

Post  riderkings on Fri Sep 30, 2011 4:52 pm

Thanks ngalemmo.

riderkings

Posts : 3
Join date : 2011-09-30

View user profile

Back to top Go down

Re: Where to store Fee breakup facts?

Post  hang on Fri Sep 30, 2011 5:52 pm

It does not matter if you use cube or not, it's about modelling your fact properly in a dimensional sense. As long as you include the measures in the same fact table with the same dimensionality, cube will love it. Obviously the monthly aggregates and balances do share the same time series dimension (month) and other dimensions, so they are in the same fact table. If all other breakups are also on monthly level, and related to other dimension keys, then you should put them together in one fact table so that you can rollup, drill down, compare your measures conveniently, especially in the cube.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Where to store Fee breakup facts?

Post  ngalemmo on Sat Oct 01, 2011 2:13 am

Yes, it has to be two fact tables, but I couched my response because I am not familiar with functionalities in the various cube-based OLAP tools. It is conceivable that a tool may have the ability to designate which dimensions a measure could be aggregated on... in other words a way of identifying semi-additive and non-additive measures, so that measures get treated differently depending on what dimensions are being used in the query.

So, if such a feature is availible, you could load the cube at the fee type level, repeating other balance number on each row. The repeated measures could be designated as not summable on the fee type dimension, so that queries that use that dimension would not sum balances by that dimension.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Where to store Fee breakup facts?

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