topic Re: Methods available to datamart API in Data Engineering

Methods available to datamart API

smpa01 — Fri, 12 Jul 2024 18:34:32 GMT

I ran the following method on datamart just to test if refreshes method is avaialble to datamart apiPath ( as it is availabale to dataset apiPath)

-- the following returns error on datamart %%sql WITH cte_workspaces AS ( SELECT explode(pbi_api_udf('/groups', '${pbi_access_token}')) as workspace ) , cteTwo as ( select workspace.name as workspace_name,workspace.id as workspace_id,workspace.apiPath as workspace_apiPath from cte_workspaces WHERE workspace.name = 'Production' ) , cteThree as ( select * ,dm.apiPath as apiPath from cteTwo LATERAL VIEW explode(pbi_api_udf(concat( workspace_apiPath, '/datamarts'), '${pbi_access_token}')) AS dm ) select * from cteThree LATERAL VIEW explode(pbi_api_udf(concat( apiPath, '/refreshes'), '${pbi_access_token}')) AS refreshes -- the following works perfectly on dataset (semantic model) %%sql WITH cte_workspaces AS ( SELECT explode(pbi_api_udf('/groups', '${pbi_access_token}')) as workspace ) , cteB as ( select workspace.name as workspace_name ,workspace.id as workspace_id ,workspace.apiPath as workspace_apiPath ,datasets.name as ds_name ,datasets.apiPath as apiPath from cte_workspaces LATERAL VIEW explode(pbi_api_udf(concat(workspace.apiPath, '/datasets'), '${pbi_access_token}')) datasetsTable AS datasets WHERE workspace.name = 'Production' ) select * FROM cteB LATERAL VIEW explode(pbi_api_udf(concat( apiPath, '/refreshes'), '${pbi_access_token}')) AS refreshes

But it returns {"error":"Not Found","status_code":"404"}

How am I to know, what methods are avaialble to the datamart apiPath? Does there exists a documentation that says what methods are available to what artifacts?

Re: Methods available to datamart API

gbrueckl — Fri, 12 Jul 2024 18:37:37 GMT

I just checked the docs (https://learn.microsoft.com/en-us/rest/api/power-bi/datasets) and it seems there are no APIs for datamarts hence you cannot query them with my code from above

Re: Methods available to datamart API

smpa01 — Fri, 12 Jul 2024 19:13:02 GMT

@gbrueckl Thanks.

The documenation does not list any method for datamart

yet the following succeeds on a workspace_apiPath concat('/groups/abcdef-ghij-klmn-opqr-stuvwx',

'/datamarts') that made me think that I might be able to call other methods.
 
I am little confused here as datamrts don't have any API methods yet it succedds
and I thought I would be able to call additional methods on apiPath? 

cteThree as ( select dm from cteTwo LATERAL VIEW explode(pbi_api_udf(concat( workspace_apiPath, '/datamarts'), '${pbi_access_token}')) AS dm ) the above returns an object { "capabilities": "4", "lastRefreshTimeUTC": "2024-05-28T22:47:54.263Z", "modifiedByUser": "{displayName=user, userPrincipalName=user@company.com, objectId=9e1103aa-a128-4bec-8b8e-d1004422b3b7}", "name": "DEV", "state": "1", "datasetObjectId": "f331fd8e-b3ce-4b24-90aa-c5a0d6699aaf", "lastRefreshStatus": "1", "datamartType": "Sql", "apiPath": "/groups/grpID/datamarts", "nextRefreshTimeUTC": "9999-12-31T23:59:59.997Z", "datasetId": "1955466", "version": "638477205269348724", "ownerUser": "{displayName=user, userPrincipalName=user@company.com, objectId=9e1103aa-a128-4bec-8b8e-d1004422b3b7}", "lastModifiedTimeUTC": "2024-05-29T00:58:29.79", "status": "1", "objectId": "245952d6-0487-4f2a-abcb-89689d4813c5", "folderObjectId": "f8a6fefa-0df7-4fe7-ac93-dc0a9003594e", "dataflowObjectId": "659bd422-955c-43b7-8304-4e50b8f1ab48", "createdDate": "2024-05-28T15:06:43.76" }

Re: Methods available to datamart API

gbrueckl — Fri, 12 Jul 2024 19:20:30 GMT

thats indeed interesting as this API is not mentioned anywhere 😄

neither in the docs nor in the SDK

https://github.com/microsoft/PowerBI-CSharp/blob/master/sdk/swaggers/swagger.json