Skip to main content

Hello,

I’ve been working on developing a Power BI dashboard that pulls the APM Data Model for InField to visualize our progress on checklists, however I’m running in to some road blocks. Currently for me to pull checklists, checklist items and measurements in power BI, I have to expand the related table columns within power BI rather than just being able to load each table individually and relate them to each other. Ideally I’d like to have the relationships to other tables as part of the model pull without needing to expand related tables as that’s creating significant folding in the data pull.

Example:

  • Loading the checklist table and expanding the checklistitems table and then the measurements table takes roughly 45 minutes to load due to the folding that occurs in the data load
  • If I load these 3 tables separately, it takes under 1 minute - however I then don’t have any way to relate the values from each table to each other. The checklist items table does not have the checklist externalId that it relates to and the measurements table does not have the externalId of the checklist item that it relates to. Because of this, I cannot load them individually….
    • The sourceId shown in the checklist item table doesn’t relate back to the checklist table at all. Seems to be related to the template item table instead

This inefficiency in expanding the related tables coupled with the fact that the source tables are missing the necessary values renders the Power BI connector almost useless. Am I doing something wrong, or are others encountering this same issue?

Hey rmaidla, I’ve raised this exact issue here. Unfortunately you’re right, we don’t have any other way to relate the tables without expanding the columns.

Hi @rmaidla and @Tatiana Marie Sakata , thank you for raising this issue. The limitation you're facing is due to the OData connector used by Power BI, which our Cognite Power BI connector depends on. Unfortunately, Microsoft controls this connector, and its development seems to be currently paused.

 

After discussions with other users, we've determined that using the domain data model with our FDM OData service is not feasible due to performance issues. Instead, the best approach is to convert the necessary data into a Sequence or a "flat" data model (DM). This involves using direct relations without edges, essentially treating the DM like a simple table with primitive variables (e.g., strings, floats). You can create a "curated" view of the data, periodically populated with functions or transformations, to avoid expansions.

 

While this workaround may not be ideal, it's the most effective solution we can offer at this time. We appreciate your understanding and are here to help with any further questions or support you might need.

 

Thanks and regards

Elka

@Elka Sierra How this solution can be applied? In my case anything too complicated isn't a solution since the end users aren't software engineers or seasoned data analysts.

Reply


Terms & ConditionsCookie Settings