How do I retrieve all Activities of a BPMN 2.0 Business Process using SQL?

[wivel]

Hi

I am trying to construct an SQL, that can give me the list of all the Activities of a specific BPMN 2.0 Business Process, but I cant seem to figure out how elements are linked hierarchically to each other in the Project browser. If I place each Business Process inside its own package, I can use the Package ID instead, but having a Package for each Business Process seems a bit overkill to me.

I am using the EA BPMN 2.0 Businesss Process as a container for the activities, lanes pools and the like, so the hierarchy can also include Pools, Lanes and BPMN sub-processes.

Do I need to traverse Diagrams and Diagram links to achieve what I need? If so, I will need to use recursive SQL, but as far as I can see, it is not working inside EA. Or is the easiest solution to add a Package for each Business Process?

The full story of what I am trying to achieve is, for each Activity within a given Business Process, list all the Components (UML Component) that is linked to that activity using the Information Flow association.  This information will be represented in a Custom Table in EA.

Regards

Henrik Wivel

[qwerty]

I'm not using BPMN (at the moment) so I can't recall how they manage it. But usually you have the activities inside that business process (which IIRC is a stereotyped activity as well). Now, that's element inside element and you can retrieve it via t_object.ParentID (of child) == Object_ID (of parent).

q.

[Geert Bellekens]

Henrik,

The idea is that you have

- Business Process
  - Pool
    - Lane
      - Activity

All of these are t_object, linked with ParentID

But sometimes activities can end up in the pool, or in the Business Process, so I would check of the parentID to be the objectID of either Lane, Pool or Business Process.

On the other hand, one of my "best practices" is to create a package for each business process. You can then indeed use PackageID to query them, but it also has other benefits (exports to xmi, apply user lock, version control)

Geert

[wivel]

Hi guys

Thanks for your replies. You are both right in you comments and after having looked at the tree traversal, I am leaning more towards using a Package for each Business Process. This will also help minimising the complexity of the SQL query.

Just for educationg myself. If I choose to traverse the hierarchical structure like this, will I nog have to go the recursive SQL way or can I use sub selects? I can't really see how sub selects can help, since I so not know the depth of the tree and at which level the Activities reside. From what I have read so far, EA does not like recursive SQL.
 
I have put en example model of what I am trying to achieve on dropbox here: https://www.dropbox.com/s/k0ute3tkiqieen1/Systems.eapx?dl=0

Henrik
 

[Geert Bellekens]

You don't need a recursive query as you know exactly how deep you want to go (BP - Pool - Lane - Activity) (unless you use subprocesses, in that case you may need to go a level deeper)

For each level you can make a new join with t_object.

Geert

[qwerty]

Not sure, but MSAccess SQL blabber can't do sub-queries, can it? I got used to just simple queries and do any recursion in program text from outside. Works fine for me all time.

q.

[wivel]

Hi Geert

True, but unfortunately I don't know the depth of the tree, since we currently have no fixed definition of how deep we should model our processes. It is unfortunately left up to the projects do decide themselves. I am trying to come up with a generic SQL, that will work in (almost) any situation, which leads me towards the Package approach. Using a package to contain the process, the depth will no longer be an issue.

Henrik

[wivel]

Hi Q

Not sure about MS Access either. The EA model I am working on is stored on an MS SQL server, so it should not be an issue.

Henrik

[qwerty]

Most likely :-) But remember that you can't execute those "complicated" queries on .eap repositories.

q.

[wivel]

I know, Q. Just have to stick to a 'real' SQL DB 

Henrik