Need to create hierarchy using only 2 fields

[#1]

I need to create a hierarchy utilizing only 2 fields.  The parent field is 'Resource Manager' and the child is 'Resource'.  The relationship already exists between the two fields, the problem is it only shows the direct Resources associated to the Resource Manager.

Example:

Resource Manager:  Simon

Resources:  Tom and Scott

Resource Manager:  Tom

Resources:  Betty and Jean

Resource Manager: Scott

Resources:  Chris, Tyler

With the above example if I select Simon, I'm only able to see his direct resources Tom and Scott.  I want to be able to see Tom and Scott as well as their direct resources. So I would like to see Tom, Scott, Betty, Jean, Chris and Tyler.  How do I setup a hierarchy like this when I only have 2 fields, the Resource Manager and Resource.

Thanks

[sudeepkm · Accepted solution]

3.5 billion records seems huge. Is that the no of records present in source table or the results you are getting from the hierarchy load. I think it would be better to consider below given approach while building it.

1. Check if you can find a flag field in the data source table that would indicate a record as active so that you can limit your data load to active resources only.

2. Load resource data in a qvw by implementing an incremental load so that it won't load the history repeatedly and then store the data in a qvd.

3. Create a separate QVW just for hier QVD generation a single job dedicated for hierarchy data load. (I think it would be better to call the hier function on the data from a table within that qvw loaded from a QVD) I know there are 19 levels or more but in the past I have used the hierarchy function to generate LOB structure that spanned until 15 levels and the data load had passed our quality review.

4. The UI will load data from Hier.qvd

The active resource count should be much lessor than all resources if that can be found then the load will be easy.

[#3]

Yea the 3.5 was from the extended leaves.  I was able to address the issue by loading the data from a QVD and then performing a resident load prior to building out the hierarchy.  Everything looks good.  Thanks for all of the help.