Same grain different aggregate tables

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

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.

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

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