Niagara PRO TIPS: History Rollup (Part 1)

BQL and Web Chart Sampling

Overview

This guide covers how to roll up granular history data (e.g., 15-minute intervals) into less frequent records (daily, monthly) in Niagara 4. Part 1 focuses on BQL history rollup functions and web chart sampling.

Niagara PRO TIPS: History Rollup (Part 1) - BQL and Web Chart Sampling

Video Tutorial: Niagara PRO TIPS: History Rollup (Part 1)

Overview

This guide covers how to roll up granular history data (e.g., 15-minute intervals) into less frequent records (daily, monthly) in Niagara 4. Part 1 focuses on BQL history rollup functions and web chart sampling.

Method 1: Using History Chart Builder

Step 1: Access the Chart Builder

• Open the legacy AX History Chart Builder view

Step 2: Configure Your Chart

1. Select a history source (e.g., "main kW")

2. Choose your chart type from the available options

3. Select a time range (e.g., "last week")

4. Choose a rollup function from the dropdown (default is "none")

   • Options include: daily average, hourly, monthly, etc.

Step 3: Build the Chart

• Click Build to generate the chart

• The result will show aggregated data (e.g., bar chart with daily averages)

Step 4: Save the Chart (Optional)

• Click the Save button to save as a PX file

• This allows you to view and modify the underlying BQL query

Method 2: Using BQL History Rollup Functions

Understanding the BQL Syntax

Basic Syntax:

bql:history:[path/to/history]?period=[timeRange]|bql:history:HistoryRollup.rollup(baja:HistoryRollup$Interval#[interval])

Example:

history:mainKw?period=lastWeek|bql:history:HistoryRollup.rollup(baja:HistoryRollup$Interval#daily)

Available Rollup Intervals (Enum Values)

Use these tag values in your BQL syntax:

• 1 minute

• 5 minutes

• 15 minutes

• Hourly

• Daily (ordinal #6)

• Monthly

• Yearly

Alternative Syntax: Using Relative Time

Instead of enum values, you can specify milliseconds:

baja:RelTime'[milliseconds]'

Example (1 day = 86,400,000 ms):

baja:RelTime'86400000'

⚠️ Note: Using enum values (daily, monthly, yearly) is preferred as they automatically account for:

• Daylight saving time changes

• Varying days per month

• Leap years

Method 3: Using BQL with Bound Tables

Step 1: Create a Bound Table

• Add a bound table component to your PX view

Step 2: Add Table Binding

• Apply the same BQL query with history rollup function

• The query returns a table with the following schema:

History Schema Columns

⚠️ Important: Schema names are camelCase (e.g., endTimestamp, not EndTimestamp)

Method 4: Using Chart Bindings

Step 1: Add Chart to PX View

• Create a chart pane with a bar chart (or other chart type)

Step 2: Configure Chart Binding

1. Add a table chart binding

2. Paste your BQL query with history rollup function

3. Specify the series name (e.g., "Average")

4. Set a brush color for the series

Step 3: Map Columns

• X Column: Select tableColumn → timestamp

• Y Column: Select tableColumn → Choose from schema (average, min, max, sum)

Step 4: Add Multiple Series (Optional)

• Copy the chart binding

• Change the Y column to a different schema value (e.g., min)

• Assign a different color to distinguish series

BQL Time Range Syntax

Floating Time Periods

?period=today
?period=lastWeek
?period=lastMonth
?period=lastYear

Fixed Time Ranges

?start=[startTime]&end=[endTime]

Time Format:

YYYY-MM-DDTHH:mm:ss.SSS[timezone]

Example:

?start=2024-01-01T00:00:00.000-05:00&end=2024-01-31T23:59:59.999-05:00

Method 5: Web Chart Sampling

Automatic Sampling

Web charts automatically apply rollup based on data volume:

• Today: Shows all 15-minute records

• Last Month: Automatically rolls up to 30-minute records

• Last Year: Automatically rolls up to 1-day records

Manual Sampling Configuration

Step 1: Access Sampling Settings

• Click the gear icon in the chart corner

• Select the Sampling tab

Step 2: Configure Sampling

1. Enable "Use sampling"

2. Select aggregate function:

   • Average

   • Min

   • Max

   • Sum

3. Choose time range:

   • Select from defaults (1 hour, 3 hours, etc.)

   • Or select Custom and enter specific interval

Chart Type Considerations

Line Charts:

• Can display more data points clearly

• Less aggressive automatic rollup

Bar Charts:

• More aggressive automatic rollup

• Today: Rolls up to 30-minute records

• Last Month: Rolls up to daily records

Key Takeaways

✅ Multiple methods available for history rollup in Niagara 4
✅ BQL provides flexible querying with standard aggregate functions
✅ Use enum intervals (daily, monthly) for date-aware rollups
✅ Web charts offer automatic and manual sampling options
✅ Chart type affects automatic rollup behavior

Coming in Part 2

Part 2 will cover:

• Series transforms for rollup functions

• Using analytics for data aggregation

Presenter: James Johnson
Series:Niagara PRO TIPS
Part:1 of 2

YouTube: http://www.youtube.com/@TridiumInc