User Cost Attribution Power BI Publishing Guidance

To be able to share your Cost Attribution Power BI report with your organization without sharing the individual Power BI Desktop file, the best solution is to publish the report to the Power BI platform and share it with your organization there.

However, because the Cost Attribution data for Power BI is provided by a custom connector, to support actively refreshing the data for a published report, clients also need to install an On-Premises Data Gateway on to a server with the connector file, and configure a few settings on the Gateway and in the Power BI platform.

The following document outlines the key requirements and the primary steps needed to enable you to share your Cost Attribution report with your organization on Power BI.

If you have any questions or concerns about this document, please reach out for support or refer to the following documentation:

• Use custom data connectors with the on-premises data gateway - Power BI | Microsoft Learn

• https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-install

• https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem

Assumptions

This document assumes the following:

• The user has active access to Nerdio Manager Premium and is familiar with the User Cost Attribution feature.

• The user has an understanding of the Power BI Platform and how to share published reports with their organization through that platform.

• The user has a Power BI environment with the minimum license needed to share reports with a group of users or organization.

• The user is capable of configuring the Gateway to work around firewall, networking, and device permission requirements, or communicating with their administrator to do so.

Requirements

You need the following to begin publishing your report:

• The Power BI Desktop application.

• A valid Microsoft Entra account that is able to access your organization’s Power BI environment.

• The NerdioReporting Custom Connector.

• A server that is able to support the On-Premises Data Gateway application.. This server needs to be available and online to support scheduled refreshing of published data.

Solution Architecture

At a high level, the solution can be simplified down to:

• The User Cost Attribution App Service lives in Azure. It not only builds the report, but also controls the movement of the report files in and out of the Storage Account. The Nerdio Manager app leverages this App Service to display report data on its page.

• The Custom Connector lives on a computer/machine and interacts with the UCA App Service to import report files into a Power BI Desktop file (.pbix).

• The On-Premises Data Gateway lives on a computer/machine, and leverages the Custom Connector to import report files into a workspace on the Power BI platform.

The following diagram illustrates this solution architecture, highlighting how data moves from the underlying app service that creates the User Cost Attribution report data into a Power BI workspace.

Publish the Report via Data Gateway

The following steps outline the process of publishing the Power BI Desktop file to the Power BI platform and setting up the On-Premises Data Gateway to allow the data provided by the ‘Nerdio Reporting’ Custom Connector to be actively fed into Power BI.

Please ensure you complete the steps on the server you would like to use the On-Premises Data Gateway with. To support active refresh and/or scheduled refresh of report data in Power BI, the Gateway needs to be constantly:

• Powered on

• Connected to the internet

• Signed into a valid M365 account with a Power BI license and access to the needed workspace

For an additional overview of this Gateway and larger process, please also see the following document:

Use custom data connectors with the on-premises data gateway - Power BI | Microsoft Learn

To publish the report via Data Gateway:

1. On the server you would like to use the On-Premises Data Gateway with, install the On-Premises Gateway application.

   Note: See https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-install for additional information.

2. When prompted, log in to the Gateway using your organizational account. This account should also be able to access your organizations Power BI environment.

3. Within the Gateway, select the Connectors blade.

   

4. In Load custom data connectors from folder, browse and select the folder for the Gateway to look at.

   Notes:

   • Select either a new location that you are able to access on this computer/server or continue to use the default one.

   • Ensure the Connector is loaded into the Gateway. You should see the ‘NerdioReporting’ Connector in a table under the Connectors table if successful.

   • For standard Gateway users, the file path cannot be within the ‘Users’ folders on the device.

5. Copy the latest connector file into the same folder that the Gateway is currently looking at.

   Tip: It is recommended that your Power BI file uses the same version of the connector to load data.

6. Restart the Gateway.

7. Confirm that you see in the ‘Connectors’ blade that the Gateway is now able to identify the ‘NerdioReporting’ connector file.

   

   Warning: From this point onwards, do not close the on-premises data Gateway application, and ensure that an account with valid access to your environment’s workspace is logged in the Gateway.

8. Open a Power BI Desktop file that uses your connector file.

9. From the tools bar, select Publish.

10. Publish to an appropriate Workspace within your organization.

    Note: If you do not have an appropriate, available Workspace to publish to, create a Workspace first from within the Power BI platform.

11. In the Power BI website, navigate to the Workspace you just published the report to.

    

12. You should see a copy of the Report, as well as a Semantic Model or dataset with the same name.

13. Hover over the Semantic Model and select Schedule refresh.

14. Navigate to the Gateway and Cloud Connections tab.

    Note: If this tab does not exist, then it is likely the Gateway was not able to sync. Restart the Gateway and confirm it has properly loaded the connector.

    You can also check Settings > Manage Connections and Gateways in Power BI to confirm if the connection to the Gateway has been established.

    If this still does not work, refer to the error message at the top of the screen, and reach out for support.

15. Locate the Gateway you wish to work with and select Settings.

    

16. At the bottom, in the Power BI section, ensure that both options are selected and select Save.

17. In the Power BI website, navigate to the Workspace.

18. Locate the Gateway you wish to work with and select Settings.

19. Select the down arrow to expand the data sources.

    

20. Select Manually add to gateway.

21. On the new window, wait for the Gateway and Cluster options to be automatically populated.

    

22. Enter the following information:

    • Connection name: Type a name for the connection.

    • Configuration Name: Type/paste exactly as on the Power BI report.

    • Base Url: Type/paste exactly as on the Power BI report.

    • Power BI Access Key: Type/paste exactly as on the Power BI report.

    • Skip test connection: Be sure to select this option.

23. Once you have entered all the desired information, select Save.

24. In the Power BI website, navigate to the Workspace.

25. Locate the Gateway you wish to work with and select Settings.

26. Select the down arrow to expand the data sources.

    

27. In the Maps to drop-down list, select the connection you created.

28. Select Apply.

You should now be able to manually refresh and Schedule Refresh for this Power BI file and data source. This also means you are now able to share the report within the organization as normal.

Please ensure you refresh through the Semantic Data Source and on the visuals for the Report itself.

Share the Report from Power BI via Excel

Once a report exists in Power BI, the underlying dataset can be exported to an Excel file and interacted with in the form of a pivot table. This process creates an Excel file within your organization’s OneDrive. This file has a built-in connection to the Power BI dataset, allowing you to refresh the data inside the Excel file to match any changes that occur to the dataset in Power BI.

This process assumes that the steps described in the previous sections have been completed, and a refreshable UCA dataset exists in Power BI.

The following should be considered for all users following this process:

• A scheduled refresh has been set-up on the Power BI dataset to ensure that the latest data is always available when refreshing in Excel.

• When building the pivot table, refer to the original Power BI Desktop template file for reference on how the data is structured in the tables.

To share via an Excel file:

1. Open the report in your Power BI workspace.

2. Select Export > Analyze in Excel.

   

3. When prompted, select the option to open the newly created Excel file.

4. In the Security Warning band, select Enable Content.

   

5. Select the Pivot Table.

6. In the Pivot Table tab, select Refresh All.

   Tip: It is highly recommended to have the Power BI Gateway configured so that you can schedule a refresh of the data in Power BI to occur regularly.