andreas

Manual Input to PI from Microsoft SharePoint?

Blog Post created by andreas on Jan 14, 2009

Introduction

Hi there!
Have you ever thought about a manual input to PI from SharePoint?

So here is what you need (at least if you do it like I did it ):

  1. Microsoft Office SharePoint 2007
  2. A SQL Server
  3. The PI OLEDB Provider
  4. And RtWebParts – because entering data is not so much fun if you cannot trend it!

O.k. now – what are we going to do?

Overview

Microsoft Office SharePoint Designer 2007 allows you to create a custom view of data – a Data View. The Data View supports various sources, in this example we will use a SQL query. The interesting part is that the Data View allows you to have an insert query as well.
And here starts the fun; we are going to insert a value from a Data View in a SQL table.

Wait a moment - this is vCampus, so what about PI? Well, we will create a job that moves that data from the SQL table to the PIArchive (via a linked server) – and voila – we have a manual input to PI from SharePoint (somehow ).
You will not get bored with the preparation - I have already a tag called ManualInputSQL, a table in my SQL server called ManualInputTest (with a datetime column as primary key and a value column) and I have created my linked PI Server called RtDemoPI.

Create a Web Part Page

So I am starting from a blank SharePoint Web Part page – and as I told you, we want to do some RtWebParts.

  1. We add an RtTrend and an RtTimeRange:

  2. RtWebpartPage1.jpg

  3. Now we are going to open this page in Microsoft Office SharePoint Designer:

  4. SharepointDesigner1.jpg

Create a Database Connection

To use a Data View we have to create a Database Connection first.

  1. We go to Data View > Manage Data Sources
  2. On the right hand column we can now expand Database Connections and select Connect to a database.
  3. We configure the database connection (in this case the server is the SharePoint server and I use the "sa" account to connect to the database.

  4. ConfDBConnection1.jpg

Note that this authentication option saves the username and password as clear text in the data connection, but for our test here this should be o.k.

  1. In the following dialog I specify the database and the table:

  2. ConfDBConnection2.jpg
  3. I am done with this part.

  4. DataSourceProp1.jpg
  5. Finally we press OK to finish the configuration of the database connection.

Put the Data View on the Web Part Page

  1. We Drop the Database connection to the Web Part Page to use the Data View:

  2. SharepointDesigner2.jpg
  3. And we change the Data View properties so that we can insert a value:

  4. DataViewProp1.jpg
  5. So it is time to see how it looks like and how we can insert data.

  6. RtWebpartPage2.jpg

The SQL Server

Now let us look at the job to move the data from SQL to PI:

  1. Open the Microsoft SQL Server Management Studio.
  2. Expand the SQL Server Agent and create a new job.
  3. Give the job a meaningful name and click the Steps page.
  4. The first step is to copy the data (remember, the PI server is a linked server called RtDemoPI, rtdemogmbh is the database, manualinputtest is the table and ManualInputSQL is the tag):

  5. INSERT rtdemopi.piarchive..picomp (tag,time,value)
    SELECT 'ManualInputSQL'AS tag,time,value
    FROM
    rtdemogmbh..manualinputtest

  6. The second step to delete the data:
  7. DELETE FROM rtdemogmbh..manualinputtest

  8. And we schedule the job to run every minute. If we have done this – we have our manual input to PI from Microsoft SharePoint!

  9. RtWebpartPage3.jpg

Outcomes