Dela via

Connect to serverless SQL pool with Power BI Professional

In this tutorial, we will go through the steps for connecting Power BI desktop to serverless SQL pool.

Förutsättningar

You need the following tools to issue queries:

  • SQL client of your choice:

    • Azure Data Studio
    • SQL Server Management Studio

  • Power BI desktop installed

Parameters:

Parameter Beskrivning
Tjänstslutpunktsadress för serverlös SQL-pool Will be used as server name
Tjänstslutpunktsregion för serverlös SQL-pool Will be used to determine what storage will we use in samples
Användarnamn och lösenord för slutpunktsåtkomst Will be used to access endpoint
Databas som du använder för att skapa vyer This database will be used as starting point in samples

Första installationen

There are two steps prior to using samples:

  1. Create database for your views
  2. Create credentials to be used by serverless SQL pool to access files in storage

Skapa databas

For this getting started article, you should create your own database to utilize as a demo. A database is needed for views creation. You'll use this database in some of the sample queries within this documentation.

Anmärkning

Databases are used only for viewing metadata, not for actual data.

Write down the database name you're using, you'll need it later on.

DROP DATABASE IF EXISTS demo;

Skapa autentiseringsuppgifter

We need to create credentials before you can run queries. This credential will be used by serverless SQL pool service to access files in storage.

Anmärkning

You need to create credentials for storage account access. Although serverless SQL pool can access storage from different regions, having storage and Azure Synapse workspace in the same region will provide a better performance experience.

Code snippet on how to create credentials for Census data containers, run:

IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer];
GO

-- Create credentials for Census Data container which resides in an azure open data storage account
-- There is no secret. We are using public storage account which doesn't need secret
CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = '';
GO

Create a Power BI desktop report

Open the Power BI desktop application and select the Get data option.

Öppna Power BI Desktop-programmet och välj hämta data.

Step 1: Select data source

Select Azure in the menu and then Azure SQL Database. Välj datakälla.

Step 2: Select database

Write the URL for the database and the name of the database where the view resides. Välj databas på slutpunkten.

Nästa steg

Advance to Query storage files to learn how to connect to serverless SQL pool using Azure Data Studio.