In this
video we
will create multiple PI tags for the
PI interface relational database,
also known as the RDBMS PI interface. This will use
the tag group strategy for querying data
from the relational database.
We have three servers in our
architecture, a PI server, a relational
database and a PI interface node with a previously
configured instance of the RDBMS
PI interface. Configuration of the RDBMS
interface with the PI ICU is shown in
another OSISoft learning channel video.
In the tag group strategy,
one SQL select
query is made to the relational database.
And the data to populate multiple PI tags is returned.
This strategy is useful if we have a
table with multiple columns from which we would like to get data.
In this table,
I have two columns or two data
streams. I can create two PI tags, one tag
for the percent committed bytes
column and one tag for the available megabytes column.
The values of the two tags will share the same
timestamp, the last modified time. The RDBMS
interface uses the unique tag configuration
settings to determine which column of data belongs
to which PI tag. One or more rows
can be returned to populate the multiple PI tags.
The SQL select statement
used in the tag group strategy includes
multiple columns, an optional column for the
timestamp, the first value column that I want,
the second value column that I want and so on.
Each value column is required to have a column for the
status or quality of the corresponding column.
Often, a zero is used for the status column.
There is no limit to the number of
value columns that can be listed in the SQL
select statement.
For this computer performance table, we would
select the last modified time column as the timestamp,
the percent committed bytes
column for the first tag with a zero for its status, and
the available megabytes column for the second tag, also with a
zero for its status.
Configurations are unique
for every situation, so please download the
relational database RDBMS via
ODBC interface manual at the tech support download website.
In the interface manual,
chapter seven discusses PI point configuration
and chapter eight discusses writing
SQL statements for the RDBMS PI interface.
To create
the tags that will be used by my
tag groups strategy, I'm actually going to start on the
node. What I want to do here
is create the .SQL file that
will contain the SQL statement
containing my query.
I'm going to open Windows
Explorer and navigate to my
SQL directory. This is Program Files,
x86, PIPC,
Interfaces,
RDBMSPI,
SQL. This is the directory that
is defined in my RDBODB
C view by my SQL Files directory.
I'm going to create a new
text file here.
And then name it something
useful and actually change it from a
text file to a .SQL file.
I'm going to edit it
and type in my SQL
query. As you'll notice, my
SQL query is pretty long which is why
I'm choosing to use a .SQL file for my
query rather than put it into the extender descriptor
tag attribute.
And starting out by
pulling back my last modified column
as my timestamp.
And then
several other columns that
will distribute to multiple tags
using the tag groups strategy.
For each value that I'm pulling back, I'm
pulling back the value column plus
this zero which is the status column.
This is coming from the node perf stat
table for the
[inaudible] in which the node
is equal to alpha 1.
I'm going to save that
and close it.
And then move to my
PI server Walnut to create
the first tag.
I'm going to create the first tag
with PI system management tools
in the point builder
plug-in.
I'm going to give my tag a
name that is unique
and also starts with a alpha numeric
character. This tag is going to
hold the percent committed bytes value
from my alpha 1 node.
I'm going to change the point source
to reflect that of my PI RDMBS interface
which is defined in the ICU.
This is RDB-SQL.
On my archive
tab, I can set the exception and
compression and then the typical
values here and then span, as I would any other PI tag.
On the classic
tab, I'm going to set my location
1 equal to the interface ID
to find
in the ICU for my RDBMS PI interface.
Location 4, I'm going
to set to a scan class to
find in my RDBMS PI interface.
I'll want to go to the user manual
to look up what values I should put in for
location 2, 3 and 5. For
my particular scenario, location 2
can be either 0 or 1.
Location 3
is going to be the column that I'm putting
into this tag. In this case,
for my SQL query, this was the second
column being returned by my select
statement. So I'll set location 3 equal to 2.
Location 5, I can leave at
0. My instrument tag,
tag attribute, contains the name of the
.SQL file. It contains the SQL
query that I'm using for this tag.
That's tag group.
Then to save my tag,
I'm going to hit the
disc icon and create my tag.
I'm now going to go to the
data current values plugin.
and wait for my tag to update with new data.
Now that I know that the first
tag in my tag group strategy
is working, I can create all the other tags
using the Excel PI tag configurator plug-in.
I've already imported my
alpha 1 percent committed
bytes tag by using the PI
SMT import tags with the PI tag
configurator. I'm now going to
create my other tags.
I can copy the settings
from my first tag
for my remaining tags.
My location 3
will be unique for all of my tags
as each tag
is getting data from a different column
returned.
And I do need all of my tags to be float 32s.
I'm going to select all of my tags
and then export them to the PI server.
Going back to my PI
server, I'm going to go into the
current values plug-in and search
for the additional alpha 1 tags that I created.
And I can see that they are
now all updating their data.