Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Graph Analysis using Force Directed Graphs in Power BI Desktop


By:   |   Last Updated: 2017-12-15   |   Comments (1)   |   Related Tips: More > Power BI

Problem

Data can contain complex relationships and it cannot always be represented in a parent-child relationship typically found in relational databases. Specialized databases like graph databases are available in the industry from commercial vendors to deal with the semantic relationships in data.

SQL Server 2017 has introduced the ability to create data structures to host data in the form of graphs. A graph is typically formed of nodes which are connected by relationships called edges. A typical example is an employee to supervisor relationship, where employee and supervisor are nodes and reports-to is the relationship which can be called an edge. Such relationships can be found in abundance in day-to-day data, but analyzing it requires visualizations that are designed for this analysis. One of the industry standard visualization for analyzing data having complex relationships is by using a force directed graph. In this tip we will learn how to use a force directed graph in Power BI Desktop.

Solution

Power BI provides a Force Directed Graph visualization in the Power BI Visuals Gallery to create a visualization for graph data analysis.

In this tip we will use a force directed graph in Power BI Desktop using a dimension from the Adventure Works DW database. It is assumed that Power BI Desktop is already installed on the development machine, as well as the sample Adventure Works DW database is hosted on SQL Server on the same machine. Follow the below steps.

1) The first step is to download the force directed graph package from here, as it is not available by default in Power BI Desktop. This visualization makes use of the D3 force layout diagram. Coding a force directed graph in D3 would require extensive coding and practice. This control eliminates the need for any coding and provides a user interface which the user can customize the graph. If interested, you can read in detail about force directed graphs from here.

Force-Directed Graph - Description: Force-Directed Graph

2) After downloading the file, open Power BI Desktop. You can click on the ellipsis in the visualization tab and select the “Import from file” menu option. This will open a dialog box to select the visualization package file, to add the visualization in Power BI. Select the downloaded file and it should add the graph control to the Power BI Desktop visualizations gallery. 

3) After the graph control is added to the report layout, enlarge it to occupy the entire available area on the report. After you have done that, it would look as shown below.

Add control in Power BI - Description: Add control

4) Now that we have the visualization, it is time to populate some data on which graph analysis can be performed. We need a dataset that has relationships explicitly / implicitly embedded. There’s a dimension table in Adventure Works DW database named “DimEmployee” that contains data of employees and their supervisors. This table contains employee key, employee parent key with their demographic details like name, title, charge rate, etc. So, it is a suitable candidate to be used for graph analysis. Click on the Get Data menu and select SQL Server as shown below.

Add Data in Power BI - Description: Add Data

5) This will open a dialog box to provide server credentials. Provide them as shown below and click OK.

SQL Server Database Connection in Power BI - Description: Server

6) Select the DimEmployee table from the database and click Load.

Load Data in Power BI - Description: Load Data

7) After the loading is complete, the model will get created in Power BI Desktop as shown below.

Data Model in Power BI - Description: Data Model

8) Now it is time to select the fields and add them to the visualization. Click on the visualization in the report layout, and add the EmployeeParentKey to the Source section and EmployeeKey to target section. The intention is to develop a node to node relationship to emphasize the reach of a supervisor to their direct reports. Here every source node is connected to the target node with the same relationship (i.e. reports to). After you add the fields the visualization should look as shown below. The reason we have IDs instead of employee names is that many employees can have same name, and displaying names would also take up a lot of space on the limited visual estate of the report layout. Once an ID of interest has been identified, one can always query the details of the employee from the database. We are not interested in any specific employee here, but the intention is to study the relationships in the data.

Force-Directed Graph in Power BI - Description: Force-Directed Graph

9) If you hover over the biggest node is the graph, it will highlight the other nodes that are related to it. From the size of the node it looks like it must be the most important node (i.e. employees in the organization). The size is largest due to the number of relationships it has with other nodes.

Force-Directed Graph in Power BI with a large number of relationships - Description: Force-Directed Graph

10) Drag and drop the BaseRate field to the weight section. This should make the width of an edge (i.e. relationship) proportional to the chargeable rate of the employee. You would find that the widest edge is not with the biggest node. This means that the employee that relates to the maximum number of nodes (which means having maximum direct reports) does not have the highest charge rate as seen below.

Force-Directed Graph with proportional data in Power BI - Description: Force-Directed Graph

11) This makes the edges important to analyze. So, let’s add the Title field (designation of the employee) to the Link Type section. This would mean that an employee is connected to another employee as CEO, Vice President, Supervisor, etc. This is a way to bring in designations in the data relationships to analyze the edges.

Force-Directed Graph Connecting Data Points in Power BI - Description: Force-Directed Graph

12) Let’s navigate to the format section and under the links area to enable the Arrows option to On. Also change the color from the default value of “Interactive” to “Link Type”, which means that employee (nodes) with the same level and reporting to the same supervisor would have the same color. This would create arrows on every target node. If you analyze the dataset you would find that only the CEO does not have a parent employee key, which means that only one node in the entire graph would not have an arrow pointing at it. If you look at the graph shown below, it loaded with too much information to analyze in a limited space.

Force-Directed Graph with colors linking relationships in Power BI - Description: Force-Directed Graph

13) Let’s filter this data to remove nodes that are not of interest to us. Navigate to the filter section, and checkout the Production Technicians as shown below. These are the nodes having the maximum population and employees at junior career levels are not of interest to us. Now the graph looks as shown below.

Force-Directed Graph with data filtering in Power BI - Description: Force-Directed Graph

14) If you carefully analyze the node having the widest edge in green, you will find a node that is mentioned as Blank. Hover your mouse over this node / edge and you would find a tooltip as shown below. This shows that the CEO has the highest base rate, but is not connected directly with a lot of other employees as their supervisor. This is obvious as the CEO might not directly manage a lot of employees.

Force-Directed Graph data with a pop-up in Power BI - Description: Force-Directed Graph

15) If you hover the mouse over the biggest node that is connected to the maximum of other nodes, you would find that the employee is a Production Control Manager and reports to the CEO.

Force-Directed Graph with data access via hovering your mouse in Power BI - Description: Force-Directed Graph

16) If we want to categorize and color employees by their rates, we can change the color option from “By Link Type” to “By Weight” as we have configured the Weight section with the BaseRate attribute. This shows employees with same rates in the same color.

Force-Directed Graph with same rates i the same color in Power BI - Description: Force-Directed Graph

In this way, without learning any D3 coding, one can easily develop and analyze relationships in a data using a force directed graph in Power BI Desktop.

Next Steps
  • Try modifying other cosmetic options in the forced directed graph visualization to analyze the data from different perspectives.
  • Check out all of the Power BI tips on MSSQLTips.com.


Last Updated: 2017-12-15


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, May 01, 2018 - 12:08:40 PM - karen santos Back To Top

Power BI does not find the database Adventure works DW called "DimEmployee ", it helps me to look at how the visualization works


Learn more about SQL Server tools