Using Aggregate Awareness in Conditions

[TomDavies]

I am trying to create a condition which is aggregate aware. Is this possible? I seem unable to use aggregate awareness in the where clause of a condition.

I have:
1. A fact table which records aircraft flights and is partitioned and indexed on "departure date" (the table is BIG!!)
2. An aggregate table which summarises the above table by "departure month" amongst others.

The condition I wish to create is for "last month". I have sucessfully created a condition that will derive the dates of the first and last days for the previous month, however if I attempt to use this condition in a report that only hits the aggregate table then I get an error!

So ... I thought I would use the @AggregateAware function to restrict by flight month dimension if possible, but I cannot make this work.

Can anyone suggest a way to make this work or and alternative?

Many thanks in advance...

 

[BobWard]

AggAware in conditions is a problem. The following is a work around though.

Define your conditions based on an existing dimension using the @select. Obviously your Dimension is agg aware. The condition will then resolove the aggware correctly in the condtion ONLY IF YOU INCLUDE the dimension aswell in the Query Panel. Hope this is a suitable work around for u.

 

[TomDavies]

Many thanks for this.
Unfortunately, I've considered using this as a work around, but I then encounter a performance problem as using the dimension will cause the database (Oracle 8.1.5) to disregard indexing and table partitioning! The table is so big that BO just curls up it's toes!

My next thought if I cannot do this, is to recreate my aggregates as materialised views and take advantage of Oracles query rewite facility. This does however seem like a heavy handed approach to my problem!!