Auto draw batch process wrt run times

[visio]

Using Visio 2003

There is a complex batch process (around 600 programs more or less sequential in nature) which runs each day. Would like to be able to automatically draw the entire process each day using the runtimes from each of the 600 process. That way can at a glance see where things ran long by either the length of the programs box in Visio or the line connecting one box to another as that would signify how long a program ran.

The programs have been extracted into an excel spreadsheet with their dependencies (order of run) and so can create a site map using the process outlined in the site below, using database wizard.

http://www.boxesandarrows.com/view/the_lazy_ia_s_guide_to_making_sitemaps

The run time for each program can be extracted from stats and added to the layout spreadsheet as an extra column matching each program with its runtime.

Is there a way to automatically draw the batch process in Vision 2003 and have each block start with a certain verticle height say 1cm and then for every 5 mins of runtime the height increases by 1mm? (or have the line between each process increase by 1mm). 

The final objective being the ability to look at what ran in the batch process and visually pick out if any particluar program ran long? This could be repeated every day for each particular run.

[Aeon]

first thought on reading it, is that the 1cm + 1mm per 5 mins is simple shapesheet formulas from what i can see.
you would need auto refresh from database (in this case the excel file) to be able to see the progress, and unfortunatly that can't be done in visio 2003
you need visio 2007 or newer for that

[visio]

So it is doable which is good.

The shapesheet idea sounds ideal so looks like an upgrade to 2007. Can work out the details later but would the refresh cause the drawing to be redrawn from scratch or will this just be resizing the shapes in an existing drawing.

What I mean it I could create a nice looking batch map then each day the shapes could change their size according to the information on a spreadsheet.

Just saying this as when doing the autodraw in 2003 found that sometimes the drawing was a bit squished up in some bits and needed a bit of manual tweeking. If no need to redraw from scratch each time then a basic drawing would be fine and this  could all be automatic.

(2007 may not actually have this issue, just wondering)

[visio]

Ok have upgraded to 2007. Is there an article or guide anywhere that would help in what I would visio to do.

My idea is
- draw the bath diagram in visio
- extract run times of jobs and put them into excel. Excel would have start and end times of each job and elapsed time
- use link of visio to excel and have the visio shapes resize themselves depending on start and end time
- output would be a visio diagram of batch which at a glance any long running processes can be seen

ie The initial diagram would be vis_example1.jpg which shows the logic. There are 10 processes with a main backbone 1,2,3,4 and 5 and 2 parallel offshoots which run processes 6,7 and 8 and 9 and 10.

What is desired is example2. this shows the start time and end time of the whole batch and the shapes that represent the processes are streched to indicate their start and end times. From example 2 it can be seen very easily that process 10 runs for a long time.

Is this something that viso can do with smart shapes? The real batch process diagram has nearly 800 individual processes but can be simpified down into groups making around 150 groups.

[Jumpy]

You could to it all with VBA, where you need to open the ExcelSheet, go through its values, calculate where to place your shapes from those values,...


Or you need a slightly modified shape for this, and Visio 2007 Pro's Linking to an external DataSource feature:

1. The Shape needs 3 ShapeData Properties: Start-Time, End-Time or Duration, Colum or Thread in which the Process belongs
For example: Prop.Start, Prop.Duration, Prop.Colum

You may eventually need additional ShapeData Rows for info which shape has to be connected to which shape

Those ShapeData could than be linked to an Excel Sheed, which has the information.

2. You have to define a timeline startpoint on the Page, for example at y=200mm which coraletes to your StartingTime of 18 h.
You could store those values in ShapeData or User defined cells oh ThePage, fo example: Prop.StartTime, Prop.StartHeight
And the info, that for example 10mm equals 1 hour in the diagramm.
And last the info how far colums are spaced in Prop.Space and at which X coord the first colum starts in Prop.Xcoord


3. The modification of the shape, could be like (in the ShapeSheet):
a) LocPinY = Height
b) PinY = ThePage!Prop.StartHeight-(ThePage!Prop.StartTime-Prop.Start)*10 mm
c) Height = Prop.Duration*10mm or (Prop.Start-Prop.End)*10mm
d) PinX = ThePage!Prop.Xcoord+(prop.Colum-1)*ThePage!Prop.Space

That should place your shapes automatically, depending on the Shape Data.


But your connectors will have to be set with VBA i fear.

[visio]

Tried using the organisational chart (in visio 2003) with import of data from excel which had all the logic and the process run order however that took a few hours at the end to make the diagram look ordered as the links sometimes went to the side of shapes aswell as the top.

Mentioning this as it sounds like the Excel way in 2007 could redraw each time and could also take a while to order.

The VBA method does sound better. Guessing that the logic and links would be set up in VBA, so essentially the drawing could be created in a way that looks good.

Once the logic drawing is set up then can link this to the database source to get the placings (wrt time) and sizes (for runtimes).

Is that interpretation correct? (The fiddly bit bieng the VBA for connectors but would only have to do this once.)

[Jumpy]

OK if the processes are always the same and in the same order and the same "colum" the its truly easy and you won't need VBA at all.

You set up the page and a shape like a mentioned in my last post. Position that shape by manually changing the ShapeData in the Shape.
Copy that shape, position the new shape,... This is some work, but you only have to do it one time.

When you have all shapes positioned and placed, you can connect them manually with connecots. That too, is work, that only will have to be done once.

Than you can link the drawing to your ecxel source (that has nothing to do with the orgchart wizard) and get the Data displayed in the so called External Data Window.
Now you can link every DataRow with the assosiated shape and you are done.

Whenever you update your Excel Sheet and then update the external date link, your Shapes will position themselfs new. All without VBA.

-------
You could use VBA to do the work of placing the Shapes at the right Position the first time you create the diagramm.

[Jumpy]

In the drawing is an example of such a shape and page.
It separetes hours and minutes because I don't like to calculate with date and time formats, so the shape should certainly be improved.
Maybe seconds should be added, too, don't know how exactly you are counting the time.

The timeline could be made smart, too.

Problems could accure when passing into a new day (batch jobs are often run through the night I think?).

And many more ways to improve, but it's a start

[visio]

Thnks for all that help, will have a start and let you know, may take a week or two.

The batch suite is (should) be static with minimal changes unless new processes added or omitted and there are variations due to end of week/month etc but changes should be minimal.

The time wil be overnight but can work on this. As for elapsed time this is extracted from stats and can be calculated in a numbe rof different ways but would probably want to go for a more user/manager friendly speak like hours and minutes. Again this all sounds relatively straightforward.

[visio]

Getting somewhere now however now have to upgrade to Visio 2007 PROFESSIONAL in order to link shapes to Excel data as that option not available in 'normal' 2007.

In meantime got a shape responding to resizing but finding a problem with shapes drawing over other shapes. (this may not happen in 2007 pro, will take a day or 2 to upgrade)

What is happening is say there are 3 processes run sequentially, redraw1.jpg. The shapes redrawn with the correct times but looks like the second (or third) shape does not move (pinned maybe). If the first shape resizes its height too much it just draws over the next shape, redraw2.jog. What would be ideal is for the second shape to be pushed down the page. (sounds like a minor thing or a step I've missed)

Should mention that for now the height is just based on the elapsed time field, teh begin/end times not used. Similar to article in link http://www.visguy.com/2009/02/23/trilogy-meter-data-graphics-part-i/

[Jumpy]

To push down another shape it has to know what's with the previous shape. You could write in the second shapes PinY cell:
Shape1.PinY - Shape1.Height/2 - X mm

Shape1 must be the name of the first shape and the LocPinY of all Shapes have to be at Height*0.5 to work.
X mm is the distance you want to have between the two shapes.

-----

Another method could be to place each shape at the right hight depending on the start time. See my shape from previous post.

[visio]

Put =Shape1.PinY - Shape1.height/2 - 5 mm into the PinY cell of shape2. Made sure that LocPinY = height*0.5 but just got error in formula. (tired a few permutations but similar problem)

Shape 1 has its pin position set to top-centre.

Hmm did I miss something?

[Jumpy]

Is Shape1 truly the name of your upper shape?

With the following formula it sould not matter where the Pin shapes lies:

I assume again, that the upper shape is Sheet.1
(In your case, you must make shure to use the correct names of your shapes. You can see them in the top line of the ShapeSheet of a shape or in the "drawing explorer window" that you can find in Menu "View").

In PinY of the second Shape:
=Sheet1.PinY-Sheet1.LocPinY-5 mm-Height+LocPinY

[visio]

Got the shapes moving around now. Had a quick tutorial (handy guide here http://www.visguy.com/2009/07/15/whats-my-shapes-id/) to learn about shape names and as you guessed my shape names were not as I thought so the reference was failing.

Slight change to the formula replacing '.' with '!' and using full name Sheet.1 rather than Sheet1

=Sheet1.PinY-Sheet1.LocPinY-5 mm-Height+LocPinY

becomes

=Sheet.1!PinY-Sheet.1!LocPinY-5 mm-Height+LocPinY

Now upgraded to 2007 Pro so on with the original plan and linking data.

Thanks for the help so far Jumpy and being so patient. I'll post progress on the auto drawing.

[Jumpy]

Sorry for the '!' error. If I only write in in the editor here, I often make this wrong. Good that Visio informs me, when I make the same mistake in the ShapeSheet ;-)

-------

Even if the formula now works. I would suggest to use the Start time to place each Shape (PinY) individually, like u use the duration to change the height.