How do I query raw data from a Proficy Historian?

Question

How can I retrieve raw time-series data from a Proficy Historian/iHistorian?

Ideally, I would ask for data for a particular tag between two dates.

Answer 1

A coworker 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:

This worked well but we ran into an issue with tags that don't update very often. If the tag didn't update near the start or end of the requested startDate and endDate, the trends would look bad. Worse, still were cases where there were no explicit points during the window requested--we'd get no data back.

I resolved this by making three queries:

1. The previous value before the start-date
2. The points between startDate and endDate
3. The next value after the 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, those queries should be parameterized.

Answer 2

Michael--in IP21 there is an "Interpolated" table, as well as the "actual" data point table. Does Proficy have that as well?

Answer 3

There are several different sampling modes you can experiment with.

• Raw
• Interpolated
• Lab
• Trend
• Calculated

These modes are available using all of the following APIs.

• User API (ihuapi.dll)
• SDK (ihsdk.dll)
• OLEDB (iholedb.dll)

Of these the trend sampling mode is probably what you want since it is specifically designed for charting/trending. Though, lab and interpolated may be useful as well.

Read the electronic 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 Historian OLE DB Provider
• Advanced Topics | Retrieval

Here is how you can construct an OLEDB to do trend sampling.

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'

Showing the equivalent methods using the User API and the SDK are complex (more so with the User API) since they require a lot of plumbing in the code to get setup.

By the way, there are a few limitations with the OLEDB method though.

• Despite what the documenation says I have never been able to get native query parameters to work. That is a showstopper if you want to use it with SQL Server Reporting Services for example.
• You cannot write samples into the archive or in any way make changes to the Historian configuration including adding/changing tags, writing messages, etc.
• It can be a little slow in some cases.
• It has no provision for crosstabbing multiple tagnames into the columns and then carrying forward samples so that a value exists for each timestamp and tag combination. The trend sampling mode gets you halfway there, but still does not crosstab and does not actually load raw samples. Then again the User API and SDK cannot do this either.