Connect to Azure Cost Management data in Power BI Desktop - Power BI (2023)

  • Article
  • 9 minutes to read

You can use the Azure Cost Management connector for Power BI Desktop to create powerful, custom visualizations and reports that help you better understand your Azure spend. The Azure Cost Management Connector currently supports customers with a directMicrosoft Customer Agreementor aEnterprise Agreement (EA).

The Azure Cost Management connector does not support usage-based Microsoft Customer Agreements or indirect Microsoft Customer Agreements. Microsoft partner agreements are also not supported. If you have an unsupported agreement, you can use exports to save the cost data to a share and then connect to it using Power BI. For more information, seeTutorial - Create and manage exported data from Azure Cost Management.

The Azure Cost Management connector uses OAuth 2.0 to authenticate with Azure and identify users who will use the connector. Tokens generated in this way are valid for a certain period of time. Power BI keeps the token for the next login. OAuth 2.0 is a standard for the process that runs behind the scenes to ensure the secure handling of these permissions. To connect, you must use onecompany administratorHave or have an Enterprise Agreement accountappropriate permissionsat the billing account or billing profile level for Microsoft Customer Agreements.

note

This connection replaces the previously available oneAzure Consumption Insights und Azure Cost Management (Beta)Connections. All reports created with the previous connector must be re-created with this connector.

Connect using Azure Cost Management

To use theAzure Cost Management-ConnectorIn Power BI Desktop, do the following:

  1. ImHeimband, selectget data.

  2. ChooseAzurblaufrom the list of data categories.

  3. ChooseAzure cost management.

    Connect to Azure Cost Management data in Power BI Desktop - Power BI (1)

  4. In the dialog that appears for theChoose areafall down, useEnter range manuallyProMicrosoft Customer Agreements, or useenrollment numberProEnterprise Agreements (EA).

Connect to a Microsoft Customer Agreement account

This section describes the steps required to connect to a Microsoft Customer Agreement account.

Connect to a billing account

To connect to a billing account, you need to get yoursBilling Account IDfrom the Azure portal:

  1. ImAzure-Portal, navigate toCost Management + Billing.

  2. Select your billing profile.

  3. Undersettingsselect in the menucharacteristicsin the sidebar.

  4. Underbilling profile, copy thatI WOULD.Connect to Azure Cost Management data in Power BI Desktop - Power BI (2)

  5. ForChoose area, SelectEnter range manuallyand enter the connection string as shown in the example below and replace{billingAccountId}with the data copied from the previous steps./providers/Microsoft.Billing/billingAccounts/{billingAccountId}

    Alternativ zChoose area, Selectenrollment numberand enter the billing account ID string as copied from the previous steps.

  6. Enter the number of months and selectOK.

    Connect to Azure Cost Management data in Power BI Desktop - Power BI (3)

    Alternatively, you can set if you want to download less than a month of datanumber of monthsto zero, and then use to specify a date rangestart dateandFinal termValues ​​equal to less than 31 days.

  7. If prompted, sign in with your Azure user account and password. You must have access to the billing account section to successfully access the billing data.

Connect to a billing profile

To connectbilling profile, you need to retrieve yourBilling Profile IDandBilling Account IDfrom the Azure portal:

  1. ImAzure-Portal, navigate toCost Management + Billing.

  2. Select your billing profile.

  3. Undersettingsselect in the menucharacteristicsin the sidebar.

  4. Underbilling profile, copy thatI WOULD.

  5. UnderBilling & Account, copy thatI WOULD.

    Connect to Azure Cost Management data in Power BI Desktop - Power BI (4)

  6. ForChoose area, SelectEnter range manuallyand enter the connection string as shown in the example below and replace{billingAccountId}and{billingProfileId}with the data copied from the previous steps.

    /providers/Microsoft.Billing/billingAccounts/{billingAccountId}/billingProfiles/{billingProfileId}

  7. Enter the number of months and selectOK.

  8. If prompted, sign in with your Azure user account and password. You must have access to the billing profile to successfully access the billing profile data.

Connect to an Enterprise Agreement account

To connect to an Enterprise Agreement (EA) account, you can get your registration ID from the Azure portal:

  1. ImAzure-Portal, navigate toCost Management + Billing.

  2. Select your billing account.

  3. On theoverviewmenu, copy theBilling Account ID.

  4. ForChoose area, Selectenrollment numberand paste the billing account ID from the previous step.

  5. Enter the number of months, then selectOK.

    Connect to Azure Cost Management data in Power BI Desktop - Power BI (5)

  6. If prompted, sign in with your Azure user account and password. You must use an Enterprise Administrator account for Enterprise Agreements.

Data available through the connector

Once you have successfully authenticated, aNavigatorA window appears with the following available data tables:

TischAccount TypeSupported Areasdescription
Balance sheet overviewNur EAof enrollmentSummary of balance for the current billing month for Enterprise Agreements (EA).
billing eventsMCA onlybilling profileEvent log for new bills, credit purchases, etc. Microsoft Customer Agreement only.
BudgetsEA, MCAEA Login, MCA Billing Account, MCA Billing ProfileBudget details to view actual costs or usage versus existing budget targets.
feesMCA onlyMCA billing profileA monthly summary of Azure usage, marketplace fees, and fees billed separately. Microsoft Customer Agreement only.
Credit lossMCA onlyMCA billing profileAzure credit ticket purchase details for the provided billing profile. Microsoft Customer Agreement only.
price sheetsEA, MCAEA Enrollment, MCA Billing ProfileApplicable meter prices for the provided billing profile or EA enrollment.
RI FeesEA, MCAEA Enrollment, MCA Billing ProfileCharges related to your Reserved Instances in the last 24 months. This table is being retired, please use RI transactions
RI Recommendations (shared)EA, MCAEA Enrollment, MCA Billing ProfileReserved Instance purchase recommendations based on all your subscription usage trends over the last 30 days.
RI Recommendations (individual)EA, MCAEA Enrollment, MCA Billing ProfileReserved Instance purchase recommendations based on the usage trends of your individual subscriptions over the last 30 days.
RI TransactionsEA, MCAEA Enrollment, MCA Billing ProfileList of reserved instance transactions in the billing account scope.
RI usage detailsEA, MCAEA Enrollment, MCA Billing ProfileConsumption details for your existing Reserved Instances over the past month.
RI Usage SummaryEA, MCAEA Enrollment, MCA Billing ProfilePercentage of daily Azure reservation usage.
Usage DetailsEA, MCAEA Login, MCA Billing Account, MCA Billing ProfileA breakdown of consumed quantities and estimated charges for the specified billing profile at EA enrollment.
Usage details amortizedEA, MCAEA Login, MCA Billing Account, MCA Billing ProfileA breakdown of consumed quantities and estimated amortized charges for the specified billing profile at EA enrollment.

You can select a table to display a preview dialog. You can select one or more tables by checking the boxes next to their name and then selectingburden.

Connect to Azure Cost Management data in Power BI Desktop - Power BI (6)

If you chooseburden, the data is loaded into Power BI Desktop.

When the data you selected is loaded, the data tables and fields appear inFelderFeld.

Considerations and Limitations

The following considerations and limitations apply to the Azure Cost Management data connector:

  • Power BI doesn't support data row requests that exceed one million rows. Instead you can try to use the export function described inCreate and manage exported data in Azure Cost Management.

  • The Azure Cost Management data connector does not work with Office 365 GCC customer accounts.

  • Data update:Cost and usage data is typically updated within 8-24 hours and is available in the Azure portal and supporting APIs. Therefore, we recommend limiting scheduled Power BI refreshes to once or twice a day.

  • Data source reuse:If you have multiple reports that retrieve the same data and do not require additional report-specific data transformations, consider reusing the same data source, which would reduce the time required to retrieve usage details.

    For more information on data source reuse, see:

    • Introduction to cross-workspace datasets
    • Create reports based on datasets from different workspaces

You may receive one400 bad requestof theRI usage detailswhen you try to update the data when you have selected a date parameter that is greater than three months. Follow the steps below to fix the error:

  1. In Power BI, select DesktopHome > Transform Data.

  2. In the Power Query Editor, select theRI usage detailsrecord and selectAdvanced editor.

  3. Update the Power Query code as shown in the following paragraphs, which will split the calls into three-month chunks. Make sure to write down and keep your registration number or billing account/billing profile ID.

    ForEAUse the following code update:

    let enrollmentNumber = „<<Enrollment Number>>“, optionalParameters1 = [startBillingDataWindow = „-9“, endBillingDataWindow = „-6“], source1 = AzureCostManagement.Tables(“Enrollment Number“, enrollmentNumber, 5, optionalParameters1), riusagedetails1 = source1{[Key="riusagedetails"]}[Data], optionalParameters2 = [startBillingDataWindow = "-6", endBillingDataWindow = "-3"], source2 = AzureCostManagement.Tables("Enrollment Number", enrollmentNumber, 5, optionalParameters2), riusagedetails2 = source2{[Key="riusagedetails"]}[Daten], riusagedetails = Table.Combine({riusagedetails1, riusagedetails2})in riusagedetails

    ForMicrosoft Customer AgreementsUse the following fix:

    let billingProfileId = "<<billing profile id>>", optionalParameters1 = [startBillingDataWindow = "-9", endBillingDataWindow = "-6"], source1 = AzureCostManagement.Tables("billing profile id", billingProfileId, 5, optionalParameters1), riusagedetails1 = source1{[Key="riusagedetails"]}[Data], optionalParameters2 = [startBillingDataWindow = "-6", endBillingDataWindow = "-3"], source2 = AzureCostManagement.Tables("BillingProfileID", billingProfileId, 5, optionalParameters2), riusagedetails2 = source2{[Key="riusagedetails"]}[Data], riusagedetails = Table.Combine({riusagedetails1, riusagedetails2})in riusagedetails
  4. After updating the code with the appropriate update from the previous step, selectDidand then selectClose & apply.

Sometimes tags in the usage details don't work or the tags column can't be converted to JSON. This issue stems from the current UCDD API that returns the tags column by trimming the start and end brackets, resulting in Power BI not being able to transform the column as it is returned as a string. Follow the steps below to mitigate this situation.

  1. Navigate toQuery Editor.
  2. ChooseUsage DetailsTisch.
  3. In the right pane thecharacteristicsarea shows theApplied Steps. You must add a custom column following the steps belowNavigationStep.
  4. Select from the menuAdd Column>Add custom column
  5. Name the column, for example you could name the columnTagsInJsonor whatever you prefer, and then enter the following text into the query:
    ```= "{"& [Tags] & "}"
  6. Performing the previous steps creates a new column ofkeywordsim JSON-Format
  7. You can now transfer and expand the column as needed.

Authentication issues with Azure Active Directory guest accounts:You may have appropriate permissions to access the registration or billing account, but receive an authentication error similar to one of the following:

  • Access to the resource is prohibited
  • We could not authenticate with the provided credentials. Please try again.

These errors can be caused by having a user account in another Azure Active Directory domain that was added as a guest user.

For guest accounts: Use the following settings or options when promptedauthentication dialogwhen connecting to the Cost Management Power BI connector:

  1. Chooselog in
  2. ChooseUse a different account(below in dialogue)
  3. ChooseLogin Options(bottom of dialog)
  4. ChooseJoin an organization
  5. Fordomain name, provide the fully qualified domain name (FQDN) of the Azure Active Directory domain to which you were added as a guest.
  6. Then forChoose an accountSelect the user account that you previously authenticated.

Next Steps

With Power BI Desktop, you can connect to many different data sources. For more information, see the following articles:

  • What is Power BI Desktop?
  • Data sources in Power BI Desktop
  • Shape and combine data with Power BI Desktop
  • Connect to Excel workbooks in Power BI Desktop
  • Enter data directly into Power BI Desktop

FAQs

How do I connect Power BI to Azure Cost Management? ›

Connect using Azure Cost Management

Select Azure from the list of data categories. Select Azure Cost Management. In the dialog that appears, for the Choose Scope drop down, use Manually Input Scope for Microsoft Customer Agreements, or use Enrollment Number for Enterprise Agreements (EA).

How do I connect my Azure data to Power BI? ›

Connect in Power BI Desktop
  1. In Power BI Desktop, click Get Data > Azure > Azure Analysis Services database.
  2. In Database, if you know the name of the tabular model database or perspective you want to connect to, paste it here. ...
  3. Select a connection option and then press Connect.
Dec 1, 2022

How do I grant access to Azure cost management? ›

Assign enrollment account scope access

Sign in to the EA portal at https://ea.azure.com with an enterprise administrator account. Select Manage in the left pane. On the Enrollment tab, select the enrollment that you want to manage. Select the Account tab and then select Add Account.

Can we connect Azure to Power BI? ›

Power BI has a multitude of Azure connections available, and the business intelligence solutions you can create with those services are as unique as your business. You can connect as few as one Azure data source, or a handful, then shape and refine your data to build customized reports.

How do I connect to an Azure managed database? ›

To connect to Azure SQL Database:
  1. On the File menu, select Connect to SQL Azure (this option is enabled after the creation of a project). ...
  2. In the connection dialog box, enter or select the server name of Azure SQL Database.
  3. Enter, select, or Browse the Database name.
  4. Enter or select Username.
  5. Enter the Password.
Nov 18, 2022

Can we use Azure cost management to view cost? ›

In the Azure portal, navigate to cost analysis for your scope. For example: Cost Management + Billing > Cost Management > Cost analysis. Select your enrollment and then view your current accumulated costs.

How does Azure cost management work? ›

Azure Cost Management lets you analyze past cloud usage and expenses, and predict future expenses. You can view costs in a daily, monthly, or annual trend, to identify trends and anomalies, and find opportunities for optimization and savings.

What is the purpose of the cost management feature in Azure? ›

Protect your data and code while the data is in use in the cloud. Accelerate time to market, deliver innovative experiences and improve security with Azure application and data modernisation. Seamlessly integrate applications, systems, and data for your enterprise.

Is Azure cost management free? ›

Microsoft Cost Management is available to Azure customers and managed service providers at no additional cost.

What are the limitations of live connection? ›

Limitations of Live Connections:

Live Connection does not support Power Query transformations; in fact, all power query options are disabled (However, as there are dataset and report files separated, we can use Power Query transformations in the dataset file)

What are the ways we can connect to Azure? ›

Connect to the virtual machine
  • Go to the Azure portal to connect to a VM. ...
  • Select the virtual machine from the list.
  • At the beginning of the virtual machine page, select Connect.
  • On the Connect to virtual machine page, select RDP, and then select the appropriate IP address and Port number.
Jul 7, 2022

How does Azure connect to local database? ›

Introduction
  1. Step 1: Get ready with local database setup.
  2. Step 2: Configure and Enable TCP port 4133 for SQL Server.
  3. Step 3: Add local machine to Azure Connect.
  4. Step 4: Create a cloud project with a Web Role and add code for SQL Server connection testing.
  5. Step 5: Add a Role to Azure Connect.
  6. Step 6: Publish the project.
Sep 27, 2011

Which tools can be used to connect to Azure SQL Database? ›

  • SQL Server.
  • Analysis Services (SSAS)
  • Integration Services (SSIS)
  • Reporting Services (SSRS)
  • SQL Server Management Studio (SSMS)
  • SQL Server Data Tools (SSDT)
  • Azure Data Studio.
Nov 18, 2022

How do I monitor my Azure monitoring costs? ›

To get started analyzing your Azure Monitor charges, open Cost Management + Billing in the Azure portal. Select Cost Management > Cost analysis. Select your subscription or another scope. You might need additional access to cost management data.

How do you analyze Azure costs? ›

To review your costs in cost analysis, open the scope in the Azure portal and select Cost analysis in the menu. For example, go to Subscriptions, select a subscription from the list, and then select Cost analysis in the menu. Use the Scope pill to switch to a different scope in cost analysis.

How do I add cost analysis to Azure dashboard? ›

Select "Group by" as "Resource group name" and then select the pin icon in the upper-right corner or just after the "<Subscription Name> | Cost analysis" and then pin to dashboard. Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What are the four 4 main processes of cost management? ›

While cost management overall is a complicated process and a critical project management knowledge area, we can break it down into four processes:
  • Resource planning. ...
  • Cost estimation. ...
  • Cost budget. ...
  • Cost control.
Mar 18, 2022

What are the 5 functions of cost management? ›

Planning, communication, motivation, appraisal, and decision-making are the features that make managing costs an important business procedure. Resource allocation, cost estimation, cost budgeting, and cost control are the major functions of the cost management process.

Who has access to Azure cost management Tool? ›

Microsoft Cost Management for Azure is provided for free to Azure customers. This service shows all of your subscriptions on one screen, enabling you to zoom in on one particular service to gain detailed information.

What are the three benefits of cost management? ›

4 Benefits of Cost-Control Management
  • Lower Expenses. The main benefit of putting cost controls in place is lowering your company's overall expenses. ...
  • Gain Operational Efficiency. ...
  • Realize Procurement Effectiveness. ...
  • Streamline Technology.
Nov 1, 2022

Is Azure cost management real time? ›

Microsoft Cost Management includes Azure service and third-party Azure Marketplace usage, and purchases including reservations. All costs are based on your negotiated prices, and data is refreshed every four hours.

What are the 3 important services offered by Azure? ›

This gives users the flexibility to use their preferred tools and technologies. In addition, Azure offers four different forms of cloud computing: infrastructure as a service (IaaS), platform as a service (PaaS), software as a service (SaaS) and serverless functions.

Is Azure better than AWS in cost? ›

AWS is up to 5 times more expensive than Azure for Windows Server and SQL Server.

Can I use Azure for free forever? ›

You can try that with Azure Web Apps. This has a free tier that you can use forever. The only catch is that the free tier isn't as powerful and doesn't have as many capabilities as paid tiers.

What are the 3 pricing models of Azure? ›

Azure Pricing Models

Microsoft offers three main ways to pay for Azure VMs and other cloud resources: pay as you go, reserved instances, and spot instances.

How do I allow access to Azure services? ›

Solution
  1. From Azure Console.
  2. Login to Azure Portal using https://portal.azure.com.
  3. Go to Azure Database for PostgreSQL server.
  4. For each database, click on Connection security.
  5. In Firewall rules.
  6. Ensure Allow access to Azure services is set to OFF.
  7. Click Save to apply the changed rule.
  8. Using Azure Command Line Interface 2.0.

How do I raise permissions in Azure? ›

Elevate access for a Global Administrator
  1. Sign in to the Azure portal or the Azure Active Directory admin center as a Global Administrator. ...
  2. Open Azure Active Directory.
  3. Under Manage, select Properties.
  4. Under Access management for Azure resources, set the toggle to Yes. ...
  5. Click Save to save your setting.

How do I set permissions in Azure? ›

In Azure RBAC, to grant access, you assign an Azure role.
  1. In the list of Resource groups, open the new example-group resource group.
  2. In the navigation menu, click Access control (IAM).
  3. Click the Role assignments tab to see the current list of role assignments.
  4. Click Add > Add role assignment.
Aug 21, 2022

How long do Azure permissions take? ›

We have consistently noted that it takes at least overnight to 24 hours for Azure AD permissions to sync with our SharePoint online site.

What are 777 permissions? ›

Some file permission examples: 777 - all can read/write/execute (full access). 755 - owner can read/write/execute, group/others can read/execute. 644 - owner can read/write, group/others can read only.

What are the ways to access resources in Azure? ›

To open a resource by the service type:
  1. Sign in to the Azure portal.
  2. In the left pane, select the Azure service. In this case, Storage accounts. If you don't see the service listed, select All services, and then select the service type.
  3. Select the resource you want to open. A storage account looks like:
Jun 10, 2021

Does Microsoft have access to my data in Azure? ›

Microsoft does not inspect, approve, or monitor applications that customers deploy to Azure. Moreover, Microsoft does not know what kind of data customers choose to store in Azure. Microsoft does not claim data ownership over the customer information that's entered into Azure.

How do I allow Azure services and resources to access this server? ›

This can be turned on directly from the Azure portal blade by switching the Allow Azure Services and resources to access this server to ON in the Firewalls and virtual networks settings. Switching the setting to ON creates an inbound firewall rule for IP 0.0. 0.0 - 0.0. 0.0 named AllowAllWindowsAzureIps.

What are the three types of role based access controls in Microsoft Azure? ›

The way you control access to resources using Azure RBAC is to assign Azure roles. This is a key concept to understand – it's how permissions are enforced. A role assignment consists of three elements: security principal, role definition, and scope.

Which administrator role has the highest access in Azure? ›

The Service Administrator has full access to the Azure portal. The Co-Administrator has the equivalent access of a user who is assigned the Owner role at the subscription scope. In the Azure portal, you can manage Co-Administrators or view the Service Administrator by using the Classic administrators tab.

Where are API permissions in Azure? ›

Select Azure Active Directory > App registrations, and then select your client application. Select API permissions > Add a permission > Microsoft Graph > Application permissions.

What are four basic permissions? ›

There are four categories (system, owner, group, and world) and four types of access permissions (Read, Write, Execute and Delete).

What permissions do I have in Azure? ›

Check Azure Active Directory permissions
  • Log in to your Azure Account through the Azure portal.
  • Select Azure Active Directory.
  • In Azure Active Directory, select User settings.
  • Check the App registrations setting. ...
  • Select Overview and Find a user from Quick tasks.
  • Search for your account, and select it when you find it.

What should you use to track the cost of Azure resources? ›

Azure Cost Management + Billing is the primary tool you'll use to analyze your usage and costs. It gives you multiple options to analyze your monthly charges for different Azure Monitor features and their projected cost over time.

References

Top Articles
Latest Posts
Article information

Author: Saturnina Altenwerth DVM

Last Updated: 10/16/2023

Views: 5763

Rating: 4.3 / 5 (44 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Saturnina Altenwerth DVM

Birthday: 1992-08-21

Address: Apt. 237 662 Haag Mills, East Verenaport, MO 57071-5493

Phone: +331850833384

Job: District Real-Estate Architect

Hobby: Skateboarding, Taxidermy, Air sports, Painting, Knife making, Letterboxing, Inline skating

Introduction: My name is Saturnina Altenwerth DVM, I am a witty, perfect, combative, beautiful, determined, fancy, determined person who loves writing and wants to share my knowledge and understanding with you.