11g - Is it possible to create an aggregate table for Parent-Child hierarchies (a.k.a. level-based)?

User_U4XQW

Pretty simple question, I hope. We have a bunch of parent-child hierarchies, some of which go 9 levels deep. We have 20 million pertinent fact records that need to be included in any given report, so aggregate tables would come in hugely handy for speeding things up.

I tried using the Aggregate Persistence wizard but I get the following message :

"Discarding aggregate (dimension(s) contain value-based hierarchies) <fact name, subject area etc.>"

Nowhere in any documentation so far about the Aggregate Persistence wizard have I seen it mentioned that aggregation doesn't work for value-based (a.k.a. Parent-Child) hierarchies.

And also, nowhere in the docs for Parent-Child/value-based hierarchies have I seen it mentioned that you can't create aggregates for them.

Does anyone have any advice? (Apart from "convert to level-based" ... of course that is an option we now have to investigate, but would rather get PC hierarchies working if possible).

Is this just a limitation of the wizard and I can create aggregate tables manually? Or is it a fundamental issue with PC hierarchies that they cannot support aggregate tables?

Thanks!

Robert Angel

The only hits I have been able to find on this is fairly old, but seems to match your experience.

I would recommend logging a service request with My Oracle Support to get the definitive on this.

I found a hit on My Oracle Support that was a dead match, but I no longer have the internal rights to see it as it was an 'Oracle only' document.