sql: activities within a swimlane

[xkelt]

Hi there,

is there a way how to construct a SQL query to fetch BPMN activities that are displayed on a given diagram within a swimlane?

The problem is the activities are not children of the swimlane in the EA model structure. The swimlanes are being reused from a central package.

I can get all the activities and swimlanes for a given diagram, yet I cannot find out the connection between an activity and a swimlane. A spatial one?

Thank you.

[qwerty]

Maybe you can do that if you are one of those SQL hackers. But it's tricky since you need to check if an element is within the bounds defined in the diagram properties. I for myself would use a script to accomplish that (maybe with a little SQL support).

q.

[Geert Bellekens]

I agree with qwerty. It is possible (of course, because all model information is stored in the database) but rather difficult because you would need to compare the position + size of the diagramobject with the position and size of the swimming lane.

That is one of the reasons I always use ActivityPartitions iso swimlanes. ActivityPartitions are actual UML elements that have real relationships with the actions on them and the Activity they are part of.

Swimlanes are nothing more then lines and boxes -> a graphical element, not a model element.

With ActivityPartition it's quite easy to use SQL to figure out which Actions are related to which ActivityPartitions.

Geert

[qwerty]

Geert is absolutely right and I do that too.

q.

[OpenIT Solutions]

Hi,

If your going to use a BPMN Lane the Sparx approach is to create a lane locally for each diagram - but use the participantRef tagged value to link to a library of participants (Role or Entity). Activities are then added as child objects to the local Lane.

The issue with this is that the local Lane name has to be filled in by each modeler - so they might enter something random that doesn't tie up with the actual participant - not very helpful.

For this reason we ended up doing as you have - creating a library of lanes that are reused across the model. Seemed like a good idea at the time. However it has caused problems...for example the RTF report templates become very difficult - as your can't include a child element section for your Lane and have all child activities listed in that section of the doc. We found ways around it - but it adds to complexity...

Re the SQL you would use something like this :

join t_object o_act on o_act.object_id = d_act.object_id and o_act.stereotype = 'Activity'
join t_diagramobjects d_OrgEntity on d_OrgEntity.diagram_id = d_l4.diagram_id
AND d_act.recttop < d_OrgEntity.recttop
AND d_act.rectleft > d_OrgEntity.rectleft
AND d_act.rectbottom > d_OrgEntity.rectbottom
AND d_act.rectleft < d_OrgEntity.rectright

Here the d_OrgEntity is my Lane and d_act is my Activity. Note the join to t_diagramobjects and the checking of the recttop etc...

[xkelt]

I agree with qwerty. It is possible (of course, because all model information is stored in the database) but rather difficult because you would need to compare the position + size of the diagramobject with the position and size of the swimming lane.

That is one of the reasons I always use ActivityPartitions iso swimlanes. ActivityPartitions are actual UML elements that have real relationships with the actions on them and the Activity they are part of.

Swimlanes are nothing more then lines and boxes -> a graphical element, not a model element.

With ActivityPartition it's quite easy to use SQL to figure out which Actions are related to which ActivityPartitions.

Geert

Thanks Geert, I will look into ActivityPartitions.

Saw that being used somewhere in the model. Not sure what exactly it is, nor how consistent it is in the model I have. I'm gonna dig into it.

Anyway, I solved the problem I had using plain (Oracle) SQL.

[xkelt]

Hi,

If your going to use a BPMN Lane the Sparx approach is to create a lane locally for each diagram - but use the participantRef tagged value to link to a library of participants (Role or Entity). Activities are then added as child objects to the local Lane.

The issue with this is that the local Lane name has to be filled in by each modeler - so they might enter something random that doesn't tie up with the actual participant - not very helpful.

For this reason we ended up doing as you have - creating a library of lanes that are reused across the model. Seemed like a good idea at the time. However it has caused problems...for example the RTF report templates become very difficult - as your can't include a child element section for your Lane and have all child activities listed in that section of the doc. We found ways around it - but it adds to complexity...

Re the SQL you would use something like this :

join t_object o_act on o_act.object_id = d_act.object_id and o_act.stereotype = 'Activity'
join t_diagramobjects d_OrgEntity on d_OrgEntity.diagram_id = d_l4.diagram_id
AND d_act.recttop < d_OrgEntity.recttop
AND d_act.rectleft > d_OrgEntity.rectleft
AND d_act.rectbottom > d_OrgEntity.rectbottom
AND d_act.rectleft < d_OrgEntity.rectright

Here the d_OrgEntity is my Lane and d_act is my Activity. Note the join to t_diagramobjects and the checking of the recttop etc...

Wow, I mean – exactly! Figured it out in the morning after some time.

This is the code I used. It solves a couple of other quirks in the model too.


select act.ea_guid As CLASSGUID --must be first
       ,act.Object_type As CLASSTYPE --must be second
       ,prc.alias AS proc_id
       ,prc.name AS proc_name
       ,lane.name as lane_name
       ,act.alias AS act_id
       ,act.name as act_name
       ,'"' + REPLACE(act.note, '"', '''' ) + '"' As [Notes] --helps import multiline data into Excel
        from (
               SELECT prc.object_id
                     ,row_number() OVER (PARTITION BY prc. alias ORDER BY prc.modifieddate DESC) as rn
                FROM t_object prc
               WHERE 1=1
                 AND prc.package_id in (#Branch#) --anchors the search to the selected and child packages only
                 AND prc.stereotype = 'BusinessProcess'
                 --AND prc.alias = 'PLA.2.18' --enter process ID
                ) single_prc
        JOIN t_object prc ON (prc.object_id = single_prc.object_id AND single_prc.rn = 1)
        join t_diagram d on (d.parentid = prc.object_id)
        JOIN t_diagramobjects do ON (do.diagram_id = d.diagram_id)
        join t_object lane on (lane.object_id = do.object_id and lane.stereotype = 'Lane' )
        join t_diagramobjects do2 on (do2.diagram_id = d.diagram_id and do2.object_id != do.object_id)
        join t_object act on (act.object_id = do2.object_id and act.stereotype = 'Activity' and act.object_type = 'Activity'
                          and do2.recttop    <= do.recttop
                          and do2.rectleft   >= do.rectleft
                          and do2.rectbottom >= do.rectbottom
                          and do2.rectright  <= do.rectright)
     order by proc_id
             ,lane_name
             ,act_id


Maybe it's gonna help someone.