分析 Power BI Desktop 中的 Azure 成本和使用情况数据Analyze Azure cost and usage 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 spending.

Power BI 目前支持连接到企业协议和客户协议的计费帐户。Power BI currently supports connecting to Enterprise Agreement and Customer Agreement billing accounts.

  • 企业协议用户应与 Azure 使用见解连接器连接(如下所示) 。Enterprise Agreement users should connect with the Azure Consumption Insights connector (below).

  • 客户协议用户应与 Azure 成本管理连接器连接 。Customer Agreement users should connect with the Azure Cost Management connector.

备注

如果你是使用 Azure 成本管理连接器的企业协议客户,但在“成本管理” 边栏选项卡中看不到“计费对象信息 ID”,则表示你不在现代商业平台中。If you're an Enterprise Agreement customer using the Azure Cost Management connector, and you don't see the Billing Profile ID in your Cost Management blade, it means you're not in the Modern Commerce Platform. 对于这种情况,请使用请求 EA 注册号的 Azure 使用见解连接器,而不是计费对象信息 ID 。For those cases, use the Azure Consumption Insights connector, which requests the EA enrollment number rather than the Billing Profile ID.

使用 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

使用 Azure 成本管理连接Connect with Azure Cost Management

本部分介绍如何连接到客户协议计费帐户。In this section, you'll learn how to connect to your Customer Agreement billing account.

备注

Azure 成本管理连接器目前支持客户协议上的客户 。The Azure Cost Management connector currently supports customers on the Customer Agreement. 企业协议客户应使用 Microsoft Azure 使用见解连接器 。Enterprise Agreement customers should use the Microsoft Azure Consumption Insights connector.

要使用 Power BI Desktop 中的 Azure 成本管理连接器,请执行以下操作 :To use the Azure Cost Management connector in Power BI Desktop:

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

  2. 从左侧的类别中选择“Azure” 。From the categories on the left, select Azure.

  3. 选择左侧的“Azure 成本管理 (Beta)” 。Select Azure Cost Management (Beta) on the right.

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

    在显示的对话框中,输入“计费对象信息 ID” 。In the dialog that appears, enter your Billing profile ID.

可以从 Azure 门户获取该 ID:You can get your ID from the Azure portal:

  1. 导航至“成本管理和计费” 。Navigate to Cost Management + Billing.

  2. 选择计费帐户。Select your billing account.

  3. 选择侧栏中的“计费对象信息” 。Select Billing profiles in the sidebar.

  4. 选择计费对象信息。Select your billing profile.

  5. 选择侧栏中的“属性” 。Select Properties in the sidebar.

  6. 复制计费对象信息 ID。Copy your billing profile ID.

    系统将提示你使用 Azure 电子邮件和密码登录。You'll be prompted to sign in with your Azure email and password. 进行身份验证后,将看到一个“导航器”窗口,其中包含 12 个可用的表 :Once you authenticate, you'll be shown a Navigator window with 12 available tables:

Table 说明Description
计费事件Billing events 提供新发票、信用卡购买等信息的事件日志。Event log of new invoices, credit purchases, and more.
预算Budgets 提供预算详细信息,以查看针对现有预算目标的实际成本或使用情况。Budget details to view actual costs or usage against existing budget targets.
费用Charges 提供 Azure 使用情况、市场费用和单独计费的月级别小结。A month-level summary of Azure usage, market place charges, and charges billed separately.
额度批次Credit lots 为提供的计费对象信息提供 Azure 额度批次购买详情。Azure credit lot purchase details for the provided billing profile.
额度摘要Credit summary 提供的计费对象信息的额度摘要。Credit summary for provided billing profile.
市场Marketplace 基于使用情况的 Azure 市场费用。Usage-based Azure Marketplace charges.
价目表Pricesheets 提供的计费对象信息的适用计量费率。Applicable meter rates for the provided billing profile.
RI 费用RI charges 过去 24 个月与预留实例相关联的费用。Charges associated to your Reserved Instances over the last 24 months.
RI 建议(单个)RI recommendations (single) 根据过去 7 天、30 天或 60 天单个订阅的使用趋势得出的预留实例购买建议。Reserved Instance purchase recommendations based on your single subscription usage trends for the last 7, 30 or 60 days.
RI 建议(共享)RI recommendations (shared) 根据过去 7 天、30 天或 60 天所有订阅的使用趋势得出的预留实例购买建议。Reserved Instance purchase recommendations based on all your subscription usage trends for the last 7, 30 or 60 days.
RI 使用情况RI usage 上个月现有预留实例的使用情况详细信息。Consumption details for your existing Reserved Instances over the last month.
使用率详细信息Usage details 提供给定计费对象信息 ID 的已使用量的明细和估计费用。A breakdown of consumed quantities and estimated charges for the given billing profile ID.

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

选择“加载”时,数据将加载到 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 使用见解在 Power BI 中分析费用Watch How to analyze spending in Power BI with Azure Consumption Insights. 此视频介绍如何使用 Azure 使用见解连接器在 Power BI Desktop 中查看费用数据。This video explains reviewing your cost data in Power BI desktop using the Azure Consumption Insights connector.

编写自定义查询Writing custom queries

可以创建自定义 M 查询来自定义月数、更改 API 版本或者对返回的数据执行更多高级逻辑。You can create a custom M query to customize the number of months, change the API version, or do more advanced logic on the returned data.

在 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 Menu from the menu that appears

在“公式栏”中,键入以下内容,将 billingProfileId 替换为实际 ID,并将“费用”替换为任何有效的表名(上文已列出) 。In the Formula bar, type the following, replacing billingProfileId with your actual ID, and "charges" with any valid table name (list above).

let
    Source = AzureCostManagement.Tables(billingProfileId, [ numberOfMonths = 3 ]),
    charges = Source{[Key="charges"]}[Data]
in
    charges

除了将 numberOfMonths 修改为 1 到 36 之间的任何值之外,还可以提供:Besides modifying the numberOfMonths to any value between 1 and 36, you can also provide:

  • apiVersion,用于自定义查询将调用的 API 版本。apiVersion to customize which API version the query calls.
  • lookbackWindow,用于 RI 建议(单个或共享),以修改要从中生成建议的窗口(有效选择:7、30 或 60 天)。lookbackWindow, for RI recommendations (single or shared), to modify the window in which to generate recommendations from (valid options: 7, 30, or 60 days).

后续步骤Next steps

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