Power BI 服務的資料來源Data sources for the Power BI service

資料是 Power BI 的核心。Data is at the heart of Power BI. 假設您正在探索資料。Let's say you're exploring data. 您可能會先建立圖表與儀表板,或利用 [問與答] 提出問題,以執行上述作業。You may do that by creating charts and dashboards, or asking questions with Q&A. 系統顯示的視覺效果和解答會從資料集擷取其基礎資料。The visualizations and answers you see are getting their underlying data from a dataset. 但是,該資料集又來自何處?But where does that dataset come from? 資料集是由資料來源而來。Well, it comes from a data source.

本文將探討可以透過 Power BI 服務連接的資料來源類型。In this article, we're going to cover the data source types you can connect to from the Power BI service. 請注意,您還可以從許多其他資料來源類型取得資料。Keep in mind that there are many other types of data sources you can get data from, too. 如果您選擇這些資料來源,您可能需要先使用 Power BI Desktop 或 Excel 的進階資料查詢與模型化功能。If you choose these data sources, you may need to use Power BI Desktop or Excel's advanced data query and modeling features first. 我們稍後會進一步討論這些選項的詳細資訊。We'll go into more about those options later. 現在,讓我們看看您可以直接從 Power BI 服務網站取得哪些不同資料來源類型。For now, let's look at the different types of data sources available right from your Power BI service site.

您可以選取頁面左下角的 [取得資料] ,以透過 Power BI 的任何資料來源取得資料。You can get data from any of the data sources in Power BI by selecting Get Data in the bottom-left corner of the page.

選取 [取得資料] ,您即可選擇要存取的資料After you select Get Data, you can choose the data you want to access

探索內容Discover content

[探索內容] 區段包含您需要的所有現有資料與報表。The Discover content section contains all the data and reports you need already prepared for you. Power BI 提供下列兩種類型的內容套件:組織和服務。In Power BI, there are two types of content packs: Organizational and Services.

組織:如果您和組織中的其他使用者都有 Power BI Pro 帳戶,即可建立、共用及使用內容套件。Organizational: If you and other users in your organization have a Power BI Pro account, you can create, share, and use content packs. 如需深入了解,請參閱組織內容套件簡介To learn more, see Intro to organizational content packs.

服務:適用於 Power BI 的內容套件實際上可提供數十種服務,且不斷增加中。Services: There are literally dozens of services with content packs for Power BI, and more are being added all the time. 大部分服務都要求您具有帳戶。Most services require you to have an account. 如需深入了解,請參閱使用 Power BI 連接到所用服務To learn more, see Connect to services you use with Power BI.

建立新內容Create new content

[建立新內容] 區段包含用來自行建立和匯入內容的選項。The Create new content section contains options for creating and importing content yourself. 在 Power BI 中,您可以使用下列兩種方式來建立或匯入自己的內容:檔案和資料庫。In Power BI, there are two ways to create or import your own content: Files and Databases.

檔案Files

Excel ( .xlsx, .xlsm) - 在 Excel 中,活頁簿可能包含不同資料類型。Excel (.xlsx, .xlsm) - In Excel, a workbook may include different data types. 例如,它可能包含您自行輸入工作表的資料,For example, it can include data you've entered into worksheets yourself. 也可能包含您使用 Power Query 從外部資料來源查詢及載入的資料。It can also include data that you've queried and loaded from external data sources by using Power Query. 您可以透過 Excel 2016 或 Power Pivot 中的 [取得與轉換資料] 來使用 Power Query。Power Query is available through Get & Transform in Excel 2016 or Power Pivot. 您可以從工作表中的資料表匯入資料,或從資料模型匯入資料。You may import data from tables in worksheets, or import data from a data model. 如需深入了解,請參閱針對 Power BI 從檔案取得資料To learn more, see Get data from files for Power BI.

Power BI Desktop ( .pbix) - 您可以使用 Power BI Desktop,從外部資料來源查詢及載入資料,並建立報表。Power BI Desktop (.pbix) - You can use Power BI Desktop to query and load data from external data sources and create reports. 您也可以使用量值與關聯性來擴充您的資料模型,或將 Power BI Desktop 檔案匯入 Power BI 網站。You can also extend your data model with measures and relationships, or import your Power BI Desktop file into your Power BI site. Power BI Desktop 最適合更進階的使用者。Power BI Desktop is best for more advanced users. 這些使用者通常非常了解自己的資料來源,Typically these users are ones who have a good understanding of their data sources. 也充分掌握資料查詢與轉換以及資料模型化概念。They also understand data query and transformation, and data modeling concepts. 如需深入了解,請參閱連接至 Power BI Desktop 中的資料To learn more, see Connect to data in Power BI Desktop.

逗點分隔值 ( .csv) - 檔案是具有多列資料的簡單文字檔。Comma-Separated Value (.csv) - Files are simple text files with rows of data. 每個資料列可包含一或多個值,並以逗號分隔每個值。Each row may contain one or more values, each separated by a comma. 例如,包含名稱與地址資料的 .csv 可能會有多個資料列。For example, a .csv containing name and address data might have many rows. 每個資料列可能會有名字、姓氏、街道地址、縣/市、州/省等的值。Each row may have values for first name, last name, street address, city, state, and so on. 您無法將資料匯入 .csv 檔案,但 Excel 等許多應用程式可以將簡單的資料表資料另存為 .csv 檔案。You can't import data into a .csv file, but many applications, like Excel, can save simple table data as a .csv file.

針對 XML 資料表 ( .xml) 或文字檔 ( .txt) 等其他檔案類型,您可以使用 [取得與轉換] ,以查詢資料、轉換資料並將其載入 Excel 或 Power BI Desktop 檔案。For other file types, like XML Table (.xml) or text (.txt) files, you can use Get & Transform to query, transform, and load that data into an Excel or Power BI Desktop file first. 然後再將 Excel 或 Power BI Desktop 檔案匯入 Power BI。You can then import the Excel or Power BI Desktop file into Power BI.

您儲存檔案的位置也會形成顯著的差異。Where you store your files makes a significant difference, too. 商務用 OneDrive 提供最大的彈性以及與 Power BI 的整合。OneDrive for Business provides the greatest amount of flexibility and integration with Power BI. 您可以將檔案保留在本機磁碟機上,It's OK to keep your files on your local drive. 不過如果您需要重新整理資料,就必須採取一些額外的步驟。Though if you need to refresh your data, there are a few extra steps. 如需詳細資訊,請參閱以下文章連結。For more information, see the article links below.

資料庫Databases

雲端資料庫 - 您可以透過 Power BI 服務即時連接下列項目:Databases in the Cloud - From the Power BI service, you can connect live to:

  • Azure SQL DatabaseAzure SQL Database
  • Azure SQL 資料倉儲Azure SQL Data Warehouse
  • Azure HDInsight 上的 SparkSpark on Azure HDInsight

Power BI 與這些資料庫的連線都是即時的。Connections from Power BI to these databases are live. 假設您要連接 Azure SQL Database,Let's say you connect to an Azure SQL Database. 因此您在 Power BI 中建立報表以開始探索其資料。You then begin exploring its data by creating reports in Power BI. 每當您將資料配量,或將其他欄位新增至視覺效果時,Power BI 即會直接對資料庫進行查詢。Whenever you slice your data or add another field to a visualization, Power BI makes a query right to the database. 如需深入了解,請參閱 Azure 和 Power BITo learn more, see Azure and Power BI.

內部部署資料庫 - 您可以從 Power BI 服務直接連線到 SQL Server Analysis Services 表格式模型資料庫。Databases on-premises - From the Power BI service, you can connect directly to SQL Server Analysis Services Tabular model databases. 若要這樣做,您必須具備 Power BI Enterprise Gateway。To do so, you'll need a Power BI Enterprise gateway. 如果您不清楚如何連接到組織的表格式模型資料庫,請洽詢您的系統管理員或 IT 部門。If you're not clear on how to connect to your organization's tabular model database, check with your administrator or IT department. 如需深入了解,請參閱 Power BI 中的 SQL Server Analysis Services 即時資料To learn more, see SQL Server Analysis Services live data in Power BI.

針對組織中的其他資料庫類型,您必須使用 Power BI Desktop 或 Excel 來連接及查詢資料,並將該資料載入資料模型。For other database types in your organization, you'll need to use Power BI Desktop or Excel to connect to, query, and load data into a data model. 然後,您即可將檔案匯入資料集所在的 Power BI。You can then import your file into Power BI where a dataset exists. 如果您設定排程重新整理,Power BI 會使用檔案中的這項設定與連線資訊,直接連接到資料來源並查詢更新。If you configure a scheduled refresh, Power BI will use that configuration and connection information from the file to connect directly to the datasource and query for updates. 接著,Power BI 即會將這些更新載入資料集。Power BI then loads those updates into the dataset. 如需深入了解,請參閱連接至 Power BI Desktop 中的資料To learn more, see Connect to data in Power BI Desktop.

如果我的資料來自不同的來源,該怎麼辦?What if my data comes from a different source?

Power BI 可以搭配使用數百種不同的資料來源。There are hundreds of different data sources you can use with Power BI. 無論您由何處取得資料,該資料之格式都必須為 Power BI 服務可取用的格式。Wherever you get your data from, that data has to be in a format consumable by the Power BI service. 藉由這些可取用的資料,Power BI 服務即可建立報表和儀表板,並透過 [問與答] 來回答問題等。With consumable data, the Power BI service can create reports and dashboards, answer questions with Q&A, and so on.

有些資料來源已針對 Power BI 服務進行資料格式化。Some data sources already have data formatted for the Power BI service. 這些來源包括來自 Google Analytics 和 Twilio 等服務提供者的內容套件。These sources are like content packs from service providers like Google Analytics and Twilio. SQL Server Analysis Services 表格式模型資料庫也可供使用。SQL Server Analysis Services Tabular model databases are ready for use, too. 您也可以即時連接到雲端資料庫,例如 Azure SQL Database 與 HDInsight 上的 Spark。You can also connect live to databases in the cloud like Azure SQL Database and Spark on HDInsight.

在其他情況下,您可能必須查詢所需的資料並將該資料載入檔案。In other cases, it might be necessary to query and load the data you want into a file. 例如,假設您擁有組織的物流資料,For example, let's say you have logistics data in your organization. 並將該資料儲存在伺服器的資料倉儲資料庫中。You store that data in a data warehouse database on a server. 在 Power BI 服務中,您無法連接到該資料庫並開始探索其資料 (除非是表格式模型資料庫)。In the Power BI service, you can't connect to that database and begin exploring its data unless it's a tabular model database. 不過,您可以使用 Power BI Desktop 或 Excel 查詢物流資料並將該資料載入資料模型,接著儲存為檔案。But, you can use Power BI Desktop or Excel to query and load that logistics data into a data model you then save as a file. 然後,您即可將該檔案匯入資料集所在的 Power BI。You can then import that file into Power BI where a dataset exists.

您可能會問:「但是,該資料庫上的物流資料每天都在變。You're probably thinking, "But the logistics data on that database changes every day. 該如何重新整理我的 Power BI 資料集?」How do I refresh my Power BI dataset?" 當您將資料匯入資料集時,您也會從 Power BI Desktop 或 Excel 檔案匯入連線資訊。When you import the data into the dataset, you also import the connection information from either the Power BI Desktop or the Excel file.

假設您設定排程的重新整理,或手動重新整理資料集。Let's say you configure a scheduled refresh or do a manual refresh on the dataset. Power BI 會使用資料集的連線資訊與幾個其他設定,直接連接資料庫。Power BI uses the connection information from the dataset, along with a couple of other settings, to connect directly to the database. 接著,它會更新查詢並將這些更新載入資料集。It then queries for updates and loads those updates into the dataset. 請注意,您可能需要使用 Power BI Gateway 來保護內部部署伺服器與 Power BI 之間的任何資料傳輸。As a side note, you likely will need a Power BI gateway to secure any data transfer between your on-premises server and Power BI. 完成傳輸時,系統會自動重新整理報表和儀表板中的任何視覺效果。When the transfer is complete, any visualizations in reports and dashboards refresh automatically.

您會發現,即使無法直接從 Power BI 服務連接資料來源,但您仍可以將該資料匯入 Power BI。You see, even though you can't connect to your data source directly from the Power BI service you can still get that data into Power BI. 只需要一些額外步驟及 IT 部門的一些協助,就能辦到。It just might take a few more steps and maybe some help from your IT department. 如需深入了解,請參閱 Power BI Desktop 中的資料來源See Data sources in Power BI Desktop to learn more.

其他一些詳細資料Some more details

Power BI 中經常使用資料集與資料來源等詞彙。You'll see the terms dataset and data source used a lot in Power BI. 這兩個詞彙通常會用來表示相同意義,They're often used synonymously. 但實際上卻是兩個相關的不同項目。But they really are two different things, although they're related.

當您使用 [取得資料] 時,即會自動在 Power BI 中建立資料集You create a dataset automatically in Power BI when you use Get Data. [取得資料] 可讓您從內容套件連接資料、檔案並加以匯入;或者,您可以連接即時資料來源。With Get Data, you connect to and import data from a content pack, file, or you connect to a live data source. 資料集包含資料來源和資料來源認證的相關資訊。A dataset contains information about the data source and data-source credentials. 在許多情況下,它也會包含從資料來源複製的資料子集。In many cases, it also includes a subset of data copied from the data source. 當您在報表與儀表板中建立視覺效果時,所看到的資料通常就是來自資料集。When you create visualizations in reports and dashboards, you're often looking at data in the dataset.

資料來源是指資料集資料的實際來源處。A data source is where the data in a dataset comes from. 例如,資料可能來自:For example, the data could come from:

  • Google Analytics 或 QuickBooks 等線上服務An online service like Google Analytics or QuickBooks
  • Azure SQL Database 等雲端資料庫A database in the cloud like Azure SQL Database
  • 本機電腦或您組織伺服器上的資料庫或檔案A database or file on a local computer or server in your own organization

資料重新整理Data refresh

您可能將檔案儲存在本機磁碟機或組織的某個磁碟機中。Maybe you save your files on your local drive or a drive somewhere in your organization. 您可能需要 Power BI Gateway,以便重新整理 Power BI 中的資料集。You may need a Power BI gateway so that you can refresh the dataset in Power BI. 進行重新整理作業時,儲存檔案的電腦必須已開機。The computer that stores the file needs to be on when a refresh happens. 您可以重新匯入檔案,或使用 [從 Excel 發佈] 或 [從 Power BI Desktop 發佈],但這些選項不是自動化程序。You can reimport your file or use Publish from Excel or Power BI Desktop, but those options aren't automated processes.

如果您將檔案儲存在商務用 OneDrive 或 SharePoint - 小組網站,即可將其連線或匯入 Power BI。If you save your files on OneDrive for Business or SharePoint - Team Sites, you can then connect to or import them into Power BI. 這樣一來,您的資料集、報表與儀表板會一律保持在最新狀態。Then, your dataset, reports, and dashboard will always be up-to-date. 由於 OneDrive 與 Power BI 都在雲端上,所以 Power BI 可以直接連接已儲存的檔案,Because both OneDrive and Power BI are in the cloud, Power BI can connect directly to your saved file. 並且約每小時連線一次,同時檢查更新。It connects about once every hour and checks for updates. 如果資料集和任何視覺效果有任何更新,即會自動重新整理。The dataset and any visualizations refresh automatically if there are any updates.

來自服務的內容套件會自動更新。Content packs from services are automatically updated. 在大部分情況下,他們會一天更新一次。In most cases, they're updated once a day. 您可以手動重新整理,但是否顯示任何更新的資料則取決於服務提供者。You can manually refresh, but if you see any updated data will depend on the service provider. 組織人員提供的內容套件更新取決於所使用資料來源。Updates to content packs from people in your organization will depend on the data sources used. 這些更新也取決於建立內容套件的人員如何設定重新整理而定。They'll also depend on how the person who created the content pack configured the refresh.

Azure SQL Database、Azure SQL 資料倉儲與 Azure HDInsight 上的 Spark 是雲端上的資料來源。Azure SQL Database, Azure SQL Data Warehouse, and Spark on Azure HDInsight are data sources in the Cloud. 由於 Power BI 服務也是在雲端中,所以 Power BI 可以使用 DirectQuery 即時連接到這些資料來源。The Power BI service is also in the cloud so Power BI can connect to those data sources live, using DirectQuery. 您在 Power BI 中看到的內容一律會同步處理,而不需要設定排程的重新整理。What you see in Power BI is always in sync and there's no need to set up a scheduled refresh.

當您從 Power BI 連接到 SQL Server Analysis Services 時,就如同在雲端中 Azure 資料庫的即時連線。When you connect to SQL Server Analysis Services from Power BI, it's a live connection just like an Azure database in the cloud. 差別在於,資料庫本身是在您組織的伺服器上。The difference is the database itself is on a server in your organization. 這種連線需要 Power BI Gateway,並由 IT 部門進行設定。This type of connection requires a Power BI gateway, which an IT department configures.

資料重新整理是 Power BI 很重要的一部分,不是本文所能深入探討。Data refresh is a super important part of Power BI, and much too deep to cover here. 如果您想要徹底了解,請務必查看 Power BI 的資料重新整理If you want to get a thorough understanding, be sure to check out Data Refresh in Power BI.

考量與限制Considerations and limitations

針對 Power BI 服務中使用的所有資料來源,請注意下列限制。For all data sources used in the Power BI service, consider the following limitations. 特定功能有其他適用限制,但下列清單適用於整個 Power BI 服務︰There are other limitations that apply to specific features, but the following list applies to the full Power BI service:

  • 資料集大小限制 - Power BI 服務之共用容量中儲存的資料集限制為 1 GB。Dataset size limit - There's a 1-GB limit for datasets stored in Shared capacities in the Power BI service. 若您需要較大的資料集,您可以使用 Power BI PremiumIf you need larger datasets, you can use Power BI Premium.

  • 資料行中的相異值 - 快取 Power BI 資料集中的資料 (有時稱為「匯入」模式) 時,資料行中可以儲存之相異值的限制為 1,999,999,997 個。Distinct values in a column - When caching data in a Power BI dataset (sometimes called 'Import' mode), there is a 1,999,999,997 limit on the number of distinct values that can be stored in a column.

  • 資料列限制 - 使用 DirectQuery 時,Power BI 會在傳送到您底層資料來源的查詢結果上加諸限制。Row limit - When using DirectQuery, Power BI imposes a limit on the query results that are sent to your underlying data source. 若傳送至資料來源的查詢傳回超過 1 百萬列,您會看到錯誤,而且查詢會失敗。If the query sent to the data source returns more than one million rows, you see an error and the query fails. 您的底層資料仍然可以包含超過 1 百萬列。Your underlying data can still contain more than one million rows. 因為大部分的報表都會將資料彙總成較小的結果集,您不太可能會遇到此限制。You're unlikely to run into this limit as most reports aggregate the data into smaller sets of results.

  • 資料行限制 - 資料集允許的資料行數目上限;資料集中所有資料表加起來是 16,000 個資料行。Column limit - The maximum number of columns allowed in a dataset, across all tables in the dataset, is 16,000 columns. 此限制適用於 Power BI 服務以及 Power BI Desktop 中使用的資料集。This limit applies to the Power BI service and to datasets used in Power BI Desktop. Power BI 會使用此方式追蹤資料集中的資料行與資料表數目,這表示資料集中每個資料表的資料行數目上限是 16,000 減一。Power BI tracks the number of columns and tables in the dataset in this way, which means the maximum number of columns is 16,000 minus one for each table in the dataset.