Aggregate Tables usage

We are building a datawarehouse and it has been proposed to contain both detail and aggregate tables, with the aggregate tables at the month level and detail at the daily level. I do understand that aggregate tables are done to gain at the performance front. But, could someone help me out with the type of reporting we should aim for against aggregate tables? Would using cubes be advised against aggregate tables or only detail or both?

Cubes ARE aggregate facts. Weither you load them from atomic or aggregate facts depends on the nature of the cube, the existing aggregates and if your cube environment supports drill-through capability.

Whereas some aggregates are obvious, such as month-end snapshots, most of the time I differ creation of aggregates until after the atomic facts are in place and performance is evaluated. Sometimes they are not necessary, but if they are, they are fairly easy to implement once the basic stars are in place.

In general, using OLAP cubes to calculate and store aggregates is more efficient in terms of performance and storage, as the data are stored in multi-dimensional database (MDB) specially designed to pre-calculate and store aggregates in the most efficient architecture, different from traditional RDBMS. It also allows calculating aggregates under numerous combinations of dimension attributes quickly at push of a button, which would otherwise be overwhelming for traditional stored procedures in RDBMS.

However stepping up to using OLAP cube is challenging as it is still a new frontier. It comes down to how many potential aggregates you need to pre-calculate, how you are going to query against aggregates. If you need to write canned reports by querying aggregates, you have to know MDX, the query language designed for extracting aggregates and navigating through hierarchies easily. If you want to stick to SQL, then you may need store the aggregates in relational format instead of MDB.

So as pointed out by ngalemmo, you can go either way. You could mix them up if necessary, but try to be consistent and avoid duplicating the aggregation logic in two different formats.