Using Visio and PowerBI with GraphDatabase in SQLServer

I went to a very interesting inaugural meeting of the South East UK Power BI Group last week. Whilst the presentation by Dr. Subramani Paramasivam was impressive for its ambition, it was the presentation by Andrew Fryer of Microsoft that particularly intrigued me. He introduced us to the world of Graph Databases, such as Neo4J and CosmosDB, but also demonstrated SQL Server 2017’s new Graph table types, called Node and Edge.  He used the latter because solution because he could use example in Power BI using the Force-Directed visual. Well, this SQL Server feature was new to me, and so I was inspired to see if I could use my favourite tool, Visio, to input data into a graph database, and to selectively display parts.

This is something that I have often grappled with because, great as Visio is for diagramming, it is not the answer in all situations. It is brilliant at connecting shapes together, and adding data to each shape, including the connector, but it would not be fantastic at displaying all the millions of data points and relationships in any graph database worth its salt. However, what if another application, such as PowerBI, could be used to analyse and filter the information down to a more manageable scale, and then export the filtered data set to Visio where it can display the relationships, and be enhanced for inclusion in reports? Although I really like PowerBI, I do not think it generally provides an output that is report quality.  Especially when reviewing the links between many nodes.
It is possible to insert data into SQL Server graph tables from csv files, but this is not always the most intuitive manner. Indeed, Andrew Fryer, whom I know to be a brilliant artist, resorts to sketching relationships freehand, and then creates tables or scripts from the diagram manually. So, what if you can use Visio to drag and drop nodes, and connect relationships between them, adding names and properties on the fly, as you go? Then, what if you could review your diagram, and then upload all of the nodes and edges to the database automatically? This process could be repeated on many pages, so that the data gets merged together in the graph database. The Visio pages can also be saved and remain available as a record of input, should any questions arise.
These new graph tables contain special column types that cannot be used directly by either PowerBI or Visio, so it is necessary to create views of the tables, thus effectively hiding them. I have a slight issue with the graphdemo database in the Microsoft article because it has extra columns on some Node and Edge tables. To me, this seems a little impure, and I think that the Node tables should only have an ID, Name and Properties column in addition to the Node columns, and the Edge tables should only have a Properties column in addition to the Edge columns. Ideally, the Properties columns in both tables should hold JSON data as text.
Anyway, I decided to create a proof of concept with just the basic Node and Edge attributes first. I also extended the sample database a little so I could have a bit more complexity. I decided to forget about using nice icons for now, and just keep it simple!

The Master Shapes

There are just two enhanced Visio shapes required, a Node master and an Edge master. The latter is the only connector that should be used, so I renamed it as Dynamic connector. This is a trick used in several of the Microsoft supplied templates too, because this is a reserved name, and is the default used by all of the automatic connection commands in Visio.
The Node master shape is a simple ellipse that can be resized, if necessary, and in the future that sizing could be controlled from data. It has a variable list Node property, text Name and Properties shape data rows that can be automatically prompted for on drop. The ID shape data row will be automatically updated if the shape is linked to the database. The page has a Prompt Input shape data row, which changes the experience when dropping shapes on to the page, and hides the shape data rows that can be linked to the database. There is also a Lock Resize shape data row in the page that can be toggled on to prevent unintentional re-sizing.
The Dynamic connector/Edge master shape is a curved connector, which can be straightened if desired, and only the Edge variable list shape data row to be completed on drop, as the rest will updated automatically if the shape is linked to the database. These may be hidden later, but for now, I left them visible. This shape also responds to the page Prompt Input shape data value, but also responds to the Connector Error Display Mode. This controls the display of Action Tags if the shape is not connected at either end. See my earlier article about this at Adding More Smartness to Visio Connectors.
In addition to these two master shapes, I also created a DataGraphic Node that colours by the Node text, and displays the Name. Similarly, I created a DataGraphic Edge that colours by the Edge text. Both of these data graphics can be modified easily to include the Properties shape data values in the future.

Capturing Nodes and Edges

So, the user can simply drag and drop a couple of Node shapes, completing the Node and Name shape data values for each, and then connect the two together with the Dynamic connector/Edge shape, and complete the Edge shape data value. Easy! This can be repeated for a page, and then some custom code, currently a snippet in the ubiquitous and excellent LinqPad, adds the Nodes and Edges to the database.

Nodes can be easily duplicated in Visio, and the Name shape data row edited.

Then the Node shapes can be connected to others using the Dynamic connector/Edge shape, and the relevant relationship type entered.

Once the Nodes have been connected, then the graph database can be updated with a little code, providing a visual feedback by applying the Data Graphics to the shapes.

Subsequently, the data can be analysed and filtered in PowerBI, from where the resultant list can be exported to Excel. This Excel table can then be used generate a new page in Visio. … but that will be in the next episode!

Using #Visio and #PowerBI with #GraphDatabase in #SQLServer – Part 2