Same grain different aggregate tables

View previous topic View next topic Go down

Same grain different aggregate tables

Post  vidyav1979 on Thu Feb 02, 2012 6:54 pm

Hi All,

We are creating aggregate tables in a retail DWH for revenue/payroll and shipping for dashboard purposes.

The data for the aggregates are all at the location and month level. But the load timings for the base tables are different - revenue/payroll base fact gets loaded at 20.00 and shipping base fact gets loaded at 6.00 AM the next day morning. It is not possible to modify these timings since it is based on the data availability at source.

The dashboard requirement is to have the new data available in the aggregate table as soon as it is available in the base tables. So, would it make sense to one table for revenue/payroll summary data and another one for shipping summary data - even though they are all at the same grain??

Regards,
Vidya


vidyav1979

Posts : 2
Join date : 2012-02-02

View user profile

Back to top Go down

Re: Same grain different aggregate tables

Post  ngalemmo on Thu Feb 02, 2012 11:28 pm

You could use one table, just use zeros (or nulls) for measures that don't come from the particular source. You can periodically aggregate the aggregate to reduce the number of rows if that becomes a performance problem.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Same grain different aggregate tables

Post  vidyav1979 on Fri Feb 03, 2012 1:35 am

Yes, that was one of the approaches that we considered. But in doing so, we are complicating the aggregate table ETL load process. We will have to run the ETL process once for the revenue/payroll data. Then when shipping data is available, we run the ETL process again for an update. Moreover, we are also introducing load dependencies between 2 functionally different areas, just because both are at the same grain. But if we were to maintain different tables, we can do a truncate and reload as and when the data for each functional area is available.

The data in the aggregate tables are not too huge. Most probably around 200,000 records.

I am not sure what the modeling best practices recommend in such a scenario

vidyav1979

Posts : 2
Join date : 2012-02-02

View user profile

Back to top Go down

Re: Same grain different aggregate tables

Post  ngalemmo on Fri Feb 03, 2012 5:13 am

If a truncate and reload works for you, that's fine.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Same grain different aggregate tables

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