I have been continuing to develop a Visio interface for SQL Server 2017 Graph databases because I believe it will be of great use in many scenarios, such as crime investigations, company and shareholder relationships, and anywhere that defining and illustrating links between items is important.
SQL Server 2017 introduced graph tables, Nodes & Edges, which is a great way of storing relationships between objects. However, there is no easy way of creating or visualising this information currently. Visio is a data-diagramming application, so what could be more natural than to use Visio to create and visualise the data?
All Visio shapes can contain data, and are easily connected to each other, so creating meaningful relationships is a breeze. Visio diagrams can be automated too, and this solution is a C# VSTO add-in that enhances the normal Visio interface. The application also has the ability to define the shapes, labels, colours and appearance from the data values, which are stored as Json objects.
An import from Excel is also provided because of its tremendous ability to gather and re-shape data from so many different sources.
The solution automatically creates SQL views of each graph table so that the data can be linked to Visio and used in Power BI too.
SQL Server
Creating Visio Validation Rules for Graph Database template
Having developed Node and Edge/ Dynamic connector masters shapes for creating Node and Edge table entries for a SQL Server 2017, I decided that I should write some validation rules. The validation feature has been in Visio since the 2010 edition, but is underused, even though I wrote a book about them … see Microsoft Visio 2013 Business Process Diagramming and Validation . I think that only six rules are necessary to validate that a page with Node and Edge shapes is well constructed before attempting to update the tables in SQL Server, or any other GraphDatabase.
Using JSON text in Visio shapes
I often create Visio masters with a fixed number of Shape Data rows, and sometimes I get requests to modify them because of changes in business needs. Well, my previous posts about Graph Databases got me thinking if it would be possible to embed JSON text in Visio shapes in some sort of meaningful way. Then the user could enter just attribute-value pairs at will, without the need for me to modify their master shape. Alternatively, the data could be inserted into the shape from a database, such as SQL server. So, in this article I discuss the suitability of JSON text in Visio shapes.
[Read more…] about Using JSON text in Visio shapes
Using Visio and PowerBI with GraphDatabase in SQLServer – Part 2
In my last article, I described how Visio can be used to input Nodes & Edges into a SQL Server graph tables (see Using #Visio and #PowerBI with #GraphDatabase in #SQLServer). In this article, I show how PowerBI can be used to create an Excel table that can then be used to automatically create a Visio diagram. This diagram can then be enhanced for reports and presentations, or used to check the validity of nodes and relationships. The shapes can then be used to update or delete edges and nodes in the database.
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!
Aliasing Data Columns for Visio Organization Chart Wizard
One of the most frequently used components of Visio is the Organization Chart Wizard (OrgChWiz), but it is also one of the most frustrating because of its apparent inflexibility. I was recently asked how to use different table column names with the wizard because the originating ODBC source could not be touched. Well, I have previously used Access to modify and enhance tables in this situation, but the OrgChWiz is quite old, and does not like using the newer Access queries as a data source unfortunately. If the basic table column names do not match the default column names in the organization chart shapes, then there may be superfluous Shape Data rows created on each shape and the ability to change the shape style is compromised, and the Shape Data rows get quite confused!
The Organization Chart template opens with the Belts – Organization Chart Shapes stencil open by default, although the Master shapes used can be replaced with those on one of the other nine using the Org Chart / Shapes gallery: