multiple 'fact' tables - daily detail and monthly allocations

View previous topic View next topic Go down

multiple 'fact' tables - daily detail and monthly allocations

Post  dansawyer on Sat Jun 05, 2010 9:05 pm

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

dansawyer

Posts : 5
Join date : 2010-05-01

View user profile

Back to top Go down

Re: multiple 'fact' tables - daily detail and monthly allocations

Post  BrianJarrett on Sun Jun 06, 2010 3:04 am

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.


Last edited by BrianJarrett on Fri Jun 11, 2010 10:18 am; edited 1 time in total (Reason for editing : Type-o)
avatar
BrianJarrett

Posts : 61
Join date : 2009-02-03
Age : 43
Location : St. Louis, MO

View user profile

Back to top Go down

Re: multiple 'fact' tables - daily detail and monthly allocations

Post  BoxesAndLines on Mon Jun 07, 2010 11:18 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: multiple 'fact' tables - daily detail and monthly allocations

Post  sgudavalli on Thu Jun 10, 2010 10:46 am

@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??

sgudavalli

Posts : 29
Join date : 2010-06-10
Age : 33
Location : Pune, India

View user profile

Back to top Go down

Re: multiple 'fact' tables - daily detail and monthly allocations

Post  BrianJarrett on Fri Jun 11, 2010 10:24 am

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.
avatar
BrianJarrett

Posts : 61
Join date : 2009-02-03
Age : 43
Location : St. Louis, MO

View user profile

Back to top Go down

Re: multiple 'fact' tables - daily detail and monthly allocations

Post  sgudavalli on Mon Jun 14, 2010 5:13 am

Thanks Brain.

sgudavalli

Posts : 29
Join date : 2010-06-10
Age : 33
Location : Pune, India

View user profile

Back to top Go down

Re: multiple 'fact' tables - daily detail and monthly allocations

Post  dansawyer on Wed Jun 16, 2010 10:38 pm

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

dansawyer

Posts : 5
Join date : 2010-05-01

View user profile

Back to top Go down

Re: multiple 'fact' tables - daily detail and monthly allocations

Post  ngalemmo on Mon Jun 21, 2010 11:14 am

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: multiple 'fact' tables - daily detail and monthly allocations

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