Aggregate Tables usage

View previous topic View next topic Go down

Aggregate Tables usage

Post  daredevil on Wed Aug 18, 2010 3:46 pm

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?

daredevil

Posts : 9
Join date : 2010-08-05

View user profile

Back to top Go down

Re: Aggregate Tables usage

Post  ngalemmo on Wed Aug 18, 2010 4:19 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Aggregate Tables usage

Post  hang on Wed Aug 18, 2010 9:12 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Aggregate Tables usage

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