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.

This slideshow requires JavaScript.

In the above example, the PowerBI table visual is filtered by the other visuals to provide a list of Nodes and Edges. The PowerBI web app can then export this table to Excel, which can then be read by some code in LinqPad (as this is just a prototype), and the Nodes and Edges can then be automatically added to the active Visio page. As this is just a proof of concept, I left the code un-optimised so that the progress of the shapes being added to the page and being connected can be seen. This would, of course, be much quicker in a finished application. Also, I have not bothered with any nice icons either, but this could also be a simple enhancement.

RecreatingFromFilteredList

Although I have used a Force-Directional visual in Power BI,  I am sure there are others that would also be useful. All I was interested in for this example was to be able to create a filtered list in a suitable form for use as a source for creating a partial graph diagram in Visio. In fact, Cypher Queries can also be written to produce tables of Nodes and Edges that could be used to create Visio diagrams too.

Deleting Nodes & Edges

Of course, now that I have created a simple way to create Nodes & Edges, then I can more easily create mistakes. So, it is also necessary to be able to delete them.

To do this safely, I assumed that no Node should be deleted if it has any Edges. Therefore, selected Edges need to be deleted first, and then the Nodes can be deleted if there are no remaining Edges. I also decided that, rather than delete the shapes, that I would simply remove the Data Graphic. Thus it is easier to re-insert the shapes back into the database, if desired. A possible enhancement is to add a Status shape data row to each shape, so that this could be changed between, say, In DB and Not in DB. These values could then be used to Color by Value or apply a Data Graphic Icon Set.

DeletingNodesAndEdges.gif

I could not find an example of deleting Edge rows, but I managed to write some dynamic SQL in LinqPad, eg:

 var results = ExecuteQueryDynamic($"DELETE FROM [{ens.Edge}] " +
 $"Where $from_id = (SELECT $node_id FROM {ens.Node1} WHERE id = {ens.Node1ID})" +
 $"And $to_id = (SELECT $node_id FROM {ens.Node2} WHERE id = {ens.Node2ID})");

In this example, my ens object is the values read from the selected Visio Dynamic connector/Edge shape. So, ens.Edge is the Edge table where the row is stored.

There are some limitations to the current SQL Server implementation of Graph objects, such as Edges are mono-dimensional, but I think it is an exciting progression. I also think that a little custom code for Visio can create an effective tool for entering, deleting and displaying information within the database. All I need now is a real project to develop a proper solution for!

 

 

4 Responses to “Using #Visio and #PowerBI with #GraphDatabase in #SQLServer – Part 2”

  1. [Reference] Power BI blog posts – the (almost) complete list 2018 – Bonjour Joël Says:

    […] Using Visio and Power BI with GraphDatabase in SQL Server – Part 2 […]

  2. Using #JSON text in #Visio shapes | bVisual - for people interested in Microsoft Visio Says:

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

davecra.wordpress.com/

Microsoft Office 365 Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: