How do I query raw data from Proficy Historian?

How do I get raw time series data from Proficy Historian / iHistorian?

Ideally, I would request data for a specific tag between two dates.

+13


source to share


4 answers


There are several different sampling modes that you can experiment with.

  • Raw materials
  • Interpolated
  • Laboratory
  • Trend
  • Calculated

These modes are available using all of the following APIs.

  • Custom API (ihuapi.dll)
  • SDK (ihsdk.dll)
  • OLEDB (iholedb.dll)
  • API Client Acess (Proficy.Historian.ClientAccess.API)

Of these, trend sampling modes are probably what you want as it is specially designed for charting / trending. Although lab and interpolated can be helpful.

Read the e-book for more information on each sampling mode. On my machine it is stored as C:\Program Files\GE Fanuc\Proficy Historian\Docs\iHistorian.chm

and I have version 3.5 installed. Pay particular attention to the following sections.



  • Using the OLE DB Historian Provider
  • Advanced themes | Indexing

This is how you can plot an OLEDB to generate a trend sample.

set 
    SamplingMode = 'Trend',
    StartTime = '2010-07-01 00:00:00',
    EndTime = '2010-07-02 00:00:00',
    IntervalMilliseconds = 1h
select 
    timestamp, 
    value, 
    quality 
from 
    ihRawData 
where 
    tagname = 'YOUR_TAG'

      

Mapping the equivalent methods using the UI and SDK is tricky (indeed with the UI) as it requires a lot of plumbing in code to set it up. The Client Access API is newer and uses WCF behind the scenes.

By the way, there are several limitations in the OLEDB method.

  • Even though the documentation says I could never get my own query parameters to work. It's showstopper if you want to use it with SQL Server Reporting Services for example.
  • You cannot archive samples or make changes to the Historian configuration in any way, including adding / changing tags, recording messages, etc.
  • It can be a little slower in some cases.
  • It does not cross-tab multiple tags into columns and then wraps patterns so that there is a value for each timestamp and tag combination. The trend fetch mode gets you halfway through, but still not crosstabs, and doesn't actually load raw samples. Then the custom API and SDK can't do that either.
+15


source


A colleague of mine put this together:

In web.config:

<add name="HistorianConnectionString" 
     providerName="ihOLEDB.iHistorian.1" 
     connectionString="
       Provider=ihOLEDB.iHistorian;
       User Id=;
       Password=;
       Data Source=localhost;"
/>

      

In the data layer:

public DataTable GetProficyData(string tagName, DateTime startDate, DateTime endDate)
{
    using (System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection())
    {
        cn.ConnectionString = webConfig.ConnectionStrings.ConnectionStrings["HistorianConnectionString"];
        cn.Open();

        string queryString = string.Format(
                "set samplingmode = rawbytime\n select value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' and value > 0 order by timestamp",
                tagName.Replace("'", "\""), startDate, endDate);

        System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        DataSet ds = new DataSet();

        adp.Fill(ds);
        return ds.Tables[0];
    }
}

      


Update:



It worked well, but we ran into an issue with tags not updating very often. If the tag was not updated near the start or end of the requested startDate and endDate, the trends would look bad. Even worse, there were still cases where there were no explicit points during the requested window - we don't return data.

I solved it by doing three queries:

  • Previous value before start
  • Points between startDate and endDate
  • Next value after endDate

This is a potentially inefficient way to do it, but it works:

public DataTable GetProficyData(string tagName, DateTime startDate, DateTime endDate)
{
    DataSet ds = new DataSet();
    string queryString;
    System.Data.OleDb.OleDbDataAdapter adp;

    using (System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection())
    {
        cn.ConnectionString = proficyConn.ConnectionString;
        cn.Open();

        // always get a start value
        queryString = string.Format(
             "set samplingmode = lab\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
            tagName.Replace("'", "\""), startDate.AddMinutes(-1), startDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        // get the range
        queryString = string.Format(
             "set samplingmode = rawbytime\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
            tagName.Replace("'", "\""), startDate, endDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        // always get an end value
        queryString = string.Format(
             "set samplingmode = lab\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
        tagName.Replace("'", "\""), endDate.AddMinutes(-1), endDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        return ds.Tables[0];
    }
}

      

And yes, I know these queries need to be parameterized.

+4


source


Michael - IP21 has an Interpolated table as well as a fact sheet. Does Proficy have it too?

0


source


We wrote a wrapper DLL that looked like this:

[DllImport("IHUAPI.dll", CallingConvention = CallingConvention.StdCall, EntryPoint = "ihuReadRawDataByTime@24")]
public static extern int ihuReadRawDataByTime(int serverhandle, string tagname, ref IHU_TIMESTAMP startTime, ref IHU_TIMESTAMP endTime, ref int noOfSamples, ref IHU_DATA_SAMPLE* dataValues);
...
private int _handle;

public HistorianTypes.ErrorCode ReadRawByTime(string tagName, DateTime startTime, DateTime endTime,
                                              out double[] timeStamps, out double[] values, out IhuComment [] comments)
{
    var startTimeStruct = new IhuApi.IHU_TIMESTAMP();  //Custom datetime to epoch extension method
    var endTimeStruct = new IhuApi.IHU_TIMESTAMP();

    int lRet = 0;
    int noOfSamples = 0;
    startTimeStruct = DateTimeToTimeStruct(dstZone.ToUniversalTime(startTime));
    endTimeStruct = DateTimeToTimeStruct(dstZone.ToUniversalTime(endTime));
    IhuApi.IHU_DATA_SAMPLE* dataSample = (IhuApi.IHU_DATA_SAMPLE*)new IntPtr(0);

    try {
        lRet = IhuApi.ihuReadRawDataByTime
            (
                _handle, // the handle returned from the connect
                tagName, // the single tagname to retrieve
                ref startTimeStruct, // start time for query
                ref endTimeStruct, // end time for query
                ref noOfSamples, // will be set by API
                ref dataSample // will be allocated and populated in the user API
            );
            ....

      

Some notes are that iFIX checks to see if the DLL is loaded on startup, so you need to do things like dynamically load / unload the DLL to keep other applications from crashing. We did this by deleting / adding registry keys on the fly.

Another, if you poll 10,000 samples and 1 of the samples is damaged, it will lose all 10,000 samples. You need to implement a bad data processor that will start on each side of the bad data and step by step to get all the data on either side of the bad sample.

There are several C header files containing all the error codes and function header for the DLL.

0


source







All Articles