EE - Forum Styles
fo

Extract DVCH data to MS Access

I am looking to extract specific data from the Continuous Historian and dump it into MS Access on a regular (or request) basis. The scenario is this: A database in MS Access is set up for operators to enter manually recorded Chromatograph data during their shift; other Chromatographs are being historized in DVCH. The operations group would like to generate reports based off of the data in Access and DVCH. The developer would like for all of the data to be placed into MS Access so that they can manipulate it there. The eventual goal would be for them to be able to query any data in the DVCH to do whatever they want.

I realize that programmatic SQL access to the DVCH is not possible (and very difficult it seems for the DV ACH); also the developer does not want to involve Excel in any way.

My research has lead me to at least two possibilities:
1. Use an OPC-ODBC driver to push the data to MS Access on a regular basis from DeltaV.
2. Use OPC-HDA to get at the data and pull it into MS Access on some schedule.

Option #1 is neat and I have been successful with it, but it is not the preferred solution because it requires more work than it seems is necessary just to get at historized data.

Option #2 or something similar is the route I would like to go, but I am getting stuck conceptually on how to use the OPC-HDA interface to extract the data. If I could use the ExcelReporter style functions in MS Access from VBA, I can visualize how that could work (Executing a data request and then inserting the data into a table).

Does anyone have any experience with this idea and making it work?

Thank you

  • Have you looked at the "VBA support for historical data" topic in Books Online?

    VBA support for historical data
    The Excel Add-in for DeltaV Reporter includes support for writing a custom VBA program that accesses historical data read from the Continuous Historian or Event Chronicle database using DeltaV Reporter. This topic describes the VBA objects, methods, and properties provided for using a custom VBA program with DeltaV Reporter. The DeltaV software includes a sample VBA program for writing historical data.
  • In reply to Brian Atkinson:

    Hi Brian,

    I did see that in BOL, but it is all focused on Writing data to the historian from Excel. I had hoped that playing with the code would reveal some non-documented method of reading data, but the only thing available is writes to the DVCH. Unfortunately, this doesn't help when trying to read and when trying to keep Excel out of the process.
  • In reply to dave_marshall:

    Dave, Some time ago, I considered a solution to a similar need using Cogent DataHub historian. We use the datahub now to pass indication-only points back and forth between DeltaV and Yokogawa. The historian can be set up to read the same points presently in your DVCH, which may seem redundant, but it you give options accessing that data. Their products are flexible, and you can purchase the components you need, plus, you can try-before-buy, which makes proof of concept testing possible.
  • There is a command line program called DVCHDUMP in the DeltaV\bin directory. This is more of a troubleshooting tool and not considered as a supported part of DeltaV (so there is nothing in BoL about it), but if you can figure out the command line syntax it should be able to extract data from the historian into a text file. Then (as you identified) use ODBC or some other Access technique to import the data from the file to your database.

    You could create a CMD file with the necessary commands (here I am assuming there is a command-line interface to Access; but I have not done that).

    Type DVCHDUMP with no parameters at a command prompt and you will get a fairly extensive message showing the options that it supports. You can pass it a history tag name and start/end times of an interval and it will show you all the data it has - either raw samples or interpolated in various ways.

    It may take some work to familiarize with the command line syntax, but once that is done this may be easier than programming an OPC-HDA solution.
  • Hello @dave_marshall~

    Sharing this solution which I received from our Reliability Solutions Team. Please let us know if this solves your problem. :)

    The procedure on how to extract data from the historical archives through OPC HDA is as follows:

    1. From the Start menu > Run > type “hdaprobe” then click OK.
    2. The DeltaV OPC HDA dialog box now opens.
    3. Click the “Connect Server” then select “DeltaV.OPCHDAsvr.1”.
    4. Click “Add Items”. Add HDA Items dialog appears.
    5. Add HDA Items dialog appears. Click “Browse”
    6. OPC HDA Browse dialog appears. Click “New Browser”. The space under OPC HDA Item ID will be populated with tags.
    7. Select the tags that you like then click OK. The tag will be added in the Add HDA Items dialog.
    8. Click Add then Done.
    9. Back to the DeltaV OPC HDA dialog box, highlight a tag, then click Read Raw.

    Best Regards,

    Rachelle McWright | Community Manager, Emerson Exchange 365

    Share. Learn. Network

  • In reply to Will Irwin:

    Hi Will,

    I ran across this tool as well and experimented with it. It certainly is workable and I can write some logic in Access to:
    1. Auto-generate the XML file that feeds it (getting the desired tags from a table)
    2. Execute the utility
    3. Parse the output from the utility into a table

    I just am reluctant to depend on something like that, but you are absolutely correct: it would be easier than programming an OPC-HDA solution. That would require a significantly larger amount of time, although it would be awesome to dig into that kind of interface and fully understand it.
  • In reply to Rachelle McWright:

    Hi Rachelle,

    The problem I have with HDAProbe is that it is a test tool. It allows me to check on the data and my connection, but I can't set up some sort of automated extraction with it.

    What would be nice from Emerson is if they were able to extend Excel Reporter to work in MS Access and give it VBA extensions for total control.

    I've started to explore what the Advanced Continuous Historian has to offer in terms of OSI PI and any data connectors it has that allow for SQL type of access.

    Thanks!
  • In reply to dave_marshall:

    Hi ,

    I reached out to one of our experts on this topic, , who will back in action on Friday. Please expect to hear from him at that time.

    Best Regards,

    Rachelle McWright | Community Manager, Emerson Exchange 365

    Share. Learn. Network

  • In reply to dave_marshall:

    Do you have a batch historian or a event chronicle? Is the work and data possibly contextualized within a batch model?

    If yes, consider leveraging the SQL architecture of these data sources driven by batch execution to achieve your goals. For instance, rather than mining the continuous historian, have the batch execution activities perform aggregation of the data into a workable structure stored in the batch historian or event chronicle SQL.

    For instance, if you need the min or max (like UV peak in a chromatograph) have a min/max function in DeltaV control logic enabled/disabled during the run (elution?) and report the information to the chronicle or batch historian through phase logic. The SQL query becomes pretty straightforward at that point, and there is no need to duplicate data into an access database.

    Additionally, the interface can all be built in DeltaV so that operators don't have to switch back and forth from the DCS to Access.
  • In reply to Youssef.El-Bahtimy:

    Hi Yousssef,

    Unfortunately, it is not batch related, but is for a continuous facility. Originally my plan was to use an OPC to ODBC driver to push data to Access on defined intervals, but for various reasons I've put that solution aside.

    I'm currently exploring options with PI and the Advanced Continuous Historian. I can see from OSI's website that they have an ODBC driver, so I am trying to procure a demo version. The downside here is that I know almost nothing about PI, but my customer does have some PI developers that I will lean on when I get far enough to actually mine data.

    The customers end goal is for them to be able to have access to the data tables and execute their own SQL queries against them. My hope is that the PI-ODBC interface will give them that ability.
  • In reply to dave_marshall:

    From either DeltaV Reporter or from Sytech's XLReporter, you should be able to setup automatic retrieval and export of data to an inter-operable file format like csv, then use task scheduling to execute regular imports of the data into the access database. From the standpoint of Emerson and Sytech's product development, Excel is far more prolific than Access for end users, and can be imported into any database type, so there is less motivation to code for Access specifically.

    Assembling PI infrastructure is always a great idea, provided you expand upon it and extract more value out of it than a single purpose endeavor.
  • Hello Dave,

    There is a solution using something like you outlined in Option #2 in your original post. It is possible to create an .EXE or service that pulls data from OPC-HDA and sends it to MS Access. The starting point for you would be DeltaV's "OPC Xi (.NET) HDA Read Client" and modify it to remove the UI and read the historical data that you need. Also, within this client you could make the connection to MS Access and populate the tables with the HDA reads. This can all be run in the background without any user interaction - it may even be possible to do this from an MS Access VBA program (I'm not sure on that, but it can be done at least with an interface .EXE/service). Let me know if you would like to proceed down this path and I will send you the "OPC Xi (.NET) HDA Read Client" code and some instructions on how to use it. Then you can turn loose and integrate your data free from Excel and manual steps.

    Best Regards,
    Brian Crandall
    DeltaV Historian and OPC Marketing Manager