Reference

Table Workspaces

Description

No description available

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.
Method Supported Reference EndPoint
SELECT get_workspaces
INSERT
UPDATE
UPSERT
DELETE
LOOKUP get_workspace

Examples

SSIS

Use Power BI Connector in API Source component to read data or in API Destination component to read/write data:

Read from Workspaces table using API Source

API Source - Power BI
Read and write Microsoft Power BI data effortlessly. Access, refresh, and manage datasets, reports, and dashboards — almost no coding required.
Power BI
Workspaces
There are no parameters to configure.
SSIS API Source - Read from table or endpoint

Read/write to Workspaces table using API Destination

API Destination - Power BI
Read and write Microsoft Power BI data effortlessly. Access, refresh, and manage datasets, reports, and dashboards — almost no coding required.
Power BI
Workspaces
Select
There are no parameters to configure.
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read workspaces

<p>Gets all workspaces available to the user.</p> 

SELECT * 
FROM Workspaces

Read a workspace by ID

<p>Gets a single workspace by ID. Supply <code>Id</code> in the <code>WHERE</code> clause.</p> 

SELECT * 
FROM Workspaces
WHERE Id='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'

Call generic API request

<p>Calls a generic Power BI API endpoint. Supply the endpoint path in the <code>Url</code> parameter.</p> 

SELECT * 
FROM generic_request
WITH (Url='/groups',
Filter='$.value[*]')


/*
EXPLANATION:
- This configuration calls Power BI REST API "Get Groups" endpoint and gets the Workspaces back.
- This is achieved by "/groups" value in the "Url" parameter.
- The SQL query parameter "Filter" uses JsonPath "$.value[*]".
- This gets JSON objects from "value" array and transforms them into SQL rows.

MORE INFORMATION:
- About "Get Groups" REST API endpoint:
  https://learn.microsoft.com/en-us/rest/api/power-bi/groups/get-groups
- About JsonPath used in "Filter" parameter:
  https://zappysys.com/blog/jsonpath-examples-expression-cheetsheet  
*/

Call generic API request with URL parameters

<p>Calls a generic Power BI API endpoint with URL parameters (e.g. filters). Supply the endpoint path and parameters in the <code>Url</code> parameter.</p> 

SELECT * 
FROM generic_request
WITH (Url='/groups?$filter=contains(name,''MyWorkspace'') or name eq ''My Blue Workspace''',
Filter='$.value[*]')


/*
EXPLANATION:
- This configuration calls Power BI REST API "Get Groups" endpoint and gets the Workspaces back.
- This is achieved by "/groups" value in the "Url" parameter.
- Workspaces are filtered on the Power BI REST API side by using the "$filter" URL parameter.
- Only those Workspaces are returned that:
  > contain a string value "MyWorkspace" or
  > if the Workspace name is "My Blue Workspace" (each single quote is escaped with two single quotes).
- The SQL query parameter "Filter" uses JsonPath "$.value[*]".
- This gets JSON objects from "value" array and transforms them into SQL rows.

MORE INFORMATION:
- About "Get Groups" REST API endpoint:
  https://learn.microsoft.com/en-us/rest/api/power-bi/groups/get-groups
- About JsonPath used in "Filter" parameter:
  https://zappysys.com/blog/jsonpath-examples-expression-cheetsheet  
*/

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Read workspaces

<p>Gets all workspaces available to the user.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * 
FROM Workspaces';

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Read a workspace by ID

<p>Gets a single workspace by ID. Supply <code>Id</code> in the <code>WHERE</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * 
FROM Workspaces
WHERE Id=''aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee''';

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Call generic API request

<p>Calls a generic Power BI API endpoint. Supply the endpoint path in the <code>Url</code> parameter.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * 
FROM generic_request
WITH (Url=''/groups'',
Filter=''$.value[*]'')


/*
EXPLANATION:
- This configuration calls Power BI REST API "Get Groups" endpoint and gets the Workspaces back.
- This is achieved by "/groups" value in the "Url" parameter.
- The SQL query parameter "Filter" uses JsonPath "$.value[*]".
- This gets JSON objects from "value" array and transforms them into SQL rows.

MORE INFORMATION:
- About "Get Groups" REST API endpoint:
  https://learn.microsoft.com/en-us/rest/api/power-bi/groups/get-groups
- About JsonPath used in "Filter" parameter:
  https://zappysys.com/blog/jsonpath-examples-expression-cheetsheet  
*/';

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Call generic API request with URL parameters

<p>Calls a generic Power BI API endpoint with URL parameters (e.g. filters). Supply the endpoint path and parameters in the <code>Url</code> parameter.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * 
FROM generic_request
WITH (Url=''/groups?$filter=contains(name,''''MyWorkspace'''') or name eq ''''My Blue Workspace'''''',
Filter=''$.value[*]'')


/*
EXPLANATION:
- This configuration calls Power BI REST API "Get Groups" endpoint and gets the Workspaces back.
- This is achieved by "/groups" value in the "Url" parameter.
- Workspaces are filtered on the Power BI REST API side by using the "$filter" URL parameter.
- Only those Workspaces are returned that:
  > contain a string value "MyWorkspace" or
  > if the Workspace name is "My Blue Workspace" (each single quote is escaped with two single quotes).
- The SQL query parameter "Filter" uses JsonPath "$.value[*]".
- This gets JSON objects from "value" array and transforms them into SQL rows.

MORE INFORMATION:
- About "Get Groups" REST API endpoint:
  https://learn.microsoft.com/en-us/rest/api/power-bi/groups/get-groups
- About JsonPath used in "Filter" parameter:
  https://zappysys.com/blog/jsonpath-examples-expression-cheetsheet  
*/';

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];