Relational Entities in excel file to directed graph

Question

Hello everyone,

I would like to convert a relational database encoded in a excel file to a directed graph that shows the entities, some of the attributes and the directed relations.

The excel file is organized is follows. Column A and B contain entity names. Column A may contain duplicated names. Column B may contain Null entries. The relation direction goes from A to B. An exmplary file can be found here: https://www.dropbox.com/s/gpcqzyls3mqulhz/database.xlsx?dl=0. The outcome of this particular file should be something like this.

Would you know an elegant way to solve this for a big excel file? For now, I copy the entity and attribute names by hand, enter the edges by hand etc. The xlsx.-import did not seem to do the trick.

Greetings from Frankfurt,

Johannes

 

 

Answer 1

Well, you need to organize the data in your spreadsheet in a different way.

Create one row for each entity. Have one column with unique IDs. (In your case that would probable be the column with entries "Q_DATEDTIMETABLE", "Q_DTBASEDATAOWNER", "Q_DTBASEDATAPARAMETERS", and "Q_DTTIMETABLEPERIODOWNER".) Add one column for each attribute of your entities. Insert one additional row before all those rows for entities. In this "first" row, enter the name of each attribute in the corresponding column. (This is not strictly necessary, but simplifies working with your data later on.) Use this set of rows as "Data Range" for the "Node List" section of the Excel Import. Specify the column with the unique IDs as the "Column of Node IDs".

Create a second set of rows consiting of only two columns. For each edge (i.e. connection) you want to have in your diagram, create one row. In the first column enter the ID of the source node, in the second column the ID of the target node. Each ID used here has to match one of the IDs used in the first column of the first set of rows. Use this second set of rows as "Data Range" for the "Edge List" section of the Excel Import. Specify its first column as the "Column of Source IDs" and its second columns as the "Column of Target IDs".

Run the import and verify that the structure is correct and all attributes are available as custom properties.

Then create appropriate visual representations for your nodes. E.g. use an entity node, remove its default attributes label and add one new label for each attribute you imported from Excel. Create a user-defined palette section and add your customized entity node to said section. Next, create a properties mapper configuration that assign the custom properties holding your attribute values to the appropriate label.

Re-run the Excel Import with the same settings as before but additionally set your custom entity node as "Template" in section "Nodes" on tab "Presentation" of the Excel Import. After the import, run your properties mapper configuration to fill the labels of your nodes with the appropriate data.

Comments

That seem to be exactly what I was looking for! Thanks! This already made things a lot easier. Two minor problems remained so far.

First, there exist bidirectional relations. In these case, yED draws duplicated nodes. The duplicate nodes don't carry the attributes.

Second, I created a template with multiple labels and mapped each attribute to one label. But they are all on top of each other. And the "Fit node size to label" does not seem to work appropriately. (The number of attributes varies for each node.) For now, I'm working with two labels. One showing the entity name and one containing all attributes with appropriate line breaks. Then I resize the node by hand.

---

Re:

First, there exist bidirectional relations. In these case, yED draws duplicated nodes. The duplicate nodes don't carry the attributes.

yEd is able to import bi-directional data. E.g. using the sample data from the yEd Manual

you will get five nodes, two of which are connected by two edges

If that does not work for you, I guess there is a typo in the edge source or target IDs in your edge list.

 

Re:

Second, I created a template with multiple labels and mapped each attribute to one label. But they are all on top of each other. And the "Fit node size to label" does not seem to work appropriately. (The number of attributes varies for each node.)

If you add a new label to a node, it is placed in the center of the node by default. If you do not want the new label at the center of the node (e.g. because there is already another label in the center), select the label (using either CTRL+left mouse button or "Tools" -> "Select Elements") and either move it to another position with the mouse or choose another "Placement" value that positions the label somewhere else.

"Fit Node to Label" takes only the first label at each node into account. This tool cannot be used to properly resize nodes with multiple labels because with multiple labels the "correct" node size is not clearly defined anymore (the correct size usually depends on the expected relative label positions but determining expectations is very difficult).

yEd does not really support properties the are specific to a subset of nodes only. If you want to simulate something along those lines, you will have to manually adjust your diagram.

---

---

---

---

---

Thanks:) The were indeed inconsistent blank spaces in some node labels. And I'll just do the node resizing manually. Importing multiple attributes with line breaks works nicely.

Johannes

---

Would you have an idea how to resize 500 entity nodes? I used the standard entity relationship node from the palette. It contains two labels. One with the entity name and one with the attribute names. Node Data 1 contains the entity name and Node Data 2 contains a list of attribute names formated with line breaks. Different nodes may have a different amount of attributes. The node description is empty. Maybe reorganizing the data could help?


Johannes

---

I do not think that there is a tool in yEd that can be used for resizing multiple nodes at once but to individual sizes. The best you can do is probably selecting all nodes at once (e.g. using CTRL+A) and enlarge all the nodes to the same size.

---

Thanks, then this is what I'll do. And am I right to assume that there is no automated source port positioning of edges in yEd? That is, position the source port of a outgoing edge not somewhere on the node boundary but specifically next to the attribute that refers to the target entity.

Johannes

---

Yes, you are right. The source port positioning feature you are looking for would require semantic knowledge specific to arranging ERD diagrams. yEd (as a general-purpose editor) never even tries to acquire such domain specific information.

---

Thanks for clearing that up!

Johannes