Accessing PI as a Relational Database or an OPC Server

Introduction
This class provides guidance to developers who want to design custom applications where PI acts as an OPC or a Relational database. It demonstrates how to use the different products included in the PI Data Access (PI DA) module. That module allows developers to access and manage PI data via the following communication standards:

• ODBC
• OLEDB
• OPC-DA
• OPC-HDA

The objective of this course is to teach the participants how to make use of the technologies comprised in the PI DA module to design and develop new applications, or integrate PI functionalities to already-existing applications.

The class is two days long. Class starts at 8:30 a.m. and ends at approximately 4:30 p.m. Lunch breaks are at noon each day and last 60 minutes.  This class provides 13 classroom hours of instruction.

Audience
The class is for application programmers who want to access/manage PI data from custom applications, using proven and standard technologies.

PI System Managers and integrators with development skills should also consider taking this class to add functionality/flexibility to their PI System.

Students in the past have been engineers and information system analysts. Students usually are familiar with a programming language such as Visual Basic 6, Visual Basic .NET, Visual C++ or C#. The participants usually also have a basic knowledge of the SQL language and concepts, as well as an understanding of the OPC concepts.

Because ODBC, OLEDB and OPC are communication standards and can be used in various environments, participants often already have applications that use relational databases (lab and maintenance systems, for example) or OPC data sources, to which they want to integrate PI functionalities.

Prerequisites
We expect students in this class to have attended our PI ProcessBook and PI DataLink class. They must be familiar with programming (Visual Basic 6, Visual Basic .NET or C#) as well as the SQL language.

• PowerPoint Presentation and Exercises* 
• PI ODBC Documentation 
• PI OLEDB Documentation
• PI OPC DA/HDA Server Documentation

 *Although exercise solutions are designed for Visual Basic 6, source code is available in Visual Basic .NET, and students can get questions answered in C#.

• PI System Architecture
• PI Point Database
• PI System Data Flow
• PI Security
• PI Time

• Connections Manager
• Tag Search
• PI Login
• Tag Attributes

• Introduction to PI OLEDB and PI ODBC
• Organization of the information
• SQL Syntax Tutorial
• Familiarization with OSIsoft's OLEDB clients
• Access of PI information through Microsoft Excel and Access
• Access/management of PI information through Microsoft SQL Server*
• Design of SQL queries to read, create, modify or suppress PI information through ODBC/OLEDB standards
  • PIArchive tables
  • PIBatch tables
  • PIDS (digital states) tables
  • PIHeading tables
  • PILog tables
  • PIModule tables
  • PIPoint tables
  • PIUser tables
• Familiarization with ADO and ADO.NET technologies
• Access and manage PI data via VBA and VB.NET code that uses PI ODBC/PI OLEDB

• Introduction to PI OPC DA/HDA Server
• Organization of the information
• Presentation of the different OPC specifications
• Familiarization with OSIsoft's OPC tools
• Familiarization with OPC Automation and OPC .NET API technologies
• Development of VB6 code to use OPC DA and HDA specifications via OPC technology
  • Connect to OPC Server with DA specification
  • Read PI values with DA specification
  • Write PI values with DA specification
  • Connect to OPC Server with HDA specification
  • Access tag attributes with HDA specification
  • Read PI values with HDA specification
  • Write PI values with HDA specification
  • Obtain aggregates using with HDA specification

* This is taught using the Microsoft SQL Server "Linked Server" concept, and is also possible through Oracle "Generic Connectivity"

• Explain the PI System
• Draw a diagram of the architecture of a PI System
• Describe how the data flows in PI
• Describe a PI Point
• List the basic point attributes and their usage
• Express absolute time in the proper format
• Express relative time in the proper format
• Select proper parameters for the exception test
• Select proper parameters for the compression test
• Create new connections to a PI Server using a common dialog window
• Modify existing connections using a common dialog window
• Search for points using a common dialog window
• Look at point attributes using a common dialog window
• Identify the products included in the PI DA module
• Expose the purpose and the capabilities of the products
• Explain what PI ODBC is
• Identify what information is accessible from PI ODBC
• Explain what PI OLEDB is
• Identify what information is accessible from PI OLEDB
• Explain the role of the PI SQL Subsystem
• Define the basics of the SQL language
• Explain the particularities of the implementation of the SQL language in PI
• Design SQL queries to list, update, delete or insert data in the following catalogs
  • PIArchive
  • PIBatch
  • PIDS (digital states)
  • PIHeading
  • PILog
  • PIModule
  • PIPoint
  • PIUser
• Indicate the possible architectures for setup of PI ODBC and PI OLEDB, with their performance implications
• Install and configure PI ODBC and PI OLEDB
• Access PI data from Microsoft Excel and Access
• Explore PI tables using PI OLEDB Snap-In for MMC (Microsoft Management Console)
• Configure PI as a Linked Server in Microsoft SQL Server
• Using Microsoft Query Analyzer, design queries that access PI tables through a SQL Server Linked Server
• Create SQL Server views that integrate information from PI tables
• Explain what ADO and ADO.NET are
• Describe the usage of the main ADO and ADO.NET objects
• Use ADO objects in VBA (under PI ProcessBook, for example)
• Use ADO.NET objects in VB.NET
• Explain what the OPC DA/HDA specifications are
• Identify what information is accessible through the PI OPC DA/HDA Server
• Define the different OPC specifications
• Differentiate the DA and HDA specifications
• Indicate the possible architectures for setup of PI OPC DA/HDA Server, with their performance implications
• Setup and configure PI OPC DA/HDA Server
• Using the PI_OPCClient utility:
  • Connect to OPC Server
  • Read PI values
  • Write PI values
• Using the PI_HDATool utility:
  • Connect to OPC Server
  • Access tag attributes
  • Read PI values
  • Write PI values
  • Obtain aggregates
• Explain what OPC Automation and OPC .NET API are
• Describe the usage of the main OPC Automation objects
• Using OPC Automation in VB6:
  • Connect to OPC Server with DA specification
  • Read PI values with DA specification
  • Write PI values with DA specification
  • Connect to OPC Server with HDA specification
  • Access tag attributes with HDA specification
  • Read PI values with HDA specification
  • Write PI values with HDA specification
  • Obtain aggregates using with HDA specification

Back to class list