Auto draw batch process wrt run times

Started by visio, April 20, 2010, 02:36:07 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

visio

Story so far

- I have a batch suite diagram in 2 formats. 1) Simple with 96 (hence 96 shapes required) subroutines only showing the subroutine names, 2) Detailed with the same 96 subroutines but this time each subroutine shows the processes that  run in each making around 800 in total (800 shapes required)
- Obviously going to go for the simple version first
- Can use the Simple diagram but would need to redraw using modified modified shapes which can redraw themselves dependig on linked data.

Basic shapes now moving around and resizing so going to progress to next stage.

Is there an easy way to create the shapesheet references or will these all have to be done individually, all be it only need to be done once. Things like to allow for the redraw by referencing the start time would need to reference sheet1 or sheet3 etc. This would need to be done 96 times for the simple drawing. Just asking before getting stuck in.

Jumpy

#16
You will have to do it individually for each shape, because you always have to reference another shape.

Therefore my suggestion to use the start time each shape has in it's shape data, to place that shape independent of other shapes.

If you want to stick with the "referencing the previous shape" solution and want to do it in good time, you will have to use VBA to place your shapes or change their formulas.
You could place sth. like this in a VBA modul of your solution and start it with the Callthis function in the EventDrop cell of your shape:

Public Sub Place_me(dropee As Visio.Shape)
Dim shp As Visio.Shape
Dim shpName, Formel as String

For Each shp In ActivePage.Shapes
If shp.DistanceFrom(dropee, 0) <= 0 And shp <> dropee And shp.OneD = False Then
 shpName = shp.NameID
 Formel = shpName & "!PinY-" & shpName & "!LocPinY-5 mm-Height+LocPinY"
 dropee.Cells("PinY").formula = Formel
 dropee.Cells("PinX").formula = shp.Cells("PinX").Resultstr("")
 Exit Sub
End If
Next

End Sub


Didn't test the part, where the formula is set, but the rest should work. So if you save a shape with the Callthis function in the shapesheet in a stencil and drop a shape based on that new master on another shape, it will be placed belov the first shape accordingly to the formula.

visio

Getting there but having issues with placement of the shapes. They are disappearing when placed, think its something to do with the scale.

For the shape the PinY is set to ThePage!Prop.StartHeight-(ThePage!Prop.StartTime-Prop.Row_2)*10 mm

Which means pin the shape at the point corresponding to the top of page minus the distance corresponding to the time that you started.

On the Page shapesheet 2 properties were created in Shape Data
Prop.StartTime - Type 5 - Format "{{HH:MM}}" - Value = 1800
Prop.StartHeight - Type 2 - Format "" - Value = 400 mm

In User-defined Cells there is
User.msvDGCalloutGap - Value DraxingScale/PageScale*0.0625 - No Formula

On the shapesheet for the shape for start time there is a Shape Data used to extract infor from an external table
Prop.Row_3 - Lable Start_time - Type 5 - Format "{{HH:mm}}" - Value DATETIME(0.7847)

When the formula was applied the shape was placed at -17580 mm. Not quite the right place as the start time was meant to correspond to 18:35...

Where would the scale of the page be found. Want to be able to match the top of the page (at 400mm) to be 18:00 and make say 10mm = 1 hour. (previous post from Jumpy 3rd post in this thread)

Jumpy

Hi and welcome back again.
In one of the post I placed an example .vsd with a shape that has roughly the behaviour you need.
When creating it I encountered the same problem you are having now and I didn't found a direct solution.

It has to do with the Date and Time format. When you look behind the really stored numbers behind a date and a time you now what I mean.

My way arround that was to store the start hour and the start minute as simple numbers in seperate ShapeData fields and so could base my formulas on numbers and not on "spacetime 23456.23 this is are the adventures of..."  ;D

visio

Yes the numbers can be fiddly especially if trying to go past midnight as the run does. Trying to work out some sense of the timing. In the meantime found this little explanation of time on the microsoft website. 18:00 would 0.75 going by this...


"Date and time values in Visio are stored internally as a 64-bit floating point number. The value to the left of the decimal represents the number of days since December 30, 1899. The value to the right of the decimal represents the fraction of a day since midnight. Noon is represented by .5."

See all very straightforward....erm....!?

visio

#20
Update

OK playing with the microsoft number format and it can be used to do all the dates and positioning/resizing once few things understood.

Input times for processes will need to have a date part yyyy/mm/dd hh:mm so the excel sheet will require a bit of formatting too. The Page will also need to have a startdate property associated with it to define the top of the page. I created a sheet 2 in the linked data source (excel sheet) to link a date variable to the page data property StartDate.

For example 6th June 2010 corresponds to 40335 (days since Dec 30 1899). Add the start time of 18:00 and you get the number 40335.075 as 18:00 corresponds to 0.75.

If a process runs for 10 mins starting at 18:00 on 6th June 2010 its end time will be 40335.7569 making 0.0069 = 10 mins.

If a process runs past midnight then the big number will increment from 40335 to 40336. The elapsed time can be easily calculated (well by Visio) going past midnight, month/year ends.

All sounds good but still fiddly.

Next bit is to match up the page scale of say 1 hour = 25 mm to the time format where things are worked out in parts of a day. This means that once the start and end time numbers are calculated they need to multiplied by something to make the scale work as DATETIMEs are given as part of a day.  

In DATETIME 12:00 is 0.5 and 18:00 is 0.75 giving 6 hours = 0.25
In my page 1 hour = 25mm so 6 hours = 150mm
So 150 mm = 0.25 in DATETIME = 6 hours
Making 150/0.25 = 600 the factor the DATETIME numbers need to be multiplied by to put them onto the scale.  


From above
6 hours = 0.25 which is 0.25 *600 mm = 150 mm
Another one
if 10 mins = 0.0069 then that equates to 4.14 mm which is about right as there are rounding errors. (25/6 is actually 4.166666666666666 mm)

PinY now equals GUARD(ThePage!Prop.StartHeight+(ThePage!Prop.StartTime-(Prop.Row_2-ThePage!Prop.StartDate))*600 mm)

Still a work in progress, any thoughts?

Jumpy

Quite a research you have done there and I appreciate the update, because I now know how to decipher the timeformat and maybe one day I need ecactly that.
At this moment I don't have any good ideas for you, but you seem to be on the right way. Keep us in the loop.

Although one think comes to mind. It could eventually be nessecary to keep the scale (that is the mm per hour) variable. Because in a normal night the whole process runs for example two hours and you space your scale accordingly to fit to the page. Now there comes a night, where the network is slow, and your process takes 5 hours. Staying with your normal scale it won't fit on a page anymore...

On the other hand a scale that is always the same makes it easy to compare runtimes of process parts on different days. You only have to compare their lenght.

Because both reasons are (imho) good ones, it may be good to apply both. Make a fixed scale and a variable one and give the page a prop.cell where you can choose which one shall apply.

visio

Used the variable for the scale (page property Prop.Scale), makes things lot easier as only need to change a number in the page properties to change the scale. Same for the page height (Prop.Startheight=Pageheight) as if things run long then can easily change the page size to accomodate the extended run time.

Bit of pain creating the page as unable to store the shape as template as it refences a page. Fairly easy to duplicate the shapes and rename them but as no data and the PINY is guarded all the shapes can get bunched up fairly quickly. Can fix this by using ficticous data in the driving spreadsheet to more the shapes vertically on the page while setting things up.

This will be more tricky if I try to do this with all the subroutines as there will be approx 800 of them.Is there a way to run a macro to copy a shape and then past and rename it. This would make the creation easy as I have a list of all the required subroutines. Either that or its manual cut and paste (just being lazy)

Apart for those minor niggles the batch process is now autodrawning nicely on the page. Its really easy to see where the long runs are located.



On a more technical note is there a way to not draw a particular shape if it hasn't run. What I mean is, the batch is a generic process but this runs for 3 different systems. The smaller systems run only a portion of the processes and there is a list of proecsses that are omitted. Easy to add an omit column to the spreadsheet.

Is it possible to use this omit column so that if omit is set to Y then the shape is not drawn on the page?


Jumpy

#23
Quote from: visio on June 14, 2010, 10:29:58 AM
Bit of pain creating the page as unable to store the shape as template as it refences a page. Fairly easy to duplicate the shapes and rename them but as no data and the PINY is guarded all the shapes can get bunched up fairly quickly. Can fix this by using ficticous data in the driving spreadsheet to more the shapes vertically on the page while setting things up.
If you copy a so created shape (that contains references to the page) in a stencil it will eventually show REF() errors in it's ShapeSheet, when you look at it in the stencil. But when you drop it on a page, that has the referenced Prop.cells and User.Cells, the references will still work. Most of the time, if you drop such a shape on a page that even doesn't have the referenced Prop. and user.cells they will be created in the ShapeSheet of the page automatically and filled with the values the shape knew last (not with eventual formulas of the original page, only values).
This will make it later easy to fetch such shapes from the stencil via macro and fill them with fictive data to place them the first time.

Quote
This will be more tricky if I try to do this with all the subroutines as there will be approx 800 of them.Is there a way to run a macro to copy a shape and then past and rename it. This would make the creation easy as I have a list of all the required subroutines. Either that or its manual cut and paste (just being lazy)
See above. I think it can be done programatically.

Quote
On a more technical note is there a way to not draw a particular shape if it hasn't run. What I mean is, the batch is a generic process but this runs for 3 different systems. The smaller systems run only a portion of the processes and there is a list of proecsses that are omitted. Easy to add an omit column to the spreadsheet.
Is it possible to use this omit column so that if omit is set to Y then the shape is not drawn on the page?
In the geometry section off the shape is a cell Geometry1.NoShow. If its value is set to true, it is invisible. Say you have a ShapeData cell "Prop.Draw"  with Y or N in it, you could write =If(STRSAME(Prop.Draw,"Y"),False,True) in the NoShow cell.

Are there connectors between your shapes? They would eventually have to get invisible as well. and a longer replacement connector may be needed:
S1-->S2-->S3 could become
S1-------->S3

visio

Ah ha, actually did save as stencil but only the values were showin which is why I thought the formulas were duff. When view changed in ShapeSheet to formula then everything clear, also shape remembered its last value too.

So far got auto drawing of groups of reports (a group being called a Session). In order to see more detail there are 2 options, either put everythng onto 1 visio sheet which would require approx 800 shapes on a sheet or create mini visio sheets using smartshapes again to represent each session and link these to each session shape on the main page. With the visio sheet saved as HTML it should be possible to double click on a SESSION and that would jump to a hidden mini visio sheet showing the run times of the processes in that individual session. Think that would be handy as you would look at the batch run at a high level, you would see what bit ran long then drill down to see if any individual process was runing long (or there was dead time) in that session.

As with the main sheet this would be linked to a data source like excel and the time formatting would be the same as for the main sheet.

Macro maybe to create the individual process shapes then group them together as required.

The noshow cell option looks like the way forward to make this all genric for any system, thanks Jumpy.

Not using connectors as they clutter diagram and the actual times things ran show pretty good logic anyway.  Will create a not smart logic diagram with connectors to show dependencies or use the mini visio session diagrams.

All looking good though.

visio

Ok drawing of Sessions getting there. If a process is set to be ommitted then can set Noshow cell to True to not draw the shape, also need to set Hidetext in miscellaneous to True to remove labels. Finally as connectors not being used in the view menu the show connnectors should be unchecked. Doing all this will make the shape effectively invisible.

Just to complicate things there are certain processes that run weekly or monthly. These are not ommitted and the automation on the machine takes care of when these run. When run times are extracted there will be no entry for these processes in the extracted spreadsheet (as they didn't run, ommitted entries do run even though for a very short time and will have spreadsheet entries). This means that when the Visio diagrams external data is updated there won't be links for certain shapes.

Is it possible to 'make a shape invisble' as above if there the link to the data source is not present?

What I'm trying to do is make a generic Session diagram with say 10 processess in it. The omitted ones can be picked up by the omit Y/N flag. The weekly/monthly ones could be picked up by their absence in the linked data. If, in the session, 2 processes are omitted and 3 are monthly then only 5 processes would need to be drawn. If this can be done then there would be no need to create separate diagrams to allow for weekly and monthly etc.

Make sense? Can this be done?

Jumpy

#26
Hello.
I think that a shape doesn't know, that its link doesn't exist. It will simply retain its previous Data. Is just a guess. Will try it out tomorrow as a test, because it's an interesting question.
Edit: But Visio knows and gives a message, where you can choose to ignore this or to delete not linked shapes.

A way to make it work could be to give your shapes an additional ShapeData entry. Create a suitable entry in excel, too. For example "monthly". In excel it shows only "True" in the monthly/weekly processes (when they are there), and "Normal" (not False!!!) in the normal processes.

In the linked shapes you got your additional ShapeData cell that either shows "Normal" or "True". When it's normal, you can ignore it, because it's a normal process. Where it is "true", you create your shapes so, that they are only visible, when that value is true (and if they are not omitted (don't know if that can apply to the monthly/weekly shapes, too)).

And now comes the trick: Prior to updating the ShapeData you run a small macro, that iterates through your shapes and looks for those new ShapeData Row. If the value is "Normal" it does nothing, if it is "True" it sets the value to "False", thus hiding all such shapes. Should be 10 lines of code, max.

Then you update your link and the "False" will be set to "True" again, if the monthly/weekly shapes got updated, too.

Could work...

Edit: Tried it out, worked. But when you update and have no Data for montly/weekly shapes, the link to those shape breaks. You get an error message in the DataGrafic Window, but you can choose to ignore this. Later, when you update again when you have Data for those shapes, the link is still broken and the shapes are not updated. But you can choose to link shapes automatic to the ExternalData and you got all links back with one mouse click. But it must be possible to identify the right Data for each shape, that means your Data (=processes) must have sth. like a unique key, for example a process name or number (when its unique).

visio

Had another idea about this. This would require a bit of manipulation of data in the driving excel (or other) database and exploiting time information. 

- Create a Session diagram in Visio with the max number of shapes, this would cover omitted and weekly/monthly processes.
- In the excel sheet every process should have a row entry whether run or not. Spreadsheet would look like below, colums only

              Process_Name     Start_time     End_time   Elapsed_time  Omit

- In the spreadsheet add an extra column to indicate the previous days run start time, easy to do as data extracted daily just need to copy the column sideways. Also add a column to indicate Run_Notrun. This column is the todays start time minus yesterdays start time. If his equals zero then the start time has not been updated for today and so that process did not run as dates between runs will always increase.

             Process_Name     Start_time_yesterday Start_time_today     End_time   Elapsed_time  Omit Run_Notrun

- Can make the the Run_Notrun equal to N if the answer is zero

All that is needed then is an extra property added to the the shape to look for Run_Notrun being N. If this is set to N then make invisible and place the shape at a default location which would be equal to the start time of the Session.

That means that if the shape is drawn its not put far away on the page making the diagram look weird.

This does mean mucking about with data but it this done then it can be done in a macro in excel which is fairly easy. Also means a generic diagram and data as number of processes are faily static even if not run. As only concerned with the actual visual output from Visio the result should be fairly clear and only show the bits that are of concern.

For this to work would need to know how to set the default if Run_Norun set to N. ie if Run_Norun set to N then set PinY=same PinY as the first process in a Session (always present), also make invisible as mentioned earlier. Could use OR logic to cover similar for OMITed processes.

Make sense again? Too fiddly?

Jumpy

I would use 1 for run and 0 for not run because N would be a string and comparing strings makes formulas longer.
The same with omitted.

So you could write in PinY for example:

=If(Prop.no_run*Prop.Omitted=1,"Then place shape correct","Place shape in default poistion")

because Visio interprets 1 as True (hope it's not the other way round?) it is possible even shorter:

=If(Prop.no_run*Prop.Omitted,"Then place shape correct","Place shape in default poistion")


The same with N:
=If(OR(STRSAME(Prop.Omitted,"N"),STRSAME(Prop.no_run,"N")),"Place shape in default poistion","Then place shape correct")

visio

Been a while, was off getting the data input into a useful format for Viso. Anywas sucessfully did that. Format is now as below (column headers) using '1' for Norun as Jumpy recommended.

Session   Uproc   Start_time   End_time   Elapsed_time   Failures   Run_notrun

The plan as mentioned before is to create a mini visio page which represents each of the session. Each session in the main session diagram would be hyperlinked to its corresponding session diagram. In the session diagram the detail of the session runtimes can be see. Basically a cut down version of the Session sheet.

Each Session always has a Begin process called <something>_B. It would be good to make the top of the page equal to the start time of this process. This means all the mini diagrams start drawing from the top of their respective pages. (Similar to Session diagram each diagram would be unique and not change much so each page would be hardcoded to reference its corresponding start process). How is this done?

The page has a property Prop.StartTime and this on the Session diagram is set to DATETIME(0.75) to make it 18:00. Want to map this instead to Prop.Start_time of Shape corresponding to the beginning process, something like ThePage!PropStart_TIME =Prop.Uproc(=Session1_B)!Start_time