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

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

Questa esercitazione illustra come creare una query con il generatore di query visive.

Nota

This tutorial forms part of an end-to-end scenario. Per completare questa esercitazione, è prima necessario completare queste esercitazioni:

1. Creare un'area di lavoro
2. Creare un magazzino
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. Nella finestra di dialogo Merge, completare le impostazioni seguenti:

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

   2. Nella griglia seguente selezionare la colonna CityKey.

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

   4. Nella griglia seguente selezionare la colonna CityKey.

   5. In the Join kind section, select Inner.

   

9. Select OK.

10. Nel riquadro di anteprima dei dati individuare la colonna fact_sale (l'ultima colonna).

    

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

    

12. Nella finestra di dialogo selettore di colonna selezionare solo queste tre colonne: TaxAmount, Profite 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. Impostare l'aggregazione come segue:

       1. Nuovo nome colonna: SumOfProfit
       2. Operation: Sum
       3. Column: Profit

    5. Aggiungere un'altra aggregazione e impostare l'aggregazione come segue:

       1. Nuovo nome colonna: SumOfTotalIncludingTax
       2. Operation: Sum
       3. Column: TotalIncludingTax

    

16. Select OK.

17. Esaminare il risultato della query nel riquadro di anteprima dei dati.

    

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