Where to store Fee breakup facts?

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 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]

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.

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?

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.

Thanks ngalemmo.

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.

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.