Edit

Visualize data with Power BI

  • Article

In this tutorial, you learn how to create a Power BI workspace, link your Azure Synapse workspace, and create a Power BI data set that utilizes data in your Azure Synapse workspace.

Prerequisites

To complete this tutorial, install Power BI Desktop.

Overview

From the NYC Taxi data, we created aggregated datasets in two tables:

  • nyctaxi.passengercountstats
  • SQLDB1.dbo.PassengerCountStats

You can link a Power BI workspace to your Azure Synapse workspace. This capability allows you to easily get data into your Power BI workspace. You can edit your Power BI reports directly in your Azure Synapse workspace.

Create a Power BI workspace

  1. Sign in to powerbi.microsoft.com.
  2. Select Workspaces, then select Create a workspace. Create a new Power BI workspace named NYCTaxiWorkspace1 or similar, since this name must be unique.
  1. In Synapse Studio, go to Manage > Linked Services.
  2. Select New > Connect to Power BI.
  3. Set Name to NYCTaxiWorkspace1 or similar.
  4. Set Workspace name to the Power BI workspace you created earlier, similar to NYCTaxiWorkspace1.
  5. Select Create.

Create a Power BI dataset that uses data in your Azure Synapse workspace

  1. In Synapse Studio, go to Develop > Power BI.
  2. Go to NYCTaxiWorkspace1 > Power BI datasets and select New Power BI dataset. Select Start.
  3. Select the SQLPOOL1 data source, select Continue.
  4. Select Download to download the .pbids file for your NYCTaxiWorkspace1SQLPOOL1.pbids file. Select Continue.
  5. Open the downloaded .pbids file. Power BI Desktop opens and automatically connects to SQLDB1 in your Azure Synapse workspace.
  6. If you see a dialog box appear called SQL Server database:
    1. Select Microsoft account.
    2. Select Sign in and sign in to your account.
    3. Select Connect.
  7. After the Navigator dialog box opens, check the PassengerCountStats table and select Load.
  8. After the Connection settings dialog box appears, select DirectQuery > OK.
  9. Select the Report button on the left side.
  10. Under Visualizations, select to the line chart icon to add a Line chart to your report.
    1. Under Fields, drag the PassengerCount column to Visualizations > Axis.
    2. Drag the SumTripDistance and AvgTripDistance columns to Visualizations > Values.
  11. On the Home tab, select Publish.
  12. Select Save to save your changes.
  13. Choose the file name PassengerAnalysis.pbix, and then select Save.
  14. In the Publish to Power BI window, under Select a destination, choose your NYCTaxiWorkspace1, and then select Select.
  15. Wait for publishing to finish.

Configure authentication for your dataset

  1. Open powerbi.microsoft.com and Sign in.
  2. On the left side, under Workspaces, select the NYCTaxiWorkspace1 workspace.
  3. Inside that workspace, locate a dataset called Passenger Analysis and a report called Passenger Analysis.
  4. Hover over the PassengerAnalysis dataset, select the ellipsis (...) button, and then select Settings.
  5. In Data source credentials, select Edit, set the Authentication method to OAuth2, and then select Sign in.

Edit a report in Synapse Studio

  1. Go back to Synapse Studio and select Close and refresh.
  2. Go to the Develop hub.
  3. To the right of the Power BI layer, ellipsis (...) button, and select Refresh to refresh the Power BI reports node.
  4. Under Power BI you should see:
    • In NYCTaxiWorkspace1 > Power BI datasets, a new dataset called PassengerAnalysis.
    • Under NYCTaxiWorkspace1 > Power BI reports, a new report called PassengerAnalysis.
  5. Select the PassengerAnalysis report. The report opens and you can edit it directly within Synapse Studio.

Next step

Monitor