Tutorial: Create a query with the visual query builder in a Warehouse

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

In this tutorial, learn how to create a query with the visual query builder.

Note

This tutorial forms part of an end-to-end scenario. In order to complete this tutorial, you must first complete these tutorials:

1. Create a workspace
2. Create a Warehouse
3. Ingest data into a Warehouse

Use the visual query builder

In this task, learn how to create a query with the visual query builder.

1. Ensure that the workspace you created in the first tutorial is open.

2. On the Home ribbon, open the New SQL query dropdown, and then select New visual query.

   

3. From the Explorer pane, from the dbo schema Tables folder, drag the fact_sale table to the visual query canvas.

   

4. To limit the dataset size, on the query designer ribbon, select Reduce rows > Keep top rows.

   

5. In the Keep top rows window, enter 10000, and then select OK.

6. From the Explorer pane, from the dbo schema Tables folder, drag the dimension_city table to the visual query canvas.

7. To join the tables, on the query designer ribbon, select Combine > Merge queries as new.

   

8. In the Merge dialog, complete the following settings:

   1. In the Left table for merge dropdown, select dimension_city.

   2. In the following grid, select the CityKey column.

   3. In the Right table for merge dropdown, select fact_sale.

   4. In the following grid, select the CityKey column.

   5. In the Join kind section, select Inner.

   

9. Select OK.

10. In the data preview pane, locate the fact_sale column (the last column).

    

11. In the fact_sale column header, select the Expand button.

    

12. In the column selector dialog, select only these three columns: TaxAmount, Profit, and TotalIncludingTax.

    

13. Select OK.

14. To aggregate the dataset, on the ribbon, select Transform > Group by.

    

15. In the Group by dialog, complete the following settings:

    1. In the three Group by dropdowns, set the following options:

       1. Country
       2. StateProvince
       3. City

    2. In the New column name box, enter the name SumOfTaxAmount.

       1. In the Operation dropdown, select Sum.
       2. In the Column dropdown, select TaxAmount.

    3. Select Add aggregation.

    4. Set the aggregation as follows:

       1. New column name: SumOfProfit
       2. Operation: Sum
       3. Column: Profit

    5. Add another aggregation, and set the aggregation as follows:

       1. New column name: SumOfTotalIncludingTax
       2. Operation: Sum
       3. Column: TotalIncludingTax

    

16. Select OK.

17. Review the query result in the data preview pane.

    

18. Rename the query, right-click on the query tab, and then select Rename.

    

19. In the Rename window, replace the name with Sales Summary, and then select Rename.

Next step

Tutorial: Use a notebook to analyze data in a Warehouse