Access inbound restricted lakehouse data from Power BI using an OPDG gateway

An on-premises data gateway (OPDG) can be used to establish cross-workspace communication between an open workspace and a workspace that restricts inbound public access. For example, if you want to access a lakehouse in an inbound restricted workspace from Power BI reports and semantic models in an open workspace, you can set up an OPDG. An OPDG is typically installed on a virtual machine or a physical server inside a private network and acts as a bridge between your data and Fabric cloud services.

In this diagram, the open workspace (Workspace 1) contains Power BI reports and a semantic model that is bound to an OPDG. The OPDG enables the connection to the lakehouse in the inbound restricted workspace. This setup allows Power BI reports and semantic models in Workspace 1 to securely access the lakehouse in Workspace 2 without exposing it to public access.

This article explains how to connect an open workspace to data in a restricted workspace using an OPDG for Power BI semantic models. It also explains how to create semantic models in import and DirectQuery mode against a lakehouse in an inbound restricted workspace.

Step 1: Create the workspaces

You need both an open workspace and a restricted workspace. In this article, the workspaces are referred to as follows:

• The source workspace is the open workspace without public access restriction and is where you create the reports and semantic models.
• The target workspace is the restricted workspace with public access restriction and is where you create the lakehouse.

To create the workspaces, follow these steps:

1. Create two workspaces in Fabric. For details, see Create a workspace.
2. In the tenant settings, enable workspace-level inbound access protection.
3. For the target workspace, set up workspace-level private links.

Step 2: Create a lakehouse in the target (restricted) workspace

Create a lakehouse in the target workspace and upload a Delta Table to it by following these steps.

1. Create a lakehouse in the target workspace using the API. Use the format:

   POST https://{workspaceFQDN}/v1/workspaces/{workspaceID}/lakehouses
   

   where {workspaceFQDN} is {workspaceID}.z{xy}.w.api.fabric.microsoft.com

   For example: POST https://aaaaaaaa000011112222bbbbbbbbbbbb.zaa.w.api.fabric.microsoft.com/v1/workspaces/aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb/lakehouses

2. Add data to the lakehouse by using Azure Storage Explorer to upload your Delta Table folder into the lakehouse. In Azure Storage Explorer, select ADLS Gen2 container or directory.

   

3. Sign in. Then enter a display name for the storage and enter the blob container URL in the following format:

   https://{workspaceFQDN}/{workspaceID}/{lakehouseID}

   where {workspaceFQDN} is {workspaceID}.z{xy}.onelake.fabric.microsoft.com

   For example: POST https://aaaaaaaa000011112222bbbbbbbbbbbb.zaa.w.api.fabric.microsoft.com/v1/workspaces/aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb/bbbbbbbb-1111-2222-3333-cccccccccccc

4. Select Connect. The storage should now be displayed.

5. Under the Tables folder, upload the Delta Table you want to use (for example, the customers table).

   

Step 3: Create a semantic model in the open workspace

Now that you have a lakehouse in the restricted workspace, you can create a semantic model in the open workspace that references this lakehouse. You can use:

• Web modeling experience in the Power BI portal.

• Power BI Desktop.

• REST APIs to deploy a model definition with the same table schema as in the restricted lakehouse.

• XMLA-based tools to deploy a semantic model programmatically (see Semantic model connectivity with the XMLA endpoint).

Option 3a: Using the Web modeling experience

1. In the source workspace, click on New item, and then in the New item pane, click on the Semantic model tile.

2. On the Add data to start building a report page, click on Get Data, and then choose Azure SQL database to connect to the data source via the SQL Analytics Endpoint.

3. In the Server textbox, enter the workspaceFQDN using the format for warehouse connection strings, https://{GUID}-{GUID}.z{xy}.datawarehouse.fabric.microsoft.com that is, add z{xy} to the regular warehouse connection string found under SQL connection string. The GUIDs in the FQDN correspond to Tenant GUID in Base32 and Workspace GUID in Base32 respectively.

4. Optionally, in the Database textbox, enter the Guid of the SQL Analytics Endpoint to which you want to connect.

5. Under Connection credentials, apply the following configuration settings:

6. Accept the remaining default settings and click on Next.

7. On the Choose data page, select the tables you want to include in the semantic model and then click on Create a report.

8. In the Create new report dialog box, verify that the source workspace is selected, provide a meaningful semantic model name, and then click on Create.

9. In the Some Steps didn’t Complete dialog box, click on Open Model View. Note that the semantic model cannot yet connect to the SQL Analytics Endpoint in the inbound restricted target workspace. You complete the connection configuration in Step 4 below.

Option 3b: Using the Web modeling experience

1. In the source workspace, click on New item, and then in the New item pane, click on the Semantic model tile.

2. On the Add data to start building a report page, click on Get Data, and then choose Azure SQL database to connect to the data source via the SQL Analytics Endpoint.

3. In the Server textbox, enter the workspaceFQDN using the format for warehouse connection strings, https://{GUID}-{GUID}.z{xy}.datawarehouse.fabric.microsoft.com that is, add z{xy} to the regular warehouse connection string found under SQL connection string. The GUIDs in the FQDN correspond to Tenant GUID in Base32 and Workspace GUID in Base32 respectively.

4. Optionally, in the Database textbox, enter the Guid of the SQL Analytics Endpoint to which you want to connect.

5. Under Data Connectivity mode, choose Import or DirectQuery according to your requirements, then click OK.

6. If prompted, select Microsoft account in the authentication dialog and then click Sign in to provide the credentials to access the data source.

7. In the Navigator dialog box, select the tables you want to include in the semantic model and then click Load.

8. Add a visual to the report canvas and then, on the Home ribbon, click on Publish.

9. Save your changes to a Power BI Desktop file on the local machine and then in the Publish to Power BI dialog, select the source workspace. Alternatively, you can import the Power BI Desktop file to the source workspace in the Power BI portal.

Option 3c: Deploying a semantic model definition

1. In the open workspace, create a semantic model using a definition with the same table schema as in the restricted lakehouse. Use the following API:

   POST https://{workspaceFQDN}/v1/workspaces/{workspaceID}/semanticModels
   

   where {workspaceFQDN} is {workspaceID}.z{xy}.w.api.fabric.microsoft.com

2. Before executing the semantic model creation, edit the data source to reference the restricted lakehouse’s connection string and lakehouse ID. Convert the definition/tables/customers.tmdl file from the semantic model definition from Base64 to JSON and copy the output.

   

3. Update the source with the restricted lakehouse’s connection string and database ID. Then convert the JSON back to Base64 and use it in your semantic model creation request.

   

4. Use the Get Lakehouse API to retrieve the connection string and lakehouse ID:

   GET https://{workspaceFQDN}/v1/workspaces/{workspaceID}/lakehouses
   

   where {workspaceFQDN} is {workspaceID}.z{xy}.w.api.fabric.microsoft.com

Step 4: Enable a gateway connection in the semantic model settings

To enable the semantic model to connect to the lakehouse in the restricted workspace, you need to set up an on-premises data gateway (OPDG) and bind it to the semantic model.

1. In the Power BI semantic model settings, turn on the toggle under Gateway Connections.

   

2. Install and configure an OPDG on a virtual machine as described in Install an on-premises data gateway.

3. Use the API to retrieve the Gateway ID for the installed OPDG instance:

   https://api.fabric.microsoft.com/v1/gateways

4. In Power BI, create the OPDG SQL Server connection. Use the lakehouse's server name and lakehouse ID as the database, authenticate using OAuth2, and copy the resulting Connection ID.

   

5. Use the API to bind the semantic model to the gateway ID and connection ID:

   https://api.powerbi.com/v1.0/myorg/groups/{workspaceID}/datasets/{SemanticModelID}/Default.BindToGateway

   

6. Verify the gateway binding. In the Semantic Model settings, refresh the page. The OPDG should now be shown as the active gateway.

   

7. Refresh the dataset and build a report.