SQL API Stored Procedures
The SQL API is a collection of SQL Server stored procedures that can read Process+ data. The API provides access to following types of Process+ data
-
The interim tag data for TagPV, TagSP, TagMV, TagControlMode, DeltaLowerLimit and DeltaUpperLimit. This interim data can be used to populate trends for the specified tags up to the last 30 days of calculated Process+ data.
-
Histogram data for Error distribution (PV - SP) and TagMV distribution in the form of bin indexes and their paired bin values. A simple vertical bar graph with bin index as the x value and bin value as the y value can make use of this data.
-
Grouped Process+ KPI data. For more information on Process+ KPIs, refer to Understanding Process+ KPIs.
-
Process+ Shift performance data.
Important Concepts and Definitions used in the SQL API
-
Entities: An entity defines a generic object type that can have properties. In Process+, all Control Loops are entities.
-
Entity Class: An Entity Class defines a specific type of object and the properties that the object may have. In Process+ there is a single Entity Class definition for a Control Loop that also defines all the properties and KPI’s that a Control Loop will expose (see Entity Class Properties).
-
Entity Class Properties: An Entity Class Property belongs to a specific Entity Class and may represent an attribute of the Entity Class or KPI definition for a specific Entity Class.
-
Entity Instance: Entity Instances are instances of an Entity Class. In Process+, all Control Loops are Entity Instances of the Control Loop Entity Class.
-
Schedules: When deploying Process+ Action Objects, scheduled determine when results are calculated.
-
Periods: Periods, when combined with a timestamp, provide the exact time and the data window (period) for which a KPI was calculated.
-
Interim data: Interim data is cached process data stored in a CSense database. This data is not kept permanently and will be removed periodically (The default setting for Interim Data to be kept is 30 days).
-
Loop Filters: Loop Filters are optional Entity Class Properties defined for each Loop. These can be used to filter Loop Results. Plant, Group and Section are typical examples of Loop Filters.
Related topics
SQL API List
The following is a list of available stored procedures
Stored Procedure |
Description |
Gets the version of this API. |
|
Gets the list of entity classes available. |
|
Gets the entity instances for a specified entity class. |
|
Gets the list of available shifts that have been used by loops. |
|
Gets the loop KPIs for a specified loop over the specified reporting period. |
|
Gets the percentage of time the limits were exceeded for each control mode across all loops over the specified reporting period and filters. |
|
Gets the percentage time spent in each control mode across all loops over the specified reporting period and filters. |
|
Gets the loop performance statistics across loops over the specified reporting period and filters. |
|
Gets the total number of configuration changes across loops over the specified reporting period and filters. |
|
Gets the PV Oscillation peaks for a specific loop for a specific reporting period. |
|
Gets a summary of control modes for a specific loop for a specific reporting period. |
|
Gets the controller output performance statistics for a loop for a specific reporting period. |
|
Gets the process variable performance statistics for a specific loop for a specific reporting period. |
|
Gets the error statistics (process variable minus set point) statistics for a specific loop for a specific reporting period. |
|
Gets the PIDF values of the reporting period for a specific loop. |
|
Gets the control loop data for a specific loop for a specific reporting period. |
|
Gets the shift performance for a specific loop for a specific reporting period. |
|
Gets the data (Bin Index and Bin Value) for the MV histogram for a specific loop for a specific reporting period. |
|
Gets the data (Bin Index and Bin Value) for the Error histogram for a specific loop for a specific reporting period. |
|
Gets the time series data for the tag PV, tag SP, the Delta Lower Limit and the Delta Upper Limit for a specific loop for a specific reporting period. |
|
Gets the time series data for tag MV and tag Control Mode for a specific loop for a specific reporting period. |
|
Gets the time series data for the tag PV, tag SP, Delta Lower Limit, Delta Upper Limit, tag MV, and Control Modes for a specific loop for a specific reporting period. |
|
Gets the distinct values for the specified property across all loops. |
|
Gets the name of the shift for the specified id. |
|
Gets the loop filters (Priority, Plant, Section and Group filters) for a specific loop. |
Get SQL API Version
Gets the version of this API. This API version can be used to check for compatibility against the expected version that is being used by client applications.
Note: Applications using this API should check the version for compatibility before use. Not verifying the version for compatibility may result in undefined behavior and/or unexpected errors.
Stored Procedure Name
GetSQLAPIVersion
Parameters
None
Output
Name |
SQL DataType |
Description |
Major |
INT |
The major version component of the API. A difference between the version expected by a client application and the version returned indicates a compatibility breaking change. |
Minor |
INT |
The minor version component of the API. The versions are compatibility if the version expected by a client application is less than or equal to the version returned. |
Patch |
INT |
The patch version component of the API. Differences between the version expected by a client application and the version returned has no effect on compatibility. |
Get Entity Classes
Gets the list of entity classes available.
Note: All loops belong to the Control Loop entity class.
Stored Procedure Name
GetEntityClasses
Parameters
None
Output
Name |
SQL DataType |
Description |
EntityClassId |
INT |
The unique id for an entity class. |
EntityClassName |
NVARCHAR(256) |
The unique name for an entity class. |
Description |
NVARHCAR(256) |
The description for an entity class. |
Get Entity Instances
Gets the entity instances for a specified entity class.
Stored Procedure Name
GetEntityInstancesForEntityClass
Parameters
Name |
SQL Data Type |
Description |
EntityClassId |
INT |
The entity class id for the specified entity class. To get the list of available entity classes that includes the entity class ids, execute the Get Entity Classes stored procedure. |
Output
Name |
SQL DataType |
Description |
EntityInstanceId |
INT |
The unique id for an entity instance. |
EntityClassId |
INT |
The unique id for an entity class. |
Name |
NVARCHAR(256) |
The unique name for an entity instance. |
Description |
NVARCHAR(256) |
The description for an entity instance. |
IsRetired |
BIT |
Property indicates if an entity instance is deployed and running or no longer deployed but still contains data. The value 0 indicates the entity instance is deployed and running. The value 1 indicates no longer running but still contains data. |
Get Periods For an Entity Class
Gets the list of available shifts that have been used by loops.
Stored Procedure Name
GetPeriodsForEntityClass
Parameters
Name |
SQL Data Type |
Description |
EntityClassName |
NVARCHAR(256) |
The unique name for an entity class. To get the list of available entity classes that includes the entity class names, execute the Get Entity Classes stored procedure. |
Output
Name |
SQL DataType |
Description |
PeriodId |
INT |
The unique id of the required shift. |
PeriodOffset |
BIGINT |
The amount of time in seconds between each Action Object execution. |
ScheduleName |
NVARCHAR(256) |
The name of the period. |
Description |
NVARCHAR(256) |
The description of the period. |
Time |
TIME |
The starting time of the period for Action Object execution. |
Period |
BIGINT |
The length in seconds of the Action Object execution window. |
Get Loop Performance Historical KPI Statistics
Gets the loop KPIs for a specified loop over the specified reporting period.
Stored Procedure Name
GetKPIHistoricalData
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceID |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances. |
Date |
DATE |
The end date of the reporting period. |
Window |
INT |
The number of days in the reporting period up to the end date. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
EntityInstanceId |
INT |
The id of the entity instance. |
NA |
Timestamp |
DATETIME2 |
The timestamp data in the local time format. |
NA |
PeriodId |
INT |
The unique id for a shift. |
NA |
PercentageAuto |
FLOAT |
The percentage of time spent in the auto control mode. |
Auto (%) |
PerecentageAutoLLExceeded |
FLOAT |
The percentage of time spent in the auto control mode while the lower limit was exceeded. |
Auto Lower Limit % |
PerecentageAutoULExceeded |
FLOAT |
The percentage of time spent in the auto control mode while the upper limit was exceeded. |
Auto Upper Limit % |
AverageAbsoluteError |
FLOAT |
The average absolute value of the tag error, where the tag error is the difference between process variable and set point. |
AVG Absolute Error |
Average |
FLOAT |
The average difference between process variable and set point. |
Average Error |
ControlAmplitude |
FLOAT |
The average amount of movement in the manipulated variable. |
Average MV Change [%] |
ReverseAmplitude |
FLOAT |
The average amount of movement in the manipulated variable before changing its direction. |
Average Oscillation Amplitude [%] |
PercentageCascade |
FLOAT |
The percentage of time spent in the cascade control mode. |
Cascade (%) |
PercentageCascadeLLExceeded |
FLOAT |
The percentage of time spent in the cascade control mode while the lower limit was exceeded. |
Cascade Lower Limit % |
PercentageCascadeULExceeded |
FLOAT |
The percentage of time spent in the cascade control mode while the upper limit was exceeded. |
Cascade Upper Limit % |
PercentageControlOn |
FLOAT |
The percentage of time the data was not of bad quality and the controller was On. The controller is considered on, when it is of a good quality and the control loop operated in the Auto and the Cascade mode. |
%Time when control on |
DValueEndOfReport |
FLOAT |
The value of D at the end of the reporting time |
Report End (Actual)- D |
PercentageNotUtilized |
FLOAT |
The percentage of time spent in the manual mode or if the controller output is saturated. |
Duration Not Utilized [%] |
StdDeviation |
FLOAT |
The standard deviation of the tag error, where the tag error is the difference between process variable and set point. |
Error Std Deviation |
FValueEndOfReport |
FLOAT |
Retrieves the value of F at the end of the reporting period. |
Report End (Actual)- F |
PercentageGoodQuality |
FLOAT |
The percentage of time that the data was not of bad quality |
Good Quality Data (%) |
IValueEndOfReport |
FLOAT |
Retrieves the value of I at the end of the reporting period. |
Report End (Actual)- I |
IntegratedSum |
FLOAT |
The absolute sum of all differences between the process variable and the set point. |
Integrated Error |
PercentageLimitsExceeded |
FLOAT |
The percentage of time the limits exceeded in all the control modes except shutdown. |
Limits Exceeded (%) |
PercentageControllerOutput |
FLOAT |
The average Manipulated variable expressed in percentage. |
Manipulated Variable [%] |
PercentageManual |
FLOAT |
The percentage of time spent in the manual control mode. |
Manual (%) |
PercentageManualLLExceeded |
FLOAT |
The percentage of time spent in the manual control mode while the lower limit was exceeded. |
Manual Lower Limit % |
PercentageManualULExceeded |
FLOAT |
The percentage of time spent in the manual control mode while the upper limit was exceeded. |
Manual Upper Limit % |
ReverseIndex |
FLOAT |
The number of times MV changes its direction. |
MV Oscillation Count [#] |
PercentageCVSaturation |
FLOAT |
The percentage of time that the MV is saturated. |
MV Saturation [%] |
NumberOfSamples |
FLOAT |
The number of samples of data (rows of data) used during the calculation of the statistics for the reporting period. |
No. of samples |
OverallPerformance |
FLOAT |
Calculates the overall performance of the loop by combining Manual (%), MV Saturation [%], and Limits Exceeded (%) into a single percentage value. |
OverallPerformance |
`PValueEndOfReport |
FLOAT |
Retrieves the value of P at the end of the reporting period. |
Report End (Actual)- P |
FFTPeriodPeak1 |
FLOAT |
The period (in seconds) of the frequency with the highest associated amplitude on the FFT graph for the reporting period. Note: This is for the first peak |
Period (Seconds) |
FFTPowerPercentagePeak1 |
FLOAT |
The power of the point in the FFT graph for which the amplitude is highest. Expressed as a percentage of the total power in the frequency spectrum of the FFT for the reporting period. Note: This is for the first peak |
Strength (%power) |
FFTPeriodPeak2 |
FLOAT |
The period (in seconds) of the frequency with the highest associated amplitude on the FFT graph for the reporting period. Note: This is for the second peak |
Period (Seconds) |
FFTPowerPercentagePeak2 |
FLOAT |
The power of the point in the FFT graph for which the amplitude is highest. Expressed as a percentage of the total power in the frequency spectrum of the FFT for the reporting period. Note: This is for the second peak |
Strength (%power) |
FFTPeriodPeak3 |
FLOAT |
The period (in seconds) of the frequency with the highest associated amplitude on the FFT graph for the reporting period. Note: This is for the third peak |
Period (Seconds) |
FFTPowerPercentagePeak3 |
FLOAT |
The power of the point in the FFT graph for which the amplitude is highest . Expressed as a percentage of the total power in the frequency spectrum of the FFT for the reporting period. Note: This is for the third peak |
Strength (%power) |
PerformanceIndex |
FLOAT |
Calculates the percentage of time when Auto and Cascade Limits Exceeded. |
PerformanceIndex |
PIDFChanges |
INT |
The sum of the number of times any of the PIDF values have changed. |
PIDF Configuration Changes |
PVVariability |
FLOAT |
The measure of how spread or closely clustered the PV data set is, expressed in percentage. |
PV Variability |
PercentagePVVariance |
FLOAT |
A measure of the deviation of PV from the average PV, expressed in percentage. |
PV Variance [%] |
SamplingPeriod |
FLOAT |
The sampling period used during the calculation of the statistics for the reporting period. |
Sampling period |
PercentageShutdown |
FLOAT |
The percentage of time spent in the shutdown control mode. |
Shutdown (%) |
PercentageShutdownLLExceeded |
FLOAT |
The percentage of time spent in the shutdown control mode while the lower limit was exceeded. |
Shutdown Lower Limit % |
PercentageShutdownULExceeded |
FLOAT |
The percentage of time spent in the shutdown control mode while the upper limit was exceeded. |
Shutdown Upper Limit % |
MovementIndex |
FLOAT |
The sum of all the changes made in the manipulated variable. |
Total MV Movement |
Get Limits Exceeded
Gets the percentage of time the limits were exceeded for each control mode across all loops over the specified reporting period and filters.
Stored Procedure Name
GetLimitsExceededBarChart
Parameters
Name |
SQL Data Type |
Description |
Required or Optional |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Required |
Date |
DATE |
The end date of the reporting period. |
Required |
Window |
INT |
The number of days in the reporting up to the end date. |
Required |
PlantFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified plant. |
Optional |
LoopPriorityFilter |
NVARCHAR(MAX) |
Filters out loops that are not of the specified priority. |
Optional |
SectionFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified section. |
Optional |
GroupFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified group. |
Optional |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
Timestamp |
DATE |
The date for each row of limits exceeded statistics. |
NA |
PercentageManualExceeded |
FLOAT |
The percentage of time the limits were exceeded while in manual control mode. |
Manual Lower Limit % + Manual Upper Limit % |
PercentageAutoExceeded |
FLOAT |
The percentage of time the limits were exceeded while in auto control mode. |
Auto Lower Limit % + Auto Upper Limit % |
PercentageCascadeExceeded |
FLOAT |
The percentage of time the limits were exceeded while in cascade control mode. |
Cascade Lower Limit % + Cascade Upper Limit % |
PercentageShutdownExceeded |
FLOAT |
The percentage of time the limits were exceeded while in shutdown control mode. |
Shutdown Lower Limit % + Shutdown Upper Limit % |
Get Control Mode Duration
Gets the percentage time spent in each control mode across all loops over the specified reporting period and filters.
Stored Procedure Name
GetDurationBarChart
Parameters
Name |
SQL Data Type |
Description |
Required or Optional |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Required |
Date |
DATE |
The end date of the reporting period. |
Required |
Window |
INT |
The number of days in the reporting up to the end date. |
Required |
PlantFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified plant. |
Optional |
LoopPriorityFilter |
NVARCHAR(MAX) |
Filters out loops that are not of the specified priority. |
Optional |
SectionFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified section. |
Optional |
GroupFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified group. |
Optional |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
Timestamp |
DATE |
The date for each row of limits exceeded statistics. |
NA |
PercentageManual |
FLOAT |
The percentage of time spent in manual control mode. |
Manual (%) |
PercentageAuto |
FLOAT |
The percentage of time spent in auto control mode. |
Auto (%) |
PercentageCascade |
FLOAT |
The percentage of time spent in cascade control mode. |
Cascade (%) |
PercentageShutdown |
FLOAT |
The percentage of time spent in shutdown control mode. |
Shutdown (%) |
Get Loop Performance Statistics
Gets the loop performance statistics across loops over the specified reporting period and filters.
Stored Procedure Name
GetLoopPerformanceTable
Parameters
Name |
SQL Data Type |
Description |
Required or Optional |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Required |
Date |
DATE |
The end date of the reporting period. |
Required |
Window |
INT |
The number of days in the reporting up to the end date. |
Required |
PlantFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified plant. |
Optional |
LoopPriorityFilter |
NVARCHAR(MAX) |
Filters out loops that are not of the specified priority. |
Optional |
SectionFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified section. |
Optional |
GroupFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified group. |
Optional |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
Name |
NVARCHAR(256) |
The name of the entity instance. |
NA |
Description |
NVARCHAR(256) |
The description of the entity instance. |
NA |
EntityInstanceId |
INT |
The id of the entity instance. |
NA |
Days |
INT |
The number of days that the entity instance has calculated statistics within the reporting period. |
NA |
PercentageManual |
FLOAT |
The percentage of time spent in manual control mode. |
AVG Manual (%) |
PercentageAuto |
FLOAT |
The percentage of time spent in auto control mode. |
AVG Auto (%) |
PercentageCascade |
FLOAT |
The percentage of time spent in cascade control mode. |
AVG Cascade (%) |
PercentageShutdown |
FLOAT |
The percentage of time spent in shutdown control mode. |
AVG Shutdown (%) |
PercentageMVSaturation |
FLOAT |
The percentage of time the controller output is saturated. |
AVG MV Saturation (%) |
OverallPerformance |
FLOAT |
The Overall Performance statistic is a statistic composed of the following elements in the following proportions: - 1/3: Percentage of time limits were exceeded in all control modes except shutdown. - 1/3: Percentage of time spent in manual control mode. - 1/3: Percentage of time the controller output is saturated. |
AVG Performance Index (100-0) |
TravelIndex |
FLOAT |
The absolute sum of all changes made in the manipulated variable. |
Total MV Movement (average loop performance) |
AverageAbsoluteError |
FLOAT |
The average absolute value of the tag error, where the tag error is the difference between process variable and set point. |
AVG Absolute Error (Aggregate) |
PercentageAverageAbsoluteError |
FLOAT |
The average absolute value of the tag error, where the tag error is the difference between process variable and set point. Expressed as a percentage of PV Span. |
Average Absolute Error % |
PVVariability |
FLOAT |
The PV Variability is a measure of how spread or closely clustered the PV data set is, expressed as a percentage. |
AVG PV Variability |
PercentageGoodQuality |
FLOAT |
The percentage of time that the data is not of bad quality. |
Good Quality Data (%) |
PIDFChanges |
INT |
The sum of the number of times any of the PIDF values have changed. |
PIDF Configuration Changes |
PercentageLimitsExceeded |
FLOAT |
The percentage of time, limits were exceeded in all the control modes except shutdown. |
AVG Limits Exceeded (%) (Auto + Cascade + Manual) |
PerformanceIndex |
FLOAT |
The percentage of time in the Auto or Cascade control mode and exceeding the upper or lower limit. |
AVG Limits Exceeded (%) (Auto + Cascade) |
Get Total Configuration Changes
Gets the total number of configuration changes across loops over the specified reporting period and filters.
Stored Procedure Name
GetTotalConfigurationChanges
Parameters
Name |
SQL Data Type |
Description |
Required or Optional |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Required |
Date |
DATE |
The end date of the reporting period. |
Required |
Window |
INT |
The number of days in the reporting up to the end date. |
Required |
PlantFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified plant. |
Optional |
LoopPriorityFilter |
NVARCHAR(MAX) |
Filters out loops that are not of the specified priority. |
Optional |
SectionFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified section. |
Optional |
GroupFilter |
NVARCHAR(MAX) |
Filters out loops that are not in the specified group. |
Optional |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
PIDFChanges |
INT |
The total number of configuration changes. |
PIDF Configuration Changes |
Get PV Oscillations
Gets the PV Oscillation peaks for a specific loop for a specific reporting period.
Stored Procedure Name
GetPVOscillations
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
Peak |
NVARCHAR(6) |
The peak number to describe the other 2 statistics (peak 1, peak 2 or peak 3). |
PV Oscillations |
FFTPeriodPeak |
FLOAT |
The period (in seconds) of the frequency with the 3 highest associated amplitudes. Note that this KPI can only be calculated when there is 100% good quality data for the period for which the FFT is calculated, otherwise this KPI will return -1. |
Period (Seconds) |
FFTPowerPercentagePeak |
FLOAT |
The power of the frequency for the 3 highest amplitudes, expressed as a percentage of the total power in the frequency spectrum. Note that this KPI can only be calculated when there is 100% good quality data for the period for which the FFT is calculated, otherwise this KPI will return -1. |
Strength (%power) |
Get Control Mode Summary
Gets a summary of control modes for a specific loop for a specific reporting period.
Stored Procedure Name
GetControlModeSummary
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
Mode |
NVARCHAR(8) |
The control mode name (Manual, Auto, Cascade or Shutdown). |
NA |
Duration |
FLOAT |
The percentage of time spent in each control mode. |
Duration % |
LowerExceeded |
FLOAT |
The percentage of time spent in each control mode while the lower limit was exceeded. |
Cascade Lower Limit % Auto Lower Limit % Manual Lower Limit % Shutdown Lower Limit % |
UpperExceeded |
FLOAT |
The percentage of time spent in each control mode while the upper limit was exceeded. |
Cascade Upper Limit % Auto Upper Limit % Manual Upper Limit % Shutdown Upper Limit % |
TotalExceeded |
FLOAT |
The percentage of time spent in each control mode while either the lower or upper limit was exceeded. |
Total Limits % |
Get Controller Performance
Gets the controller output performance statistics for a loop for a specific reporting period.
Stored Procedure Name
GetControllerPerformance
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
ControllerHeading |
NVARCHAR(33) |
The name of the controller output statistic. |
NA |
ControllerStat |
FLOAT |
The calculated value for each controller output statistic.
Manipulated Variable: The average Manipulated variable expressed as a percentage.
Total MV Movement: The sum of all changes made in the manipulated variable.
Average MV Change: The average amount of movement in the manipulated variable.
MV Oscillation Count: The number of times that the manipulated variable changes direction divided by 2.
Average Oscillation Amplitude: The average amount of movement in the manipulated variable before changing direction.
MV Saturation: The percentage of time the controller output is saturated.
Duration Not Utilized: The percentage of time spent in manual mode or if the controller output is saturated. |
NA
Manipulated Variable [%]
Total MV Movement
Average MV Change [%]
MV Oscillation Count [#]
Average Oscillation Amplitude [%]
MV Saturation [%]
Duration Not Utilized [%] |
Get PV Performance
Gets the process variable performance statistics for a specific loop for a specific reporting period.
Stored Procedure Name
GetPVPerformance
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
PVHeading |
NVARCHAR(15) |
The name of the process variable statistic. |
NA |
PVStat |
FLOAT |
The calculated value for each process variable statistic. PV Variance: A measure of the deviation of PV from the average PV, expressed as a percentage. PV Variability: A measure of how spread or closely clustered the PV data set is, expressed as a percentage. |
PV Variance [%]
PV Variability |
Get Error Statistics
Gets the error statistics (process variable minus set point) statistics for a specific loop for a specific reporting period.
Stored Procedure Name
GetErrorStats
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
ErrorHeading |
NVARCHAR(22) |
The name of the error statistic. |
NA |
ErrorStat |
FLOAT |
The calculated value for each error statistic.
Integrated Error: The absolute sum of all differences between the process variable and the set point.
Average Error: The average difference between process variable and set point.
Average Absolute Error: The average absolute value of the tag error, where the tag error is the difference between process variable and set point.
Percentage Average Absolute Error: The average absolute value of the tag error, where the tag error is the difference between process variable and set point. Expressed as a percentage of PV Span.
Error Std Deviation: The standard deviation of the tag error, where the tag error is the difference between process variable and set point. |
Integrated Error
Average Error
Average Absolute Error
Average Absolute Error %
Error Std Deviation |
Get PIDF Start And End Values
Gets the PIDF values of the reporting period for a specific loop.
Stored Procedure Name
GetPIDFStartAndEndValues
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
Report |
NVARCHAR(256) |
The PIDF report labels. |
NA |
P |
NVARCHAR(256) |
The P values of the report. |
Report Start (Actual)- P Report End (Actual)- P |
I |
NVARCHAR(256) |
The I values of the report. |
Report Start (Actual)- I Report End (Actual)- I |
D |
NVARCHAR(256) |
The D values of the report. |
Report Start (Actual)- D Report End (Actual)- D |
F |
NVARCHAR(256) |
The F values of the report. |
Report Start (Actual)- F Report End (Actual)- F |
Get Control Loop Data
Gets the control loop data for a specific loop for a specific reporting period.
Stored Procedure Name
GetControlLoopData
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
ControlHeading |
NVARCHAR(22) |
The name of the control loop data statistic. |
NA |
ControlData |
FLOAT |
Sampling period: The sampling period used during the calculating of the statistics for the reporting period.
No. of samples: The number of samples of data (rows of data) used during the calculating of the statistics for the reporting period.
Good quality data: The percentage of time that the data is not of bad quality.
Time when control on: The percentage of data that is not of bad quality and the current control mode is enabled. |
Sampling period
No. of samples
Good Quality Data (%)
Duration % |
Get Shift Performance
Gets the shift performance for a specific loop for a specific reporting period.
Stored Procedure Name
GetShiftPerformance
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
ScheduleName |
NVARCHAR(256) |
The name of each shift. |
NA |
PercentageManual |
FLOAT |
The percentage of time spent in manual control mode. |
Manual (%) |
PercentageAuto |
FLOAT |
The percentage of time spent in auto control mode. |
Auto (%) |
PercentageCascade |
FLOAT |
The percentage of time spent in cascade control mode. |
Cascade (%) |
PercentageShutdown |
FLOAT |
The percentage of time spent in shutdown control mode. |
Shutdown (%) |
PercentageLimitsExceeded |
FLOAT |
The percentage of time limits were exceeded in all control modes except shutdown. |
Limits Exceeded (%) |
Get MV Histogram
Gets the data (Bin Index and Bin Value) for the MV histogram for a specific loop for a specific reporting period.
Stored Procedure Name
GetMVHistogram
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
BinIndex |
FLOAT |
The table of data that represents the bin index values for the MV histogram. |
BinValue |
FLOAT |
The table of data that represents the bin values for the MV histogram. |
Get Error Histogram
Gets the data (Bin Index and Bin Value) for the Error histogram for a specific loop for a specific reporting period.
Stored Procedure Name
GetErrorHistogram
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
BinIndex |
FLOAT |
The table of data that represents the bin index values for the Error histogram. |
BinValue |
FLOAT |
The table of data that represents the bin values for the Error histogram. |
Get PVSP Trend
Gets the time series data for the tag PV, tag SP, the Delta Lower Limit and the Delta Upper Limit for a specific loop for a specific reporting period.
Stored Procedure Name
GetPVSPTrend
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
Timestamp |
DATETIME2 |
The timestamp data in local time. |
Tag_PV |
FLOAT |
The raw tag pv data stored in the cache. |
Tag_SP |
FLOAT |
The raw tag sp data stored in the cache. |
LowerLimit |
FLOAT |
The raw delta lower limit data stored in the cache. |
UpperLimit |
FLOAT |
The raw delta upper limit data stored in the cache. |
Get MV Mode Trend
Gets the time series data for tag MV and tag Control Mode for a specific loop for a specific reporting period.
Stored Procedure Name
GetMVModeTrend
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
Timestamp |
DATETIME2 |
The timestamp data in local time. |
Tag_Control_Type |
FLOAT |
The raw tag control mode data stored in the cache. |
Tag_MV |
FLOAT |
The raw tag mv data stored in the cache. |
Get Control Loop Trend Data
Gets the time series data for the tag PV, tag SP, Delta Lower Limit, Delta Upper Limit, tag MV, and Control Modes for a specific loop for a specific reporting period.
Stored Procedure Name
GetControlLoopTrendData
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Date |
DATE |
The end date of the reporting period. |
PeriodId |
INT |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
KPIs Name (See Understanding Process+ KPIs) |
Timestamp |
DATETIME2 |
The timestamp data in local time. |
NA. |
Tag_PV |
FLOAT |
The raw tag pv data stored in the cache. |
NA. |
Tag_SP |
FLOAT |
The raw tag sp data stored in the cache. |
NA. |
LowerLimit |
FLOAT |
The raw delta lower limit data stored in the cache. |
NA. |
UpperLimit |
FLOAT |
The raw delta upper limit data stored in the cache. |
NA. |
Tag_MV |
FLOAT |
The raw tag mv data stored in the cache. |
NA. |
ManualMode |
FLOAT |
This is based on the raw tag control mode data stored in the cache. It will indicate if the current control mode is Manual. The value will be 100 when in the Manual mode and 0 when in the other modes. |
NA. |
AutoMode |
FLOAT |
This is based on the raw tag control mode data stored in the cache. It will indicate if the current control mode is Auto. The value will be 100 when in the Auto mode and 0 when in the other modes. |
NA. |
CascadeMode |
FLOAT |
This is based on the raw tag control mode data stored in the cache. It will indicate if the current control mode is Cascade. The value will be 100 when in the Cascade mode and 0 when in the other modes. |
NA. |
ShutdownMode |
FLOAT |
This is based on the raw tag control mode data stored in the cache. It will indicate if the current control mode is Shutdown. The value will be 100 when in the Shutdown mode and 0 when in the other modes. |
NA. |
Get Distinct Property Values
Gets the distinct values for the specified property across all loops.
Stored Procedure Name
GetDistinctPropertyValuesForEntityClass
Parameters
Name |
SQL Data Type |
Description |
EntityClassName |
NVARCHAR(256) |
The unique name for an entity class. To get the list of available entity classes that includes the entity class names, execute the Get Entity Classes stored procedure. Note: The API is specific to Process+ therefor the entity class would need to be Control Loop. |
PropertyInstanceName |
NVARCHAR(256) |
An property name for the entity class. |
Output
Name |
SQL DataType |
Description |
Value |
NVARCHAR(256) |
The distinct values for the specified entity class property across all entity instances that belongs to the specified entity class. |
Get Name For PeriodId
Gets the name of the shift for the specified id.
Stored Procedure Name
GetNameForPeriodId
Parameters
Name |
SQL Data Type |
Description |
PeriodId |
NVARCHAR(256) |
The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure. |
Output
Name |
SQL DataType |
Description |
ScheduleName |
NVARCHAR(256) |
The name of the period. |
Get Loop Filters For Specific Loop
Gets the loop filters (Priority, Plant, Section and Group filters) for a specific entity instance that belongs to the Control Loop entity class.
Stored Procedure Name
GetLoopFiltersForEntityInstanceId
Parameters
Name |
SQL Data Type |
Description |
EntityInstanceId |
INT |
An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.
|
Output
Name |
SQL DataType |
Description |
Name |
NVARCHAR(256) |
The name of the filter (Priority, Plant, Section and Group). |
Value |
NVARCHAR(256) |
The value for the filter. |
Related topics: