在 Power BI Desktop 中连接到 Azure 使用见解数据Connect to Azure Consumption Insights data in Power BI desktop

可以使用 Power BI Desktop 连接到 Azure,并获取有关组织的 Azure 服务使用情况的深度数据。You can use Power BI desktop to connect to Azure and get in-depth data about your organization's Azure service usage. 可以使用这些数据创建自定义报表和度量值,从而更好地了解和分析 Azure 支出。With this data, you can create custom reports and measures to better understand and analyze your Azure spend.

备注

对 Microsoft Azure 使用见解 (Beta) 的支持有限。There is limited support for the Microsoft Azure Consumption Insights (Beta). 对于新功能,请使用适用于 Power BI 的 Azure 成本管理连接器For new functionality, use the Azure Cost Management connector for Power BI.

使用 Azure 使用见解连接Connect with Azure Consumption Insights

通过 Azure 使用见解可连接到 Azure 企业协议计费帐户。Azure Consumption Insights allows you to connect to Azure Enterprise Agreement billing accounts.

本部分介绍如何使用 Azure 企业连接器获取需要迁移的数据。In this section, you learn how to get the data you need migrate using the Azure Enterprise Connector. 还可以在 ACI(Azure 使用见解)API 中找到可用的使用情况详细信息列映射 。You'll also find a usage details columns mapping available in the ACI (Azure Consumption Insights) API.

要成功使用 Azure 使用见解连接器,需要能够访问 Azure 门户企业功能 。To successfully use the Azure Consumption Insights connector, you need access to the Azure portal Enterprise features.

要使用 Power BI Desktop 中的 Azure 使用见解连接器,请执行以下操作 :To use the Azure Consumption Insights connector in Power BI Desktop:

  1. 从“开始”功能区选择“获取数据” 。From the Home ribbon, select Get Data.

  2. 从左侧的类别中选择“联机服务” 。From the categories on the left, select Online Services.

  3. 选择“Microsoft Azure 使用见解 (Beta)” 。Select Microsoft Azure Consumption Insights (Beta).

  4. 选择“连接” 。Select Connect.

    在出现的对话框中,输入你的“Azure 注册号” 。In the dialog that appears, enter your Azure Enrollment Number.

此版本的连接器仅支持来自 https://ea.azure.com 的企业合约。This connector version only supports enterprise enrollments from https://ea.azure.com. 目前尚不支持中国合约。China enrollments aren't currently supported.

接下来,提供“访问密钥” 进行连接。Next, provide your Access key to connect.

提供“访问密钥”并选择“连接”后,将出现“导航”窗口并显示九个可用的表 :Once you provide your Access key and select Connect, a Navigator window appears and shows nine available tables:

Table 说明Description
预算Budgets 提供预算详细信息,以查看针对现有预算目标的实际成本或使用情况。Budget details to view actual costs or usage against existing budget targets.
市场MarketPlace 基于使用情况的 Azure 市场费用。Usage-based Azure Marketplace charges.
价目表PriceSheets 为注册提供适用的费率(由指示器计费)。Applicable rates by meter for an enrollment.
RIChargesRICharges 过去 24 个月与预留实例相关联的费用。Charges associated to your Reserved Instances over the last 24 months.
RIRecommendations_SingleRIRecommendations_Single 根据过去 7 天、30 天或 60 天单个订阅的使用趋势得出的预留实例购买建议。Reserved Instance purchase recommendations based on your single subscription usage trends over the last 7, 30, or 60 days.
RIRecommendations_SharedRIRecommendations_Shared 根据过去 7 天、30 天或 60 天所有订阅的使用趋势得出的预留实例购买建议。Reserved Instance purchase recommendations based on your usage trends across all your subscriptions over the last 7, 30 or 60 days.
RIUsageRIUsage 上个月现有预留实例的使用情况详细信息。Consumption details for your existing Reserved Instances over the last month.
摘要Summaries 余额、新购买、Azure 市场服务费用、调整和超额费用的月度摘要。A monthly summary for balances, new purchases, Azure Marketplace service charges, adjustments, and overage charges.
UsageDetailsUsageDetails 已使用量的明细和估算的注册费用。A consumed quantities breakdown and estimated enrollment charges.

可以选中任意表旁边的复选框来查看预览。You can select a checkbox beside any table to see a preview. 可以通过勾选表名称旁边的框来选择一个或多个表,然后选择“加载” 。You can select one or more tables by checking the box beside their name, then select Load.

备注

“摘要”和“价目表”这两个表仅适用于注册级 API 密钥 。The Summary and PriceSheet tables are only available for the enrollment-level API Key. 此外,这些表中的数据默认包含“使用情况” 和“价目表” 的当前月份数据。Also, the data in these tables has, by default, the current month's data for Usage and PriceSheet. 未将“摘要” 和“市场” 这两个表限制到当前月份。The Summary and MarketPlace tables are not constrained to the current month.

选择“加载”时,数据将加载到 Power BI Desktop 。When you select Load, the data is loaded into Power BI Desktop.

加载所选数据后,可以在“字段” 窗格中看到选择的表和字段。Once the data you selected is loaded, the tables and fields you selected can be seen in the Fields pane.

使用 Azure 使用情况见解Using Azure Consumption Insights

要使用 Azure 使用见解连接器,请访问 Azure 门户企业功能 。To use the Azure Consumption Insights connector, you access to the Azure portal Enterprise features.

使用 Azure 使用见解连接器成功加载数据后,可以使用“查询编辑器”创建自己的自定义度量值和列 。Once you successfully load data using the Azure Consumption Insights connector, you can create your own custom measures and columns using Query Editor. 并且可以创建可在 Power BI 服务中共享的视觉对象、报表和仪表板 。You can also create visuals, reports, and dashboards for sharing in the Power BI service.

使用空查询可以检索 Azure 自定义查询集合示例。With a blank query, you can retrieve a sample Azure custom queries collection. 可以通过两种方法执行此检索:There are two ways you can do this retrieval:

在 Power BI Desktop 中执行以下操作 :In Power BI Desktop:

  1. 选择“开始”功能区 Select the Home ribbon
  2. 选择“获取数据” > “空查询” Select Get Data > Blank Query

或者在“查询编辑器”中执行以下操作 :Or, in Query Editor:

  1. 单击左侧的“查询”窗格 Right-click in the left Queries pane
  2. 从显示的菜单中选择“新建查询”>“空查询” Select New Query > Blank Query from the menu that appears

在编辑栏中键入以下内容 :In the Formula bar, type:

= MicrosoftAzureConsumptionInsights.Contents

下图展示的是出现的示例集合。The following image shows a samples collection that appears.

处理报表和创建查询时,可以:When working with reports and creating queries, you can:

  • 要定义从当前日期开始的月数,请使用 numberOfMonth To define the number of months starting from the current date, use numberOfMonth
    • 使用介于 1 到 36 之间的值。Use a value between one and 36. 表示自当前日期开始要导入的月数。Represent the number of months, from the current date, you want to import. 建议不要获取超过 12 个月的数据。We recommend getting no more than 12 months of data. 此限制可避免 Power BI 查询导入约束和数据量阈值。This limit avoids Power BI query import constraints and data volume thresholds.
  • 要在历史时间窗口中定义一段时间内的月份,请使用 startBillingDataWindow 和 endBillingDataWindow To define a period of months in a historical time window, use startBillingDataWindow and endBillingDataWindow
  • 请勿将 numberOfMonth 与 startBillingDataWindow 或 endBillingDataWindow 一起使用 Don't use numberOfMonth together with startBillingDataWindow or endBillingDataWindow

从 Azure 企业连接器迁移Migrate from the Azure Enterprise Connector

一些客户使用 Azure 企业连接器 (Beta) 创建视觉对象 。Some customers created visuals using the Azure Enterprise Connector (Beta). 最终它将替换为 Azure 使用见解连接器 。Eventually, it will be replaced with the Azure Consumption Insights connector. 新的连接器具有以下功能和增强:The new connector has features and enhancements that include:

  • “余额汇总” 和“市场购买” 可用的其他数据来源Additional data sources available for Balance Summary and Marketplace Purchases
  • 新增参数和高级参数,如 startBillingDataWindow 和 endBillingDataWindow New and advanced parameters, such as startBillingDataWindow and endBillingDataWindow
  • 更好的性能和响应能力Better performance and responsiveness

接下来的步骤演示如何转换为 Azure 使用见解连接器 。The next steps show how to transition to the Azure Consumption Insights connector. 这些步骤保留在创建自定义仪表板或报表时已完成的工作。These steps preserve the work you've already done in creating custom dashboards or reports.

步骤 1:使用新的连接器连接到 AzureStep 1: Connect to Azure using the new connector

第一步是使用前文介绍过的 Azure 使用见解连接器 。The first step is use the Azure Consumption Insights connector described in detail earlier in this article. 在此步骤中,在 Power BI Desktop 的“主页” 功能区中选择“获取数据”>“空查询” 。In this step, select Get Data > Blank Query from the Home ribbon in Power BI Desktop.

步骤 2:使用高级编辑器创建查询Step 2: Create a query in Advanced Editor

在“查询编辑器”中,从“开始”功能区的“查询”部分选择“高级编辑器” 。In Query Editor, select Advanced Editor from the Home ribbon's Query section. 在出现的“高级编辑器”窗口中,输入此查询 :In the Advanced Editor window that appears, enter this query:

let    
    enrollmentNumber = "100",
    optionalParameters = [ numberOfMonth = 6, dataType="DetailCharges" ],
    data = MicrosoftAzureConsumptionInsights.Contents(enrollmentNumber, optionalParameters)   
in     
    data

需要将 enrollmentNumber 值替换为自己的注册号 。You'll need to replace the enrollmentNumber value with your enrollment number. 可以从 Azure Enterprise 门户获取该编号。You can get your number from the Azure Enterprise Portal. numberOfMonth 参数表示要从当前日期返回几个月的数据 。The numberOfMonth parameter is how many months of data you want going back, from the current dat. 当前月份用零 (0) 表示。Use zero (0) for the current month.

在“高级编辑器”窗口中选择“完成”后,预览将会刷新,表中会显示指定月份范围的数据 。Once you select Done in the Advanced Editor window, the preview refreshes and data from the specified month range appears in the table. 选择“关闭并应用” ,然后返回。Select Close & Apply and return.

步骤 3:将度量值和自定义列移动到新报表Step 3: Move measures and custom columns to the new report

接下来,需要将创建的全部自定义列或度量值移动到新的详细信息表中。Next, you'll need to move any custom columns or measures you created into the new details table. 步骤如下。Here are the steps.

  1. 打开记事本(或其他文本编辑器)。Open Notepad (or another text editor).

  2. 选择要移动的度量值,从“公式”字段中复制文本并粘贴到记事本中 。Select the measure you want to move, copy the text from the Formula field, and place it in Notepad.

  3. 将 Query1 重命名为原始详细信息表名称。Rename Query1 to the original details table name.

  4. 若要创建新的表度量值和自定义列,请右键单击表,然后选择“新建度量值” 。To create new table measures and custom columns, right-click on your table, and choose New Measure. 然后剪切并粘贴存储的度量值和列,直到全部完成。Then, cut and paste your stored measures and columns until they're all done.

许多仪表板包含用于查找或筛选的其他表,例如日期表或用于自定义项目的表。Many dashboards have additional tables that are used for lookup or filtering, such as date tables or tables used for custom projects. 重新建立这些关系可解决大部分遗留问题。Reestablishing those relationships resolves most remaining issues. 下面介绍如何执行该操作。Here's how to do it.

  • 在 Power BI Desktop 的“建模” 选项卡中,选择“管理关系” 会弹出允许你管理模型中关系的窗口。In the Modeling tab in Power BI Desktop, select Manage Relationships to bring up a window that lets you manage relationships within the model. 根据需要重新关联表。Relink your tables, as needed.

步骤 5:验证视觉对象,并根据需要调整字段格式Step 5: Verify your visuals, and adjust field formatting as needed

此时大部分原始视觉对象、表和向下钻取应该按预期方式工作。At this point, most of your original visuals, tables, and drill-downs should be working as expected. 不过可能需要进行一些小调整,才能精确地设置外观和风格。Some minor tweaks may be necessary, however, to precisely format the look and feel. 花点时间查看每个仪表板和视觉对象,确保它们按预期显示。Take a bit of time to look over each of your dashboards and visuals to ensure they look how you want them.

使用 Azure 使用情况和见解 (ACI) API 获取使用情况数据Using the Azure Consumption and Insights (ACI) API to get consumption data

Azure 还提供了 Azure 使用情况和见解 (ACI) API Azure also provides the Azure Consumption and Insights (ACI) API. 用户可以使用 ACI API 创建自己的自定义解决方案来收集、报告和直观显示 Azure 使用情况信息。You can create your own custom solutions to gathering, reporting, and visualizing Azure consumption information using the ACI API.

映射门户、连接器和 API 之间的名称和使用情况详细信息Mapping names and usage details between the portal, the connector, and the API

Azure 门户列和详细名称与 API 和连接器中的相关信息类似,但并不总是完全一致。The Azure portal columns and details names are similar in the API and the connector, though not always identical. 为了阐明这一点,下表提供了一个映射。To help clarify, the following table provides a mapping. 还指示了列是否已过时。Also indicated is whether the column is obsolete. 若要了解详细信息和术语定义,请查看 Azure 帐单数据字典For more information, and term definitions, see the Azure billing data dictionary.

ACI 连接器/ContentPack ColumnNameACI Connector / ContentPack ColumnName ACI API 列名称ACI API Column Name EA 列名称EA Column Name 已过时/用于向后兼容Obsolete / Present for backward compatibility
AccountNameAccountName accountNameaccountName Account NameAccount Name No
AccountIdAccountId accountIdaccountId Yes
AcccountOwnerIdAcccountOwnerId accountOwnerEmailaccountOwnerEmail AccountOwnerIdAccountOwnerId No
AdditionalInfoAdditionalInfo additionalInfoadditionalInfo AdditionalInfoAdditionalInfo No
AdditionalInfoldAdditionalInfold Yes
Consumed QuantityConsumed Quantity consumedQuantityconsumedQuantity Consumed QuantityConsumed Quantity No
Consumed ServiceConsumed Service consumedServiceconsumedService Consumed ServiceConsumed Service No
ConsumedServiceIdConsumedServiceId consumedServiceIdconsumedServiceId Yes
开销Cost costcost ExtendedCostExtendedCost No
Cost CenterCost Center costCentercostCenter Cost CenterCost Center No
日期Date 日期date 日期Date No
Day Day No
DepartmentNameDepartmentName departmentNamedepartmentName Department NameDepartment Name No
DepartmentIDDepartmentID departmentIddepartmentId Yes
Instance IDInstance ID Yes
InstanceIdInstanceId instanceIdinstanceId Instance IDInstance ID No
位置Location Yes
Meter CategoryMeter Category meterCategorymeterCategory Meter CategoryMeter Category No
Meter IDMeter ID Yes
Meter NameMeter Name meterNamemeterName Meter NameMeter Name No
Meter RegionMeter Region meterRegionmeterRegion Meter RegionMeter Region No
Meter Sub-CategoryMeter Sub-Category meterSubCategorymeterSubCategory Meter Sub-CategoryMeter Sub-Category No
MeterIdMeterId meterIdmeterId Meter IDMeter ID No
Month Month No
产品Product 产品product 产品Product No
ProductIdProductId productIdproductId Yes
Resource GroupResource Group resourceGroupresourceGroup Resource GroupResource Group No
Resource LocationResource Location resourceLocationresourceLocation Resource LocationResource Location No
ResourceGroupIdResourceGroupId Yes
ResourceLocationIdResourceLocationId resourceLocationIdresourceLocationId Yes
ResourceRateResourceRate resourceRateresourceRate ResourceRateResourceRate No
ServiceAdministratorIdServiceAdministratorId serviceAdministratorIdserviceAdministratorId ServiceAdministratorIdServiceAdministratorId No
ServiceInfo1ServiceInfo1 serviceInfo1serviceInfo1 ServiceInfo1ServiceInfo1 No
ServiceInfo1IdServiceInfo1Id Yes
ServiceInfo2ServiceInfo2 serviceInfo2serviceInfo2 ServiceInfo2ServiceInfo2 No
ServiceInfo2IdServiceInfo2Id Yes
Store Service IdentifierStore Service Identifier storeServiceIdentifierstoreServiceIdentifier Store Service IdentifierStore Service Identifier No
StoreServiceIdentifierIdStoreServiceIdentifierId Yes
订阅名称Subscription Name subscriptionNamesubscriptionName 订阅名称Subscription Name No
标记Tags 标记tags 标记Tags No
TagsIdTagsId Yes
Unit Of MeasureUnit Of Measure unitOfMeasureunitOfMeasure Unit Of MeasureUnit Of Measure No
年份Year 年份Year No
SubscriptionIdSubscriptionId subscriptionIdsubscriptionId SubscriptionIdSubscriptionId Yes
SubscriptionGuidSubscriptionGuid subscriptionGuidsubscriptionGuid SubscriptionGuidSubscriptionGuid No

后续步骤Next steps

可使用 Power BI Desktop 连接到多个不同数据源。You can connect to many different data sources using Power BI Desktop. 有关详细信息,请参阅以下文章:For more information, see the following articles: