Setup and administration for Power BI and Excel

You can use OpenID Connect and your existing identity provider (IdP) framework to manage access to CDF data securely. We currently support Microsoft Entra ID, Microsoft's cloud-based identity and access management service.

This article explains how an Microsoft Entra ID (ME-ID) administrator can register and consent for an organization to use the Microsoft Power Query for Excel application and allow the Cognite Power BI OData Connector to impersonate and act on behalf of a user to retrieve data from a CDF project.

When you have registered the Microsoft Power Query for Excel application, users can sign in with their organizational ID and use the Cognite Power BI OData Connector to retrieve data from a CDF project.

NOTE

The Cognite Power BI OData Connector and Excel both use the Microsoft Power Query for Excel enterprise application to retrieve data from CDF. If your organization is using both the Cognite Power BI OData Connector app and Excel to retrieve data from CDF, you only need to register Microsoft Power Query for Excel for one of them.

Register the Microsoft Power Query for Excel application

Follow the steps below to register the Microsoft Power Query for Excel application and allow Microsoft Excel and Microsoft Power BI to use it to impersonate and act on behalf of a user to retrieve data from a CDF project:

1. First, make sure that you have:

   • Registered the Cognite API and the CDF portal application in Microsoft Entra ID.
   • Set up Microsoft Entra ID and CDF groups to control access to CDF data.

2. Open Microsoft Excel.

3. In Excel, select Data > Get data > From Other Sources > From OData Feed.

4. Enter the URL for the OData Feed you want to connect to and click OK. Read more about the OData services on their dedicated documentation page. If you need to sign in to Microsoft Entra ID as a guest user, you might also have to add the tenant ID as a query variable at the end of the URL, for example, oDataUrl?tenantID=MyEntraIDTenant.onmicrosoft.com.

   

5. In the Authentication dialog, select Organizational account and then Sign in with your organizational ID. Only supported for CDF projects using Microsoft Entra ID Idp.

   

6. Sign in with an Azure Global Administrator role account, review, and consent to the permission request on behalf of your organization, and select Accept.

   

7. The Microsoft Power Query for Excel application has now been registered in Microsoft Entra ID. To test that the connection is working, select Connect.

8. In the Navigator, you should see the tables and data you have access to in CDF.

   

9. Verify that the configuration is successful: Sign in to Power BI Desktop with a non-admin identity to confirm that regular users in your Microsoft Entra ID can sign in and retrieve data from the CDF project.