Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Aggregate Tables usage

3 posters

Go down

Aggregate Tables usage Empty Aggregate Tables usage

Post  daredevil 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?


Posts : 9
Join date : 2010-08-05

Back to top Go down

Aggregate Tables usage Empty Re: Aggregate Tables usage

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

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

Back to top Go down

Aggregate Tables usage Empty Re: Aggregate Tables usage

Post  hang 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.


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

Back to top Go down

Aggregate Tables usage Empty Re: Aggregate Tables usage

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum