You can use data from a Fabric Lakehouse in Power BI in two ways: via Direct Lake or via the SQL Endpoint. In this chapter, we explain how the SQL Endpoint works and outline its advantages and disadvantages.
With the SQL Endpoint, Power BI connects to the Lakehouse tables and loads the data into the semantic model using Import mode. The data is therefore copied into the Power BI model, and the report becomes independent of the Lakehouse files. Changes in the source are only visible after a dataset refresh.
Advantages of SQL Endpoint:
- Fabric capacity does not need to be enabled while using the report. After the data is imported, the report runs entirely on the Power BI model. Fabric capacity is only required during refresh, not during viewing or interacting with dashboards.
- Because the SQL Endpoint uses the familiar Import mode, all Power Query functionalities are available.
Disadvantages of SQL Endpoint:
- Unlike Direct Lake, changes and new data in the Lakehouse are only visible after refreshing the Power BI semantic model.
- Since all selected data is physically copied into the Power BI model, refreshing large tables can take more time.
- Dataset refresh consumes Fabric Capacity Units (CUs), as the data must be reloaded via the SQL Endpoint. This usage is usually limited but depends on the size of the tables being imported.
- Unlike Direct Lake, you cannot work entirely in the Power BI Service with the SQL Endpoint. Building the data model and report requires Power BI Desktop.
Import via SQL Endpoint (in Power BI Desktop)
With the SQL Endpoint, you can load data from a Fabric Lakehouse into Power BI Desktop using Import mode. The selected tables are physically copied into the Power BI semantic model. Follow the steps below to import the data. (In this example, we use the English version of Power BI Desktop.)
- Go to OneLake Catalog in Power BI Desktop and select Lakehouses (alternative: choose Get Data and then Lakehouses).
- You will see a list of all available Lakehouses in your tenant. Select the Lakehouse you want to use; in our example, this is ‘LakehouseExactOnline’.
- Click the Connect button and then select the option ‘Connect to SQL Endpoint’.
- Select the tables you want to load. These are usually all tables without a prefix. You do not need to load the tables from the ‘staging’ schema. Likewise, the tables in the ‘queryinsights’ and ‘sys’ schemas are not relevant for reporting.
- Click Load to import the selected tables.
- Select the ‘Import’ option and click OK.
The tables are now imported and stored in the semantic model in Power BI Desktop.
You can now further shape the data model, for example by:
- Adding relationships to create an optimal star schema
- Defining measures for calculations
- Creating visualizations in the report
All standard Power BI capabilities remain fully available when working with the SQL Endpoint.