multiple 'fact' tables - daily detail and monthly allocations

I am sure this has been asked but I could not find a direct answer. We have an EDW with a transaction level fact table including pertinent financial detail. For the purposes of this question there are multiple transactions per client, and multiple clients. The normal time grain is daily. Additionally there are monthly 'transactions' by client, such as late payment fees, volume discounts, etc. The monthly 'transactions' are account level, they are substantially different from the transactions in the fact table. The EDW with the transaction fact table and time dimension exist.

My question is: What should the structure for supporting the monthly account level 'transactions' look like? This is 'fact' information, but does not fit into a dimension structure. Conversely, its content does not map consistently with the existing fact table. Is it as simple as a new 'cube' containing common summary financial information from the current fact table combined with the monthly allocations.

The purpose is to look at total account information, including aggregates of detail performance and monthly allocations.

Thanks - Dan

Sounds like you've got two fact tables here. One is at a daily grain, the other is at a monthly grain. The monthly table will join to a shrunken month dimension. The tables are then comparable, albeit only at a month level. You'll run a multi-pass SQL against both the monthly and daily fact tables and then combine those at a month level to compare them.

What Brian said. What you are noticing are the problems associated with multi grain fact tables. A bad design practice.

@BoxesAndLines hi why this is a bad design practice.

working on EDW of capital markets; most of the times we do receive facts at different grains.
and vendor is doing lot of calculations and sending out these facts.

it is very much req to capture this info in the DW.

how to handle this problem in a better way??

It's not that you can't store it, you just shouldn't store it in the same fact table. The basic rule of a fact table is that all the data in it is at the same grain. Just build two fact tables, one at a daily level and one at a monthly level, then run two SQL passes to get your data.

No matter what you can't compare your monthly data to your daily data. It's impossible because you're missing the daily data in one of your comparison tables. It's not a limitation of the design, it's a limitation of your data. You can, however, roll your daily data up to the month and then compare them. Now your grain matches and you've still captured your daily data at the day level in your warehouse.

Thanks Brain.

Thank you all for the comments, they help significantly. The concept of two time grains being represented in two different cubes makes sense.

A follow up question: The monthly period is made up of lumpy account level events, such as orders, invoices, shipments, and payments. There may be several orders, invoices, shipments, etc in a month. It would seem based on the discussion that creating a cube at the event grain showing 'account' level activity, versus transaction level activity, would be in line with the principals discussed. Is that a valid conclusion?

A second follow up question, it would seem to be easier to build the summary summary order 'fact' from the existing DW then to reconstruct it from the transaction data.

Thanks, Dan

Assuming you have transaction level activity in a fact table in your data warehouse, then there is no problem creating a cube with account level summaries of that data. Standard practice would be to create the cube directly from the trasaction level fact table rather than sourcing transactional data from the operational system a second time.