Skip to main content

Power BI: Retrieve data from a CDF data model

Connect a Cognite Data Fusion (CDF) data model as a data source and use Power BI to query, transform, and visualize the CDF data.

info

Before you can sign in with your organizational ID and retrieve data from CDF, an administrator needs to register and consent for your organization to use Microsoft Power Query for Excel.

To access the data in a data model, you need these capabilities on a project or per space level: dataModelsAcl:read, dataModelInstancesAcl:read. See

See Configure access to data models.

To access the metadata of CDF core elements, such as time series or files, you need to have access to the element on a project or data set level. See Assign capabilities.

Connect the Power BI connector to a data model​

  1. Before you start: To connect to a data model with the Cognite Power BI connector, you need to know the name and version of the data model and CDF project where it's being used. To find the information:

    1. Navigate to fusion.cognite.com, sign in to the CDF project, and navigate to Explore > Model your data.

    2. Select the data model, and then select the Power BI button.

    3. In the dialog box, copy and make a note of the project name and CDF environment.

      Get settings for the Power BI connector

  2. Open Power BI Desktop and sign in with your organizational account.

  3. On the Home tab in the ribbon, select Get Data and then More. Then, in the Get Data dialog box, select Other and the Cognite Data Fusion connector.

    Get data

  4. Enter the information you noted about the data model and CDF project in the Project Name and CDF: Environment fields.

    Optional: To sign in with a specific Microsoft Entra ID (formerly Azure Active Directory) tenant ID, append the tenantid={tenantId} parameter to the project name URI in this format:

    {projectName}/models/spaces/{space}/datamodels/{datamodel}/versions/{version}?tenantid={tenantId}

    • Where {tenantId} is the GUID for the Microsoft Entra ID tenant you want to use, for example, d1ce9074-3079-488a-9a2d-f0522babd7a9.

    To find the Microsoft Entra ID tenant ID, contact your admin, or follow the steps in this article.

  5. In the Authentication dialog, select Organizational account and then Sign in with your organizational ID.

    Authentication

  6. The Power BI connector lists all the types for the selected data model.

    Each Type in the data model displays as a table in Power BI with each Instance of the type as a row.

  7. Select a table (type), and then select Transform Data.

    caution

    The table can contain a large number of entries. Don't select Load unless you want to load all entries.

    Navigator

  8. You can now transform the data with the Power Query Editor.

    To limit the data set, you can, for example, select Keep Rows > Keep Top Rows and specify the number of rows to keep.

    Navigator

    To load the resulting values into Power BI, select Close and Apply.

info

Power BI lets you expand a related item to flatten a table. This can have significant negative impact on performance and we recommend that you don't use this functionality.

caution

Presence of the special characters ! * ' ( ) ; : @ & = + $ , in either space or externalId might prevent Power BI from successfully reading data from CDF. Alphanumeric characters and special characters - _ . ~ are safe to use.

Filter items in data models​

Filtering is vital to create workable data sets in Power BI. We recommend that you apply filtering in Power Query rather than in the Power BI report/dashboard. This improves performance and makes it easier to work with reusable data sets.

The Power BI Connector pushes the filters to CDF via the Cognite OData service. CDF performs the filtering and returns only the matching rows to Power BI. See also Filter CDF data in Power BI.

Data modeling pushdown filters​

TypePushdown
Model propertyYes, except EndsWith and Contains
Time series metadataNo
Data and string pointstimestamp only
Files metadataNo

Power Query performs EndsWith and Contains string filtering operations in memory.

JSONObjects are presented as text values in Power BI.

Working with time series and data points​

Data modeling supports getting Timeseries from data models. To get data points for a specific time series, you can navigate to string- or dataPoints. To get data points from a time range, filter the data point on a timestamp value. CDF doesn't support filtering time series on their values.

Time series can contain highly granular data. To improve performance and reduce the data load, you can use the CDF dataPoint Aggregate function, similar to Get aggregates for a single time series.

Power BI automatically converts a Unix Epoch timestamp value to a UTC DateTime value.

To create a visualization that shows two last days of data from a time series, we recommend that you used the aggregate function for the Time Series. With Power Query, you can specify dynamic values that update on every refresh of the data set. for example, the DateTimeZone.UtcNow() Power Query function always returns the current date and time in UTC time. By combining it with Date.AddX functions like Date.AddDays, you can create a dynamic range for the query to push down to CDF as an aggregate function call, resulting in a quicker data refresh.

When using dates as a filter of function parameters, use the PowerQuery DateTimeZone type.

Learn more:

Changing the signed-in user​

When you have signed in, Power BI stores your data source credentials. If you want to use a different user to access the data model, you can delete the stored sign-in credentials for the data source:

  1. In Power BI Desktop, navigate to File > Options and settings > Data source settings.
  2. Edit or delete the sign-in credentials for your data sources.
Last updated