User Cost Attribution Combine Reports Using Power BI

User Cost Attribution (UCA) provides a Power BI Connector file, and optional Power BI template, that allows users to ingest their UCA report as a table within a Power BI file. This connector file supports ingesting one or multiple configurations/reports at a time. Ingesting multiple configurations allows you to optionally have a report view that combines the cost and session data from each configuration.

This document is a brief guide on how to prepare a Power BI file that combines UCA data from two or more reports, as well as provides suggestions on how to prepare your UCA reports to avoid overlap in data when combining them.

Assumptions

This document assumes:

• You have experience with the Nerdio Manager User Cost Attribution feature, including an understanding of how to navigate the tool and create new report configurations.

• The latest version of the Power BI connector is installed on the machine you are using.

• The UCA environment already has multiple report configurations created, with each configuration having at least a complete data set for the previous month.

• An understanding of how AVD and LAW are configured across your Azure environment.

If anything in this documentation is unfamiliar, please refer to the Nerdio Manager Knowledge Base for additional information, or reach out for support.

It is also recommended that you consult with your Azure administrator before attempting to follow these processes.

Combine Configurations in Power BI

Prepare your configurations

Before the configurations can be ingested into your Power BI report, you need to ensure that the configurations themselves are compatible and won’t cause issues when combined.

Particularly, if there is any overlap in data between configurations, it will cause the combined report to have some duplicated cost/session data, and generally misrepresent the actual cost of your environment.

There are a few questions you need to ask about your configurations:

• Do any of the configurations have overlapping subscriptions? If two configurations have an overlap in subscriptions, and are ingested into a combined Power BI report, then the combined report will likely have some duplicate cost and resource data. This inflates the final cost of the report, and generally causes it to be inaccurate.

• Do any of the configurations have overlapping Log Analytics Workspaces? If two configurations have an overlap in Log Analytics Workspaces, and are ingested into a combined Power BI report, then the combined report will likely contain some duplicate session and VM data.

To review the subscriptions and Log Analytics Workspaces:

1. In Nerdio Manager, navigate to Settings > Integrations.

2. In the User cost attribution tile, in the Report configs section, you can view all the available configurations.

   

3. Select the report configuration you wish to review.

   

4. Take note of the subscription and Log Analytics Workspaces.

If there is any overlap of subscription or LAW between two or more configurations, you need to reorganize your configurations to prevent this. Currently, User Cost Attribution does not support editing an existing configuration. Therefore, you need to create new configurations and use those when completing the next few steps.

Load multiple configurations into Power BI

When using the ‘Nerdio Reporting’ connector, you are prompted to nominate which configuration to load into the report. This is done by entering the name of the configuration into the appropriate field. Instead, you load in multiple configurations by similarly entering in the name of each configuration, each separated by a comma. For example, if you want to load the configurations Complete AVD deployment and Config1 into a single report, you would type the following: Complete AVD deployment, Config1

1. In Nerdio Manager, navigate to Settings > Integrations.

2. In the User cost attribution tile, in the Report configs section, make a note of the report configurations you wish to combine.

3. Create a list of all configurations, by writing their names separated by commas.

4. Open the Power BI template file, and use ‘Get Data > More’ to find and launch the ‘Nerdio Reporting’ connector.

   

5. Enter the following information:

   

   • Report Name(s): Enter the list of configuration names you previously created.

   • Base Url

   • PowerBI Access Key

     Note: These fields can be found through the ‘Power BI’ button on your report in the Nerdio Manager Cost Attribution dashboard.

6. Once you have entered all the desired information, select OK.

7. When prompted, continue through until you reach the list of tables to load into the report.

   

8. Select the AVD, CUR, and MAU tables.

9. If you have an ERR (error) table, select it to see a preview of which months you were unable to load data from. (that is, no built data).

10. Once you have made the selections, select Load.

Combine the configurations into a single report

If multiple configurations were named when loading in your report into Power BI, you should now have data from all those reports available and combined in your Power BI file.

1. Navigate to a Report page.

2. In the upper right corner, select the Report drop-down list to see a list of all the report configurations.

   

3. Optionally, you can use the Report drop-down list to select which reports are visible at any given time.

   Note: If you want to combine data from all the reports, ensure that all the reports are selected. Alternatively, if you want to view one report, deselect all reports other than the one you want to view.