Author Topic: Actors Catalogue - SQL query  (Read 1139 times)

tjlindsay

  • EA User
  • **
  • Posts: 21
  • Karma: +2/-0
    • View Profile
Actors Catalogue - SQL query
« on: March 09, 2017, 01:36:09 am »
Hi all,

Problem statement - I am trying to produce an actors catalog which can be generated from EA into Microsoft Word.  The actors catalog is a central folder holding all actors within EA.  Each project (if using use cases) will produce a use case context diagram and re-use the actors available in the central actors catalog.  I want to run a report on the use cases folder, assess all use case elements and identify all actors whom have an association with the uses cases in the said folder, removing duplicates where they exist.  I then want to use the query in my use cases report by means of a fragment.  The result should be a use case report which first details the actors and then the uses cases.

I've tried doing this through sql with no effect, although I think sql is the only way to achieve the desired result.

Would anyone have any ideas on how to achieve the above please?

Help greatly appreciated.

TJ

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7747
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Actors Catalogue - SQL query
« Reply #1 on: March 09, 2017, 02:16:11 am »
Yes, an SQL fragment is the easiest way to achieve that.
Supposing you are starting from a package that contains one or more use case diagrams on where you start from you'll need something like

Code: [Select]
select act.Name AS ActorName, act.Note as [Description-Formatted]
from t_object act
inner join t_connector c on act.Object_ID in (c.Start_Object_ID, c.End_Object_ID)
inner join t_object uc on uc.Object_ID in (c.Start_Object_ID, c.End_Object_ID)
and uc.Object_Type = 'UseCase'
inner join t_diagramobjects uc_do on uc.Object_ID = uc_do.Object_ID
inner join t_diagram d on uc_do.Diagram_ID = d.Diagram_ID
where d.Diagram_ID = #PACKAGEID#

Then in your fragment you can use custom fields
{ActorName} and {Description.Formatted} in a table that will contain all the actors linked to use cases shown on diagrams owned by the package you are reporting on.

Geert

PS. This works for SQL Server. For .eap files (MS-Access) you'll have to make some minor adjustments to the SQL Syntax

tjlindsay

  • EA User
  • **
  • Posts: 21
  • Karma: +2/-0
    • View Profile
Re: Actors Catalogue - SQL query
« Reply #2 on: March 09, 2017, 02:59:14 am »
Hi Geert - Thanks for the response.  I've applied the following, but the report doesn't appear to output the actors yet.  The use cases do have an 'association' to an actor.


I've created a template fragment which has you sql query.  I've created a table in the fragment to output ActorName and Description.Formatted as directed.


I've inserted the fragment in my use case specification report under the tag <diagram


I can confirm that I am using sql server, so it should work.  Any ideas?


Many thanks in advance,


TJ

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7747
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Actors Catalogue - SQL query
« Reply #3 on: March 09, 2017, 04:16:03 am »
No, no ideas without seeing the actual model and templates.

Try the query first seperately in the search scratch pad to be sure that produces the results you need.

Geert

tjlindsay

  • EA User
  • **
  • Posts: 21
  • Karma: +2/-0
    • View Profile
Re: Actors Catalogue - SQL query
« Reply #4 on: March 10, 2017, 02:34:42 am »
Hi Geert,

Unfortunately I am unable to add images to represent what is happening, but will try and explain in textual format (hopefully with more clarity than last time)
The package structure in the Project Browser:

1.Group
1.1 Catalogs
1.1.1 Actors
1.2 Projects
1.2.1 Example Project
1.2.1.1 Use Cases - contains 10 use cases
1.2.1.2 Traceability

I am running a report on package '1.2.1.1 Use Cases'.  The report specification is called 'Use Case Specification' and is structured as follows:


package >
diagram >
{Diagram.DiagramImg}
< diagram
element >
{Template - Use Case Actor Names}
[---Contains element attributes---]
scenario >
Scenarios
structured scenarios >
{Scenario_Structured.Step}   {Scenario_Structured.Action}
exception >
{Exception.Type}:  {Exception.Name} - re-joins the basic path at step {Exception.Join}
< exception   {Scenario_Structured.Uses}
< structured scenarios
< scenario
external requirements >
< external requirements
child elements >
< child elements
< element
child packages >
< child packages
< package

The fragment {Template - Use Case Actor Names} is structured as follows:

custom>
{ActorName}
<custom

The fragment has the following query as specified by Geert.

select act.Name AS ActorName, act.Note as [Description-Formatted]
from t_object act
inner join t_connector c on act.Object_ID in (c.Start_Object_ID, c.End_Object_ID)
inner join t_object uc on uc.Object_ID in (c.Start_Object_ID, c.End_Object_ID)
                              and uc.Object_Type = 'UseCase'
inner join t_diagramobjects uc_do on uc.Object_ID = uc_do.Object_ID
inner join t_diagram d on uc_do.Diagram_ID = d.Diagram_ID
where d.Diagram_ID = #PACKAGEID#
On running the 'Use Case Specification' report on package '1.2.1.1 Use Cases', all use cases are documented, but not the actors associated to the use cases.
I'd even considered creating a virtual document with 2 model documents.  1 model document which reports on the use cases, identifying all actors associated to each use case, removing duplicates and then outputting in a table format recording name and notes.  This is what the sql script is trying to resolve.  The 2nd model document will just concentrate on documenting the details of each use case.

Hopefully this provides additional clarity (hope so anyway).

Many thanks in advance,
TJ

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7747
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Actors Catalogue - SQL query
« Reply #5 on: March 10, 2017, 02:38:40 am »
You have to move the fragment up one level to the package level (it's now on the element level)

Have you tried the query in the search SQL scratch pad (with the actual PackageID of the use case diagrams package)?

Geert

tjlindsay

  • EA User
  • **
  • Posts: 21
  • Karma: +2/-0
    • View Profile
Re: Actors Catalogue - SQL query
« Reply #6 on: March 10, 2017, 03:20:11 am »
Hi Geert,


I've moved the fragment into the package space of the report as directed.  Still not outputting the actors in the document.


Used the SQL scratch pad to identify the Package_ID using the SQL logic:

select * from t_package
where t_package.Name = 'Use Cases'


Identified the package_ID as being 5269


Then ran your fragment query in the SQL Scratch Pad replacing #PACKAGEID# with '5269' which didn't return anything.  SQL script used below:

select act.Name AS ActorName, act.Note as [Description-Formatted]
from t_object act
inner join t_connector c on act.Object_ID in (c.Start_Object_ID, c.End_Object_ID)
inner join t_object uc on uc.Object_ID in (c.Start_Object_ID, c.End_Object_ID)
                              and uc.Object_Type = 'UseCase'
inner join t_diagramobjects uc_do on uc.Object_ID = uc_do.Object_ID
inner join t_diagram d on uc_do.Diagram_ID = d.Diagram_ID
where d.Diagram_ID = '5269'



Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7747
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Actors Catalogue - SQL query
« Reply #7 on: March 10, 2017, 02:13:51 pm »
Hi Geert,


I've moved the fragment into the package space of the report as directed.  Still not outputting the actors in the document.


Used the SQL scratch pad to identify the Package_ID using the SQL logic:

select * from t_package
where t_package.Name = 'Use Cases'


Identified the package_ID as being 5269


Then ran your fragment query in the SQL Scratch Pad replacing #PACKAGEID# with '5269' which didn't return anything.  SQL script used below:

select act.Name AS ActorName, act.Note as [Description-Formatted]
from t_object act
inner join t_connector c on act.Object_ID in (c.Start_Object_ID, c.End_Object_ID)
inner join t_object uc on uc.Object_ID in (c.Start_Object_ID, c.End_Object_ID)
                              and uc.Object_Type = 'UseCase'
inner join t_diagramobjects uc_do on uc.Object_ID = uc_do.Object_ID
inner join t_diagram d on uc_do.Diagram_ID = d.Diagram_ID
where d.Diagram_ID = '5269'
Ah, I think I see it. Try it with

where d.Package_ID = 5269

Geert

tjlindsay

  • EA User
  • **
  • Posts: 21
  • Karma: +2/-0
    • View Profile
Re: Actors Catalogue - SQL query
« Reply #8 on: March 16, 2017, 06:09:35 pm »
Hi Geert,


Apologies for the delay, replaced d.Diagram with d.Package_ID and it works :-).  The SQL that is embedded in my fragment is as follows:



select DISTINCT act.Name AS ActorName, CAST(act.Note AS NVARCHAR(MAX)) as [Description-Formatted]
from t_object act
inner join t_connector c on act.Object_ID in (c.Start_Object_ID, c.End_Object_ID)
inner join t_object uc on uc.Object_ID in (c.Start_Object_ID, c.End_Object_ID)
and uc.Object_Type = 'UseCase'
inner join t_diagramobjects uc_do on uc.Object_ID = uc_do.Object_ID
inner join t_diagram d on uc_do.Diagram_ID = d.Diagram_ID
where d.Package_ID = #PACKAGEID#
and act.Object_Type = 'Actor'


Thanks again for your assistance in getting this to work - greatly appreciated.


Thanks,


TJ.