Overview

When using Power BI for the first time with Cosmos there are a few setup steps to go through to ensure that Cosmos can connect to your Power BI tenant to read in your team's Power BI Workspaces and Reports, as well as ensure that Power BI can connect to your Cosmos data. This article walks through the steps to initially configure Power BI to work with Cosmos.

It is important to note that to complete this process you must have the Global Administrator role assigned to you in Azure Active Directory. This enables you to provide security consent on behalf of your organization.

Details

Assign Power BI Roles to Users

For Cosmos users to be able to view and/or create Power BI reports they must be assigned the proper role in the Cosmos Portal to be able to do so. The two roles related to Power BI are:

• API User: Users with this role will be able to leverage the Cosmos API to pull data out of Cosmos and into tools such as Power BI or Excel. Any user that will be creating and publishing Power BI reports will need to have the API User role assigned to them to create a connection to your Cosmos data.
• Power BI Viewer: Users with this role will be able to open and view Power BI reports in the Cosmos Portal that have been shared with them. Users without this role will not see the Power BI menu under Reports and will not be able to interact with Power BI reports from within the Cosmos Portal.

To assign roles to your users, navigate to the Administration menu and click Users. From there you will be able to select the dropdown for the users and add the necessary Power BI roles for them.

More details on modifying roles can be found in the article here: How to Assign Roles to a User.

Enable Cosmos to Read Power BI Workspaces and Reports

For Cosmos to be able to read the Power BI Workspaces and Reports that have been shared with your users, consent must be given for Cosmos to be able to do this. To provide Cosmos with this required access, navigate to the Administration section and click Power BI. Next, click the Grant Consent button. 

You will then be prompted to accept permission for Cosmos to read in data from Power BI. Click Accept and the window will close and inform you that consent was successfully granted.

Next, you will navigate to the Reporting menu and click Power BI.

The first time this is done you will be prompted to allow Cosmos to read the Power BI Reports and Workspaces for your users. Click the Consent on behalf of your organization checkbox and click Accept. This will ensure that none of your other users will be asked for this in the future.

Enable Power BI to Read Data from the Cosmos API

Once permission has been granted so that Cosmos can read data from Power BI, Microsoft also requires that permission be granted for Power BI to read data out of the Cosmos API. 

To grant this permission, open the Power BI Desktop application click Get Data, and select OData Feed as the connection type. Click the Connect button at the bottom of the Get Data window.

When prompted for the URL, enter: https://api.cosmosdatatech.com/odata and click OK.

When asked how to authenticate, select Organizational Account and then click Sign In and log in with your Microsoft Account. Once you have successfully signed in with your Microsoft Account click Connect. 

You will then be prompted to allow Power BI to read data from the Cosmos API. Be sure to click the Consent on behalf of your organization box and click Accept. This will ensure that none of the other users in your organization will be prompted in the future.

You will then see all the tables available from Cosmos and can start building and publishing Power BI reports against your Cosmos data.