Azure Synapse Analytics (formerly SQL Data Warehouse) with DirectQuery

Azure Synapse Analytics (formerly SQL Data Warehouse) with DirectQuery allows you to create dynamic reports based on data and metrics you already have in Azure Synapse Analytics. With DirectQuery, queries are sent back to your Azure Synapse Analytics in real time as you explore the data. Real-time queries, combined with the scale of Synapse Analytics enables users to create dynamic reports in minutes against terabytes of data.

When you use the Azure Synapse Analytics connector:

  • Specify the fully qualified server name when you connect (see details later in this article).
  • Ensure firewall rules for the server are configured to "Allow access to Azure services".
  • Every action such as selecting a column or adding a filter will directly query the data warehouse.
  • Tiles are set to refresh approximately every 15 minutes and you don't need to schedule a refresh. You can adjust refresh in the Advanced settings when you connect.
  • Q&A isn't available for DirectQuery datasets.
  • Schema changes aren't picked up automatically.

These restrictions and notes can change as we continue to improve the experience. Steps to connect are in the next section.

Build dashboards and reports in Power BI

Important

We continually improve connectivity to Azure Synapse Analytics. For the best experience to connect to your Azure Synapse Analytics data source, use Power BI Desktop. After you've built your model and report, you can publish it to the Power BI service. The previously available direct connector for Azure Synapse Analytics in the Power BI service is no longer available.

The easiest way to move between your Synapse Analytics and Power BI is to create reports in Power BI Desktop. To get started, download and install Power BI Desktop.

Connect through Power BI Desktop

You can connect to an Azure Synapse Analytics using the Get data button in Power BI Desktop.

  1. Select the Get data button from the Home menu, and then choose Azure.

    Screenshot of the get data menu with get data, Azure, Azure Synapse Analytics SQL, and connect highlighted.

  2. Select Azure Synapse Analytics, choose Connect.

  3. In the window that appears, enter your Server and optionally the Database to which you want to connect. You can also select your data connectivity mode: Import or DirectQuery. For real-time access to information in your Azure Synapse Analytics, use DirectQuery.

    Screenshot of the SQL Server database dialog.

  4. For advanced options for the Azure Synapse Analytics connection, select the arrow beside Advanced options to display more options for your connection.

    Screenshot of the SQL Server database dialog with advanced options highlighted.

The next section describes how to find parameter values for your connection.

Find Parameter Values

Your fully qualified server name and database name can be found in the Azure portal. Azure Synapse Analytics only has a presence in the Azure portal at this time.

Screenshot of the Azure portal with SQK demo data highlighted.

Note

If your Power BI tenant is in the same region as the Azure Synapse Analytics there will be no egress charges. To find where your Power BI tenant is located, see Find the default region for your organization.

Single sign-on

After you publish an Azure SQL DirectQuery dataset to the service, you can enable single sign-on (SSO) using Azure Active Directory (Azure AD) OAuth2 for your end users.

To enable SSO, go to settings for the dataset, open the Data Sources tab, and check the SSO box.

Configure Azure SQL DQ dialog box

When the SSO option is enabled and your users access reports built atop the data source, Power BI sends their authenticated Azure AD credentials in the queries to the Azure SQL database or data warehouse. This option enables Power BI to respect the security settings that are configured at the data source level.

The SSO option takes affect across all datasets that use this data source. It does not affect the authentication method used for import scenarios.

Note

For SSO to work properly, the dataset must be on the same tenant as the Azure SQL resource.

Next steps

More questions? Ask the Power BI Community