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. In addition, the Build dashboards + reports link allows users to create Power BI reports using their Synapse Analytics.
When using the Azure Synapse AnalyticsAzure Synapse Analytics connector:
- Specify the fully qualified server name when connecting (see below for details)
- 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 refresh does not need to be scheduled. Refresh can be adjusted 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 may change as we continue to improve the experience. The steps to connect are detailed below.
Build dashboards and reports in Power BI
We have been improving our connectivity to Azure Synapse Analytics. For the best experience to connect to your Azure Synapse Analytics data source, use Power BI Desktop. Once 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. You can use the Build dashboards + reports button within the Azure portal.
To get started, download and install Power BI Desktop. See the get Power BI Desktop article for information about downloading and installing, or go directly to the next step.
You can also click the Build dashboards + reports link to download Power BI Desktop.
Connecting through Power BI Desktop
You can connect to an Azure Synapse Analytics using the Get data button in Power BI Desktop.
Select the Get data button from the Home menu.
Select More... to see all available data sources. From the window that appears, select Azure from the left pane, then select Azure Synapse Analytics from the list of available connectors in the right pane.
In the window that appears, input your Server and optionally state 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.
For advanced options for the Azure Synapse Analytics connection, select the down arrow beside Advanced options to display additional options for your connection.
The next section describes how to find parameter values for your connection.
Finding Parameter Values
Your fully qualified server name and database name can be found in the Azure portal. Note that Azure Synapse Analytics only has a presence in the Azure portal at this time.
If your Power BI tenant is in the same region as the Azure Synapse Analytics there will be no egress charges. You can find where your Power BI tenant is located using these instructions.
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.
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.
For SSO to work properly, the dataset must be on the same tenant as the Azure SQL resource.
More questions? Try the Power BI Community