Skip to Content

@aggregate aware or Derived Tables ..

Hello,

the original data in my Universe has about 2 Mio. i have 2 questions ..

is there any reference values for best performance of a universe (e.g 1. Mio rows .. what ever)

how can i get best performance: @aggregate aware or with derived tables .. (other Alternatives)

Production DB: Oracle

BOXIR2 SP 4

0
Add a comment
10|10000 characters needed characters exceeded

Mohamed Moktatif Jun 10, 2009 at 11:35 AM

0

45

Actions

1 Answer

Best Answer
author's profile photo Former Member
Former Member Jun 10, 2009 at 12:01 PM
2

Hi,

You can get better performance with aggregate awareness, but you need the aggregate tables in the db.

As a brief overview consider a table prod_cust_order (1million rows)

Product, Customer, Order Amount

This could be aggregated to product_order Product, Order Amount (500 rows)

If the user wants to see order amount by customer then they are going to have to query the original table, but if they are interested in product only they they can go to Product_order table.

The aggegrate awareness allows you to create a measure object with both tables as the source, and will then select the approriate one when building the query based on the dimension you have selected. So if the user selects customer then it will go to the top prod_cust_order but if they don't it will source it from the much smaller Product_order table. The user does not see the change in WebI unless they look at the underlying SQL.

Derived tables are not so good for performance as the enter derived query is included in the SQL built.

Regards

Alan

Add a comment
10|10000 characters needed characters exceeded