Edit

Share via

Azure Synapse Analytics (formerly SQL Data Warehouse) with DirectQuery

  • Article

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 semantic models.
  • 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 process described in the Power Query article about Azure SQL Data Warehouse.

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 semantic model to the service, you can enable single sign-on (SSO) using Microsoft Entra ID OAuth2 for your end users.

To enable SSO, go to settings for the semantic model, 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 Microsoft Entra 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 semantic models that use this data source. It does not affect the authentication method used for import scenarios.

Note

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

Related content

More questions? Ask the Power BI Community