Derived Streams From Expressions Troubleshooting Tips

Quick Tips

• The Modified column displays when a dependent's stream was last appended. Does the date and time make sense for each stream?
• The Head and Tail columns indicate the time range of data for each stream. Do the streams have data? Do the ranges make sense?
• Compare the Calculated Up To column dates. Derivation will not run if the derived stream's date is the same or later than its immediate dependents. Walk down the tree, while looking at the Calculated Up To column, to find the stream that is the one blocking derivation of its parents.
• Ensure the derived stream's Calculated Up To date is not set to the future. If so, it will block derivation until the current time reaches the Calculated Up To date
• Click the Drill Into button to see all of the data being used for the derived stream.
• The Deps Changed column indicates how many dependent changes have occurred since the last derivation. The value must be greater than zero for a derivation run attempt to be made. It will not reset to zero until a successful derivation was made. A large negative number indicates it's a kicker stream. Kicker streams force derivation attempts.
• Look over each derived stream's settings in the right-side pane and ensure they are not causing any issues.
• Look at the derived stream's expression in the right-side pane and ensure there are no issues. Copy and paste the expression while adding an equal sign at the beginning, into an free Excel Formula Beautifier online to make it easier to read. Click the Expression Functions and Details button and ensure all functions used in the expression are being used correctly.
• Consider all of the dependent stream's settings and whether they are impacting the results. Settings such as constraints, time filters, stream types, value types. Value type conversions may cause issues (i.e. converting a string to a number).

Derived Stream Results are Wrong

• By default, a row is not calculated if any variables in the expression are NULL.
• A NULL result removes the sample in the store with the same sample time.
• Use the Drill Into button to view the values being used. Each row in the Drill Into window is evaluated using the derived stream's expression. Ensure no dependent variable values are NULL (or empty) for each row. The Drill Into window does not apply Offsets.

• The expression syntax is verified during the saving of a component. Errors are displayed to the user during the save.
• Expression errors while deriving a row are logged:
  • As Job Notification errors when processed by job servers
  • As System Notification errors when derivation is manually started from within the website studio
  • Not logged at all during MQTT Publish calls and HTTP API Feed PUT calls. The error will be detected and logged by the job servers every minute or two

The Stream is not Deriving

A stream will derive when its expression dependents append data or modify historical values. It's a simple concept, but things become complex with different stream types, different value types, sample times differ and data arrival times differ.

Derivation typically does not recalculate all data, from the head to the tail, every time derivation runs. Normal derivation is an appending process. It will only derive from its last Calculated Up To date. This keeps derivation scalable and fast. There are exceptions. The next sections goes over them.

• Every minute or two by the job servers.
• When any derived stream's dependent's data is appended or modified within the web studio.
• Manually when the Re-derive Stream button, or menu item, is clicked within the website studio.
• During an MQTT API Feed Publish that appends or modifies stream data
• During an HTTP API Feed PUT that appends or modifies stream data if the derMaxS parameter is set.
• Any of the stream's derivation settings have changed. The entire stream will be re-derived

• When the current time is later, or equal to, the derived stream's effective date.
• There are no dependents. Just a constant in the expression. This is called a kicker stream. It is used as a dependent to other streams to force calculates up to the current time.
• ALL_ARRIVE (default) setting:
  • All time-series dependent sample times must align for an expression evaluation to occur. The interval's endDate is used for interval streams. A point stream value is used for every evaluation. Use rollup cycles and functions for variable definitions to assist with sample time alignments when using regular (non-interval) streams.
  • For Interval Derived Streams: When its "Calculated Up To date is less than the earliest dependent's Calculated Up To date. It will derived up to the earliest dependent's "Calculated Up To date.
  • When any dependent had a historical change. The derived stream's data will be deleted from the earliest dependent historical change just prior to a derivation attempt. This resets the derived stream's Calculated Up To date to the start of the earliest historical change.
• ANY_ARRIVE setting:
  • No sample times have to align for derivation to occur. But, when they do, they are used within the same expression evaluation. The interval's endDate is used for interval stream's. A point stream value is used for every evaluation. Use rollup cycles and functions for variable definitions to assist with sample time alignments when using regular (non-interval) streams.
  • This setting should only be used for special circumstances. It is the cause of many "not deriving" issues.
  • This setting can make it very difficult to have repeatable results because results depend on when data arrives.
  • If dependents arrive at different times, every result will be NULL unless the expression is using the isNull(..) function.
  • When its Calculated Up To date is less than the latest dependent's Calculated Up To date. It will derived up to the latest dependent's Calculated Up To date. It will not re-derive that range if another dependent has data arrive within that range later. This is a case where results might change as data arrives when using the ANY_ARRIVE setting.
  • When any dependent had a historical change. The derived stream's data will be deleted from the earliest dependent historical change just prior to a derivation attempt. This resets the derived stream's Calculated Up To date to the start of the earliest historical change.
• Point Streams
  • Point streams are treated as constants
  • Point Stream as a Dependent:
    • Changing the Point stream's value only impacts derived values going forward.
    • A change to a dependent Point stream's value will not trigger a parent derivation.
  • A Derived Point Stream (precedent):
    • ALL_ARRIVE: The point stream's Calculated Up To date has to be earlier than the latest dependent's Calculated Up To date. It will then calculate up to the earliest dependent's Calculated Up To date
    • ANY_ARRIVE: The point stream's Calculated Up To date has to be earlier than the earliest dependent's Calculated Up To date. It will then calculate up to the earliest dependent's Calculated Up To date

FK-Resolved Dependencies (Resolution SQL)

Some variables use a Resolution SQL to dynamically determine which stream to read from at derivation time. These are called FK dependencies. In the diagnostic tree, FK dependencies are marked with (FK) in the Var Name column. If the FK has not yet been resolved by the background job, it shows as (FK?) with the message "Waiting for background resolution."

• The background job resolves FK dependencies approximately every 10 minutes. If the stream was just created or saved, wait for the next resolution cycle.
• If it stays unresolved, check the Resolution SQL displayed in the right-side details pane. Ensure the SQL is valid and returns results. Common issues:
  • Misspelled table or column names in the SQL
  • The SQL references a component that does not exist
  • The SQL uses @_component_uid but the owning component has no matching row in the referenced table
• Check Job Notifications for FK resolution errors. The background job logs failures with the component and stream name.

• The Resolution SQL determines which stream to read from. Review the SQL in the details pane and verify it returns the correct component UID and stream ID for the current relationship.
• If the relationship recently changed (e.g., a meter was reassigned to a different customer), the background job re-resolves FK dependencies every ~10 minutes. The resolved stream will update on the next resolution cycle.
• After re-resolution, the old precedent link is removed and a new one is created on the correct source stream.

• FK dependencies require precedent links to trigger derivation on data arrival. Precedent links are created by the background job during FK resolution.
• Check the Deps Changed column on the parent derived stream. If it stays at zero after the FK source stream gets new data, the precedent link may not exist yet.
• Wait for the next background job resolution cycle (~10 minutes) or re-save the component to trigger precedent link creation.