best Practice Agregate Tables for agregate awarness ..

[Former Member]

Hello,

the question i m facing now is how to improve performance trough agregate awarness. How to plan the agregate tables in order to get the best covering for reporting requirments ? How to realize maximum use of agregate tables and minimum use of Detail tables.

Is there a method / technique to do that the best way?

until now did it this way.

1. I generated a kind of select * from DetailTable. (exp. 5 Mio rows)

2. progressivly i removed one or more dimension from the statment and got for exp. only 3 Mio Rows. the removed dimension is something like Postal code of Customers or so that i get only information about city and country. Or removing Day from date so that i only have info about the month .. etc.

3. I remove further dimensions .. (less rows exp 1 Mio)

4. at the end i have 5 or 6 agregate tables candidate for use in agregate awarness ..

Is that Ok like this .. could u please share your experiences ..?

Edited by: MM on Sep 9, 2010 3:57 PM

[Former Member]

What was already suggested is, of course, valid. You need to find out what questions your business users are asking else you run the risk of providing an answer to an unasked question. However, there are a few other tips I would like to share.

First, if you can't get an order of magnitude reduction, it's not worth the effort. Going from 5 million to 3 million rows is not good enough to consider. Going from 5 million to 500,000 rows is, as the resulting aggregate is smaller by a factor of 10. And then going from 500,000 to 50,000 could be even better.

Second, you might consider what options your database has instead of using Aggregate Aware. For example, Teradata offers the AJI or Aggregate Join Index feature which - to be honest - is far more flexible than using Aggregate Aware in the universe. Using your database means your aggregate tables are usable by any tool that hits the database, even from tools that do not hit the universe such as Crystal.

Third, and this goes back to the first point in a way, you have to be very careful in defining your aggregate path. The Aggregate Aware feature of the semantic layer is powerful but it does have limitations. For example, you have to build a linear reduction in your path. That probably doesn't make sense (and I have a draft blog post that I have to get finished one day) but here it is in a nutshell. Suppose you have five factors in your fact, call them A, B, C, D, and E. You want to build an aggregate table at the A, B level. That's fine. You can also build an aggregate at the A, C level. But you can't use both of them in the same @Aggregate_Aware() object definition. You have to build a pyramid, like this:

A

A, B

A, B, C

A, B, C, D

A, B, C, D, E

This does not work because items disappear and reappear later on, and that does not work.

A

A, B

B, C

A, C

A, D, E

A, B, C, D, E

With Teradata AJI definitions you can do this second case. It does not work in the universe, because you need one clear "best" answer at all times. In the second case, where is the single "best place" to retrieve attribute B? There isn't one, and the aggregate aware process fails.

Hope these ideas help.

[Former Member]

Thanx a lot for your "very helpful" answers ..

[PPaolo]

Hello,

you are using a bottom up approach with the risk that you build powerful aggregate tables which won't be used by the business.

A top down approach would be to go and ask your business users which kind of queries they are more likely to do: at what level of each dimension and how they mix dimensions together.

Once you have an idea of the most typical queries, you can prepare aggregated tables which already answer those queries.

Hope that it helps

PPaolo