This is a common problem that is not well documented yet. The fastest
solution lies with data connections. This sample solution works well in
Office 2007.
Here is the general idea. You have designed your great ER diagram with Visio
and need to have some accurate data representing one example row of data
beside your declared entity to help management better understand actual data
that may be stored in your database. Since you are doing your ER diagram now,
I presume you don't have a database already created so we'll hack in some
data from an Excel spreadsheet. For the sake of example simplicity, we'll
work with a single entity definition in Visio. No problem, here we go.
1) Create your entity object and define the Table Name and Columns in Visio.
[example]
Table: coreTable01
Column 1: ID (integer)
Column 2: SKU (varchar(10))
Column 3: Description (varchar(25))
Column 4: Cost(currency)
2) Save your Visio document (as always "Save Early. Save Often." is a good
rule).
3) Open or create an Excel document and create two rows of data, one for the
sample headers and one for the sample data. You can format the cell data as
you so choose.
[example]
ROW 3, COL B: ID
ROW 3, COL C: SKU
ROW 3, COL D: DESCRIPTION
ROW 3, COL E: COST
ROW 4, COL B: 1
ROW 4, COL C: 4B23T69
ROW 4, COL D: Widget 1
ROW 4, COL E: $24.99
NOTE: Your example headers should closely match your database field names.
4) Create a Range for this group of headers and data. This is simple. Drag
your mouse to select all 8 cells including headers and data. On the selected
area, right-click and select the "Name A Range..." option from the context
menu. Give this group of data a name (not includeing spaces or illegal
characters) such as "TestDataRange_1".
NOTE: You can have a single Excel Worksheet containing as many test data
ranges as you want, just give each range these same two types of rows i.e. a
Header row and a Data row. This way you can connect individual Visio Entities
to a different Range definition in Excell. If you have 20 tables defined in
Visio, you can have 20 example data ranges defined in this Excel Worksheet.
5) You MUST save your Excel document after defining the range for this to
work.
6) Minimize the Excel application and re-open or return to your Visio ER
document.
7) Select the ER Entity that you want to work with.
8) From the Vision "Data" menu, select "Link Data to Shapes..." and the Data
Connection Wizard will be displayed.
9) Select the "Microsoft Excel Workbook" and click "Next".
10) Select the desired Excel document in which you have defined your test
data. If it's not in the selection list, Browse for it. When you have it,
click "Next".
11) Since we have previously defined the Range of data we want for the
particular Entity mapping, choose that Range.
NOTE: You may, of course choose an entire Worksheet, different ranges, or
select a range directly from this wizard...pretty cool...but for this
example, just choose the range you have defined previously in your Excel
worksheet.
12) Insure you have CHECKED the option where it states "First row of data
contains column headings". Click the "Next" button.
13) For simplicity of this example, just accept the defaults to include All
Columns and Rows by just clicking on the "Next" button.
14) If you have a unique identifier, you can select it in this next screen,
but in our simple example, we just click the "Next" button and accept the
defaults.
15) Click the "Finish" button and the wizard is done and you will see your
data show up in the lower section called "External Data". This should have
pulled your data directly from your Excel spreadsheet range.
16) Drag the row of data in the External Data area onto the Visio ER Entity
of choice and a little link icon will be displayed next to that row of data.
This Excel data is now linked to this Visio Entity object.
17) Select the Visio Entity.
18) From the Visio "Data" menu, select "Display Data on Shapes" and a window
will be displayed as to how you want to display the data next to your Entity.
Choose the visual layout you desire for how this data is to be displayed next
to your Entity.
NOTE: When you select a visual layout on this Entity, you will get a Yes/No
option notice asking if you want to choose the fields to be displayed. Choose
"YES".
19) In this next window, you can map the data you want displayed next to
your Entity. To do this, you will be provided with two data lines, you can
add more with the "New Item" option on this window (usually choose a text
item) and you can define the display order with the small up and down icons
also on this window. For this example we will simply map the four fields we
care about.
[example]
Display Row 1: ID (text, default)
Display Row 2: SKU (text, default)
[...add these next two rows and select the headers...]
Display Row 3: DESCRIPTION (text, default)
Display Row 4: COST (text, default)
NOTE: You can also choose the position of this displayed data, but we will
continue.
20) Finish with this window and your data will be displayed correctly next
to your Entity. Tada!
NOTE: Use Microsoft's powerful connection features as you need. You can
change the data in your Excel document and re-save the Excel document. Then
go back to your Visio ER diagram. Right-click anywhere in the "External Data"
window and choose "Refresh data" from the context menu. This will refresh all
Data Connections and like magic, all of the data examples in your diagram
will be updated accordingly! Way-cool.
This may seem like a lot of steps, but I went into detail of a simple Data
Connection process that once you get used to can be done in less than two
minutes as you get better at Visio and Excel.
Hope this helps. Happy coding.
-=*=-
Serena said:
I want to add a small table of sample data beside each of the entities on my
diagram. Although I have found a 'table' shape, if I attempt to copy and
paste the data from Excel, it just puts it all as non-delimited text in one
cell. Even if I save it as a csv and copy it from Notepad, it just pastes it
all into the cell including the commas.
Is there a way of creating a table of data easily other than manually
copying each individual cell ?
Thanks
Serena
Serena said:
I want to add a small table of sample data beside each of the entities on my
diagram. Although I have found a 'table' shape, if I attempt to copy and
paste the data from Excel, it just puts it all as non-delimited text in one
cell. Even if I save it as a csv and copy it from Notepad, it just pastes it
all into the cell including the commas.
Is there a way of creating a table of data easily other than manually
copying each individual cell ?
Thanks
Serena
