具有 DirectQuery 的 Azure SQL DatabaseAzure SQL Database with DirectQuery

了解如何直接連線至 Azure SQL Database 及建立使用即時資料的報表。Learn how you can connect directly to Azure SQL Database and create reports that use live data. 您可以將您的資料保留在來源,而不是在 Power BI 中。You can keep your data at the source and not in Power BI.

藉由 DirectQuery,當您瀏覽報表檢視中的資料時,會將查詢傳送至您的 Azure SQL Database。With DirectQuery, queries are sent back to your Azure SQL Database as you explore the data in the report view. 對於熟悉所連接資料庫與實體的使用者,會推薦這種做法。This experience is suggested for users who are familiar with the databases and entities they connect to.

注意:Notes:

  • 連接時請指定完整的伺服器名稱 (請參閱以下以取得詳細資料)Specify the fully qualified server name when connecting (see below for more details)
  • 請確定已將資料庫的防火牆規則設定為 [允許存取 Azure 服務]Ensure firewall rules for the database are configured to "Allow access to Azure services"
  • 例如選取資料行或加入篩選器的每一個動作,都會傳送查詢回資料庫Every action such as selecting a column or adding a filter will send a query back to the database
  • 圖格會每小時重新整理 (重新整理不需要排程)。Tiles are refreshed every hour (refresh does not need to be scheduled). 當您連接時,這可以在 [進階] 設定中調整。This can be adjusted in the Advanced settings when you connect.
  • 問與答不能用於 DirectQuery 資料集Q&A is not available for DirectQuery datasets
  • 不會自動挑選結構描述變更Schema changes afre not picked up automatically

隨著我們持續改善這些體驗,這些限制和備註可能會變更。These restrictions and notes may change as we continue to improve the experiences. 連接的步驟如下所述。The steps to connect are detailed below.

Power BI Desktop 和 DirectQueryPower BI Desktop and DirectQuery

您必須使用 Power BI Desktop,才能連線到使用 DirectQuery 的 Azure SQL Database。In order to connect to Azure SQL Database using DirectQuery, you will need to use Power BI Desktop. 這個方法提供額外的彈性和功能。This approach provides additional flexability and capabilities. 使用 Power BI Desktop 建立的報表可以發行至 Power BI 服務。Reports created using Power BI Desktop can then be published to the Power BI service. 您可以深入了解如何在 Power BI Desktop 中連線到 DirectQuery 的 Azure SQL DatabaseYou can learn more about how to connect to Azure SQL Database using DirectQuery within Power BI Desktop.

單一登入Single sign-on

在您將 Azure SQL DirectQuery 資料集發行至服務之後,您可以透過 Azure Active Directory (Azure AD) OAuth2 為終端使用者啟用單一登入 (SSO)。After you publish an Azure SQL DirectQuery dataset to the service, you can enable single sign-on (SSO) via Azure Active Directory (Azure AD) OAuth2 for your end users.

若要啟用 SSO,請移至資料集、開啟 [資料來源] 索引標籤,然後檢查 [SSO] 方塊。To enable SSO, go to settings for the dataset, open the Data Sources tab, and check the SSO box.

設定 Azure SQL DQ 對話方塊

若已啟用 SSO 選項,且您的使用者存取在資料來源上建立的報告,Power BI 會在對 Azure SQL 資料庫的查詢中傳送其已驗證的 Azure AD 認證。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. 這可讓 Power BI 遵從在資料來源層級所設定的安全性設定。This enables Power BI to respect the security settings that are configured at the data source level.

SSO 選項會在使用此資料來源的所有資料集中生效。The SSO option takes affect across all datasets that use this data source. 它不會影響用於匯入案例的驗證方法。It does not affect the authentication method used for import scenarios.

尋找參數值Finding Parameter Values

在 Azure 入口網站中可以找到您完整的伺服器名稱和資料庫名稱。Your fully qualified server name and database name can be found in the Azure Portal.

後續步驟Next steps

在 Power BI Desktop 中使用 DirectQueryUse DirectQuery in Power BI Desktop
開始使用 Power BIGet started with Power BI
取得 Power BI 的資料Get Data for Power BI
有其他問題嗎?More questions? 試試 Power BI 社群Try the Power BI Community