Searching for REAL orphans in Enterprise Architect

Enterprise Architect comes standard with an Find Orphans search, which is supposed to give you a list of all elements that are no longer used in the model, and thus better be removed from the model altogether.

Find Orphans

The problem with this search is that it will only report elements that are not shown on a diagram; which should not be the only criterion to determine if an element is an orphan or not. Not being on a diagram is just fine. I have lots of elements in my model that are not on a diagram, that doesn’t mean they are not used anymore.

If you are going to delete all elements reported in the standard orphans search you might do serious damage to your model.

So in order to aid with the maintenance of a large model I wrote the following SQL Search.

select distinct o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name, o.Stereotype,
package.name as PackageName ,package_p1.name as PackageLevel1,package_p2.name as PackageLevel2 ,package_p3.name as PackageLevel3 
from ((((((((((((((((( t_object o
left join t_diagramobjects dob on dob.[Object_ID] = o.[Object_ID])
left join t_object inst on inst.[Classifier] = o.[Object_ID])
left join t_objectproperties otv on otv.VALUE = o.[ea_guid])
left join t_connectortag ctv on ctv.VALUE = o.[ea_guid])
left join t_attributetag atv on atv.VALUE = o.[ea_guid])
left join t_operationtag optv on optv.VALUE = o.[ea_guid])
left join t_object u on u.Pdata1 = o.ea_guid)
left join t_connector cs on cs.Start_Object_ID = o.Object_ID)
left join t_connector ce on ce.End_Object_ID = o.Object_ID)
left join t_attribute att on att.Classifier like o.Object_ID)
left join t_operation op on op.Classifier like o.Object_ID)
left join t_operationparams opp on opp.Classifier like o.Object_ID)
left join t_object owned on owned.ParentID = o.Object_ID)
inner join t_package package on o.package_id = package.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 
o.Package_ID in (#Branch#)
and (o.name is null or o.Name like '#WC#<Search Term>#WC#')
and o.Object_Type not in ('Package')
and (o.Stereotype is null or o.Stereotype not in ('model document'))
and dob.Object_ID is null
and inst.Object_ID is null
and otv.VALUE is null
and ctv.Value is null
and atv.Value is null
and optv.Value is null
and u.Object_ID is null
and cs.Start_Object_ID is null
and ce.End_Object_ID is null 
and att.Classifier is null
and op.Classifier is null
and opp.Classifier is null
and owned.Object_ID is null

This search will look for all elements that

  • Are located in the currently selected package branch
  • Are not a package or a model document  (section in a virtual document)
  • Are not used on a diagram
  • Are not used as the Classifier of an instance
  • Are not referenced by any tagged value
  • Are not used as the source or target of any relation
  • Are not used as the type of an attribute
  • Are not used as the return type of an operation
  • Are not used as the type of a parameter
  • Do not own any other elements

Elements that show up in this search are most probably real orphans that clutter your model and can be safely deleted.

Of course, before you delete anything from the model, make sure you have a backup!

Find Real OrphansIn order to use this search create a new search of type SQL and paste the above query into the query builder field.

If you find anything that should be added to this search please let me know.

Related

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 = '')

Harvesting the power of EA’s SQL searches

Enterprise Architect from Sparx Systems is the UML CASE tool I use day-to-day to get my analysis done.

One of the great features of EA is that it allows you to define your own searches using SQL. Because EA runs on pretty much any type of database (the standard for local models is MS Access) defining your searches in SQL can be very powerful.

On of the problems with working on a big (as in 50.000 elements) model is the ability to quickly find the elements you need.

Out of the box EA comes with some search options. You can search in the project browser, one by one, or you can search the model with one of the predefined searches.

Free download

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

Searching in the project browser is sometimes useful, but when you need a specific class or attribute, it can take quite a while to find it since EA selects each candidate one by one, and you have to click the “next” button to search further.

Using the predefined searches like “simple” or “extended” works to some extend, but they are slow as hell, only give you Elements as a result, and they will probably show too much noise.

But as mentioned, EA has provided us with an alternative. In my day-to-day modeling I never use of the search options provided by EA, but only my own defined SQL searches because they are much faster and much more precise then the predefined searches.

It also allows me to control exactly what information is shown in the result list:

You can find the full explanation of EA’s search feature in the manual which you conveniently can find online.

Once defined you can also export one or more searches into an xml file that your colleagues can import into their copy of EA.

I’ll just highlight some of the special tricks to make SQL searches work.

First of all the first two columns in your resultset always have to be the same.

  • CLASSGUID should contain the ea_guid column of whatever you are searching for. This column is hidden from the results, but will be used to allow you to select the element in the browser.
  • CLASSTYPE should contain the type of the element you are searching for. This column is used to show the correct icon for this type of element.

To be able to use the content of the search box you need to use <Search Term>. Each occurrence of the <Search Term> is replaced by EA with the context of the search box before the query is executed on the repository.

The usage of wild-cards is another issue. The ansi SQL standard for 0..n characters is %, but of course Microsoft thought it would be better to define their own “standard” for MS Access, and they decided to use * instead.
If you want your SQL searches to be able to run on both local .eap files and DBMS repositories you can use the macro #WC# in the query. Each occurrence of this macro will be replaced by the proper wildcard for the database you are working on.

You don’t have to use the #WC# in the query itself however. You can also type “firstpart%” or “firstpart*” into the searchbox if you prefer to have more control about the search results.

Here are some of my favorite searches

Find Classes or Interfaces by name:

select o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, o.name as Name
,package.name as 'Package Name' ,package_p1.name as 'Package level -1',package_p2.name as 'Package level -2',package_p3.name as 'Package level -3'
from (((( t_object o
inner join t_package package on o.package_id = package.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
o.Object_Type in ('Class','Interface')
and o.Name like '#WC#<Search Term>#WC#'

Find Elements by stereotype:

select o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, o.name as Name
,package.name as 'Package Name' ,package_p1.name as 'Package level -1',package_p2.name as 'Package level -2',package_p3.name as 'Package level -3'
from (((((t_object o
left join t_xref stereo on stereo.Client = o.ea_guid)
inner join t_package package on o.package_id = package.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
o.Stereotype like '#WC#<Search Term>#WC#'
or stereo.description like '@STEREO;Name=#WC#<Search Term>#WC#;#WC#'

Find Attributes by Type:

select a.ea_guid as CLASSGUID,'Attribute' as CLASSTYPE,a.name as Name, a.Type as 'Type',  class.name as 'Class Name'
,package.name as 'Package Name' ,package_p1.name as 'Package level -1',package_p2.name as 'Package level -2',package_p3.name as 'Package level -3'
from (((((t_attribute  a
inner join t_object class on a.object_id = class.object_id)
inner join t_package package on class.package_id = package.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 a.Type like '#WC#<Search Term>#WC#'

Find attributes by name:

select a.ea_guid as CLASSGUID,'Attribute' as CLASSTYPE,a.name as Name, a.Type as 'Type',  class.name as 'Class Name'
,package.name as 'Package Name' ,package_p1.name as 'Package level -1',package_p2.name as 'Package level -2',package_p3.name as 'Package level -3'
from (((((t_attribute  a
inner join t_object class on a.object_id = class.object_id)
inner join t_package package on class.package_id = package.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 a.Name like '#WC#<Search Term>#WC#'

Find Operations by Parameter type:

select o.ea_guid as CLASSGUID,'Operation' as CLASSTYPE,o.name as Name,p.Name as 'Parameter Name',p.Type as 'Type',  class.name as 'Class Name'
,package.name as 'Package Name',package_p1.name as 'Package level -1',package_p2.name as 'Package level -2',package_p3.name as 'Package level -3'
from ((((((t_operation  o
inner join t_operationparams  p on o.operationID = p.operationID)
inner join t_object  class on o.object_id = class.object_id)
inner join t_package  package on class.package_id = package.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 p.type like '#WC#<Search Term>#WC#'

Find Operations by name:

select o.ea_guid as CLASSGUID,'Operation' as CLASSTYPE,o.name as Name, class.name as 'Class Name'
,package.name as 'Package Name',package_p1.name as 'Package level -1',package_p2.name as 'Package level -2',package_p3.name as 'Package level -3'
from (((((t_operation  o
inner join t_object  class on o.object_id = class.object_id)
inner join t_package  package on class.package_id = package.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 o.name like '#WC#<Search Term>#WC#'