Relationships with Microsoft Fabric GraphQL API

By: Koen Verbeeck   |   Updated: 2024-10-23   |   Comments   |   Related: > Microsoft Fabric

Problem

We are using Microsoft Fabric for our analytical data platform. The data is hosted in a warehouse and modeled in a star schema. Using the GraphQL API for Fabric, we are exposing it to end users, and for some reports, we want to join data together. Is this possible in a GraphQL query?

Solution

In a previous tip, What is the API for GraphQL in Microsoft Fabric?, we introduced the GraphQL API feature of Microsoft Fabric. With GraphQL, you can build a data access layer for querying your Fabric data (supported sources are warehouses, lakehouses, and mirrored databases) through an API interface. The tip, Query Microsoft Fabric GraphQL API from an External App, explains how you can query this API from a C# application.

However, with the Microsoft Fabric GraphQL API, you can do more than query data from a single table. In this tip, we will discover how to create relationships to query data from different tables together.

How to Use Relationships in the GraphQL Schema

When a source is imported into the GraphQL schema, Fabric will add all the tables with their columns to generate queries and mutations. Any existing relationships from the underlying data source are not copied. If you want to use a relationship between tables – for example, to do some sort of inner join – you will need to create a relationship between the two tables.

Suppose we have the following query, which selects the first 10 customers:

Aside from customer information, we now also want to display some details about purchases the customers need. This data is stored in the table fact_sale. We can expand the query like this:

query {
   dimension_customers(first: 1) {
     items {
       Customer
      ,CustomerKey
      ,Category
      ,PostalCode
      ,fact_sale{
        items{
         CustomerKey
         ,WWIInvoiceID
        }
      }
     }
   }
}

But when we execute the query, an error is returned:

This is because there's no relationship defined yet between the two tables. When you click on the ellipsis next to the customer table, you can choose Manage relationships from the context menu.

In the dialog, click +New relationship.

In the pop-up, you can specify the details of the new relationship. For the cardinality, you can choose between the following:

For our example between a dimension and a fact table, we need a "One to many" relationship. Then, select the "from" table (dimension) and choose the fields to make up the keys on the source side.

Unlike Power BI, you can specify multiple fields to link to. There's no option to explicitly map columns from the "from table" to the "to table," so it assumes it's based on the order in which they are specified. Next, select the "to table" and its corresponding fields.

Click Create relationship.

Back in the GraphQL schema, we can see a "fact_sale" object was added to the customer table (with a relationship icon):

The query can now be executed without error. The first parameter had to be changed to 1; otherwise, a timeout would be thrown. Querying from a dimension to a fact seems to be resource-intensive for the GraphQL API. For reference, a trial capacity (similar to an F64 capacity) was used, and the fact table has about 50 million rows.

Now that the relationship is created between the two tables, can we reverse the direction? Let's try the following query, which takes 10 random sales and retrieves info about the corresponding customers:

query {
    fact_sales(first: 10){
      items{
           CustomerKey
          ,WWIInvoiceID
          ,dimension_customer{
               Customer
              ,CustomerKey
              ,Category
              ,PostalCode
          }
      }
    }
}

The same error is thrown as before:

A relationship in the GraphQL API has only one single direction, just like a normal relationship in Power BI. This means a relationship looks like an inner join in SQL, but it's not entirely the same because, in SQL, you can swap the order of the tables, which is not possible in our query here.

To make the query work, we need to create a relationship in the other direction ("many-to-one"):

We now have two relationships between the two tables, each in the opposite direction.

The query now runs without an issue:

It's interesting to note a subtle difference between the two queries. In the last query, there's no item object inside the dimension_customer reference. The distinction becomes more clear when you look at the two queries side-by-side:

This happens because the right-side query can have multiple sales items for a single query, while the left-side query can only have a single customer for a specific sale.

Many to Many Relationships

The GraphQL API in Fabric also supports the more advanced, many-to-many relationship type. To make this work, the data in the source database needs to be modeled with a bridge table. You can execute the following T-SQL script to create sample tables with sample data:

CREATE TABLE dbo.Books(
     BookID INT NOT NULL
    ,BookName VARCHAR(250) NOT NULL);
 
CREATE TABLE dbo.Author(
     AuthorID INT NOT NULL
    ,AuthorName VARCHAR(250) NOT NULL);
 
INSERT INTO dbo.Books(BookID, BookName)
SELECT 1, 'The Definitive Guide to DAX'
UNION ALL
SELECT 2, 'Analyzing Data with Power Bi and Power Pivot for Excel'
UNION ALL
SELECT 3, 'Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model'
UNION ALL
SELECT 4, 'T-SQL Window Functions: For Data Analysis and Beyond';
 
INSERT INTO dbo.Author(AuthorID, AuthorName)
SELECT 1, 'Marco Russo'
UNION ALL
SELECT 2, 'Alberto Ferrari'
UNION ALL
SELECT 3, 'Chris Webb'
UNION ALL
SELECT 4, 'Itzik Ben-Gan';
 
CREATE TABLE dbo.BookAuthor(
     BookID INT NOT NULL
    ,AuthorID INT NOT NULL
);
 
INSERT INTO dbo.BookAuthor(BookID, AuthorID)
SELECT 1,1
UNION ALL
SELECT 1,2
UNION ALL
SELECT 2,1
UNION ALL
SELECT 2,2
UNION ALL
SELECT 3,1
UNION ALL
SELECT 3,2
UNION ALL
SELECT 3,3
UNION ALL
SELECT 4,4;

It's a simple data model that resembles the relationship between books and authors. An author can write multiple books, and a book can be written by multiple authors.

In the GraphQL editor, import the newly created table using the Get data wizard.

Go back to the relationship management screen and create a new relationship. When the cardinality is set to "many to many," new fields appear that allow you to set the "linking type," which corresponds with the bridge. For our sample data, the configuration looks like this:

Click Create relationship, and Fabric will create two relationships for you, one for each direction.

Now, we can query, for example, the books and retrieve the authors for each book:

Or the other way around, we can retrieve the authors and the books they've written.

Next Steps

• If you want to try it out yourself, follow the steps from the tip, What is the API for GraphQL in Microsoft Fabric?, to get started with the GraphQL API.
• Stay tuned for more tips about the Microsoft Fabric GraphQL API!
• You can find all Fabric-related tips in this overview.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips