Power BI 服务数据源Data sources for the Power BI service

数据是 Power BI 的核心。Data is at the heart of Power BI. 当研究数据、创建图表和仪表板,以及使用问答进行提问时,所看到上述全部可视化效果和答案实际上均是从数据集获得其基础数据的。Whenever you're exploring data, creating charts and dashboards, asking questions with Q&A, all of those visualizations and answers you see are really getting their underlying data from a dataset. 但是,数据集来自何处呢?But where does that dataset come from? 它来自数据源。Well, from a data source.

在本文中,我们将重温可以从 Power BI 服务连接到的不同类型的数据源。In this article, we're going to go over the different types of data sources you can connect to from the Power BI service. 请记住,你还可以从很多其他类型的数据源获取数据。Keep in-mind, there are many other types of data sources you can get data from, too. 但是,可能需要先使用 Power BI Desktop 或 Excel 的高级数据查询和建模功能才能使用这些数据源。But those might require first using Power BI Desktop or Excel's advanced data query and modeling features. 稍后我们将详细了解有关内容。We'll go into more about that later. 现在,让我们看一下可直接从 Power BI 服务站点连接到的不同类型的数据源。For now, let's look at the different types of data sources you can connect to right from your Power BI service site.

你可以通过单击我的工作区 > 获取数据,获取来自 Power BI 中的任何这些数据源的数据。You can get data from any of these data sources in Power BI by clicking My Workspace > Get Data.

文件Files

Excel (.xlsx, xlxm) Excel 的与众不同之处在于,工作薄可拥有你自行输入到工作表的数据,而你可通过使用 Power Query(Excel 2016 中的“获取和转换”)或 Power Pivot 从外部数据源查询和加载数据。Excel (.xlsx, xlxm) � Excel is unique in that a workbook can have both data you've entered into worksheets yourself, and you can query and load data from external data sources by using Power Query (Get & Transform in Excel 2016) or Power Pivot. 你可以导入工作表的表中的数据(数据必须在表中),或导入加载到数据模型中的数据。You can import data that is in tables in worksheets (the data must be in a table), or import data that is loaded into a data model. 若要了解详细信息,请参阅从 Excel 中获取数据To learn more, see Get data from Excel.

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, extend your data model with measures and relationships, and create reports. 你可以将你的 Power BI Desktop 文件导入 Power BI 站点。You can import your Power BI Desktop file into your Power BI site. Power BI Desktop 最适合于非常了解其数据源、数据查询和转换,以及数据建模概念的更高级的用户。Power BI Desktop is best for more advanced users who have a good understanding of their data sources, 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 can contain one or more values, each separated by a comma. 例如,一个包含名称和地址数据的 .csv 可以具有许多行,其中每行都有名字、姓氏、街道地址、市/县、州等的值。For example, a .csv containing name and address data can have a number of rows where each row has values for first name, last name, street address, city, state, and so on. 不能将数据导入 .csv 文件,但许多应用程序(如 Excel)可将简单的表格数据另存为 .csv 文件。You cannot 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 big difference, too. OneDrive for Business 提供了最大程度的灵活性以及与 Power BI 的集成。OneDrive for Business provides the greatest amount of flexibility and integration with Power BI. 可将文件保留在本地驱动器上,但是如果需要刷新数据,则还需执行一些步骤。If you keep your files on your local drive, that's ok, but if you need to refresh your data, a few extra steps are involved. 链接的文章中提供了更多详细信息。More details are provided in the linked articles.

内容包Content packs

内容包包含为你准备好的所有所需数据和报表。Content packs contain all of the data and reports you need already prepared for you. 在 Power BI 中,有两种类型的内容包;来自 Google Analytics、Marketo 或 Salesforce 等服务的内容包以及你的组织中的其他用户创建和共享的内容包。In Power BI, there are two types of content packs; those from services like Google Analytics, Marketo, or Salesforce, and those created and shared by other users in your organization.

服务 - 差不多有数十个具有适用于 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. 若要了解详细信息,请参阅连接到服务To learn more, see Connect to 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 Organizational content packs.

数据库Databases

云中的数据库 - 从 Power BI 服务中,你可以实时连接到 Azure SQL 数据库、Azure SQL 数据仓库、Azure HD Insight 上的 Spark,以及使用 DirectQuery 的 SQL Server Analysis Services。Databases in the Cloud � From the Power BI service, you can connect live to Azure SQL Database, Azure SQL Data Warehouse, Spark on Azure HD Insight, and SQL Server Analysis Services using DirectQuery. 从 Power BI 实时连接到这些数据库,这表示当连接到数据库(如 Azure SQL 数据库)时,你通过在 Power BI 中创建报表来浏览其数据,且每次你进行数据切片或将其他字段添加到可视化效果时,都直接查询数据库。Connections from Power BI to these databases are live, that is, when you've connected to say an Azure SQL Database, and you begin exploring its data by creating reports in Power BI, anytime you slice your data or add another field to a visualization, a query is made 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 是必需的。A Power BI Enterprise gateway is required. 如果不确定如何连接到你的组织的表格模型数据库,请联系你的管理员或 IT 部门。If you're unsure 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 Tabular data in Power BI.

对于你的组织中的其他类型的数据库,需要先使用 Power BI Desktop 或 Excel 连接到数据模型,并在数据模型中查询和加载数据。For other types of databases in your organization, you'll need to first 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 is created. 如果设置了计划刷新,Power BI 将使用来自文件的连接信息和你所配置的刷新设置,以直接连接到数据源,并查询更新。If you setup scheduled refresh, Power BI will use connection information from the file along with refresh settings you configure to connect directly to the datasource and query for updates. 然后,这些更新将加载到 Power BI 中的数据集。Those updates are then loaded into the dataset in Power BI. 若要了解详细信息,请参阅连接到 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 literally hundreds of different data sources you can use with Power BI. 但是,无论从何处获取数据,这些数据的格式必须满足如下要求:Power BI 服务可用其来创建报表和仪表板,以及通过问答回答问题等等。But regardless of where you get your data from, that data has to be in a format the Power BI service can use to create reports and dashboards, answer questions with Q & A, and so on.

一些数据源的数据已具有准备好用于 Power BI 服务的格式,例如 Google Analytics 和 Twilio 之类的服务提供商提供的内容包。Some data sources already have their data in a format ready for the Power BI service, like content packs from service providers like Google Analytics, and Twilio. SQL Server Analysis Services 表格模型数据库也准备就绪了。SQL Server Analysis Services Tabular model databases are ready, too. 并且可以实时连接到云中的数据库,如 Azure SQL 数据库和 HDInsight 上的 Spark。And you can 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 a data warehouse database on a server in your organization. 在 Power BI 服务中,你不能直接连接到该数据库并开始浏览其数据(除非它是表格模型数据库)。In the Power BI service, you cannot connect directly to that database and begin exploring its data (unless it is a tabular model database). 但是,你可以使用 Power BI Desktop 或 Excel 查询该物流数据,并将其加载到你稍后要保存为文件的数据模型中。You can, however, 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 is created.

你可能会想:但是,数据库上的物流数据每天都会改变。You're probably thinking �But that logistics data on that database changes every day. 我如何确保我在 Power BI 中的数据集已被刷新?来自 Power BI Desktop 或 Excel 文件的连接信息将随数据导入到数据集中。How do I make sure my dataset in Power BI is refreshed?� Connection information from the Power BI Desktop or Excel file is imported into the dataset along with the data. 如果设置了计划刷新或在数据集上执行手动刷新,Power BI 将使用来自数据集的连接信息和一些其他设置,直接连接到数据库、查询更新并将更新加载到数据集。If you setup scheduled refresh or do a manual refresh on the dataset, Power BI will use the connection information from the dataset, along with a couple other settings, to connect directly to the database, query for updates, and load those updates into the dataset. 可能需要 Power BI Gateway 以保护本地服务器和 Power BI 之间的任何数据传输。A Power BI gateway will likely be required to secure any data transfer between your on-premises server and Power BI. 报表和仪表板中的所有可视化效果都将自动刷新。Any visualizations in reports and dashboards are refreshed automatically.

可以看出,不能直接从 Power BI 服务连接到数据源并不意味着无法将该数据导入 Power BI。You see, just because you cannot connect to your data source right from the Power BI service doesn't mean you can't 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 use synonymously, but they really are two different things, albeit related.

使用“获取数据”连接到数据以及从内容包、文件导入数据或是连接到实时数据源时,会自动在 Power BI 中创建数据集A dataset is automatically created in Power BI when you use Get Data to 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, data source credentials, and in many cases, a sub-set of data copied from the data source. 大多数情况下,在报表和仪表板中创建可视化效果时,看到的就是数据集中的数据。In most cases, when you create visualizations in reports and dashboards, you're looking at data in the dataset.

数据源是指数据集中的数据的实际来源。A data source is where the data in a dataset really comes from. 例如,联机服务(如 Google Analytics 或 QuickBooks)、云中的数据库(如 Azure SQL 数据库)或者自己组织中的本地计算机或服务器上的数据库或文件。For example, an online service like Google Analytics or QuickBooks, a database in the cloud like Azure SQL Database, or a database or file on a local computer or server in your own organization.

数据刷新Data refresh

如果你将文件存储在本地驱动器或位于组织中的某个位置的驱动器上,可能需要 Power BI Gateway 以刷新 Power BI 中的数据集。If you save your files on your local drive, or a drive somewhere in your organization, a Power BI gateway might be required in-order to refresh the dataset in Power BI. 并且,进行刷新时,保存文件的计算机必须处于开机状态。And, the computer where the file is saved must be on when a refresh happens. 你还可以重新导入你的文件,或使用 Excel 或 Power BI Desktop 中的“发布”,但这些均不是自动的过程。You can also re-import your file, or use Publish from Excel or Power BI Desktop, but those are not automated processes.

如果你将文件保存在 OneDrive for Business 或 SharePoint - 团队网站上,然后连接到 Power BI,或将文件导入 Power BI,则你的数据集、报表和仪表板将始终保持最新。If you save your files on OneDrive for Business or SharePoint � Team Sites, and then connect to or import them into Power BI, 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, about once every hour, and check for updates. 如果发现任何更新,将自动刷新数据集和任何可视化效果。If any are found, the dataset and any visualizations are refreshed automatically.

来自服务的内容包将自动更新。Content packs from services are automatically updated. 在大多数情况下,一天自动更新一次。In most cases, once a day. 可以手动刷新,但是能否看到更新的数据将取决于服务提供商。You can manually refresh, but whether or not you'll see any updated data will depend on the service provider. 来自你的组织中的其他人的内容包将取决于所使用的数据源,以及创建内容包的人员如何设置刷新。Content packs from others in your organization will depend on the data sources used and how the person who created the content pack setup refresh.

Azure SQL 数据库、Azure SQL 数据仓库和 Azure HDInsight 上的 Spark 的独特之处在于,它们都是云中的数据源。Azure SQL Database, Azure SQL Data Warehouse, and Spark on Azure HDInsight are unique in that they are data sources in the Cloud. 因为 Power BI 服务也位于云中,Power BI 可使用 DirectQuery,实时地与它们进行连接。Because the Power BI service is also in the cloud, Power BI can connect to them live, using DirectQuery. 在 Power BI 中显示的内容始终是同步内容,无需设置刷新。What you see in Power BI is always in-sync and there's no need to setup refresh.

SQL Server Analysis Services 的独特之处在于,当从 Power BI 连接到它时,此连接类似于云中的 Azure 数据库的实时连接,但数据库本身位于你的组织中的服务器上。SQL Server Analysis Services is unique in that when you connect to it from Power BI, it's a live connection just like an Azure database in the cloud, but the database itself is on a server in your organization. 此类型的连接需要 Power BI Gateway,其通常由 IT 部门配置。This type of connection requires a Power BI gateway, which is usually configured by an IT department.

数据刷新是 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 checkout Data Refresh in Power BI.

注意事项和限制Considerations and Limitations

以下注意事项和限制适用于在 Power BI 服务中使用的所有数据源。For all data sources used in the Power BI service, the following considerations and limitations apply. 还有其他限制适用于特定的功能,但下面的列表适用于全面的 Power BI 服务:There are other limitations that apply to specific features, but the following list apply to the Power BI service overall:

  • 数据集大小限制Power BI 服务中每个数据集的大小限制为 1 GB。Dataset size limit - there is a 1 GB limit for each dataset in the Power BI service.
  • 行限制 - 数据集(在不使用 DirectQuery 的情况下)的最大行数为 20 亿,保留其中三个行(从而使最大可用行数为 1,999,999,997);在使用 DirectQuery 的情况下,最大行数为 100 万。Row limit - the maximum number of rows in your dataset (when not using DirectQuery) is 2 billion, with three of those rows reserved (resulting in a usable maximum of 1,999,999,997 rows); 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 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 included in the dataset, which means the maximum number of columns is 16,000 minus one for each table used in the dataset.