Exposing PI Data with the PI SQL Framework

Learn the best way for your organization to integrate the PI System into applications built for relational databases.

About this course

This course is primarily suited for the professional who develops applications that interact with the PI System in a relational database (SQL) manner. It is for someone who needs to understand the various product options for interacting with the PI System using SQL queries, develop SQL queries to run against these query engines, and integrate these queries into production applications. Overall, this course is intended to prepare you to develop the best way for your organization to integrate the PI System into applications built for relational databases. Generic SQL knowledge will not be taught in this course.

By the end of the course, you will be able to:

  • Select the optimal PI SQL product(s) and architecture for your environment
  • Set up a simple PI SQL framework architecture
  • Develop PI SQL queries against one or more of the query engines in the PI SQL Framework
  • Integrate PI SQL queries into third-party SQL based applications such as Microsoft SQL Server Integration and Reporting Services

The course has multiple paths depending on which products you will use. To successfully complete the course, all students must complete the videos and exercises pertaining to PI SQL Client/RTQP. The additional optional exercises and videos are tagged with the query engine or endpoint that they pertain to. If these do not apply to your scenario, feel free to skip these sections.

  • OLEDB Provider - These sections are for those still using PI OLEDB Provider for the query engine, as opposed to the new RTQP Engine
  • OLEDB Enterprise - These sections are for those still using PI OLEDB Enterprise for the query engine, as opposed to the new RTQP Engine
  • ODBC - These sections are primarily for those using applications that require an ODBC connection to data source
  • JDBC - These sections are primarily for those using Java and JDBC in their projects

Audience

This course is for someone who needs to understand the various product options for interacting with the PI System using SQL queries, develop SQL queries to run against these query engines, and integrate these queries into production applications.

Level: Introductory

Study Time: 30 hours

Course Access: Unlimmited access. The only exception is the Training Cloud Environment for which you have 30 day access. After those 30 days you can purchase additional access with one of the two options below:

Prerequisites

  • Basic knowledge of the PI System (AF hierarchies, elements, attributes, templates, PI points, etc), and your own data and AF hierarchies.
  • Basic knowledge of SQL query writing
    • This includes things such as SELECT, FROM, WHERE, INNER JOIN, CROSS APPLY 
    • For PI OLEDB Provider Write queries, this includes INSERT, UPDATE, and DELETE
    • For assistance in this topic, we recommend a training site such as General SQL Training - w3schools
  • A computer that can access our YouTube content, and pass our connection test

This Course Includes...

  • Videos, exercises and quizzes to help you learn the material
  • A Cloud Environment accessible for 30 days and configured to complete all the exercises in the course
  • Sharable certificate of completion

Further Information

  • This is a self-paced course. Any questions or assistance needed about the material can be asked in this course's space in the OSIsoft PI Square community
  • When you complete the examination at the end of the course, you will receive a certificate of completion which can be shared and directly posted on LinkedIn.
  • For more information about our Online Courses please visit our FAQ page

You can audit the full video lecture content right now on the OSIsoft Learning YouTube Channel

Course Outline

  • Getting Started
  • Key Course Information
  • Course Grading Scheme
  • How to Navigate This Course
  • Offline Course Videos for Blocked YouTube Users
  • Lesson 1 - Architecture and Product Selection
  • Overview of PI SQL Framework Architecture
  • Which PI SQL Framework should I pick?
  • Deciding Between Direct OLEDB Connections or Routing Through an External Database
  • Knowledge Check: Selecting the Best Product
  • Solution: Selecting the Best Product
  • Lesson 2 - Installation and Configuration
  • Install, configure and test PI SQL DAS (RTQP Engine) & PI SQL Client
  • Knowledge Check: PI SQL Client (OLEDB) and RTQP Configuration
  • Solution: PI SQL Client (OLEDB) and RTQP Configuration
  • Lesson 3 - Writing Queries
  • Understand the basics of RTQP Engine schema
  • Using Functions Within the RTQP Query Engine
  • Knowledge Check: Writing PI SQL Client and RTQP Queries
  • Solution: Writing PI SQL Client and RTQP Queries
  • Knowledge Check: Write Transpose Queries for RTQP (PI SQL Client)
  • Solution: Write Transpose Queries for RTQP (PI SQL Client)
  • Using AFSDK Pass-through Search Queries in the RQTP Query Engine
  • Knowledge Check: Creating Custom Functions with the RTQP Engine
  • Solution: Creating Custom Functions with the RTQP Engine
  • RTQP Engine vs. PI OLEDB Enterprise performance
  • RTQP Performance Considerations
  • Lesson 4 - Installation and Configuration for OLEDB Provider and OLEDB Enterprise (Optional)
  • Install and test PI OLEDB Provider and PI OLEDB Enterprise
  • Knowledge Check: PI OLEDB Provider Configuration
  • Solution: PI OLEDB Provider Configuration
  • Knowledge Check: PI OLEDB Enterprise Configuration
  • Solution: PI OLEDB Enterprise Configuration
  • Install, configure and test PI ODBC Driver
  • Knowledge Check: PI ODBC Driver Configuration
  • Solution: PI ODBC Driver Configuration
  • Install, configure and test PI JDBC Driver
  • Knowledge Check: PI JDBC Driver Configuration
  • Solution: PI JDBC Driver Configuration
  • Knowledge Check: Connect to PI System from DBVisualizer using PI JDBC Driver
  • Solution: Connect to PI System from DBVisualizer using PI JDBC Driver
  • Read: How to Customize GetSnap and GetEASnap for PI JDBC Driver
  • Knowledge Check: GetEASnap for PI JDBC Driver
  • Solution: GetEASnap for PI JDBC Driver
  • Lesson 5 - Writing Queries for OLEDB Provider (Optional)
  • Write queries using PI OLEDB Provider Schema
  • Knowledge Check: Making PI OLEDB Provider Queries - Reads
  • Solution: Making PI OLEDB Provider Queries - Reads
  • Knowledge Check: Making PI OLEDB Provider Queries - Writes
  • Solution: Making PI OLEDB Provider Queries - Writes
  • Lesson 6 - Writing Queries for OLEDB Enterprise (Optional)
  • Write queries using PI OLEDB Enterprise schema
  • Knowledge Check: Making PI OLEDB Enterprise queries
  • Solution: Making PI OLEDB Enterprise queries
  • Create transpose functions to customize PI OLEDB Enterprise queries
  • Knowledge Check: Writing PI OLEDB Enterprise Transpose Queries
  • Solution: Writing PI OLEDB Enterprise Transpose Queries
  • Optimize PI OLEDB Enterprise query performance
  • Lesson 7 - Guided Project
  • Introduction to guided project - Create a SSRS application
  • Prepare custom database objects and queries
  • Set up a MS SQL Server linked server
  • Knowledge Check: Using a Microsoft SQL Server Linked Server
  • Solution: Using a Microsoft SQL Server Linked Server
  • Create an SSIS package in MS Visual Studio
  • Deploy an SSIS package to a SQL server
  • Build and Deploy SSRS reports to showcase PI System data
  • Final Exam
  • Final Exam
  • Course Evaluation
  • How did it go?
  • Training Cloud Environments
  • Cloud Environments Introduction
  • Cloud Environments Instructions
  • Launch Cloud Environment
  • Next Steps
  • Additional Resources
  • End of the course

About this course

This course is primarily suited for the professional who develops applications that interact with the PI System in a relational database (SQL) manner. It is for someone who needs to understand the various product options for interacting with the PI System using SQL queries, develop SQL queries to run against these query engines, and integrate these queries into production applications. Overall, this course is intended to prepare you to develop the best way for your organization to integrate the PI System into applications built for relational databases. Generic SQL knowledge will not be taught in this course.

By the end of the course, you will be able to:

  • Select the optimal PI SQL product(s) and architecture for your environment
  • Set up a simple PI SQL framework architecture
  • Develop PI SQL queries against one or more of the query engines in the PI SQL Framework
  • Integrate PI SQL queries into third-party SQL based applications such as Microsoft SQL Server Integration and Reporting Services

The course has multiple paths depending on which products you will use. To successfully complete the course, all students must complete the videos and exercises pertaining to PI SQL Client/RTQP. The additional optional exercises and videos are tagged with the query engine or endpoint that they pertain to. If these do not apply to your scenario, feel free to skip these sections.

  • OLEDB Provider - These sections are for those still using PI OLEDB Provider for the query engine, as opposed to the new RTQP Engine
  • OLEDB Enterprise - These sections are for those still using PI OLEDB Enterprise for the query engine, as opposed to the new RTQP Engine
  • ODBC - These sections are primarily for those using applications that require an ODBC connection to data source
  • JDBC - These sections are primarily for those using Java and JDBC in their projects

Audience

This course is for someone who needs to understand the various product options for interacting with the PI System using SQL queries, develop SQL queries to run against these query engines, and integrate these queries into production applications.

Level: Introductory

Study Time: 30 hours

Course Access: Unlimmited access. The only exception is the Training Cloud Environment for which you have 30 day access. After those 30 days you can purchase additional access with one of the two options below:

Prerequisites

  • Basic knowledge of the PI System (AF hierarchies, elements, attributes, templates, PI points, etc), and your own data and AF hierarchies.
  • Basic knowledge of SQL query writing
    • This includes things such as SELECT, FROM, WHERE, INNER JOIN, CROSS APPLY 
    • For PI OLEDB Provider Write queries, this includes INSERT, UPDATE, and DELETE
    • For assistance in this topic, we recommend a training site such as General SQL Training - w3schools
  • A computer that can access our YouTube content, and pass our connection test

This Course Includes...

  • Videos, exercises and quizzes to help you learn the material
  • A Cloud Environment accessible for 30 days and configured to complete all the exercises in the course
  • Sharable certificate of completion

Further Information

  • This is a self-paced course. Any questions or assistance needed about the material can be asked in this course's space in the OSIsoft PI Square community
  • When you complete the examination at the end of the course, you will receive a certificate of completion which can be shared and directly posted on LinkedIn.
  • For more information about our Online Courses please visit our FAQ page

You can audit the full video lecture content right now on the OSIsoft Learning YouTube Channel

Course Outline

  • Getting Started
  • Key Course Information
  • Course Grading Scheme
  • How to Navigate This Course
  • Offline Course Videos for Blocked YouTube Users
  • Lesson 1 - Architecture and Product Selection
  • Overview of PI SQL Framework Architecture
  • Which PI SQL Framework should I pick?
  • Deciding Between Direct OLEDB Connections or Routing Through an External Database
  • Knowledge Check: Selecting the Best Product
  • Solution: Selecting the Best Product
  • Lesson 2 - Installation and Configuration
  • Install, configure and test PI SQL DAS (RTQP Engine) & PI SQL Client
  • Knowledge Check: PI SQL Client (OLEDB) and RTQP Configuration
  • Solution: PI SQL Client (OLEDB) and RTQP Configuration
  • Lesson 3 - Writing Queries
  • Understand the basics of RTQP Engine schema
  • Using Functions Within the RTQP Query Engine
  • Knowledge Check: Writing PI SQL Client and RTQP Queries
  • Solution: Writing PI SQL Client and RTQP Queries
  • Knowledge Check: Write Transpose Queries for RTQP (PI SQL Client)
  • Solution: Write Transpose Queries for RTQP (PI SQL Client)
  • Using AFSDK Pass-through Search Queries in the RQTP Query Engine
  • Knowledge Check: Creating Custom Functions with the RTQP Engine
  • Solution: Creating Custom Functions with the RTQP Engine
  • RTQP Engine vs. PI OLEDB Enterprise performance
  • RTQP Performance Considerations
  • Lesson 4 - Installation and Configuration for OLEDB Provider and OLEDB Enterprise (Optional)
  • Install and test PI OLEDB Provider and PI OLEDB Enterprise
  • Knowledge Check: PI OLEDB Provider Configuration
  • Solution: PI OLEDB Provider Configuration
  • Knowledge Check: PI OLEDB Enterprise Configuration
  • Solution: PI OLEDB Enterprise Configuration
  • Install, configure and test PI ODBC Driver
  • Knowledge Check: PI ODBC Driver Configuration
  • Solution: PI ODBC Driver Configuration
  • Install, configure and test PI JDBC Driver
  • Knowledge Check: PI JDBC Driver Configuration
  • Solution: PI JDBC Driver Configuration
  • Knowledge Check: Connect to PI System from DBVisualizer using PI JDBC Driver
  • Solution: Connect to PI System from DBVisualizer using PI JDBC Driver
  • Read: How to Customize GetSnap and GetEASnap for PI JDBC Driver
  • Knowledge Check: GetEASnap for PI JDBC Driver
  • Solution: GetEASnap for PI JDBC Driver
  • Lesson 5 - Writing Queries for OLEDB Provider (Optional)
  • Write queries using PI OLEDB Provider Schema
  • Knowledge Check: Making PI OLEDB Provider Queries - Reads
  • Solution: Making PI OLEDB Provider Queries - Reads
  • Knowledge Check: Making PI OLEDB Provider Queries - Writes
  • Solution: Making PI OLEDB Provider Queries - Writes
  • Lesson 6 - Writing Queries for OLEDB Enterprise (Optional)
  • Write queries using PI OLEDB Enterprise schema
  • Knowledge Check: Making PI OLEDB Enterprise queries
  • Solution: Making PI OLEDB Enterprise queries
  • Create transpose functions to customize PI OLEDB Enterprise queries
  • Knowledge Check: Writing PI OLEDB Enterprise Transpose Queries
  • Solution: Writing PI OLEDB Enterprise Transpose Queries
  • Optimize PI OLEDB Enterprise query performance
  • Lesson 7 - Guided Project
  • Introduction to guided project - Create a SSRS application
  • Prepare custom database objects and queries
  • Set up a MS SQL Server linked server
  • Knowledge Check: Using a Microsoft SQL Server Linked Server
  • Solution: Using a Microsoft SQL Server Linked Server
  • Create an SSIS package in MS Visual Studio
  • Deploy an SSIS package to a SQL server
  • Build and Deploy SSRS reports to showcase PI System data
  • Final Exam
  • Final Exam
  • Course Evaluation
  • How did it go?
  • Training Cloud Environments
  • Cloud Environments Introduction
  • Cloud Environments Instructions
  • Launch Cloud Environment
  • Next Steps
  • Additional Resources
  • End of the course