How to Configure External Property Storage for Populating Metadata in Virtual Views (version 24)

Some properties are stored within the Historian. They are typically logged alongside the time-series data that comes from a Canary Collector. In some cases though, a user may wish to add more properties that are not available from the data source as they are stored in another location or the properties coming from the data source are not sufficient. Enabling external property storage allows the Views service to read properties from an external MSSQL database and link them to tags stored within the Historian. This may be very beneficial, or necessary, if wishing to build Virtual Views using properties instead of the tag names themselves. In this way, properties help to contextualize the tags in the Historian.

Configuration

1. Launch the Canary Admin client.
2. Navigate to the Views tile>Configuration>Settings screen.
3. Check the box Is Enabled then select the SQLite or MSSQL option.
4. If choosing MSSQL, configure the parameters for the MSSQL server and database. (*Note - The user account will need to have permissions to the SQL database.)

MSSQL Provider

Each DataSet is aware of external properties through a DataSet table that is required in the database with the schema as shown below. 

The Views service calls into the table every 30 seconds looking for an updated timestamp on each row.  When a new timestamp is found, this triggers the Views service to re-pull or re-cache the properties for that DataSet.  Once the properties are re-cached for the DataSet, all models that are built upon that DataSet are notified that they should rebuild because they may be impacted by new property values placed in memory.  This pulling of the properties from the database is accomplished by logic built in the required stored procedures sp_GetTagProperties and sp_GetTagPatterns.

The SQL store procedures below will provide the required link to the Canary Views service.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_GetTagProperties]
    @Dataset nvarchar(max)
AS
BEGIN
       SET NOCOUNT ON;

--     select TagName, PropertyName, Value, Timestamp from ExternalProperties

END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_GetTagPatterns]
       @Dataset nvarchar(max)
AS
BEGIN
       SET NOCOUNT ON;

       if exists (select 1 from Dataset where Dataset = @Dataset and IsUsingPatterns = 1)
       begin
              SELECT Pattern + '.%', PropertyName, Value, Timestamp from TagPatterns tp
                     inner join Properties p on p.PropertyKey=tp.PropertyKey
       end
END
GO

Canary doesn't require a fixed table schema.  This provides complete flexibility for the client to store or link to other databases for properties/metadata they also want Canary to be aware of.   This means, as an organization, you can feed Canary external properties from SQL databases that are part of other production systems as well as custom built databases that serve as repositories - the choice is yours.

Properties can be provided to Canary on a tag by tag basis or in mass using tag naming patterns.  Patterns are useful when the properties may pertain to a piece of equipment and you want to propagate these to every tag for that piece of equipment.

Upon successfully importing metadata from external properties, you will be able to view the metadata by browsing to the tags by using the Views tile within the Canary Admin.  Metadata will not be visible as properties when browsing through the Historian tile.

Writing Properties to an External Database

Some Collectors have the option of writing properties to an external database (e.g. MQTT Collector) instead of the Historian. In that case, external property storage must be enabled and the SQLite option selected. Uncheck the Is Read Only box. Properties cannot be written to a MSSQL database.