Table Workspaces

Description

No description available

Parameters

Parameter	Label	Required	Options	Description	Help
There are no parameters

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

Power BI

Workspaces

SSIS API Source - Read from table or endpoint

Read/write to Workspaces table using API Destination

Power BI

Workspaces

Select

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Workspaces - Get Workspaces

SELECT * 
FROM Workspaces

Workspaces - Get a Workspace

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

Generics - A simple generic API request

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  
*/

Generics - A generic API request with URL parameter

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:

Workspaces - Get Workspaces

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

EXEC (@MyQuery) AT [LINKED_SERVER_TO_POWER_BI_IN_DATA_GATEWAY];

Workspaces - Get a Workspace

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

EXEC (@MyQuery) AT [LINKED_SERVER_TO_POWER_BI_IN_DATA_GATEWAY];

Generics - A simple generic API request

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 [LINKED_SERVER_TO_POWER_BI_IN_DATA_GATEWAY];

Generics - A generic API request with URL parameter

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 [LINKED_SERVER_TO_POWER_BI_IN_DATA_GATEWAY];