Query to return folder hierarchy from DTREEANCESTORS, including nesting depth

I'm trying to figure out how to query DTREEANCESTORS to return row information like "Item (folder or document) X is an Nth level child of Folder Y". A returned row would look something like {DataID, Depth, AncestorID}. I was thinking of a CONNECT BY PRIOR type of query but the data in DTREEANCESTORS is problematic. DTREEANCESTORS stores a row for every Ancestor, regardless of the generational separation. If an item is nested two or more levels deep, then there are multiple paths that can be followed. You also need to exclude all of the rows that identify an item as an ancestor of itself.

Here is an example to illustrate the problem, with data from one of our DEV databases:

DATAID ANCESTORID
2111 2001
2991 2001
2991 2111
2991 2991

2001 and 2111 represent folders, where 2111 is a sub-folder of 2001. 2991 is a document contained in 2111. As you can see, 2991 is identified directly as a child of 2001, and also as a grandchild via 2111. What I want to see returned by the query for DataID=2991 is the following:

DATAID ANCESTORID DEPTH
2991 2001 2
2991 2111 1

Given the redundant paths in the data between 2991 and 2001, I don't see how to achieve this using CONNECT BY PRIOR. Does anyone see any flaw in my reasoning, or have a solution to my problem?

Thanks
Patrick

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

David Templeton

For the sake of completeness blah, blah, blah LOL

SELECT dt.DataID, dt.Name, anc.level FROM DTree dt inner JOIN (SELECT a.DataID, COUNT(a.AncestorID) 'level' FROM DTreeAncestors a WHERE a.DataID IN (SELECT d.AncestorID FROM DTreeAncestors d WHERE d.DataID = 62116) GROUP BY a.DataID) AS anc ON anc.DataID = dt.DataID

This will get you the list in order from the top of the tree to the object on my system

DataID Name Level
2000 Enterprise 1
49787 >Departments 2
49788 Marketing 3
61967 Household Demand Operations 4
62115 Projects + Analytics 5
62116 APRD Challenge Campaign 6

This is NOT my recommendation for usage :-)

All comments

Benjamin Shapiro

I don't know the answer offhand, but do you have to use DTreeAncestors? In DTree[Core] it has the immediate ParentID right there.

Cheers,
Ben

David Templeton

I am going to ask the same thing I always ask, WHY? What is the problem you are solving? Direct access to the schema should the avoided at all costs. What interface you trying to do this from?

For the record connect by prior is one of the worse operations in SQL

DATAID ANCESTORID
2111 2001
2991 2001
2991 2111
2991 2991

the only statement you can make about 2991 is that it has ancestors of 2001 and 2111. Nothing about the child relationship.

Some points you made

_If an item is nested two or more levels deep, then there are multiple paths that can be followed. _
HUH?

You also need to exclude all of the rows that identify an item as an ancestor of itself.
DTA is a transitive closure, therefore the object is itself an ancestor. Again another reason to not use the schema directly

Patrick_McManus

I'm not trying to solve any problem. I have multiple Content Server system clients who are writing LiveReports against their systems and who are raising these types of questions. Why they are writing such reports is their business.

Regarding my statement "If an item is nested two or more levels deep, then there are multiple paths that can be followed., it's obvious from the example I gave: 2991 => 2001, or 2991 => 2111 => 2001.

DBROWSEANCESTORCORE has the same situation.

Yes, my clients could iteratively walk through DTreeCore by ParentID, would that be any more efficient for their LiveReports? Wouldn't it also involve CONNECT BY?

Patrick_McManus

FWIW what I am investigating is whether it is feasible to export a subset of the core CS database that the clients most often query to an external database, and in the process to transform it into a more reporting-efficient schema.

David Templeton

well, the livereport method for displaying what is essentially browse is just bad it is a security hole in the making since it is not guaranteed the user can see up the complete tree. Especially with RM & security clearance. LR should be used for simple reporting NOT as a replacement for real api work. Without some kind of caching and optimization will absolutely suck for performance and impact the DB like crazy since for each row it will do the same thing repeatedly.

as for this

Regarding my statement "If an item is nested two or more levels deep, then there are multiple paths that can be followed., it's obvious from the example I gave: 2991 => 2001, or 2991 => 2111 => 2001.

As I said, all the result states is 2991 has an ancestor of 2001 and an ancestor of 2111. There is nothing about relationships. Not sure how that derived. Sorry. ( pulling in my Canadian response now )

I need to swing around and ask, what is the root problem they are trying to solve?

Patrick_McManus

The root problem is reporting performance in general. This was just an example of the types of queries. Similar queries are written against other tables with recursive hierarchies, such as KUAF. I don't ask why with 37 clients all trying to report in different ways.

I get that Content Server is optimized for transaction processing, not least at the database level. However, Content Server is enterprise software that is targeted at enterprise customers. Your main client base is largely made up of Fortune 500-level companies and large government departments. These share two important characteristics - they generate huge volumes of content (data) in Content Server, and they have large cadres of senior management who demand volumes of reports and analytics on all manner of probable and improbable extracts from said data. The immediate solution is LiveReports /WebReports querying the core ContentServer database, but there are limits to how far you can satisfy the reporting demands of senior management with that solution, and still maintain an acceptable standard of performance.

David Templeton

For the sake of completeness blah, blah, blah LOL

This will get you the list in order from the top of the tree to the object on my system

DataID Name Level
2000 Enterprise 1
49787 >Departments 2
49788 Marketing 3
61967 Household Demand Operations 4
62115 Projects + Analytics 5
62116 APRD Challenge Campaign 6

This is NOT my recommendation for usage :-)

John Underhill

The CONNECT BY PRIOR wouldn't work on the Ancestors tables. It would work on DTREECORE as there is a single PARENTID for each DATAID as @Benjamin Shapiro said.

That said, be aware that some nodes in the tree are Volumes so will have negative PARENTID. Maybe ABS(PARENTID) would work but I've not tried it.

FWIW I don't think it's unreasonable to want to query for reports on structure and volumes. When you're into 100's of millions of objects it's hard to get a handle through APIs or the UI. But maybe the merits of doing it are a different discussion.

Patrick_McManus

@John Underhill said:
The CONNECT BY PRIOR wouldn't work on the Ancestors tables. It would work on DTREECORE as there is a single PARENTID for each DATAID as @Benjamin Shapiro said.

That said, be aware that some nodes in the tree are Volumes so will have negative PARENTID. Maybe ABS(PARENTID) would work but I've not tried it.

FWIW I don't think it's unreasonable to want to query for reports on structure and volumes. When you're into 100's of millions of objects it's hard to get a handle through APIs or the UI. But maybe the merits of doing it are a different discussion.

That's what I observed also. In my example above, the document represented by DataID 2991 has multiple ancestors, multiple "PRIORs to CONNECT BY".
Volumes are not necessarily undesireable here, as that could tell you if a document was in the Enterprise Workspace or a Personal Workspace, for example.

John Simon

Just be careful with ABS(PARENTID). In oracle, even if you have an index on PARENTID the function (ABS in this case) renders that index invalid resulting in full table scans. If this is something you do a lot then you will want to create another index based on the function.
Cheers...

David Templeton

to break this down further

the customer needs reports
Sure.
The customer wants a 'path' of the object to be shown in LR
Understand that, does the customer need the entire hierarchy up the tree? Given the average depth is 13 levels this gets to be really messy really quickly. Not to mention the other points on security etc.
37 clients...
If there are 37 clients needing this functionality then it is a very strong position that be requested that LR be enhanced to provide a column in the object display of the parentage for the given object. Then this can be optimized like crazy since search results do this sort of thing already.
CS reporting for large datasets sucks! ( paraphrasing )
ok I can respect that, help me make the product better by getting this data to those that matter, the PM's. Not working around it cause if we change the system, and we will, such integration will break and then who screams?

Patrick_McManus

Maybe I should rephrase the problem slightly. DTreeAncestors is just one example of a broader problem that many clients face when writing LiveReports. In my case, our clients write their own reports, and sometimes wish to show at least a part of the folder tree above a document. Even with knowledge of the database schema, there is no easy way to do this from DTreeAncestors. Other complexities arise when reporting from tables like KUAF and DTree, which are both generalized to contain many different types of objects, and are self-referential. What I ultimately want to do is export data from these tables (plus some other supporting ones) to an external database, with a schema that is better optimized for reporting efficiency and ease of query writing.

Benjamin Shapiro

I should also note that if OpenText doesn't want raw DB queries, one can use the core WebReport tag "NODEFINO:LOCATION" or "NODEINFO:PATH", but I don't know how well that would work for your export scenario.

Patrick_McManus

Thanks for the tip, Ben. I am not familiar with WebReports, so I don't know either.

Greg Griffiths

The CS database is mainly optimised for use rather than reporting, in the past I've copied over some content to a separate DB (and server) for reporting, including transforming the data into "eaiser" structures for reporting, to allow an easier format for querying of key data as well as moving the hit of the report to be moved off the operational DB.

Patrick_McManus

@Greg Griffiths said:
The CS database is mainly optimised for use rather than reporting, in the past I've copied over some content to a separate DB (and server) for reporting, including transforming the data into "eaiser" structures for reporting, to allow an easier format for querying of key data as well as moving the hit of the report to be moved off the operational DB.

That is under consideration, but there are potential security and compliance issues. The reporting database is no longer subject to Content Server's permissions and security model, which could allow users to access content which they cannot see in Content Server. Also, the exported copies of records-related content and metadata are no longer controlled by Content Server's Records Management features, and may be retained beyond their disposition deadlines.

Content Server data that is exported to a reporting database is still subject to E-Discovery and Access To Information demands.

Greg Griffiths

Patrick, in this specific case we use a Materialised View in Oracle to provide the data, so it was kept in line with RM etc, we also created the queries on the main server, so that they respected all the perms etc and then moved them to the Reporting DB. Agree completely with the points you raise

David Templeton

@Greg Griffiths said:
Patrick, in this specific case we use a Materialised View in Oracle to provide the data, so it was kept in line with RM etc, we also created the queries on the main server, so that they respected all the perms etc and then moved them to the Reporting DB. Agree completely with the points you raise

@Greg_Griffiths fyi the materialized view is a really expensive construct :-) Since that updates the data in realtime. The load on the system must be crazy?!?!??!

Greg Griffiths

@David Templeton said:

@Greg Griffiths said:
Patrick, in this specific case we use a Materialised View in Oracle to provide the data, so it was kept in line with RM etc, we also created the queries on the main server, so that they respected all the perms etc and then moved them to the Reporting DB. Agree completely with the points you raise

@Greg_Griffiths fyi the materialized view is a really expensive construct :-) Since that updates the data in realtime. The load on the system must be crazy?!?!??!

we had the Materialised Views on a daily refresh cycle so it wasnt that bad and the users accepted that the data would be from the previous working day. Issue was more clients infra provider couldnt provide a powerful enough Oracle RAC setup to handle the load for Operations and Reporting so we had to split into two.

Chris Meyer

@Patrick_McManus said:
FWIW what I am investigating is whether it is feasible to export a subset of the core CS database that the clients most often query to an external database, and in the process to transform it into a more reporting-efficient schema.

How big do you expect your reports to get, and what type of data are you reporting on? Categories & Attributes?

What's the target output? Excel? HTML (with paging, filtering, sorting)?