Expose data from Fabric (lakehouse/warehouse/powerBI) via API

[shervinmir]

Hi team,

I need to expose data within my lakehouse/warehouse via API. So the requirement is for a none Azure external users to be able to get access to some of the data on my Fabric instance. Also in some cases I need to have control on the who can pull what. So for example if I give access to the API too two diffrent users. Each user should be able to see their own data and do not have access to all the data. 

I looked at the Fabric API docuemntatin and it seems like its only avaiable to create/edit/delete and get information on tables, and not neccesary pull the data within the table. Can anyone suggest the cleanest way to do this? I basic requiremnt is to expose data within Fabric to outside users with some permission control.

Thanks in advance,

Shervin

[shervinmir]

Hi team,

I was able to get the GraphQL API working using the tables within my lakehouse/warehouse. Now what I want to achieve is the easiest way to upload tables within my Power BI Desktop file to my lakehouse/warehouse in Fabric so then I can expose those tables using GraphQL API.  Looking at the GraphQL documenation I can see that the supported data sources are either warehouse, lakehouse, mirrored databases or datamarts. So the question is what is the easisest way to import the tables within the Power BI desktop file to my lakehouse/warehouse? The two options I have explored so far is

1. I was able to copy the table from Power BI desktop to CSV and import the CSV into my lakehouse using pipeline, and lastly I exposed the table via GraphQL API. The table column names have spaces and some special characters within them.  Within my table import pipeline I had to map all the column data types and also remove all the spaces and invalid characters. I could be wrong but this does not feel like the easiest solution

2. I was able to load the Power BI Desktop file into Fabric worksplace and after that when I click the schematic model I can share model, query and export the tables. But in order to use the GraphQL API I need the tables to be in my lakehouse/warehouse. I cant seem to find a way to import the tables from the Power BI desktop file I uploaded into my lakehouse/warehouse. So here is some extra information about the tables. So within the report I have some base tables that are connecting to diffrent SQL server sources and then I have a Dax query that combines these tables into one final table. Need to know if its possible to use this table after uploading the Power BI desktop file onto Fabric within my lakehouse/warehouse. I dont want to recreate the logic/connections again using multiple pipelines for each table. One thing to note is the tables are very large some just below 1 million rows and some over 1 million rows

Thanks again for your help

[shervinmir]

Hi @Anonymous ,

Any update on my latest question? 

Thanks

[Anonymous]

Hi @shervinmir ,

Apologies for the delay in reply from our side.
The best course of action is to open a support ticket and have our support team take a closer look at it.

Please reach out to our support team so they can do a more thorough investigation and can provide an appropriate solution: Link 

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Hope this helps. Please let us know if you have any other queries.

[Anonymous]

Hi @shervinmir ,

Thanks for using Fabric Community.
Unfortunately we don't have the rest api which exposes data from Fabric.
At present we only have the below supported APIs -

Lakehouse management API - Microsoft Fabric | Microsoft Learn

Items - REST API (Warehouse) | Microsoft Learn

Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

Hope this helps. Please let me know if you have any further queries.

[shervinmir]

Thank you for your response. I was wondering if I can perhabs use API for GraphQL to connect to one of the tables and then I can run some queries using API? Under "Data Development" I can see there is a option for "API for GraphQL" which takes me to a flow for connecting to the data source on my Fabric isntance. Is that valid option for what I'm trying to achieve? If so can you point me to the documentation on how I can set it up? Also I want to test the flow using python request libary.  Using (https://learn.microsoft.com/en-us/fabric/data-engineering/get-started-api-graphql)I have successfully ran a GraphQL query after connecting it to one of my tables in my datawarehouse on my Fabric instance in the power bi portal. Now im trying to see how I can run the same query using python requests libary to do POST/GET request. I have found few examples online about calling running GraphQL queries using Python but when I try it with my endpoint I keep getting 400 bad request response. Below is what my Python code looks like

import requests

url = '{my GraphQL endpoint}'

query = """

query {

  employees {

    items {

      FirstName

      CompanyId

      EmployeeId

    }

  }

}

"""

headers = {

    'Content-Type': 'application/json',

    'Authorization': 'Bearer {my azure token}'

}

response = requests.post(url, json={'query': query}, headers=headers)

# Print the JSON response

print(response.json())

[Anonymous]

Hi @shervinmir ,

Inorder to authorize API calls to the GraphQL endpoint, applications require a Tenant ID and a Client ID. Have you registered?

Docs to refer - Unleashing the power of data for analytics applications with the new Microsoft Fabric API for GraphQ...

Hope this is helpful.

[shervinmir]

Thanks you for your reply. Yes I have registred the app. Do we need the application ID in the GraphQL  POST request I have posted above? Or that is only used for the get token step? Also I wanted too verify something. Is there a way for us to resitrcit the data on the GraphQL API? For example on the table I'm exposing there is a column name company ID. I want to restrict access to the rows that only belong to a certain user. So for example if user1 calls the API I want to only return the rows for company ID equals to 1 based on token or whatever unique identifier that identifies the user. If it dont come out of the box with this feautre can you suggest a way to do this? 

Thanks again

[Anonymous]

Hi @shervinmir ,

Apologies for the delay in reply from our side. I think application ID is not required along with post request once you get the token. 

Unfortunately at present we don't have any option or way to restrict access with Fabric API Graph QL.

Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

Hope this helps. Please let me know if you have any further queries.

[Anonymous]

Hello @shervinmir ,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

Thanks

[Anonymous]

Hi @shervinmir ,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

Thanks