Azure SQL Data Warehouse with DirectQuery
Azure SQL Data Warehouse with DirectQuery allows you to create dynamic reports based on data and metrics you already have in Azure SQL Data Warehouse. With DirectQuery, queries are sent back to your Azure SQL Data Warehouse in real time as you explore the data. Real-time queries, combined with the scale of SQL Data Warehouse 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 SQL Data Warehouse.
When using the SQL Data Warehouse 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 SQL Data Warehouse. For the best experience to connect to your Azure SQL Data Warehouse 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 SQL Data Warehouse in the Power BI service is no longer available.
The easiest way to move between your SQL Data Warehouse 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 a SQL Data Warehouse 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 SQL Data Warehouse 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 SQL Data Warehouse, use DirectQuery.
For advanced options for the Azure SQL Data Warehouse 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 SQL Data Warehouse only has a presence in the Azure portal at this time.
If your Power BI tenant is in the same region as the Azure SQL Data Warehouse 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.
- About using DirectQuery in Power BI
- What is Power BI?
- Get Data for Power BI
- Azure SQL Data Warehouse
More questions? Try the Power BI Community