Calculating Aggregated Timeseries Data
Last updated
Last updated
The Timeseries
data function in the ENLYZE Power BI Connector allows you to query machine data within a defined time range.
This tutorial demonstrates how to retrieve time series data based on other data structures that define time ranges (such as production runs) and aggregate these data to calculate key performance indicators (KPIs).
We will walk through the process of calculating the specific energy consumption for each production order in a plant with five production lines. This requires machine variables that measure:
Line throughput (amount of product processed)
Effective power consumption (energy used per production line)
Query time series data for throughput and power consumption during each production order.
Aggregate the data to obtain total production output and total energy consumption per order.
Calculate specific energy as:
You will need the following from the ENLYZE Power BI Connector:
The Timeseries
function
The Variables
table
The Production Runs
table
The Production Runs
table is specific to this example. Any table that includes start
and end
timestamps in separate columns can be used instead.
Before querying time series data, you must filter the relevant machine variables.
Create a copy of the Variables
table and name it FilteredVariables
.
Filter the table to keep only the variables relevant to your analysis.
Sort the variables by display_name.
The next step is to create a modified version of the Production Runs
table with an additional column for time series data.
Before proceeding, filter the Production Runs table to include only relevant runs. This will reduce processing time.
fxGetMachineVariables
Since each production order is linked to a specific machine, we need a function to retrieve the correct variables for each machine.
Create an empty query and name it fxGetMachineVariables
.
Define a Power Query M function that filters FilteredVariables
based on machine UUID:
fxTimeseriesForRuns
Next, we define a function to apply the Timeseries
query to each row of the Production Runs
table.
Create an empty query named fxTimeseriesForRuns
.
Define a Power Query M function that retrieves time series data for each production run:
TimeseriesPerProductionRun
TableCreate an empty query named TimeseriesPerProductionRun
.
Apply the fxTimeseriesForRuns
function to the Production Runs
table:
The resulting TimeseriesPerProductionRun table will contain all production runs, with an additional timeseries column storing the corresponding time series data.
Now, we aggregate the retrieved time series data to calculate total throughput and total energy consumption per production run.
fxAggregateTimeseries
This function takes the run table with “timeseries” column (TimeseriesPerProductionRun
) from the previous part as input and aggregates the values in additional columns.
In our example, we use List
.Sum to aggregate, but other methods can be used like average
, min
, max
, first
or last
.
Create an empty query named fxAggregateTimeseries
.
Define a Power Query M function that calculates aggregated values:
Transform TimeseriesPerProductionRun
by applying the aggregation function:
Congrats! You have successfully queried time series data for each production run and aggregated the values.