Skip navigation
All Places > PI Developers Club > Blog > 2014 > May
2014
skwan

Back to the Future

Posted by skwan May 14, 2014

Hi vCampus Members,

 

We are excited to announce the availability of our first Community Technology Preview (CTP) of the next major version of the PI Data Archive, code-named “DeLorean” and tentatively labeled “PI Data Archive 2014 R2”.  This release introduces two major features:  (1) the ability to store time series data with future time stamps and (2) the ability to migrate PI Batch data to PI Event Frames.  As with any CTP, please review the limitations detailed in the accompanying documentation before proceeding too far, and please conduct your evaluation in a test environment.  You can download the files from the Download Center in the Pre-Release category.

 

Prior to DeLorean, the PI Data Archive rejected data with timestamps that were greater than 10 minutes into the future relative to the server’s clock.  Because of this limitation, users that still needed future data have been forced to deploy complex and costly workarounds that offer a limited user experience at best.  CTP1 finally removes this limitation and allows you to store data with timestamps throughout the entire UTC time range:  January 1970 to January 2038.  In order to support this new storage capability, the PI Data Archive is introducing the concept of “future PI Points” or “future tags” allowing separation from all the data for traditional “historical PI Points” or “historical tags”.  While the PI Data Archive is critical to unlocking the value of future data in the PI System, many more components – particularly our PI System Access (e.g., PI AF SDK) and PI Visualization (e.g., PI Coresight and PI ProcessBook) products – are also getting updated in order to deliver a superior user experience.  New versions of these other products are not available with CTP1, but we are providing detailed documentation on limitations and workarounds based on released versions so that you can start your evaluation of future data right away.

 

PI Event Frames were introduced several years ago, and rollout of their implementation throughout the PI System has occurred in different waves.   As we near the completion of “Wave 2”, we are focusing our efforts on providing PI Event Frames support for standard PI Batch users.  CTP1 provides the ability to perform a complete, one-way migration of your PI Batch data to PI Event Frames in order to establish a new system of record.  During and after migration, the entire PI Batch database remains in a read-only state for reference purposes.  Just like with future data storage, CTP1 has several limitations, so please consult the accompanying documentation for details.

 

Even though CTP1 is not feature complete and has significant limitations, soliciting early feedback from our technical and forward-thinking vCampus community will only make our upcoming release that much more reliable and valuable.  We build the OSIsoft ecosystem together, and we hope that you share our enthusiasm for DeLorean’s features and their potential for the PI System.  If you have any questions or wish to provide feedback, please contact us at BetaPIServer@osisoft.com.

 

Thank you for your time and vibrant engagement.

 

Regards,

 

Doc Brown and the PI Data Archive Team

On this blog post, I will share with you my finding about how to create an CLR assembly stored it in the  SQL Server database to communicate with your PI Data Archive with PI SDK. But I want to warn you that creating a new project with PI SDK is not a recommended solution since it is based on an old technology. On the other hand, PI AF SDK which is  the recomemnded technology cannot be used on an CLR assembly as it is shown next.

Background

There are two major building blocks in Microsoft platform: DLL and exe. One is useful to support execution and the other is executed on an individual level. Basically SQL Server also provides the facility to create assembly files whose methods could be accessed from a SQL Server assembly. Here an assembly (DLL) file is created, stored in the database and accessed via a Stored Procedure.

 

I am currently using Visual Studio 2013 on a client machine and SQL Server 2012 SP1 x64 on a Server machine. PI SDK 1.4.2 x64 is installed on both machines.

Creating the Class Library

Open Visual Studio and create a SQL Server Database Project and shown on Figure 1.

 

 

 

2330.fig1.jpg

 

Figure 1 – Creating a new SQL Server Database project.

 

 

 

On Solution Explorer, right click on the project and choose Add-->New Item… Click first on “SQL CLR C#” on the left pane and on the option “SQL CLR C# User Defined Function” on the right pane.

 

 

 

5852.fig2.jpg

 

Figure 2 – Creating a new SQL CLR C# User Defined Function.

 

Add the 64-bit assemblies of OSIsoft.PISDK and OSIsoft.PISDKCommon. Make  sure they are located on the 64 bit PIPC folder (C:\Program Files\PIPC  by default). Check the properties from both files. Make sure to change the option called “Copy Local” to “True” as shown on the Figure 2.

 

 

 

3107.fig3.jpg

 

Figure 3 – Properties of PI SDK references.

 

 

 

Copy the content below and paste it on the file PISDK_CLR_StoredProcedure.cs.

 

 

 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using PISDK;


public partial class StoredProcedures
{

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void InsertSnapshotValuePISDK(SqlString piServerName, SqlString piPointName, SqlString snapshotValue)
    {
        Server myPIServer = null;

        try
        {
            PISDK.PISDK sdkroot = new PISDK.PISDK();
            myPIServer = sdkroot.Servers[piServerName.ToString()];
            myPIServer.Open();
            PIPoint myPIPoint = myPIServer.PIPoints[piPointName.ToString()];
            myPIPoint.Data.UpdateValue(snapshotValue.ToString(), DateTime.Now);
        }

        catch (Exception ex)
        {
            SqlContext.Pipe.Send("An error occured" + ex.Message + ex.StackTrace);
        }

        finally
        {
            if (myPIServer != null)
            {
                myPIServer.Close();
            }
        }
    }
}

 

 

Build the project and open the Debug folder. Make sure that there are four files with DLL extension (3 of them related to PI SDK).

 

 

 

6082.fig4.jpg

 

Figure 4 – Files created on the debug folder after building the solution

 

 

 

Copy all the DLL files to the SQL Server machine and paste it where you find it appropriate. In my case, I have pasted them on the folder C:\Program Files\PIPC\CustomLibraries\CLR_Assemblies\PISDK_CLR_Sample.

Create the assembly in SQL Server with a specified path

 

 

Open SQL Server Management Studio (SSMS) and create a new database on the SQL Server to stored your assemblies. In my case, I have created and named the new database SampleCLRs.

 

Make sure the CLR is enabled by executing the following code snippet:

 

 

 
sp_configure 'clr enabled', 1;
GO

 

 

Since PI SDK access external resources, you need to give access to your SQL Database to store this type of library. The following SQL query might be used to achieve this goal:

 

 

 
ALTER DATABASE SampleCLRs SET TRUSTWORTHY ON;

 

 

After following these steps, you can register the PISDK_CLR_StoredProcedure.dll:

 

Create the assembly in SQL Server with a specified path:

 

 

 
Create Assembly PISDK_CLR
From 'C:\Program Files\PIPC\CustomLibraries\CLR_Assemblies\PISDK_CLR_Sample\PISDK_CLR_StoredProcedure.dll'
WITH PERMISSION_SET = UNSAFE
GO

In order for the command above to work, it requires that the SysAdmin privilege to enable the SQL CLR, since it is an instance wide configuration change. Also, an assembly marked as “UNSAFE” will likely trigger a strong response from the DBA.

 

And also create the stored procedure:

 

 

 
CREATE PROCEDURE dbo.InsertValuesPISDK
(
 @piServerName nvarchar(50),
 @piPointNamee nvarchar(50),
 @snapshotValue nvarchar(50)
)
AS EXTERNAL NAME PISDK_CLR.StoredProcedures.InsertSnapshotValuePISDK;
GO

 

 

Finally, execute the procedure:

 

 

 
EXECUTE [dbo].[InsertValuesPISDK]
'piserver_name’,'pipoint_name','snapshot_value'
GO 

 

 

For this specific test, you will have to create a test PI Point whose point type is string.

Why not using PI AF SDK instead?

 

 

Since PI AF SDK is our most modern technology and considering the fact that this reference  is able to communicate with the PI Server directly, why is this post about PI SDK and not PI AF SDK instead?

 

The answer is simple: it is not possible to do a similar procedure with PI AF SDK. When you execute the CREATE ASSEMBLY command on the SQL Server, even if you have copied all the required .NET libraries to the custom assembly folder, you will ultimately receive the following error message:

 

"Msg 6544, Level 16, State 1, Line 1

 

CREATE ASSEMBLY for assembly 'System.ServiceModel' failed because assembly 'microsoft.visualbasic.activities.compiler' is malformed or not a pure .NET assembly. Unverifiable PE Header/native stub."

 

According to this article, since some of PI AF SDK referenced libraries are not pure .NET assemblies, this procedure can only be used with PI SDK.

Conclusions

 

 

Although a custom CLR assembly could be developed with PI SDK, since OSIsoft is developing its newer products on top of PI AF SDK, it is likely that this solution won't be used long-term. PI SDK misses out on RDA performance which comes with PI AF SDK starting on version 2012 (2.5).

 

PI OLEDB Enterprise should be used as the product to access the PI System from the SQL Server.

 

Nevertheless, if there are good reasons to develop a custom CLR assembly, I would try to develop it on top of PI Web API which wraps PI AF SDK. I am not sure if this would work, but definitely it would be my first option to try. In this case, you would have access to the PI AF Server and also to the PI Data Archive.  It would be possible to send values to the PI Server. Even if you upgrade PI Web API, you would not have issues with the assemblies. IMHO, the disadvantage would be low performance when compared to the PI OLEDB Enterprise but tests should be carried out before drawing conclusions.

 

I hope you enjoyed this blog post and stay tuned for the next ones!

Filter Blog

By date: By tag: