Microsoft offers several ways to analyze and report on your cloud costs. For quick exploration of subscriptions and billing accounts, start with smart views in Cost analysis in the Azure portal or Microsoft 365 admin center. When you need more control or to save and share charts, switch to customizable views. But what should you do when you need to build out more advanced, interactive reports or need to share reports with business and finance stakeholders? This is where Power BI comes in, especially since many organizations are already leveraging Power BI for business and financial reporting outside of cloud costs.

Cost Management connector for Power BI

The Cost Management app for Power BI has always been a popular option for organizations with an Enterprise Agreement (EA) enrollment. But the app was designed exclusively for EA enrollments and doesn’t support Microsoft Customer Agreement (MCA) accounts. This left many organizations with no other option than to create new reports from scratch using the Cost Management connector for Power BI. But this presents a few challenges:

• The connector uses outdated APIs that don’t include all details.


• The underlying datasets use different column names than EA data, so the same reports can’t be used.


• The connector can only handle up to around $2-5 million in total monitored spend.


• The connector doesn’t support connecting to multiple billing accounts, so there’s no way to view historical costs after an EA renewal or MCA migration.

The latest recommendation from the Cost Management team is to export data to a storage account and use the Azure Data Lake Storage Gen2 connector or Microsoft Fabric to pull data into Power BI reports. This approach ensures your reports will have the latest complete data in a more scalable way. But the problem here is that you have to start from scratch with a new, empty report. This is where the FinOps toolkit comes in.

Power BI reports for Cost Management exports

Based on the direction from the Cost Management team, the FinOps toolkit provides a set of reports that connect to exported data from Cost Management. Simply export your data, download the reports, and point them at your storage account. It’s that simple. And the great news is, these reports:

• Use the latest data in the FinOps Open Cost and Usage Specification (FOCUS) format, which includes all available details about your cost and usage.


• Use a single set of columns for both EA and MCA billing accounts.


• Support connecting to multiple billing accounts and even subscriptions.

The FinOps toolkit includes 5 reports:

• Cost summary – Track cost over time and get a general overview of cost and savings with common breakdowns to get you started.


• Rate optimization – Review cost savings from negotiated and commitment discounts and identify opportunities to increase savings.


• Policy + governance – Summarize your governance posture with standard metrics aligned with the Cloud Adoption Framework.


• Workload optimization – Gain insights into resource utilization and efficiency opportunities based on historical usage patterns.


• Data ingestion – Review FinOps hubs cost and monitor Cost Management exports to identify and resolve common issues.

The Cost summary and Rate optimization reports are based on the EA template app, so if you’re familiar with that, you should feel right at home. A lot has been added, so you’ll also find some welcome additions.

Getting started with FinOps toolkit reports

FinOps toolkit Power BI reports support a few deployment options, from raw Cost Management exports in Azure Data Lake Storage (ADLS) to data processed and ingested into storage, Azure Data Explorer, or Microsoft Fabric via the FinOps hubs orchestration engine. For today, we’ll focus exclusively on raw Cost Management exports in storage.

Step 1. Create an Azure Data Lake Storage Gen2 storage account

FinOps toolkit reports use ADLS for improved performance and load times. When creating a new storage account, select Premium performance and Block Blobs redundancy, then select Enable hierarchical namespace on the Advanced tab.

Make note of the subscription and storage account name. You’ll need those in the next step.

To learn more, see Create a storage account for Azure Data Lake Storage.

Step 2. Create Cost Management exports

Next, let’s create exports.

1. Open Cost Management in the Azure portal.


2. Select Reporting + analytics > Exports, and then select + Create.


3. In the export wizard, select the All costs (FOCUS) + prices template.


4. Select Next.


5. Specify the subscription and storage account from step 1.


6. Specify the desired container and directory path.


7. Specify Parquet format with Snappy compression and overwrite data to avoid data duplication with raw exports.
   
   
   
   • CSV with or without Gzip compression will also work but may impact data refresh performance.
   
   
   
   


8. Select Next.


9. Confirm the configured settings and select Create.

With cost and price exports created, now create reservation recommendation exports. One important note about reservation recommendation exports is that each export can only cover one reservation scope, one service, and one lookback period. If you want to collect shared and scoped recommendations, or recommendations from multiple services, or recommendations for different lookback periods, you will need to create multiple exports. You can create up to 10 exports in the new export wizard. To create more, run thru the wizard for the remaining datasets. I recommend creating shared and single scoped recommendations for each service you use.

1. Select + Create at the top of the page.


2. Select the Create your own export template.


3. Select + Add export,


4. Select the Reservation recommendations type, specify each of the available options, and select Add.


5. Repeat 3 and 4 for each desired reservation recommendation dataset.


6. Select Next.


7. Specify the same destination settings from the cost and price exports above, then select Next.


8. Confirm the configured settings and select Create.

Repeat 1-8 for any additional datasets.

Normally, exports will run within 24 hours. Select the checkboxes for each of the new exports, then select the Run now command at the top of the page.

After you configure exports for your current scope, use the scope picker at the top of the page to select any additional scopes you’re interested in. If you recently transitioned from EA to MCA or even had an EA renewal in the last 7 years, you can create cost and price exports on those older billing accounts. And of course, if you’re managing multiple subscriptions, you can do the same for each subscription. Note that price and reservation exports are only available for EA billing accounts and MCA billing profiles.

To learn more, see Create and manage Cost Management exports.

Step 3. Connect Power BI reports to storage

The FinOps toolkit includes a set of demo reports with sample data and report templates that you can open in Power BI Desktop. If you don’t already have Power BI Desktop, you can learn more and download it from the Power BI Desktop page.

Before we setup each report, let’s get the storage account URL. The URL consists of 3 parts:

{dfs-endpoint}/{container}/{optional-folder-path}

To get the DFS endpoint, open the desired storage account in the Azure portal, select Settings > Endpoints in the menu, and copy the Data Lake Storage URL.

Replace “{container}” with the container name specified in the exports and, if you used a common storage path across all exports and scopes, replace the “{optional-folder-path}” with the common part of the storage path. Don’t include scope-specific folders if you intend to report on multiple scopes. If you need to change any export container or storage path values, re-run the export to ensure it lands in the correct place.

Next, download the latest demo reports (PBIX files) or report templates (PBIT files), extract the ZIP, and open the desired files in Power BI Desktop. I recommend starting with Cost summary and Rate optimization. (If you opted to download demo reports, you can also upload these directly to any Microsoft Fabric or Power BI workspace.)

If you downloaded the demo reports, you can explore the pages with demo data freely without connecting to a storage account. (Although, these reports have limited data.) When you’re ready to connect to your data, select Transform data > Edit parameters.

If you downloaded the templates, you’ll see a dialog pop up requesting the needed parameters.

Set Storage URL to the Data Lake Storage (DFS) URL we captured earlier and Number of Months to the number of closed months you’d like to see in the report. You can leave the rest of the values with their default values.

If you exported prices and want to see missing prices be populated for reservation usage, select the Experimental: Add Missing Prices parameter. Please note this option joins the cost and price data and may severely impact data refresh performance. If you experience performance issues, please switch to using FinOps hubs with Data Explorer or Microsoft Fabric.

The Default Granularity option specifies whether you would like most reports to be summarized at a usage or monthly level.

If using a template, select Load and your report should start to ingest data from the storage account. If using a demo report, select OK and then select the Apply button in the banner. Data load times can take anywhere from minutes to hours, depending on how much data you have. You can publish the report to the Power BI service using the Home > Publish command in Power BI Desktop (or by saving the report and manually uploading the PBIX file). Once uploaded, open the Power BI workspace in a web browser, locate the semantic model for the report, and select the Schedule refresh icon to configure the report to automatically update every month.

Repeat this process for each report using the same parameter values.

Note that Power BI has certain limits and guardrails that may constrain the amount of data you can ingest from storage. The amount of data will also impact data refresh performance. Depending on your license and services you use, Power BI reports can load around $1-5 million worth of spend per month with incremental refresh configured; however, loading this much data can take hours to ingest.

If you experience errors or delays in the data refresh, we recommend using FinOps hubs with Data Explorer or Microsoft Fabric. FinOps hubs offer massively improved performance and powerful new capabilities for organizations who need to monitor over $100K in total cost.

Step 4. Populate historical data

Once you have reports up and running, you should think about how much historical data you want to report on. Many organizations start with 13 months, but some are interested in longer, year-over-year reporting. Just remember the constraints of loading no more than $1-5 million in spend per month with scheduled refresh configured. If you do not set up incremental refresh, you will only be able to load a total of $1-5 million in spend.

You can populate historical data in the portal or via PowerShell. If you have multiple scopes or want to populate data from more than a few months, PowerShell can save time.

To run exports from the portal, open each export in Cost Management and select the Export selected dates command at the top of the flyout. Specify the start and end date for one month and select Execute. Repeat this for each month and each export for each scope.

To run exports from PowerShell, you can use either PowerShell 7 or Azure Cloud Shell. Refer to Install the FinOpsToolkit PowerShell module for initial setup instructions.

Once the FinOpsToolkit module is installed, run the following command for each exports and each scope. You can copy the billing account and billing profile IDs from the Properties page for each scope in the Azure portal. (This is under Settings > Configuration in Cost Management.)

Start-FinOpsCostExport `
-Scope ‘/providers/Microsoft.Billing/billingAccounts/###/billingProfiles/###’ `
-Name ‘{export-name}’ `
-Backfill 13 # or desired number of months

This command will kick off one export for each month in the specified date range (number of -Backfill months) and will pause for API throttling. Once complete, you can run the command again for the next export or next scope.

Each export can take 15 minutes or more, depending on the size of the account. Once all exports have completed, your reports should automatically update after your next scheduled refresh. You can also manually refresh the report in Power BI Desktop or the semantic model in your Power BI workspace.

To learn more about this command, see Start-FinOpsCostExport.

Summary and next steps

By now, you should have reports setup and populated with at least one month of data and additional months being actively populated. These reports are intended to help you get started on your FinOps journey, so please do think about how you can improve and augment them to meet your needs. Power BI is a powerful platform that allows you to bring in your own data and visualize that in various ways. The FinOps toolkit reports are just the beginning!

If you have any questions, post them in the FinOps toolkit discussion forum. If you have specific feedback about ways to improve the reports, PowerShell, or even a new idea, create a new feature request. The FinOps toolkit community is here to help!

Next, I’ll dig into FinOps hubs, which offer improved performance and additional capabilities for organizations looking to do more with their data.