how to team up power bi with fabric kql (or adx) to unlock graph insights.
introduction.
Those familiar with graph data structures – whether through tools like Neo4j or programming languages like Python or R – probably recognize the unique advantages graphs offer compared to other data structures. To make it clear upfront, when we speak about graphs in this post, we do not refer to the Microsoft Graph API. Although that API also follows a graph structure of relationships and interactions between entities such as users, groups and events, we refer to graphs as the conceptional and technical way of structuring and representing data as a collection of nodes (also known as vertices) and edges. Graphs excel in specific scenarios, providing a natural and efficient way to represent interconnected data. Graphs can be a viable data structure approach especially in social media, supply chain management, logistics, or process mining.
For those unfamiliar with graph structures, let me try to bring up their upsides:
They shine in scenarios where you want to represent and analyse interconnected data. Particularly, when we need to understand the data entities themselves as much as the relationships between those data entities, using graph data structures can be a smart choice.
Of course, we are able to analyse interconnected data with other data structures than graphs, too. However, one key strength of organizing data in graphs is their dynamic nature. In the past, when tackling such graph-like data problems with approaches such as relational modelling, hard joins as well as the flattening of tables, I constantly found myself to be forced to predefine the structure and relationships. This meant I had to decide the order and number of levels I wanted to consider, as well as how to organize and represent the data – all of which beforehand. Unlike graph structures, where relationships can be dynamic and even evolve over time, those techniques (at least the ones I came up with) require predetermined structure. This may limit flexibility and scalability in representing interconnected data.
If this sounds too abstract to you, bear with me. The walkthrough hereinafter will hopefully shed light upon it. Also in the future, I am planning to blog about other aspects of graphs by using other use cases than the social media one described below. This will hopefully help understanding when the outlined approach might be handy and when not. Also, feel free to let me know, if you have worked with graph data structures in other settings, too. I am always keen to see what ideas other people come up with and if the approach outlined in this blog post could be helpful in any way.
While Power BI supports hierarchical and relational data modelling, its capabilities for representing and analysing graph structures are, by nature, fairly limited. One can import data from graph databases like Neo4j into Power BI, but in my experience we then end up in the same situation described above where we need to transfer the dynamic graph data into a static and rigid tabular design. Of course, there is the possibility to utilise languages like Python and R in Power BI for that matter as well. Also, there are dedicated Parent-Child DAX functions (PATH, PATHCONTAINS, PATHITEM, PATHITEMREVERSE and PATHLENGTH) that can help us with certain graph data structures. In this article though, we wanna completely outsource the calculating and traversing through the graph away from Power BI to another engine.
This is where KQL comes into play. With KQL featuring graph calculations and Power BI’s Direct Query capability, you can leverage KQL to establish and query graphs and return the results directly to Power BI. In other words, we swap one versatile tool for another, giving us the best of both worlds: The reporting power of Power BI on the one hand, and the graph capabilities of KQL on the other hand. In Fabric, KQL even works with shortcuts, meaning you don’t have to build an integration that shovels the data over to the KQL database e.g. from your gold layer (you still need to in ADX though). Sloppily speaking, with the shortcut feature in place, KQL simply acts as a serverless engine for direct graph querying from Power BI. KQL does come with other perks within the area of real-time and machine learning. These ones, we do not consider in this article.
Lastly, I wanna stress that this setup might diverge from common best practices. Further, there are probably a number of things that can be tweaked to make this approach work even better. Anyway, the main goal of this article is to spark some creativity in how we can team up Power BI with KQL in Fabric (or ADX) to analyse graph data structures.
prerequisites.
1. A Fabric capacity and workspace
2. Power BI Desktop
1. What’s the goal?
In this blog post, we combine Power BI’s native Vertipaq engine (for tabular data) with the KQL engine (for graph data). We leverage Power BI’s Direct Query when calculating and querying graphs in the KQL engine. In addition, we use Fabric’s shortcut feature, so we do not need to build an integration that loads data from our Gold Layer into the KQL database. Sloppily speaking, we’ll use KQL as a serverless engine to directly query our graph data from Power BI.
Just to clarify, we do not focus on the graph Python visualization you see on the right side in the report (I am planning a blog post about visualising graphs in Power BI, too). Instead, once again, our goal is to dynamically query the graph data structures via Direct Query from the KQL database. We use parameters that you can see in the slicers on top of the screenshot for the filter predicates in our KQL query.
2. The setup in Fabric
First, let’s have a look at our current setting. We have two delta tables in our Lakehouse, SocialMediaUser and SocialMediaUserEdge. The data in both tables are made up. The respective Python scripts for creating those can be found in the appendix.
SocialMediaUser contains all our users of our network and their attributes, like birthday, residence, highest degree or current profession: