It is often useful to construct Signal names using an amalgamation of several columns in the table or view of the RDBMS.  However, querying samples and capsules using these constructed names can have slow performance because these fields are not all defined as primary keys within the tables and views.  If the constructed names perform fine in the runtime query - that's fine and no additional work is needed.  But for the databases that query slow, with the Seeq SQL v2 connector, the users can have it both ways - a long descriptive name that performs well at runtime!  All that is needed is a column in the table or view is a primary key for the signal tag.  The example JSON configuration below shows a Signal that is created using 'SELECT DISTINCT' along with COALESCE to concatenate 3 columns in the view to create a variable called "signalTag".  Along with that, we created a "signalId" variable that holds the [PointInfoID] field, which is a Primary Key in this view; we used this next in the "Sql" field, shown as variable ${signalId}.  What about our friendly Signal name?  That is defined in the "Name" property using the ${signalTag} variable from the COALESCE statement.

    {
      "Name" : "SignalsFromTable",
      "Type" : "SIGNAL",
      "Sql" : "SELECT [vDATA_PointReading].[TimeStamp], [vDATA_PointReading].[Value] FROM [vDATA_PointReading] WHERE [CFG_PointInfoID] = '${signalId}'",
      "Enabled" : true,
      "TestMode" : false,
      "Variables" : [ {
        "Names" : [ "signalTag", "signalId" ],
        "Values" : [ [ "${result}" ] ],
        "Sql" : "SELECT DISTINCT COALESCE([vCFG_AverageInfo].[UnitID], '') + ' - ' + COALESCE([vCFG_AverageInfo].[PointName], '') + ' - ' + COALESCE(CAST([vCFG_AverageInfo].[PointDescription], '')  + ')', [PointInfoID] FROM [SiteInfoView].[dbo].[vCFG_AverageInfo]"
      } ],
      "Properties" : [ {
        "Name" : "Name",
        "Value" : "${signalTag}",
        "Sql" : null,
        "Uom" : "string"
      }, {
        "Name" : "Interpolation Method",
        "Value" : "linear",
        "Sql" : null,
        "Uom" : "string"
      }, {
        "Name" : "Maximum Interpolation",
        "Value" : "7day",
        "Sql" : null,
        "Uom" : "string"
      } ],
      "CapsuleProperties" : null
    }

Seeq's SQL v2 Connector provides a flexible interface to create human-readable signals that perform fast.  My recommendation is to start simple and then add complexity to address those aspects, as needed.

Overview

Often times two (or more) units that the same products and unit procedures. This is common in continuous processes that are running a specific "grade"; also in batch processes that are manufacturing the same product ID.  Since they are identical pieces of equipment or units, the “profiles” of critical process parameters are similar and we want to use profiles from multiple units for our reference profile.  In this example we will create a profile from 2 units - the same process can be applied for additional units ad infinitum.  Then we will apply this profile to a running batch to look for deviations in near real-time.

Step 1: Add Signals and Conditions to Display

Add the signals for profiling.  Then specify the modes of operation, or unit procedures, using Seeq's Condition logic to create capsules - there are various ways to do this using Seeq's Capsule logic Formula, Value Search, Custom Condition, Composite Condition, etc.  Shown below are temperature signals for two units R-401 and R-402, along with their corresponding unit operations.  Note that some capsules are overlapping in time, but don't worry, Seeq will handle this just fine.

Step 2: View the signals in Capsule Time

Here we use the Seeq trending tools to examine the profiles.  Capsule Time is a great way to do this. The 'Group' button in the menu can select the appropriate capsules to overlay the signals - and handle those pesky overlapping capsules on the different units.  We can filter out additional capsules if we want; but let's use them all in this example. Here are the profiles we’ll use to make our reference profile:

Step 3: Build the Reference Profiles

The key to building a reference profile with multiple signals and conditions is to use the Formula Tool.  The Reference Profile Tool in the Tools Pane provides a nice user experience for a single signal with a single condition - that approach won't work for us here.  We will create two unique profiles, upper and lower, but we’ll need 4 formulas so we can repeat the profile over both the R-401 unit procedures and the R-402 unit procedures.

We “selected” each capsule in the formula by creating a bunch of capsule groups that just contained a single capsule.  This approach works in all cases including overlapped capsules. Here’s the formula for the upper profile, +3 Standard Deviation, repeated over R-401 and we plotted the reference profile in capsule view.

Below is the Formula that we used, for easy cutting/pasting.  *Pro Tip* - Use the Start and End times from the Capsules Pane to define the capsule() times in the toGroup() function.  The times do not have to match the exact times of the capsule - they only need to fully enclose the single capsule.  Usually this gives us a little wiggle-room on the times.

This formula uses the referenceTable() function to build the profile across the 2 units and 2 signals.  We specified each capsule and each corresponding signal in each capsule then added them using addRows() to the referenceTable().  Then we told Seeq which condition to draw the reference profile using repeatOver() along with the statistic to calculate, +3 StdDev, in this case.

Important!  The referenceTable() function must include Bounded Capsules, i.e. those with specified maxDurations, otherwise you will get an error suggesting to use the removeLongerThan() function.  Make sure that the $c401 and $c402 conditions are either bounded or were created with the removeLongerThan() function.

$R401_1 = $c401.toGroup(capsule('2019-10-07T05:00-06:00', '2019-10-07T07:00-06:00'))
$R401_2 = $c401.toGroup(capsule('2019-10-07T09:00-06:00', '2019-10-07T11:00-06:00'))
$R401_3 = $c401.toGroup(capsule('2019-10-07T15:00-06:00', '2019-10-07T16:30-06:00'))
$R401_4 = $c401.toGroup(capsule('2019-10-07T16:30-06:00', '2019-10-07T18:00-06:00'))
$R401_5 = $c401.toGroup(capsule('2019-10-08T00:00-06:00', '2019-10-08T02:00-06:00'))
  
$R402_1 = $c402.toGroup(capsule('2019-10-07T05:30-06:00', '2019-10-07T07:30-06:00'))
$R402_2 = $c402.toGroup(capsule('2019-10-07T11:00-06:00', '2019-10-07T13:30-06:00'))
$R402_3 = $c402.toGroup(capsule('2019-10-07T14:00-06:00', '2019-10-07T15:15-06:00'))
$R402_4 = $c402.toGroup(capsule('2019-10-07T15:20-06:00', '2019-10-07T16:40-06:00'))
$R402_5 = $c402.toGroup(capsule('2019-10-08T00:30-06:00', '2019-10-08T02:30-06:00'))

$rt = referenceTable (2min)
   .addRows ($t401, $R401_1)
   .addRows ($t401, $R401_2)
   .addRows ($t401, $R401_3)
   .addRows ($t401, $R401_4)
   .addRows ($t401, $R401_5)
  
   .addRows($t402, $R402_1)
   .addRows($t402, $R402_2)
   .addRows($t402, $R402_3)
   .addRows($t402, $R402_4)
   .addRows($t402, $R402_5)
  
   .repeatOver($c401 , ReferenceTableStat.StdDev, 3 )
  
$rt

Repeat the above process for the R-401 lower profile.  *Pro Tip* - Use the 'Duplicate' functionality by clicking on the 'Item Properties' i-icon in the Details Pane.  Then we only need to change the "3" to a "-3" at toward the end of the formula to do a -3 Standard Deviation.

Repeat this process 2 more times for the R-402 upper and lower profiles.  We only need to change the parameter in the repeatOver() function from $c401 to $c402.  Here is everything plotted in Calendar Time as Reference Profiles and then as Boundaries after using the Boundary Tool.

And now with Boundaries:

Step 4: Apply Boundary to Running Unit

Lastly, let's apply this boundary to a currently running mode of operation or batch.  Let's focus on unit R-401 at the moment.  All that we need to do is add the currently running capsule to the existing 'R-401 Unit Procedure - Product X' Condition.  This can be done in a variety of ways using Seeq's Formula functions for modifying conditions.  Here I'll simply add a Capsule by drawing it using the Custom Condition tool.  Note, the result is a capsule that shows the boundary of the running batch, projected into the future.  This same approach can be applied to unit R-402.

Seeq is able to handle any arbitrary amount of units, signals, overlapping or not to create your reference/golden profiles.

Summary

There are many use cases where the user wants to do an aggregation over a periodic time frame, but only include certain values.  Examples abound: for cement calculate the average daily clinker production only when the kiln is running, for biotech pharma the standard deviation of dissolved oxygen only when the batch is running, etc.  Here our user is looking into equipment reliability for compressors.  She wants to calculate the average daily compressor power to examine its performance over time. 

Steps

1. Add the signal into Seeq.

2. Use the 'Periodic Condition'  or 'Formula' Tool to add a condition for days.  This doesn't have to be days, it can be any arbitrary time, but it is usually periodic in nature.  To use a custom periodic Condition, consider using the periods() function.  For example to do this for days, the formula is:

periods(1day)

As an example, to change this to 5-minute time periods the formula is:

periods(5min)

Here are how the days and statistics in the Capsule Pane look for her:

3. Find only the desired values to be used in the aggregate (e.g. Average) statistic.  From the values in the Capsule Pane she sees that the average compressor powers results are too low.  This is because all the time when the compressor was OFF, near 0, are included in this calculation.   She wants to only include times when the compressor is running because that will provide a true picture of how much energy is going into the equipment - and can indicate potential problems.

To do this, she creates a 'Value Search' for times to include in this calculation, in this case when the Compressor is ON or > 0.5kW.

4. Create a new signal to only have values *within* the 'Compressor ON' condition.  Use the aptly named 'within' function in the 'Formula' Tool.  Notice how the resulting Signal in the bottom lane only has values within the 'Compressor ON' condition.  Now she can use this because all those 0's that were causing the time-weighted Average to be low... are now gone.

5. Examine the resulting statistical values.  She now sees that the calculations are correct and can use this resulting 'Compressor Power only when ON' Signal in other calculations using 'Signal from Condition', 'Scorecard Metric', and other Seeq Tools!

Example using 'Signal from Condition' tool to calculate Average daily Compressor power when ON

Content Verified APRIL2023

Microsoft Access databases are contained in a (.mdb) file.  To connect Seeq to this file, think of them like an Excel or csv file.  The .mdb files must be continuously imported it into an RDBMS, most naturally MSSQL as it is in the Microsoft software family.  After that, Seeq can connect to it via our SQL v2 connector.  The .mdb file import can be scripted/automated/scheduled, but it is basically like bringing an arbitrary file into MSSQL.  This link provides good instructions on the steps to import Access data into MSSQL: https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-an-access-data-source-sql-server-import-and-export-wizard?view=sql-server-2017 (edited)

Summary

Chain View enables a nice visual of all the time periods stacked side-by-side; however, sometimes it is useful to create a new signal from all these capsules that has been scrunched together.  This avoids the maximum limit of how many capsules can be shown in Chain View.  Note this is different from "time warping" which realigns the samples by some amount.  Signal scrunching keeps the relative sample alignment the same within each capsule - but it does move each "snippet" of the signal next to each other.

Steps

Here is a screenshot of all the signals and conditions needed for this analysis.

1. Add signal to the display with the specified time range.
2. Create the time periods, condition capsules, of interest.
3. Using the Custom Condition tool, create a new capsule that surrounds all the capsules containing the data for the new signal.  Note, the signal will start at the beginning of this capsule.
4. Create Inverse of the time periods of interest that occurs within the 'Condition for New Signal'.  This will be used to calculate the time lag between the capsules in the 'Condition for Time Periods of Interest'.

   • $cftp.inverse().removeLongerThan(1wk).intersect($cfns)

      

5. Create the signal snippets that occur only within the 'Condition for Time Periods of Interest' and the 'Condition for New Signal'.

   • $t.within($cftp.intersect($cfns))

      

6. Create a new signal to calculate the delay between each of the 'Condition for Time Periods of Interest' capsules.

   • 1.toSignal().aggregate(totalized(), $icfc, startKey()).convertUnits('h').toStep()

      

7. Create a running sum of the delay - this will be used to shift all the snippets to the beginning of the 'Condition for New Signal' capsule.

   • $dbtp.runningSum($cfns)

      

8. Scrunch the signal.

   • $ts.delay(0-$rsod, 1wk)

      

Thanks @YogurtYoda for highlighting how to set the maximum capsule duration to resolve that formula error.