Skip to main content

Capabilities and limitations

Early adopter

The features described in this section are currently in Beta and are only available to customers via our Early Adopter program. For more information and to sign up, visit the Early Adopter Group on the Cognite Hub.

The Power BI PostgreSQL connector lets you load datapoint aggregates on demand using DirectQuery, drastically reducing the amount of data loaded into Power BI.

When configuring a dashboard to use data point aggregates in DirectQuery mode, the connector only retrieves aggregates related to the currently selected time range. With automatic granularity, updating a chart takes a relatively short time whenever the filtering changes.

In DirectQuery mode, Power BI queries the data source when it populates visuals in a dashboard. DirectQuery combines the filters defined in the data source in the Power Query Editor with any additional filters included in the dashboard itself, such as time slicers, external_id selectors, unit, etc.

Prerequisites

Time series must be compatible with DirectQuery and have a value as-is when requesting aggregated data points.

Examples of compatible time series:

  • The time series is the current value of some equipment that needs to be visualized.
  • The time series is a cumulative sum of some output or derivative, i.e., flow rate.
  • The time series is the result of some computation.
  • The amount of data requested at once is likely to load on demand without reducing the user experience. We recommend letting the connector determine the granularity automatically.

Examples of in-compatible time series:

  • A time series where the data points aren't immediately valuable when visualized and the result requires computation on multiple time series together.
  • The value of the time series comes from combining it with data from a different source that requires alignment on, for example, a timestamp, and where the timestamp is not immediately available in the other source.

Capabilities: Resources

Assets

Recommended connection mode: Import

ColumnType
external_idtext NOT NULL
nametext
descriptiontext
parent_external_idtext
sourcetext
metadatajson (interpreted as text)
created_timetimestamp_with_timezone NOT NULL
last_updated_timetimestamp_with_timezone NOT NULL

Time series

Recommended connection mode: Import

ColumnType
external_idtext NOT NULL
nametext
descriptiontext
unittext
is_stepboolean NOT NULL
metadatajson (interpreted as text)
asset_external_idtext
created_timetimestamp_with_timezone NOT NULL
last_updated_timetimestamp_with_timezone NOT NULL

Datapoint aggregates

Recommended connection mode: DirectQuery

Requires one or more timeseries_external_ids to display any data. You can retrieve this in the dashboard by declaring a many-to-one relationship between datapoint_aggregates.timeseries_external_id and timeseries.external_id

ColumnType
timeseries_external_idtext NOT NULL
timestamptimestamp_with_timezone NOT NULL
granularitytext NOT NULL
averagedouble precision
countinteger
maxdouble precision
mindouble precision
sumdouble precision
continuous_variancedouble precision
discrete_variancedouble precision
total_variationdouble precision
interpolationdouble precision

Capabilities: Filtering

SQL queries made by Power BI are converted to CDF queries. CDF resources may have multiple different endpoints for querying, and the filtering support for each endpoint may vary. A query made to the Power BI connector will only be sent to a single endpoint per query. Therefore, some filter combinations cannot be processed even if each component in a filter is supported individually. For example, a query with an external_id StartsWith filter cannot also use an external_id Union filter. That would require sending the request to both the list and get by id endpoints and then filtering the results.

Terms

  • RangeFilter: Satisfies if the value is between min and max, i.e., WHERE X >= min AND X <= max in SQL
  • StartsWith: String starts with pattern
  • Union: List of values to match on a column, including variants. For example "value in ('a', 'b', 'c')" or "value = 'a' or value = 'b' or value = 'c'"

The following tables describe the filtering capabilities of each table.

Assets table

ColumnFilter endpointByExternalIDs endpointNo filter support
external_idStartsWithUnion
nameX
descriptionX
parent_external_idUnion
source=
metadataX
created_time>=, >, <=, <, RangeFilter
last_updated_time>=, >, <=, <, RangeFilter

Time series table

ColumnFilter endpointByExternalIDs endpointNo filter support
external_idStartsWithUnion
nameX
descriptionX
unit=
is_step= (true/false)
is_string= (true/false)
metadataX
asset_external_id=
created_time>=, >, <=, <, RangeFilter
last_updated_time>=, >, <=, <, RangeFilter

Datapoint aggregates table

ColumnCDF endpointNo filter support
timeseries_external_idUnion
timestamp>=, >, <=, <, RangeFilter
granularity=
continuous_varianceX
discrete_varianceX
total_variationX
interpolationX
averageX
countX
minX
maxX
sumX