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. Power Query 可通过 Excel 2016 中的“获取和转换”或 Power Pivot 获取 。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 for Business 提供了最大程度的灵活性以及与 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 数据库Azure 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 数据库。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 网关。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 数据库和 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 网关来保护本地服务器和 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 数据库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 网关,以便可以在 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 for Business 或 SharePoint - 团队网站,则随后可连接到 Power BI,或将文件导入 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 数据库、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. 此类连接需要 IT 部门配置的 Power BI 网关。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 each dataset in the Power BI service.
  • 行限制 - 如果不使用 DirectQuery,数据集中的最大行数为 20 亿行。Row limit - The maximum number of rows in your dataset when not using DirectQuery is 2 billion. 其中 3 行为保留行,因此最多可以使用 1,999,999,997 行。Three of those rows are reserved so that results in a usable maximum of 1,999,999,997 rows. 如果使用 DirectQuery,最大行数为 100 万行。The maximum number of rows when using DirectQuery is 1 million rows.
  • 列限制 - 数据集中允许的最大列数为 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 减去 1。Power BI uses an internal row number column per table in the dataset, which means the maximum number of columns is 16,000 minus one for each table in the dataset.