Introduction

VantagePoint EMI is an Enterprise Manufacturing Intelligence platform. One of the many integration points with Microsoft SQL Server is a so called Tag Provider that transforms a database table into a stream of time series data that can be easily processed by any of the VantagePoint clients like the Trending tool.

Figure 1: Transforming a SQL Server table into a stream of time series data and displaying it in trend

This blog post provides a recipe for creating a Tag Provider example from scratch including the SQL Server database and the sample data.

Recipe

1. Generate sample data
2. Create SQL Server database and import sample data
3. Create a VantagePoint SQL Server Connector and configure a Tag Provider
4. Run Trend

The first and second steps are already covered in earlier blog posts. The attachment of this post combines all resources together.

Create a VantagePoint SQL Server Connector and configure a Tag Provider

The key to TagProviders are set of SQL Server Query templates that contain place holders for the start and end time and the tag identifier. Besides that, the date time format string is also very important. The Query templates can consist in any SQL query that returns a data set with four fields in the following order:

1. Tag ID
2. Tag value
3. Tag value time stamp
4. Tag value quality (192 for good quality, follows the OPC [OLE for Process Control] standard)

The result set is sorted by the tag value time stamp (ascending). To make queries simpler for multiple tag ids, the query template definition uses a <SingleValueQuery> markup attribute at the top of the query. This signals the query execution engine to run the query for each tag id separately. Additionally you need to create a tag provider for each tag value type.

Now let’s get started. First open VantagePoint Manager. Browse to System\Sources\DB\MsSQL and create a new SQL Server connector pointing to the server where you created the sample database. Use Server authentication and provide a valid SQL Server login for the sample database. In our example the database is called TagValueDB. Use a dot . if you need to connect to the default instance of on the local machine. Pick TagValueDB from the Database drop down box and click the Test Connection button.

Note: This examples requires you to complete steps 1 and 2 of the recipe above.

Figure 2: Create SQL Server connector

Figure 3: SQL Server Connector properties

Figure 4: SQL Server Connector CreateInstance operation

Figure 5: SQL Server Connector TestConnection operation

Figure 6: SQL Server Connector GetDatabases operation

Now right click on the TagProviders node under the new SQL Server instance and choose the New Item. Give the new TagProvider a name and then click on the New button to create a new Template.

Figure 7: Add new TagProvider and new Template

Provide a name for the template and five queries. The first query is the History Command query.

Figure 8: Enter the History Command query

<SingleTagQuery>
SELECT t.TagName, f.TagValue, f.TagTime, f.TagQuality  
FROM FloatTagPoint f, Tag t
WHERE t.TagID = f.TagID AND TagName IN ({0}) AND TagTime >= '{1}' AND TagTime <= '{2}'
ORDER BY TagTime DESC

You can test any of the queries using the Test Queries button. Just provide a valid tag id/name and a valid time range in the query test dialog and click the execute button.

Figure 9: Testing TagProvider queries.

If everything is configured correctly you should see data coming back from the sample database.

Figure 10: Query test results

Close the Test Results dialog and return to the Edit Template dialog. From the Queries drop down box choose the Current Server UTC Time selection and copy and paste the following command:

SELECT [CurrentTime] = GETUTCDATE()

Figure 11: Configure the Current Server UTC Time command

Now pick the Live Command selection from the Queries drop down box and enter the following query:

<SingleTagQuery>
SELECT TOP 1 t.TagName, f.TagValue, f.TagTime, f.TagQuality  
FROM FloatTagPoint f, Tag t
WHERE t.TagID = f.TagID AND TagName IN ({0})
ORDER BY TagTime DESC

Figure 12: Configure the Live Command

Next pick the Maximum Row Count Query selection and enter the following statement:

SET ROWCOUNT {0}

Figure 13: Configure the Maximum Row Count command

The fifth and last query is the Point In Time Command. Select it from the Queries drop down box and paste the following SQL script:

<SingleTagQuery>
SELECT TOP 1 t.TagName, f.TagValue, f.TagTime, f.TagQuality  
FROM FloatTagPoint f, Tag t
WHERE t.TagID = f.TagID AND TagName IN ({0}) AND TagTime <= '{1}'
ORDER BY TagTime DESC

Figure 14: Configure the Point In Time Command query

After you configure the queries, you need to add VantagePoint tags with matching data types and map them to the tag name / id field in the SQL Server table.

Figure 15: Adding on VantagePoint tag for each tag name / id in the corresponding SQL table

:

Figure 16: All tags are entered now

Done. The new TagProvider is now configured and its tags can be used in any of the VantagePoint data analysis tools (Trend, Dashboard, XY-Plotter and Excel).

Figure 17: TagProvider properties in the VantagePoint model browser

Figure 18: TagProvider Template properties in the VantagePoint model browser

In my example I created a tag table for each data type in the SQL database. Float, Integer, String and Discrete.

Figure 19: One TagProvider for each table (tag value data type)

The following screen shots show the TagProvider in action. You just need to pick the TagProvider tags from the model and drag it onto the graph area in Trend. Behind the scenes a SQL Query gets issued and returns the time series data that is stored in the tag value tables. Pretty slick.

Figure 20: TagProvider time series data in Trend

Figure 21: Tags from two TagProviders in one Trend

Date format configuration

The TagProvider template has a field for specifying the format of the date time string. The resulting string will be used in the SQL query that gets sent to the SQL Server. If the server doesn’t understand the query because of an invalid date time string a conversion error occurs.

The universal date time format works with any regional OS settings:

-- Universal Time Format
yyyy-MM-ddTHH:mm:ss.ffff

-- German Time Format
dd.MM.yyyy HH:mm:ss

Figure 22: Universal time format configuration in TagProvider template

Figure 23: Universal time format used in test query

Troubleshooting

The two most common issues are security and date time format configuration. Make sure that your credentials are valid during the query test and in runtime. Choose the correct date time format, otherwise you will get an error. When in doubt look at the VantagePoint log file, run the query in the SQL Management Studio and analyze the query in the SQL Profiler.

Download

All resource file to create a TagProvider example form scratch can be downloaded here: BasicTagProviderFromScratch.zip

Ausblick

This is just the tip of the SQL connectivity ice berg. In the next blog posts would like to cover other data sources and database systems like Oracle 10g XE and MySQL. Different table layouts like wide tables. And finally I will talk about the other leg of the RDMS integration: Mapped types.