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

To begin,

1. Launch the Canary Admin application
2. Navigate to the Historian tile
3. Select the 'Configuration' tab at the bottom of the screen
4. Open the 'Settings' menu at left to configure the connection to the SQL server
5. Check the box 'External Property Storage' to then configure the parameters for the SQL server and database. (*Note - The user account will need to have permissions to the SQL database.)

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 we 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.