Concepts

Tasks

Settings

Configuring Data Sources for the DB Plugin

Before configuring indicators to collect readings, you must define the data source in APM. This definition brings together information about the service provider, the database (DB) ODC plugin, and the connection string for the target database. The data source definition also includes the SQL statements that locate data tags and return readings to APM. When setting up the data source, you can connect to the target database to run and test your SQL statements.

This topic explains how to:

•	Create an ODC data source in APM

•	Provide connection information for ODBC, OLE DB, and SQL Server data providers

•	Enter SQL statements for browsing tags and returning values

Note: Before creating the data source definition, make sure that the connection to the database is working. When creating the data source, you must connect to the database.

Note: If you are using the APM on-premises ODC agent, you must create and configure the data source on the premises. It’s easiest to do this is on the actual agent computer. However, you can also set up the data source in the local network context on a computer with the OPC, PI, or DB components installed.

Note: Do not create two data sources that point to a single database. Polling will fail for the second data source with an error message such as “System.NullReferenceException: Object reference not set to an instance of an object”.

To Create a Data Source for the DB Plugin

1.	From the Site window, select the Inspection Management view and the Settings tab. Select the Indicators tab and the Indicator Settings node in the tree.

Tip: You can also click the Administration menu, Inspection Management Settings, and then Indicator Management. The Indicator Settings window appears.

2.	Select the ODC tab and then the Data Sources tab.

Note: You can also create a data source that is available to all sites that use the Online Data Collection module. On the Enterprise window, select the Services view, ODC tab, and then the Data Sources tab.

3.	Click New. The ODC Data Source dialog appears.

4.	Enter a name for the data source (for example, LAB_DATA_OIL_VIS).

5.	Select a service provider from the list.

6.	Click Browse Plugins. The Browse ODC Plugins dialog appears.

7.	Select Ivara ODC Database Plugin and click OK.

8.	To permit future-dated indicator readings, click Allow future-dated readings. If the server clocks are not perfectly synchronized, the ODC service is still able to return the most recent indicator readings.

Note: Not allowing future-dated readings only prevents readings from having a future date, not a later time today.

9.	If you are using the APM On-Premises ODC agent to manage data collection, select Via on-premises gateway.

10.	Click Browse. The Data Source Configuration dialog appears:

11.	Select a .NET Framework data provider from the Data Source list. The options are:

•

ODBC

•

OLE DB

•	SQL Server

For an example, see To Configure Connection Information for ODBC.

12.	Click Configure to set up the connection to the database. When you are finished, click OK to return to the Data Source Configuration window.

13.	Click Connect to test the connection. A successful connection is required to select tags for your SQL statement.

14.	Select the Browse Tags tab, enter and run SQL statements to identify the tags. Select the Get Tag Value tab to create client-side queries for getting data. For more information, see Browsing for Data Tags and Getting Tag Values.

15.	When you have finished setting up tags and their values, click OK in the Data Source Configuration dialog to return to the ODC Data Source dialog, where the Plugin information is populated. For example:

The Identifier value (obscured in the example above) is encrypted and displayed as hashed for security reasons.

16.	Select the Configuration tab to enter additional configuration information about the data source.

17.	Select the Description tab to enter additional information about the data source.

Tip: After you have configured indicators and started collecting online data, you can return to the ODC Data Source dialog to see information about how the data source is used. Select the Usage tab to see which indicators use the data source. Select the Messages tab to view ODC message logs.

18.	Click OK to save the data source and close the dialog.

To Configure Connection Information for ODBC

1.	Click Configure in the Data Source Configuration dialog. The ODBC Connection Information dialog appears.

2.	Select the ODBC data source from the list.

3.	Provide the user ID and password for authenticating the connection. Click OK to return to the Data Source Configuration dialog.

Browsing for Data Tags and Getting Tag Values

Using the Data Source Configuration dialog, you can develop and test SQL statements that are needed to browse for the data tags and get the tag values from the source database. This section provides a simple example for developing SQL statements.

Example Database

The examples in this section are based on the following database schema:

To Browse for Data Tags

If you have not already done so, connect to the data source before selecting the Browse Tags tab. On this tab, you can enter the SQL statement that will provide a list of tags to get data from.

4.	Enter the SQL query that browses the database for a tag. For example:

SELECT DISTINCT ltrim(rtrim(EntityInfo.EntityNumber)) EntityNumber, EntityInfo.Description, ltrim(rtrim(PLCData.PointID)) PointID

FROM EntityInfo INNER JOIN

PLCData ON EntityInfo.EntityNumber = PLCData.EntityNumber

5.	Click Run. The Tag browsing grid shows the results. For example:

6.	In the Key Columns list, select which columns, when combined, identify the selected tag.

7.	If you select more than one column, you can specify a separator character to be used when combining key values to build the tag name. Choose a value that is not used in the key columns’ data values.

Note: You must select key columns in the Browse Tags tab before the Run and Browse buttons on the Get Tag Value tab will work.

8.	Select a row in the Tag browsing grid. A preview of the tag name is displayed in the Tag Name box.

You can also click Browse to preview the tag name as it will appear in the Get Tag Value tab.

Using the example database, the Browse Tags would look like this:

To Get Tag Values

1.	Select the Get Tag Value tab. On this tab, you can enter the SQL statements that will select tags and return their values to the ODC service.

2.	You can save the SQL statements for different queries using profiles. To create a profile, provide a descriptive name and click Add/Update. The profile name is added to the list. For example:

When you have developed the SQL statement, click Add/Update to save the profile.

To delete a profile, select it and click Delete.

3.	Enter the SQL statement to return the most recent value for a specified tag name. The following example returns the raw data since the last polling time:

Here is an example for Oracle:

Select PLCData.ValueCollected, PLCData.DateTime

From EntityInfo

inner join PLCData on EntityInfo.EntityNumber = PLCData.EntityNumber

Where

(PLCData.EntityNumber = '<EntityNumber>')

AND

(PLCData.PointID = '<PointID>')

AND

(datetime < TO_TIMESTAMP('<Last Polling Date Time>','yyyy-mm-dd hh24:mi:ss.ff3') )

Order by

plcdata.datetime desc

4.	Click Run to test the statement. The results appear in the data grid. For example:

Note: If you wish to collect quality values for the data tags, quality must be numeric in both the database and the SQL statement. You must use the following values: good=0, bad=1, uncertain=2. If the quality is null, a value of “good” is returned. If the quality is null, a value of “good” is assumed by APM.

5.	When you select a row in the grid, a preview of the tag name is displayed in the Tag Name box.

You can also click Browse to preview the tag name.

6.

You can insert variables in the SQL statement that represent the value of that column in the tag name. From the Key Columns list, select the column name that you want to insert as a variable and click Insert. In this example, <EntityNumber> will be replaced by 1 and PointID will be replaced by AMPS before the SQL statement is executed, thus returning the data for tag 1:AMPS.

7.	You can insert keywords as variables into the SQL statements to denote the date and time of the start of polling or the most polling time. Select sample key word values for testing purposes.