Tutorial: Use serverless SQL pool with Power BI Desktop & create a report

Neste tutorial, você aprenderá a:

Pré-requisitos

Para concluir este tutorial, precisa dos seguintes pré-requisitos:

• Power BI Desktop - needed to visualize the data and create a report.
• Azure Synapse workspace - needed to create database, external data source, and view.

Opcional:

• A SQL query tool, such as Azure Data Studio, or SQL Server Management Studio (SSMS).

Values for the following parameters:

1 - Create database

For the demo environment, create your own demo database. You use this database to view metadata, not to store actual data.

Create the demo database (and drop an existing database if necessary) by running the following Transact-SQL (T-SQL) script:

-- Drop database if it exists
DROP DATABASE IF EXISTS Demo
GO

-- Create new database
CREATE DATABASE [Demo];
GO

2 - Create data source

A data source is necessary for the serverless SQL pool service to access files in storage. Create the data source for a storage account that is located in the same region as your endpoint. Although serverless SQL pool can access storage accounts from different regions, having the storage and endpoint in the same region provides better performance.

Create the data source by running the following Transact-SQL (T-SQL) script:

-- There is no credential in data source. We are using public storage account which doesn't need a secret.
CREATE EXTERNAL DATA SOURCE AzureOpenData
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/')

3 - Prepare view

Create the view based on the external demo data for Power BI to consume by running the following Transact-SQL (T-SQL) script:

Create the view usPopulationView inside the database Demo with the following query:

DROP VIEW IF EXISTS usPopulationView;
GO

CREATE VIEW usPopulationView AS
SELECT
    *
FROM
    OPENROWSET(
        BULK 'censusdatacontainer/release/us_population_county/year=20*/*.parquet',
        DATA_SOURCE = 'AzureOpenData',
        FORMAT='PARQUET'
    ) AS uspv;

The demo data contains the following data sets:

US population by gender and race for each US county sourced from 2000 and 2010 Decennial Census in parquet format.

4 - Create Power BI report

Create the report for Power BI Desktop using the following steps:

1. Open the Power BI Desktop application and select Get data.

   

2. Select Azure>Azure SQL Database.

   

3. Type the name of the server where the database is located in the Server field, and then type Demo in the database name. Select the Import option and then select OK.

   

4. Select preferred authentication method:

   • Example for AAD

     

   • Example for SQL Login - Type your User name and password.

     

5. Select the view usPopulationView, and then select Load.

   

6. Wait for the operation to complete, and then a pop-up will appear stating There are pending changes in your queries that haven't been applied. Selecione Aplicar alterações.

   

7. Wait for the Apply query changes dialog box to disappear, which may take a few minutes.

   

8. Once the load completes, select the following columns in this order to create the report:

   • countyName
   • population
   • stateName

   

Limpeza de recursos

Once you're done using this report, delete the resources with the following steps:

1. Delete the credential for the storage account

   DROP EXTERNAL DATA SOURCE AzureOpenData
   

2. Delete the view

   DROP VIEW usPopulationView;
   

3. Drop the database

   DROP DATABASE Demo;
   

Próximos passos

Advance to the Query storage files to learn how to query storage files using Synapse SQL.