Interactive Relationship Mapping: Untangling the Mess

Tom Becker is a Senior Consultant at PA Consulting. Views are his own. He does not profess to have any Lord of the Rings expertise.

On a recent assignment, I wanted to help client stakeholders understand how a set of over 90 different business activities fed into one another. I was aiming for a clear, visual map of the activities and the connections between them. This post explains the solution I used, available to anyone with access to Power BI.

I knew I wanted to:

1. Show whether a connection represented an input or output (i.e. use arrows)
2. Automate layout of the visual as much as possible and avoid a tangled mess of arrows
3. Not have to buy access to any additional paid-for software so that the client could continue to edit the visual well into the future

I ended up using the Force-Directed Graph visualisation in Power BI. It's a tool that could help anyone produce large, complex relationship maps without the need for coding.

Here I've used relationships between Lord of the Rings characters to illustrate how I used the tool. Is Frodo really at the centre of this complex web? (Short answer: yes.)

If you've got the raw data to hand, the Force-Directed Graph visual will let you create a basic relationship map within minutes. With a little more work behind the scenes, you can produce an interactive map capable of being filtered for different audiences.

No doubt paid-for software or a more bespoke tool produced from scratch could provide greater functionality and customisation. But to start generating useful relationship maps quickly and using existing Power BI tools, have a look through the steps below.

Step one: Create the raw data

First we need the raw data to generate the visualisation. One approach that can help identify interations between entities is to use an N-squared chart.

The individual entities you want to identify relationships between run diagonally through the grid, top-left to bottom-right.

Outputs from one entity (marked on the horizontal) provide inputs to another entity (marked on the vertical). E.g. An output from Aragon is an input to Frodo in this example.?

The completed N-squared chart shows directional relationships between characters (e.g. Aragon influences Frodo).

This data needs to be in table format for Power BI to read it.

The table opposite reflects the interactions identified in the N-squared chart above. I automated conversion of the N-squared chart into table format in Excel - worth doing if you're working with a large number of relationships.

Iteration 1: Basic visual and functionality

Reading the tabular data into the Force-Directed Graph visual in Power BI initially produces the visual opposite, showing directional relationships between the characters.

The visual already includes some functionality, such as highlighting connections by hovering over any one of the nodes.

Iteration 2: Colour-coded connections

The visual allows you to colour-code the connections between nodes by defining the "link type".

To do this, I added an extra column to the table of character interactions to show whether the influence of one character on another was positive or negative.

I only used two categories (postive / negative) to colour-code the links on the visual - you could use more. Note that colour-coding the links removes the highlight functionality illustrated in iteration 1.

Iteration 3: Filter by character

I wanted to be filter the visual by one or more character; to see all the connections to/from the specific character(s) I was interested in.

Without some work behind the scenes, you can only filter for connections going from or to selected characters, not both. We effectively want to be able to filter by two columns ('From' and 'To') in our data table at the same time.

The solution involves a few steps:

• Create a new column in the data table that combines the entries in the From and To columns (e.g. From: Legolas combined with To: Gimli becomes Combined: Legolas | Gimli)

• Create a new Slicer table that matches each Combined column entry to each individual character involved in that interaction, with each match on a separate row. So Combined: Legolas | Gimli would be matched with both Legolas and Gimli. This means there are two entries in the Slicer table for every one in the original table.

• Set up a relationship in Power BI between the Slicer table and the original data table, ensuring this relationship has a many-many cardinality and a single filter direction. This means the Slicer table will filter the original data table and not vice versa.

This article explains the approach in more detail.

With that behind the scenes work done, I could create a slicer in Power BI that filters the relationship map using the newly created Slicer table. This shows all the connections both to and from the character(s) I'm interested in.

Sharing the visual

Power BI reports can be published on Sharepoint pages (depending on your Power BI licence) for a defined audience to view and interact with.

Publishing on Sharepoint means your audience can use the interactive features of the Force-Directed Graph visual and will always be looking at the most up-to-date version you choose to publish.

Further options

Link weighting

The visual includes the option to weight links between entities. A higher weighting is represented by a thicker line. This could be useful in mapping strength of relationships between stakeholders, for example.

Node weighting

The Force-Directed Graph visual automatically sizes the nodes in the visual based on the number of links to/from each node. I haven't found any options for adjusting this setting.

Node image

The visual gives the option to represent nodes using images. I haven't tested that feature, but you could use it to represent groups of nodes using different images (e.g. Elijah Wood's face for hobbits and Sean Bean for human characters).

Filter by groupings

If the entities you're mapping fall into distinct groups (e.g. stakeholders that sit in different departments within an organisation), you could add the option to filter the visual to only show entities within a specified group.

This would require a variation of the steps under iteration 3 above - I'm yet to give that a go.

I'm no Power BI pro, but increasingly interested in how existing tools available many organisations can be applied and leveraged. If you have any expert Power BI tips, I'm all ears.