How to produce a product breakdown structure in a table

[Roche Pso]

I have built a number of system architectures in our EA system model. Each architecture is a hierarchy of objects showing how the system decomposes. Pretty normal stuff. People call these hierarchies product breakdown structures, but all differ on which one is *the* PBS and which ones are other similar views of the system architectures.

Due to working with mechanical engineers I have a need to export various architectures or parts of architectures as PBS tables, they can't cope with diagrams. This is where I am having trouble.

If all the objects in the hierarchy are nested in the project browser then it is easy to make a standard report using the element/child thing in the report template and it comes out with a nice hierarchical list in the report:

If the objects in the hierarchy are linked via some kind of aggregation/composition type relationship, but are not nested in the project browser, I have a working recursive query that can start at any object and give me a table containing all the linked objects beneath it to the bottom of the structure. So I have used this as the basis for a report template and it works fine. HOWEVER... this query and report does not seem to be able to be ordered into the nice hierarchy list I get with the basic report template method. Instead I can choose, in the query, a field to order by, so I can order alphabetically by name, or I can order by the level within the tree etc. None of these give me what I need. I have resorted to outputting to excel and manually reordering the lines into the structure I need.

How do I achieve a proper hierarchy table when things are not nested in the project browser?

[Geert Bellekens]

With a query you should be able to do that.

I usually don't use a recursive query, but instead opt for a "fixed" query with enough levels.
That allows you to keep track of the "fully qualified name" to use as the order by field.

Geert

[Modesto Vega]

Interesting topic and a problem that I face regularly.

What would you put in the query? I am assuming that by using the query a document fragment will be required.

I have a similar requirement and I can only think of one field I could use to order the results of a query per the requirements I need to meet: populate the Alias with an alphanumerical series matching the order I need.

Do you have any other suggestions?

[Geert Bellekens]

Suppose I have the following structure

package1
          - Package1.1
                     - Element1.1.1
                     - Element1.1.2
          - Package1.2
package2

I would make a query that contains a field with the fully qualified name of each element

package1
package1.Package1.1
package1.Package1.1.Element1.1.1
package1.Package1.1.Element1.1.2
package1.Package1.2
package2
And sort on that field.

Geert

[Roche Pso]

I'll give that a go and see what happens

I like the recursive query because I don't have to guess how many levels I will have and I can use it on any object at any level without having to modify it, it's just the output isn't the way I want it 

[Geert Bellekens]

I'll give that a go and see what happens

I like the recursive query because I don't have to guess how many levels I will have and I can use it on any object at any level without having to modify it, it's just the output isn't the way I want it 

I understand. Maybe this output is possible with recursive queries as well, but I don't have enough experience with those (they tend to hurt my head when I try to use them)

Geert

[Modesto Vega]

Suppose I have the following structure

package1
          - Package1.1
                     - Element1.1.1
                     - Element1.1.2
          - Package1.2
package2

I would make a query that contains a field with the fully qualified name of each element

package1
package1.Package1.1
package1.Package1.1.Element1.1.1
package1.Package1.1.Element1.1.2
package1.Package1.2
package2
And sort on that field.

Geert

This does not take into consideration elements linked relationships such aggregations or compositions, elements not nested in the browser - i.e., it does not address visual nesting. This something that Roche mentioned in his original post and caught my attention.

I also agree with Roche that a recursive query factoring in relationships should address the requirement but the problem is getting the thing sorted correctly.

[wivel]

As far as I recall, EA does not like recursive queries. Is that still the case?

Henrik

[Modesto Vega]

As far as I recall, EA does not like recursive queries. Is that still the case?

Henrik

Depends on the RDBMS and how they are implemented. We have written plenty of recursive queries as views and expose them as simple SELECT statements with a WHERE clause.

Generally speaking Sparx EA does not like anything that does not start with a SELECT statement, this immediately eliminates Common Table Expressions. In my opinion, it is already time that limitation is addressed.

[Roche Pso]

Geert's suggestion of creating a path / fully qualified name works perfectly

A heavily simplified version of my query is:

Code: [Select]

select * from (

with recursive hierarchy as (
select
parent.Object_ID as ParentID,
parent.name as ParentName,
child.Object_ID as ChildID,
child.name as ChildName,
parent.Object_ID || '.' || child.Object_ID as pth,
'' as indent
from
t_object as parent
join
t_connector as connector on parent.Object_ID = connector.End_Object_ID
join
t_object as child on connector.Start_Object_ID = child.Object_ID
where
parent.Object_ID = #CurrentElementID# and connector.Connector_Type = 'Aggregation'

union all

select
h.ChildID as ParentID,
h.ChildName as ParentName,
child.Object_ID as ChildID,
child.name as ChildName,
h.pth || '.' || child.Object_ID as pth,
h.indent || '-' as indent
from
hierarchy as h
join
t_connector as connector on h.ChildID = connector.End_Object_ID
join
t_object as child on connector.Start_Object_ID = child.Object_ID
where
connector.Connector_Type = 'Aggregation'
)

select
indent || Childname as rowName
from
hierarchy
order by
pth
)

pth (path seems to be a keyword so I avoided it) and indent are the bits I have added to the base recursive query: sorting by pth gets everything in the right order and then concatenating indent onto the front of the Childname gives you the kind of indenting people are used to in PBS tables (I would like to use space not "-" as the indenting character but for some reason that didnt work)

Thanks Geert!

[Modesto Vega]

Really nice query, that looks like a PostgreSQL, Oracle, and SQLite query and elegant idea. Will love to see a SQL Server version of it.

[Roche Pso]

I am very pleased with it, but I can't take any real credit for it. Co-Pilot gave me the initial query which didn't quite work but had the basic structure right and only needed a small bit of tweaking to make it work in EA. Then Geert provided the last bit of inspiration needed to give the correctly formatted output.

[Modesto Vega]

At least you should take credit for combining your idea with Geert’s suggestion. I’ll see if Copilot can help converting your query to a SQL Server query/view.

[shimon]

Hi,
Sorry to break up the party, but SQL Server does not support CTE ( Common Table Expressions).
When I first saw this solution, my first thought was "that ain't gonna work", until I read on that this is for Oracle or some other DB.
I used to use CTE very often (in DB2). It makes the queries so much easier to understand, test and debug. It is a pity that Microsoft chooses not to support it.
Shimon 

[shimon]

P.S. You can ask copilot to give you fix it so that it works on SQL server. It won't be so elegant, but it'll probably work.
Shimon