Hierarchical usage search in Enterprise Architect with SQL

How can I find out where an element or one of its specialized elements is used on a diagram in Enteprise Architect?

With this SQL search you can answer that question immediately and in a useful way.

Hierarchical usage search regults

Free download

[ecwid_product store_id=”6824265″ product_id=”48156490″]

 The model

Suppose we have modeled an inheritance hierarchy of animals as such

Animals Hierarchy

And our animals are being used as the classifier of Activity partitions in various Activity diagrams

Activity2

Activity1

And on the occasional Sequence diagram

Interaction1

Even with a simple example like this one it is not trivial to find all diagrams that use a rodent, or a bird.

The query

The query should return all diagrams that use an element either as link or as classifier. It should also include the diagrams for the sub-classes of the element we are looking for.

In theory a search like this should be carried out recursively, traversing the hierarchy until it reaches the bottom of the hierarchy. Unfortunately SQL and recursive are not the best friends, so we will have to limit the search to a pre-defined number of levels. In this case we go three levels down, so from the parent, to the the children, the grandchildren and the great-grandchildren.

Each of these levels is queried individually and each of the queries are added together with unions.

The first part of the query is to find diagrams that use the parent element as a link:

select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  po.Name as UsedElement, 'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((t_object po
inner join t_diagramObjects do on do.Object_ID = po.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'

Then we get the diagrams that use the parent element as a classifier

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  po.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((t_object po
inner join t_object poi on po.Object_ID = poi.Classifier)
inner join t_diagramObjects do on do.Object_ID = poi.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'

Then descending a level we get the diagrams showing the children as link. Note that we include all possible variations of Generalization and Realization just to be sure we have everything. EA has been know to be not too consistent with these connector types in the past.

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c1.Name as UsedElement,'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c1.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

And we do the same for the children being used a classifier.

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c1.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_object c1i on c1.Object_ID = c1i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c1i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

Descending another level to the grandchildren

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c2.Name as UsedElement,'Link' as UsageType,
 package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
 from ((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c2.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

And the grandchildren as classifier

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c2.Name as UsedElement,'Classifier' as UsageType,
 package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
 from (((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_object c2i on c2.Object_ID = c2i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c2i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

Then to the last level, the great-grandchildren as link

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c3.Name as UsedElement,'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_connector poc3 on poc3.End_Object_ID = c2.Object_ID)
inner join t_object c3 on c3.Object_ID = poc3.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c3.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc3.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

An the last one, the great-grandchildren as classifier

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c3.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_connector poc3 on poc3.End_Object_ID = c2.Object_ID)
inner join t_object c3 on c3.Object_ID = poc3.Start_Object_ID)
inner join t_object c3i on c3.Object_ID = c3i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c3i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc3.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

The complete query then becomes

select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  po.Name as UsedElement, 'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((t_object po
inner join t_diagramObjects do on do.Object_ID = po.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  po.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((t_object po
inner join t_object poi on po.Object_ID = poi.Classifier)
inner join t_diagramObjects do on do.Object_ID = poi.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c1.Name as UsedElement,'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c1.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c1.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_object c1i on c1.Object_ID = c1i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c1i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c2.Name as UsedElement,'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c2.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c2.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_object c2i on c2.Object_ID = c2i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c2i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c3.Name as UsedElement,'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_connector poc3 on poc3.End_Object_ID = c2.Object_ID)
inner join t_object c3 on c3.Object_ID = poc3.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c3.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc3.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c3.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_connector poc3 on poc3.End_Object_ID = c2.Object_ID)
inner join t_object c3 on c3.Object_ID = poc3.Start_Object_ID)
inner join t_object c3i on c3.Object_ID = c3i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c3i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc3.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

The results

If you execute the search for Rodent you get something like following

Hierarchical usage search results_raw

By dragging the column UsedElement up to the grouping header you get an even better result

Hierarchical usage search regults

Although the icon for diagrams in search results is a bit weird, you can still use them as you would other search results. You can double-click a line to open the diagram, or you can right click and select it in the project browser.

More about SQL Searches

Instant impact analyses in Enterprise Architect with SQL searches

One of the major benefits of using a tool such as Enterprise Architect is that you create traceability. You store the relations between different model elements in your model.

The tricky part is to effectively use that traceability for your impact analyses. Following these instructions you can create SQL Searches in EA that instantly show the impact a certain element has on the rest of the model and present them in a clear and useful way.

Traceability_results_final

The meta model

In order to do impact analysis you have to know, and preferable document, your meta model. The meta model describes which elements from your model are to connected to each other, and it also describes which relation is used.

For the benefit of this example lets start with an imaginary meta model.

Traceability_metamodel

While this view of the meta model already helps a lot we still need to know exactly how these meta-concepts are realized in our Enterprise Architect model.

The model

Traceability_example model

So we end up with a model that looks a bit like this. You see that even for a miniature model like this it comes rather complicated very quickly. The questions that we need to answer when doing impact analysis questions such as

  • If I change requirement RQ-002, which other elements in my model are impacted?
  • I I change the CreateNewOrder function, where can I expect impact?

In general you’ll want to know

  • If I change X how does that impact the rest of my model.

Graphically that looks a bit like this:

Traceability_views

Enterprise Architect has an excellent view called traceabilty view

Traceability_view

This is great when working on the model, but it doesn’t really give you an overview, and it can’t be used for exporting or document generation.

The query

In order to get an overview of all impacted elements will make a custom SQL Search. See Harvesting the power of EA’s SQL searches for more information on how to make and use SQL searches.

We’ll want to create a search starting from each of the different levels. From their we work our way through the meta-models layer using a union for each level.

Lets start with the first one, from the Requirements to Use Case,  to Business Process, and to Application Service.

It’s best to work through this gradually. The first query will only return the requirement itself.

select req.ea_guid AS CLASSGUID, req.Object_Type AS CLASSTYPE,'1_Requirement' as TraceLevel, req.Name, req.Stereotype
, package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((( t_object req
inner join t_package package on package.Package_ID = req.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where req.name like '#WC#<Search Term>#WC#'
and req.Object_Type = 'Requirement'

Traceability_searchFromReq1

The we tackle the next level and we go from the Requirement to the Use Case. We add this query with a union to the previous query.

union
select uc.ea_guid AS CLASSGUID, uc.Object_Type AS CLASSTYPE,'3_Use Case' as TraceLevel, uc.Name, uc.Stereotype
, package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((( t_object req
inner join t_connector requc on requc.End_Object_ID = req.Object_ID)
inner join t_object uc on requc.Start_Object_ID = uc.Object_ID)
inner join t_package package on package.Package_ID = uc.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where req.name like '#WC#<Search Term>#WC#'
and req.Object_Type = 'Requirement'
and uc.Object_Type = 'UseCase'

Notice that we make a join to t_connector and then back to t_object for the Use Case. Mind the direction of the connector between use case and requirement. Since it starts at the use case and goes to the requirement we need to join them using the appropriate End_Object_ID or Start_Object_ID.

Next part is the Business Process. Again we join t_connector and t_object to reach the business processes

union
select bp.ea_guid AS CLASSGUID, bp.Object_Type AS CLASSTYPE,'2_Business Process' as TraceLevel, bp.Name, bp.Stereotype
, package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((( t_object req
inner join t_connector requc on requc.End_Object_ID = req.Object_ID)
inner join t_object uc on requc.Start_Object_ID = uc.Object_ID)
inner join t_connector ucbp on ucbp.Start_Object_ID = uc.Object_ID)
inner join t_object bp on ucbp.End_Object_ID = bp.Object_ID)
inner join t_package package on package.Package_ID = bp.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where req.name like '#WC#<Search Term>#WC#'
and req.Object_Type = 'Requirement'
and uc.Object_Type = 'UseCase'
and bp.Object_Type = 'Activity' and bp.Stereotype = 'process'

From the business process we can reach the Business Information Items. Notice that we only return the information items that are the results of the business processes. Information items that serve as input to our business are not impacted when we change our business process.

union
select bi.ea_guid AS CLASSGUID, bi.Object_Type AS CLASSTYPE,'2_Business Information Items' as TraceLevel, bi.Name, bi.Stereotype
, package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((( t_object req
inner join t_connector requc on requc.End_Object_ID = req.Object_ID)
inner join t_object uc on requc.Start_Object_ID = uc.Object_ID)
inner join t_connector ucbp on ucbp.Start_Object_ID = uc.Object_ID)
inner join t_object bp on ucbp.End_Object_ID = bp.Object_ID)
inner join t_connector bpbi on bpbi.Start_Object_ID = bp.Object_ID )
inner join t_object bi on bpbi.End_Object_ID = bi.Object_ID )
inner join t_package package on package.Package_ID = bi.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where req.name like '#WC#<Search Term>#WC#'
and req.Object_Type = 'Requirement'
and uc.Object_Type = 'UseCase'
and bp.Object_Type = 'Activity' and bp.Stereotype = 'process'
and bi.Object_Type = 'InformationItem'

The last level we want to add to our impact analysis query is the Application Function. These functions are again linked to the use cases. Notice that we both test the stereotype for NULL as for an empty string. That is just to be sure to get all the Application Functions. EA is know to be inconsistent in some areas, sometimes using NULL, sometimes an empty string or 0.

union
select af.ea_guid AS CLASSGUID, af.Object_Type AS CLASSTYPE,'4_Application Function' as TraceLevel, af.Name, af.Stereotype
, package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((( t_object req
inner join t_connector requc on requc.End_Object_ID = req.Object_ID)
inner join t_object uc on requc.Start_Object_ID = uc.Object_ID)
inner join t_connector ucaf on ucaf.End_Object_ID = uc.Object_ID)
inner join t_object af on ucaf.Start_Object_ID = af.Object_ID)
inner join t_package package on package.Package_ID = af.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where req.name like '#WC#<Search Term>#WC#'
and req.Object_Type = 'Requirement'
and uc.Object_Type = 'UseCase'
and af.Object_Type = 'Activity' and (af.Stereotype is null or af.Stereotype = '')

Now if you paste all the different parts of the query together you can start doing impact analysis and see the impact a single requirement has to the rest of the system.

If at first you run the query the results look a bit like this

Traceability_results_raw

But if you drag the “TraceLevel” up to the grouping part

Traceability_results_group

and then remove the column altogether from the results then you get a much nicer looking result

Traceability_results_final

Now you can create similar searches from the other elements in your meta-model, each time working your way up or down the model.

Below the complete SQL code for the search described here.

select req.ea_guid AS CLASSGUID, req.Object_Type AS CLASSTYPE,'1_Requirement' as TraceLevel, req.Name, req.Stereotype
, package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((( t_object req
inner join t_package package on package.Package_ID = req.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where req.name like '#WC#<Search Term>#WC#'
and req.Object_Type = 'Requirement'
union
select uc.ea_guid AS CLASSGUID, uc.Object_Type AS CLASSTYPE,'3_Use Case' as TraceLevel, uc.Name, uc.Stereotype
, package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((( t_object req
inner join t_connector requc on requc.End_Object_ID = req.Object_ID)
inner join t_object uc on requc.Start_Object_ID = uc.Object_ID)
inner join t_package package on package.Package_ID = uc.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where req.name like '#WC#<Search Term>#WC#'
and req.Object_Type = 'Requirement'
and uc.Object_Type = 'UseCase'
union
select bp.ea_guid AS CLASSGUID, bp.Object_Type AS CLASSTYPE,'2_Business Process' as TraceLevel, bp.Name, bp.Stereotype
, package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((( t_object req
inner join t_connector requc on requc.End_Object_ID = req.Object_ID)
inner join t_object uc on requc.Start_Object_ID = uc.Object_ID)
inner join t_connector ucbp on ucbp.Start_Object_ID = uc.Object_ID)
inner join t_object bp on ucbp.End_Object_ID = bp.Object_ID)
inner join t_package package on package.Package_ID = bp.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where req.name like '#WC#<Search Term>#WC#'
and req.Object_Type = 'Requirement'
and uc.Object_Type = 'UseCase'
and bp.Object_Type = 'Activity' and bp.Stereotype = 'process'
union
select bi.ea_guid AS CLASSGUID, bi.Object_Type AS CLASSTYPE,'2_Business Information Items' as TraceLevel, bi.Name, bi.Stereotype
, package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((( t_object req
inner join t_connector requc on requc.End_Object_ID = req.Object_ID)
inner join t_object uc on requc.Start_Object_ID = uc.Object_ID)
inner join t_connector ucbp on ucbp.Start_Object_ID = uc.Object_ID)
inner join t_object bp on ucbp.End_Object_ID = bp.Object_ID)
inner join t_connector bpbi on bpbi.Start_Object_ID = bp.Object_ID )
inner join t_object bi on bpbi.End_Object_ID = bi.Object_ID )
inner join t_package package on package.Package_ID = bi.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where req.name like '#WC#<Search Term>#WC#'
and req.Object_Type = 'Requirement'
and uc.Object_Type = 'UseCase'
and bp.Object_Type = 'Activity' and bp.Stereotype = 'process'
and bi.Object_Type = 'InformationItem'
union
select af.ea_guid AS CLASSGUID, af.Object_Type AS CLASSTYPE,'4_Application Function' as TraceLevel, af.Name, af.Stereotype
, package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((( t_object req
inner join t_connector requc on requc.End_Object_ID = req.Object_ID)
inner join t_object uc on requc.Start_Object_ID = uc.Object_ID)
inner join t_connector ucaf on ucaf.End_Object_ID = uc.Object_ID)
inner join t_object af on ucaf.Start_Object_ID = af.Object_ID)
inner join t_package package on package.Package_ID = af.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where req.name like '#WC#<Search Term>#WC#'
and req.Object_Type = 'Requirement'
and uc.Object_Type = 'UseCase'
and af.Object_Type = 'Activity' and (af.Stereotype is null or af.Stereotype = '')