Creating network diagrams in MS Visio using input from MS Excel

Started by Gbawa, July 24, 2009, 04:12:30 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Gbawa

how do I create a visio diagram from excel input.
This diagram should have the following structure
Application1
     - > Environment 1
              -> Database Servers
                       -> DB Srever Name1
                       -> DB Srever Name2
              -> Web Servers
                       -> Web Srever Name1
                       -> Web Srever Name2
              -> Link Servers
                       -> Link Srever Name1
                       -> Link Srever Name2
     - > Environment 2
              -> Database Servers
                       -> DB Srever Name1
                       -> DB Srever Name2
              -> Web Servers
                       -> Web Srever Name1
                       -> Web Srever Name2
              -> Link Servers
                       -> Link Srever Name1
                       -> Link Srever Name2
Application2
     - > Environment 1
              -> Database Servers
                       -> DB Srever Name1
                       -> DB Srever Name2
              -> Web Servers
                       -> Web Srever Name1
                       -> Web Srever Name2
              -> Link Servers
                       -> Link Srever Name1
                       -> Link Srever Name2
     - > Environment 2
              -> Database Servers
                       -> DB Srever Name1
                       -> DB Srever Name2
              -> Web Servers
                       -> Web Srever Name1
                       -> Web Srever Name2
              -> Link Servers
                       -> Link Srever Name1
                       -> Link Srever Name2
.
.
.
Application N
     - > Environment 1
              -> Database Servers
                       -> DB Srever Name1
                       -> DB Srever Name2
              -> Web Servers
                       -> Web Srever Name1
                       -> Web Srever Name2
              -> Link Servers
                       -> Link Srever Name1
                       -> Link Srever Name2
     - > Environment 2
              -> Database Servers
                       -> DB Srever Name1
                       -> DB Srever Name2
              -> Web Servers
                       -> Web Srever Name1
                       -> Web Srever Name2
              -> Link Servers
                       -> Link Srever Name1
                       -> Link Srever Name2

Each server having a different shape and Environment bein a Cluster shape.

Is this even possible to do?

aledlund

What you're describing is a relatively simple dependency diagram. Some usual examples are tree diagrams, and organization charts. David Parker did a good discussion on his blog (Creating an Org Chart without the Org Chart Wizard) of org charts which should be modifiable. Given the very probable chance of redundant entries in servers, etc., if you're doing this for other than a one time shot you might want to consider a database approach.
al

Gbawa

Thanks Al.
But I'm not looking to create a  simple org chart.would have been too easy to do :).
What I want to do is something like the attached doc.

joemako

Your attached image is very nice, and looks like it was created with care.

I have some experience in creating one-offs to automate Visio from an excel file, specifically:

- Creating bounded areas, like your colored rounded rectangles
- placing shapes inside those areas or outside, arranged based on their number, or hirarchy; type of shape based on other fields along with their details.
- connecting shapes with the lines styled as needed
- any additional definable annotations

From my experience, there are two key things:

1. Preparing your data
2. Having a design layout that scales

In other words, know your data, and know what you want to do with it.

Writing the VBA is relatively easy if your data is prepared for it, and you know exactly what you want to do.

If I wanted to create your attached image from an excel file, I would have three to four sheets/tables of data, write some VBA that loops through the data keeping track of shape IDs for use when creating connections.

Bounded areas
- Name
- Order
- Color
- Count of objects inside

Objects
- Name
- Type
- Parent area
- other placement details

Connections
- Shape Start
- Shape End
- Type
- (possibly connection points for each end if a shape would have multiple connections)

The problem is, most data sources that I would want drawn in Visio, do not have this format, and I have to spend time getting the data structure right, with all the details I would need to automate the construction of the drawing.

I would then write VBA that:
- Draws the areas
- places the shapes
- makes the connections

If you can not prepare your data, I could see the VBA getting quite complex.

aledlund

To add to Joe's comments v2010 has a new 'container' shape that "containers break up a diagram into understandable pieces" that would play very well with this type of diagram for the bounded areas, but this is still a custom code type of solution. Working on the other pieces would keep you busy enough until v2010 is released.
al

saveenr

@GBawa - I prototyed a tool that does most of what you want (except for placing shapes "inside" containers) - try it out here: http://cid-19ec39cb500669d8.skydrive.live.com/browse.aspx/Public/Visio/Tools/AutoDiagram. It renders connected diagrams from an Excel file much in the manner joemako describes.


aledlund

The reason that I had suggested originally that a tree view might be sufficient was based on the fact that gBawa description (IMHO) might be viewed as a horizontal tree. As a network architect by trade the discipline can look at the process of describing a communications network as a series of iterations that can move from logical, to conceptual, to physical/detail. There is also the dimension of dataflows, which technically does not deal with the physical interconnect but rather the flows of information (and more often cascaded transactions).
The demo posted for path analysis allows for the description of nodes (servers?) and connections that can be fed via an excel source. It's a starting point.
At the other end of the spectrum is the documenting of data centers. My experience (others may be different) has shown me that a spreadsheet doesn't hack it. My first iteration was to attempt to use Access and as a sandbox I thought it was adequate. For prototype I moved to Sql Server (a lot of reasons).

Check it out.

http://www.youtube.com/watch?v=IU9v_8Vv3zs
hth,
al