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

UML Best Practice: Attribute or Association

Short

Use Associations for Classes and Attributes for DataTypes

Purpose

Make an informed choice between Attributes and Associations when modeling a relation between two Classifiers.

Details

When modeling the structure of your system there are basically two ways to express a structural relationship between two Classifiers. You could use an Association between the two Classifiers, or you could create an Attribute owned by one Classifier with it’s type set to the other Classifier.

Both ways, Association or Attribute are pretty much equivalent. There’s not really a big difference between the two except for personal preferences.

The problem with modeling teams working on the same model is of course that you can’t allow personal preferences, you have to make a clear choice what to use in which circumstances.

To explore the details of the two approaches it is best to have a look at the UML meta model.

In this meta diagram we see that both the Attribute as the Association use the same Property object to link to a type.

The association has two or more Properties as MemberEnd. Each of these Properties has a Type, so that is the way the association links two or more Classes. The derived link from Association to EndType is derived from the type of the Properties in the memberEnds.

The Attribute of a class is in fact a Property in the ownedAttributes of a class.  Again through the fact that a Property is a TypedElement an thus has a Type as “type” we get the relation to another Classifier.

In the years I’ve been working with different modelling teams I’ve found that the rule that works best is to use Associations for Classes and Attributes for DataTypes.

Now whats the difference between a Datatype and a Class? Well, they are actually pretty similar. The UML specification states:

A data type is a special kind of classifier, similar to a class. It differs from a class in that instances of a data type are identified only by their value.

So that means that DataTypes are much like the primitive types and enumerations we know in the programming world. This concept is generally referred to as being immutable. So you can think of things like Integer, Date, MoneyAmount, but also enumerations such as Color, DayOfTheWeek etc..

If we add Datatype and Enumeration to the meta diagram we get following

You can see that DataType is a subtype of Classifier, and that Enumeration is a subtype of Datatype.

Following example shows how to use Classes and Datatypes when following this best practice.

In this diagram we see two Enumerations: Currency and ProductCategory. ProductCategory is being used as the type of the attribute Product.Category while Currency is being used by the Datatype MoneyAmount.

I’ve added dependencies to visually express which Datatype is being used by which Classifier, but those dependencies are usually not there in a production model.

More UML best practices

UML Composition vs Aggregation vs Association

What makes a UML Composition different from an Aggregation or a regular Association?

The concepts of Association, Aggregation and Composition exist in UML since the first published versions, but the exact meaning of these concepts, especially the Aggregation still leads to heated debates among UML experts.

But before we go into the details, let’s have a look at how these concepts are defined in UML. I guess every UML user is familiar with the graphical notation, but how do these concepts look like in the UML (v 2.5) meta model?

UML 2.5 Associations Meta Model

This is a the part of the UML meta model that defines Association. (I’ve hidden the elements not relevant to the subject for clarity)

What we see is that an Association has at least two Properties in the role of memberEnd. A property has an attribute aggregation of type AggregationKind. It’s this AggregationKind that specifies the difference between a regular Assocation, an Aggregation and a Composition.

The three possible values for AggregationKind are defined in the UML specifications as follows:

  • none
    Indicates that the Property has no aggregation.
  • shared
    Indicates that the Property has a shared aggregation.
  • composite
    Indicates that the Property is aggregated compositely, i.e., the composite object has responsibility for the existence and storage of the composed objects (parts).

But a bit further, in the semantics section of Properties we find the same explanation except for a small addendum

  • shared
    […] Precise semantics of shared aggregation varies by application area and modeler.

So basically the OMG is saying: We don’t know what it means, make up your own definition.

Looking for more clues in the definition of Association we find the constraint:

Only binary associations can be aggregations.

memberEnd->exists(aggregation <> AggregationKind::none) implies (memberEnd->size() = 2 and memberEnd->exists(aggregation = AggregationKind::none))

OK, that doesn’t really help us. All it states is that the Aggregations and Compositions can only exist in Associations that have maximum two members, but that’s like the “normal” Association for most of us. I haven’t seen many Associations with more then two members yet.

And the second part of the OCL constraint tells us that only one of the two ends can play the whole part, so the other end must play the part part.

Looking further in the specs we find in the semantics section of the Property the following

Composite aggregation is a strong form of aggregation that requires a part object be included in at most one composite object at a time. If a composite object is deleted, all of its part instances that are objects are deleted with it.

So that paragraph already tells us a little bit more about the nature of the Composition. Let’s dissect this paragraph and figure out what to remember

  • that requires a part object be included in at most one composite
    object at a time
    So a part cannot play the role of part in two compositions at the same time. This implies that the multiplicity of a composite association can only be [0..1] or [1..1] on the composite end.
  • If a composite object is deleted, all of its part instances that are objects are deleted with it.
    This is one of the parts where v 2.5 is different from previous versions. Previous versions of the UML specifications had the phrase “are normally deleted with it”. By leaving the “normally” out there no more ambiguity. Deleting the whole will always result in deleting the part in a composition.

But there still a loophole for the delete story.

NOTE. A part object may (where otherwise allowed) be removed from a composite object before the composite object is deleted, and thus not be deleted as part of the composite object.

So before the whole is deleted we can remove the part to avoid having to delete the part as well.

And then there a last paragraph that deals with Compositions

Compositions may be linked in a directed acyclic graph with transitive deletion characteristics; that is, deleting an object in one part of the graph will also result in the deletion of all objects of the  subgraph below that object. The precise lifecycle semantics of composite aggregation is intentionally not specified. The order and way in which composed objects are created is intentionally not defined. The semantics of composite aggregation when the container or part is typed by a DataType are intentionally not specified.

  • Compositions may be linked in a directed acyclic graph with transitive deletion characteristics:
    Now this is a difficult one. The directed acyclic graph part tells us that, when following the links from whole to part, we will not visit the same element twice. Combined with the “at most one composite at a time” constraint this even means that composite relations form a hierarchical tree. The transitive deletion part means that deleting one element from the tree would then delete the whole branch under this element. Unfortunately the word may in the sentence means that this again is no hard constraint, but merely an indication of how it can be used.
  • […]are intentionally not specified
    This is simply sad… These few sentences basically tell us again nothing at all, except that we shouldn’t look for a specification of these aspects in the UML specifications.

So that’s about all  the UML specification has to say about the different types of aggregation. All other constraints you find in books and on the internet are purely interpretations and added semantics of the authors.

Now let’s have a look at some typical examples of Composition and Aggregation (or aggregationKind = shared and aggregationKind = composite as we learned from the specifications)

This example shows the class structure of the popular social networking site LinkedIn

On the right we see the Group structure as a set of Compositions because
a) each “whole” can be considered as a grouping of “parts”
b) each “part” can belong to only one “whole” at a time
c) the “parts” should be deleted when the “whole” is deleted (except when we move them to another “whole” first)

Note that discussions can be moved from one section to another (Usually from Discussions to Jobs or Promotions), but they cannot be part of two sections at the same time.

The relation between Group and User however is an Aggregation because
a) a Group can be considered a “grouping” of users
b) a User can be part of multiple Groups at the same time
c) a User should not be deleted when a Group is deleted.

To summarize

The Composition is a type of Association with real constraints and impact on development, whereas the Aggregation is purely a functional indication of the nature of the Association with no technical impact.