Sifiso W. Ndlovu

How to plot a SQL Server 2017 graph database using PowerBI

January 9, 2018 by

In the article How to plot a SQL Server 2017 graph database using SQL Server R, I highlighted the lack of built-in graph data visualisation as one major limitation of the SQL Server 2017 graph database feature. In the same article, I went on to suggest making use of SQL Server R as one workaround that could be utilised in order to successfully plot and visualise diagrams out of SQL Server 2017 graph database objects. However, whilst 3rd party graph database vendors such as Neo4j provide an interactive and hyperlinked graph diagrams that allows you to – amongst other things – easily drilldown and identify node-relationships as indicated in Figure 1, the graph plotted using SQL Server R is not very interactive in fact it is simply a static image file as shown in Figure 2.

Figure 1: Interactive graph plotted using Neo4j

Figure 2: Static image file plotted using SQL Server R

Although the perceived lack of animation in graphs plotted using SQL Server R might not be a deal breaker for data scientists and R developers alike, you may find it hard to convince business end-users to abandon an interactive data visualisation for a static image file. Fortunately, the Microsoft Power BI tool is designed to provide interactive data visualisations for business end-users and it just so happens that it also supports visualisations based off SQL Server 2017 graph database. Therefore, in this article, we take a look at the integration of Power BI and SQL Server 2017 graph database.

Get Data: SQL Server 2017 Graph Database

Continuing to use our sample SocialGraph graph database created in this article, we begin by importing our sample nodes and edges tables into Power BI Desktop client. The first thing that you will notice is that as you attempt to load these tables, you will run into an error message similar to the one shown in Figure 3.

Figure 3: Cannot access internal graph column error message

At first glance, one might assume that there is a software bug in the Power BI Desktop tool which causes internal graph columns to be inaccessible. However, regardless of the client tool you may be using, internal graph columns in SQL Server 2017 are not meant to be accessible by applications external to the SQL Server engine. For instance, Figure 4 shows the properties of node table dbo.Club.

Figure 4: Properties of a Node Table

Using SQL Server Management Studio (SSMS) as my T-SQL client, I attempted to read the value of the graph_id_* column by executing the code shown in Script 1.

Script 1

However, the execution of Script 1 led to the error message shown below – which is actually similar to the error message returned in Figure 3 by Power BI Desktop.

Msg 13908, Level 16, State 1, Line 9
Cannot access internal graph column ‘graph_id_94FADC773CB2462DB180F6BCD8456123’.

So how do we get around an error like this one?

Well, it looks like the following steps do the trick:

  1. Write a SELECT statement against graph database table specifying a list of column names that you are looking for (excluding internal graph columns)
  2. Wrap the above SELECT statement in a SQL Server view object
  3. Point Power BI to the newly created SQL Server view object

Figure 5 shows a SQL Server view (i.e. getgraph) created using the aforementioned steps being successfully imported into Power BI Desktop.

Figure 5: Preview of graph database in Power BI Desktop

Now that we have found a mechanism to extract a graph database table into Power BI without any errors, we next modify the definition of our sample getgraph view to include joins to dbo.Player, dbo.Club and dbo.playedFor graph database tables as shown in Script 2.

Script 2

Import Custom Power BI Visual

Whilst writing this article, I was using the December 2017 release of Microsoft Power BI Desktop and the release did not readily provide visualisations that supported effective plotting of graph databases. Thus, I had to download and import a Power BI custom visual to successfully plot an interactive graph database. One such custom visual, is the Force-Directed Graph which is an open source custom visual developed by Microsoft and designed for visualising connections between two or more entities. Although my demo is based off the desktop version of Power BI, the Force-Directed Graph visual also works with the web version of Power BI.

Regardless of the version of Power BI you may be using, the installation of the Force-Directed Graph is straightforward; simply click the ellipsis button under the Visualisation pane and choose the import type – in my case, I chose to Import from file. Once the import has been completed, the Force-Directed Graph visual will appear in the list of your visualisations as shown in Figure 6.

Figure 6

Generate Graph Database Using Power BI

Having imported data off a SQL Server 2017 graph database and successfully installed a custom Force-Directed Graph visual, we are only left with the easiest yet interesting part and that is to plot and generate an interactive Power BI graph. Just like in most Power BI visuals, the Force-Directed Graph visual has several properties that can be configured according to user preference. In terms of configuring data fields, the custom visual is likely to auto-detect what dataset fields goes where but just to be certain, Figure 7 indicates the allocation of fields from the getgraph view according to Source, Target and Link Type properties.

Figure 7: Data fields configuration for Force-Directed Graph visual

Figure 8 shows that the Force-Directed Graph visual further providers a Format option that can be used to configure properties such as the background appearance of the graph to be plotted, specify data labels and turn on/off graph animations. You can further represent your nodes in the graph using custom images.

Figure 8: Some of the formatting options that can be applied to the graph

Figure 9 shows a basic graph database that we have managed to plot using Power BI. However, the graph can further be improved by spending some time playing around with the “look and feel” properties displayed in Figure 8.

Figure 9: Graph Database in Power BI

Summary

In addition to using SQL Server R for plotting SQL Server 2017 graph database as discussed in this article, I have used this article to demonstrate a Power BI mechanism for plotting and visualising graph databases. Although, it relies on custom visual, Power BI provides an interactive option for business end-users to utilise in order to visually make sense of their SQL Server 2017 graph database.

See more

For BI documentation, consider ApexSQL Doc, a tool that documents SQL Server instances, databases, objects, SSIS packages, SSAS cubes, SSRS reports, Tableau server sites and SharePoint Server farms.

References



Sifiso W. Ndlovu

Sifiso W. Ndlovu

Sifiso is a Johannesburg based certified professional within a wide range of Microsoft Technology Competencies such SQL Server and Visual Studio Application Lifecycle Management.

He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

He currently works for Clientele Life as an Assistant Manager in Business Software Solutions.

View all posts by Sifiso W. Ndlovu
Sifiso W. Ndlovu
SQL Server 2017, T-SQL

About Sifiso W. Ndlovu

Sifiso is a Johannesburg based certified professional within a wide range of Microsoft Technology Competencies such SQL Server and Visual Studio Application Lifecycle Management. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg. He currently works for Clientele Life as an Assistant Manager in Business Software Solutions. View all posts by Sifiso W. Ndlovu

1,764 Views