Edit

Tutorial: Create cross-warehouse queries with the SQL query editor

  • Article

Applies to: SQL analytics endpoint and Warehouse in Microsoft Fabric

In this tutorial, learn about how you can easily create and execute T-SQL queries with the SQL query editor across multiple warehouse, including joining together data from a SQL analytics endpoint and a Warehouse in Microsoft Fabric.

Add multiple warehouses to the Explorer

  1. Select the Data Warehouse Tutorial workspace in the navigation menu.

  2. Select the WideWorldImporters warehouse item.

  3. In the Explorer, select the + Warehouses button.

    Screenshot from the Fabric portal Explorer, showing the + Warehouse button boxed in red.

  4. Select the SQL analytics endpoint of the lakehouse you created using shortcuts previously, named ShortcutExercise. Both items are added to the query.

    Screenshot from the Fabric portal Add warehouses window. Two warehouses are selected, including the ShortcutExercise SQL analytics endpoint.

  5. Your selected warehouses now show the same Explorer pane.

Execute a cross-warehouse query

In this example, you can see how easily you can run T-SQL queries across the WideWorldImporters warehouse and ShortcutExercise SQL analytics endpoint. You can write cross-database queries using three-part naming to reference the database.schema.table, as in SQL Server.

  1. From the ribbon, select New SQL query.

    Screenshot from the Fabric portal showing the ribbon, and the New SQL query option boxed in red.

  2. In the query editor, copy and paste the following T-SQL code.

    SELECT Sales.StockItemKey, 
Sales.Description, 
SUM(CAST(Sales.Quantity AS int)) AS SoldQuantity, 
c.Customer
FROM [dbo].[fact_sale] AS Sales,
[ShortcutExercise].[dbo].[dimension_customer] AS c
WHERE Sales.CustomerKey = c.CustomerKey
GROUP BY Sales.StockItemKey, Sales.Description, c.Customer;

  3. Select the Run button to execute the query. After the query is completed, you will see the results.

    Screenshot from the Fabric portal showing the results of a cross-warehouse query.

  4. Rename the query for reference later. Right-click on SQL query 1 in the Explorer and select Rename.

  5. Type Cross-warehouse query to change the name of the query.

  6. Press Enter on the keyboard or select anywhere outside the tab to save the change.

Execute a cross-warehouse cross-workspace query

To query data from Warehouse A residing in another workspace than your Warehouse B, follow these steps:

  1. Create a lakehouse in the same workspace as your Warehouse B.
  2. In that lakehouse, create a shortcut pointing to the required databases or tables from Warehouse A.
  3. Through the previous cross-warehouse sample query, you can now query tables in that lakehouse which are just a shortcut to Warehouse A. For example: 
    SELECT * FROM [lakehouse].[dbo].[table_shortcuted_from_warehouse_A]

Note

Cross-warehouse cross-workspace querying is currently limited for queries within the same region.

Next step

Tutorial: Create Power BI reports