OSIsoft: Overview & Import from External Table. PI AF v2010

If there's data that you have stored in a Relational
Database somewhere, like, for example, in this SQL Server
Database here. I've got a Table
called Material Properties, and here's some, some data
I would like to bring into AF. If you've got a situation
like this with SQL Server, or really any
OLE DB-compliant database,
you can do that a couple of different ways. One of the ways we can bring it in
is to simply import it, in which case
we, we kind of bring it in en masse and,
and duplicate it as a Read Write Table
in AF. So it would basically be a
brand new Table. I can call this,
you know, Data from SQL or whatever it is
that it's actually called.
And, then, one of the Options here is, as you can see,
is Import. In the previous video we showed
you how to define one. You don't have to define it now; you simply have
to know where the Data is. And,
when you do Import this, you're going to have to know some
SQL specifics here. You're going to have to know how
to connect to that Table. Now the second way is
to link this, and by linking it what we mean
is we're simply establishing a link to this Table,
and then we're retrieving it as we are required to, as the
AF Clients request the information. And, of course,
that's, that's nice because it doesn't duplicate the data.
The data still stays in its original location. We only
bring it when we need to. It gives you a Read Only Link
to that original Table. So the Import
brings it in, creates a new Table that's Read Write.
Link will bring it in, a kind of an ad hoc
basis, just whenever you need it, and it'll be Read
Only. And, as you're going to notice,
as you're doing this, this is the point at which
we have to get somebody involved who's
got some Relational Database knowledge, who's familiar
with making SQL Queries, etc.
So, I'm sorry if, you know, if you're not, you're not the person
at your site who's, who's doing that kind of work, who's
familiar with making Queries like this
Select Statement you see up here. If you're not really involved
in that, better get them involved at this point because, you know, this
is the point at which we don't have a Graphical User Interface for
creating these Queries. We simply ask
you to use the SQL expertise you've got
locally for going out and making these retrievals.
As you're going to notice, there are tremendous
number of Options when it comes to Security, and
so, in this video, what we're going to do is just kind of
go over what some of those fundamentals are, and then point you
over to where you can learn more. And, again, at that point, you're
very likely going to have to get some local
IT resources to decide, you know, whether you're going to be
impersonating the Client, or connecting User SQL
credentials, etc., etc., all those choices.
So, first what we're going to do now is we're going to show you how
to Import, and then we're going to show Link to the existing
Table without duplicating the data into AF.
And then, later on, we're going to go over some of the
Security recommendations and where to find out more information about
that. So, I'm going to go back into my AF
Database, and this is the Table that I just started
creating. It's called Data From SQL. And I
specified I was going to use Import. So when
we click on Import, this is where you better, you know, have somebody
standing by who's familiar with your databases,
the names of the Relational Databases you're trying to connect to.
And also, you're going to need somebody who
knows how to build a Query. So, we'll start by building a
Connection String. That's what this field is all about. That's where you would
put a Connection String. If you already have a Connection String and you're familiar
with what that is, go ahead and use that. But I'm going to use
the Graphical User Interface that comes from Windows. This
is really basically a Windows construct here, and
what I need to do is go out and find the
existing Relational Databases and specify
how we want to retrieve the data. Now I know in this case
I've got data that's sitting in SQL Server, so I'm going to choose this
Microsoft OLE DB Provider for SQL Server.
I know that because if I switch over here you
can see I do have a SQL Server
Table. It's the same copy of SQL Server, by the way, that's holding
the AF SQL Database, and
that's no problem. It can either be that one or some different one.
And I've got a Table called Material Properties.
So I'll go ahead and choose this OLE DB Provider
for SQL. Now this
is where we need to choose which copy of SQL Server, which
instance of SQL Server we're connecting to. So I
know, if I take a look at this drop-down,
now if SQL's configured properly I should be able to see all the
copies of SQL that are out there,
all the, I guess they call them
Instances of SQL Server Running out on my
network. I'll go ahead and choose this one.
Now, we get to choose at this point whether we're
using Windows Integrated Security
or if we prefer to use a SQL
User Name and Password, you can go ahead and specify
SQL User Name and Password. Now that actually
has to do, the, the choice that you make here
is now where we're starting to get into some heavy-duty
system management topics because,
if you take a look at some of our documentation on this,
what we're going to specify is that
in order to do this you have to have a log-in
configured, and that log-in -- let me go ahead and
open that up -- that log-in can
either be specified to be using
the Windows Integrated Security
or SQL Server Security. So, in my
case, I'm using, within this log-in, I'm
using Windows Integrated Security, so that's
the one I'm going to be choosing in this case. Now,
all this stuff that we're seeing right here
is stuff that we're going to address a little bit later on when
we talk about security, so don't worry if this is something,
stuff you're not familiar with at this point. Again, that's why
we suggest that you get somebody involved who's familiar
with SQL, and specifically
applications like the SQL Server Management Studio
Application for configuring this. So, again, I
guess to make a long story short, what you put here
really depends on how things are configured
on SQL Server in your local environment.
We've got everything set up here so that we're using Windows
Integrated Security, so I'm going to choose that.
Let's go ahead and see if I can make the connection. , I,
I do see a list of the databases that are on that
Server. I'm going to choose this one. If I test my
connection, I can see that that succeeded, so this looks
like it's, it's good news. We're ready to, to go ahead
and make the retrieval or do the Import. And
the grand finale to this is to go ahead and write a
Select Statement. So, this is where I would say something like
select, density,
material id,
id from and then the Name of the Table.
I'll be honest. If you're not yourself
a SQL expert then you're going to have to go out and use either something like
MS Query or Access, or just even the tools
within Microsoft SQL Server itself to create that
Select Statement. Now, I've already created one here.
It's selecting these three
Columns: Material ID, Heat Capacity and Density,
from a total of -- there are some more,
there are some other Columns
as well I could have chosen from,
but I've just chosen three of the -- what are those, six columns that we
see here: Viscosity, Melting Temperature, etc. -- I could have
chosen those as well. But, anyway, I'm just going to Copy this
and this is what I'm going to Paste as my Query.
This is what we're going to be, what determines
what we're going to Import. I'll go ahead
and say OK. And,
if that's worked properly, then what we should see is --
there we go. We should see a brand new Table. Now, this is a Table that
we have access to, we have control over now, because
we've imported it. And, if I decide, well,
that's not 1.4, that should be 1.7,
that's no problem. I can do that because I've got Read Write
access to this, an Imported Table.
And you'll notice, if we were to do this,
instead of an Import we were to do this as a Link, you wouldn't,
you really would not have that option, because what we're doing really
is linking to the original. In this case we are
bringing this in, we're Importing it in to AF,
which some of you may recognize as being bad technique.
I mean, typically, you don't want to have duplicate data
spread across your system. You know, and now, now,
now there is the problem of what happens when you change
things here do they get changed on the original, etc.? But, it
is, it's an Option available, it's a popular feature
to be able to Import stuff en masse, you know, from,
from SQL Server. And, so, you can do that.