Questions about Parent-Child hierarchy table

Jerry S.

I am going to try to develop a parent-child hierarchy, but I am a little confused about a few things.

(1). During the process, scripts are created to build and populate the hierarchy table.

Does this hierarchy table get built in the actual database that I am working with, does it just get built in the Oracle OBIEE data base

and then referenced by the RPD, or does it just get built only as a construct in the RPD?

I ask because I only have read access to the actual data base that I am working with.

(2). In the data base I am working with, there is a table that has both a key, and a parent key.

Can I use this table as the basis for a parent-child hierarchy, or is the parent-child hierarchy table something special?

(3). Can a view created in the RPD be used as a parent-child hierarchy table if all of the required fields are present?

(4). Does anyone know of a really good, step by step tutorial on creating a parent-child hierarchy?

I've looked at a few, but they always seem to leave out steps, or assume that you know about the process.

3491696

Jerry, let me try to answer a few things for you:

(1). During the process, scripts are created to build and populate the hierarchy table.

Does this hierarchy table get built in the actual database that I am working with, does it just get built in the Oracle OBIEE data base

and then referenced by the RPD, or does it just get built only as a construct in the RPD?

I ask because I only have read access to the actual data base that I am working with.

My understanding is that the Hierarchy table is something that is populated as a materialized view. After being frustrated with performance, I rewrote it to save to a table, but both instances require writing to the DB. Running real-time in the RPD would require some fancy footwork to not be a huge burden on performance. Doable, but writing back is the preferred method.

(2). In the data base I am working with, there is a table that has both a key, and a parent key.

Can I use this table as the basis for a parent-child hierarchy, or is the parent-child hierarchy table something special?

The script for populating the table needs something to run the logic off of, and that's the key. The actual layout of the hierarchy table has all combinations of ancestor (parent, parent of parent, parent of parent of parent) to children, how many generations down it is, and if it's the end of that thread, or leaf.

(3). Can a view created in the RPD be used as a parent-child hierarchy table if all of the required fields are present?

Yes. Performance is not going to be optimal, but doable.

(4). Does anyone know of a really good, step by step tutorial on creating a parent-child hierarchy?

I've looked at a few, but they always seem to leave out steps, or assume that you know about the process.

I believe that most people like to just run the scripts, and not get their hands dirty in the code.

That being said, your situation is a bit unique, as most people are trying to do this in a DB they have write access to. I doubt there'll a tutorial on what you want.

And that being said, what an excellent idea for some fun scripting today.

Ping me if you have any questions.

Jerry S.

Thank you for the great information.