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

Time: 30 hours

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

Price: 

  • 259 USD: By signing up for this course you will have access to:
    • OSIsoft Engineers who can review your final project
    • Sharable certificate of completion that can be directly posted on LinkedIn (*To obtain the certificate of completion, you need to pass the final project.)

You can also audit the video lecture content on the OSIsoft Learning YouTube Channel. This does not include the exercises, supplementary readings, graded final project or certificate of completion. 

Software Requirements: 

Note: Learners are can use their own test PI System environment to complete the majority of the course. However, we do not recommend practising administrative tasks in a production environment. This course contains optional tracks. The software requirements for completing the exercises and the final project are as follows.

 

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 sources
    • JDBC - These sections are primarily for those using Java and JDBC in their projects

 

To successfully complete the course, the PI SQL Client/RTQP sections must be covered. Therefore, all students will need the following at a minimum:

    • Access to the following Install Kits: PI SQL Client 2018 (4.0.18208.1)
    • PI Data Archive
    • AF Server 2018 or later and have the PI SQL Data Access Server (RTQP Engine) installed
    • For ODBC or JDBC use cases
      • PI SQL Client 2018 R2 (4.1.18355.1)
    • PI OLEDB Enterprise is currently required for the generation of sample NuGreen event frames
      • These event frames are used in the course for sample queries, but if you have your own event frames to query then PI OLEDB Enterprise is not required

 

Optionally, students may choose to perform either of the PI OLEDB Provider or PI OLEDB Enterprise exercises. For these, students will need the following:

  • Access to the following Install Kits: PI OLEDB Enterprise 2017 R2A (1.4.3.17)
  • For ODBC use cases:
    • PI ODBC Driver 2016 R2 (3.2.16300.1), PI SQL Data Access Server (OLEDB) 2018 (1.6.18206.1)
  • For JDBC use cases:
    • PI JDBC Driver 2018 (1.6.18206.1), PI SQL Data Access Server (OLEDB) 2018 (1.6.18206.1), Java Runtime Environment 8 or later

 

If you do not have access to your own PI System, subscribe to the optional Training Cloud Environment to work with the "Exposing PI Data with the PI SQL Framework - Cloud Environment " :

Grading and Certification of Completion: 

Once you register for this course, you must select one of the final submission review periods below. During this period, our course facilitators will be available to review and grade your project. If you don't receive a passing grade in the first submission, you can re-submit your work again during the 1 week review period.

Final Project Submission Deadline

Event Date Spaces left
Exposing PI Data with the PI SQL Framework (Final Project Due) - Virtual Sept. 30, 2019, midnight -
Oct. 4, 2019, 4 p.m. PDT
79

Course Outline

  • Getting Started
  • Key Course Information
  • Course Grading Scheme
  • Final Project Submission Deadline
  • 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 Project
  • Final Project - Creating Queries for your own Application
  • Course Evaluation
  • How did it go?
  • Next Steps
  • Additional Resources

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

Time: 30 hours

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

Price: 

  • 259 USD: By signing up for this course you will have access to:
    • OSIsoft Engineers who can review your final project
    • Sharable certificate of completion that can be directly posted on LinkedIn (*To obtain the certificate of completion, you need to pass the final project.)

You can also audit the video lecture content on the OSIsoft Learning YouTube Channel. This does not include the exercises, supplementary readings, graded final project or certificate of completion. 

Software Requirements: 

Note: Learners are can use their own test PI System environment to complete the majority of the course. However, we do not recommend practising administrative tasks in a production environment. This course contains optional tracks. The software requirements for completing the exercises and the final project are as follows.

 

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 sources
    • JDBC - These sections are primarily for those using Java and JDBC in their projects

 

To successfully complete the course, the PI SQL Client/RTQP sections must be covered. Therefore, all students will need the following at a minimum:

    • Access to the following Install Kits: PI SQL Client 2018 (4.0.18208.1)
    • PI Data Archive
    • AF Server 2018 or later and have the PI SQL Data Access Server (RTQP Engine) installed
    • For ODBC or JDBC use cases
      • PI SQL Client 2018 R2 (4.1.18355.1)
    • PI OLEDB Enterprise is currently required for the generation of sample NuGreen event frames
      • These event frames are used in the course for sample queries, but if you have your own event frames to query then PI OLEDB Enterprise is not required

 

Optionally, students may choose to perform either of the PI OLEDB Provider or PI OLEDB Enterprise exercises. For these, students will need the following:

  • Access to the following Install Kits: PI OLEDB Enterprise 2017 R2A (1.4.3.17)
  • For ODBC use cases:
    • PI ODBC Driver 2016 R2 (3.2.16300.1), PI SQL Data Access Server (OLEDB) 2018 (1.6.18206.1)
  • For JDBC use cases:
    • PI JDBC Driver 2018 (1.6.18206.1), PI SQL Data Access Server (OLEDB) 2018 (1.6.18206.1), Java Runtime Environment 8 or later

 

If you do not have access to your own PI System, subscribe to the optional Training Cloud Environment to work with the "Exposing PI Data with the PI SQL Framework - Cloud Environment " :

Grading and Certification of Completion: 

Once you register for this course, you must select one of the final submission review periods below. During this period, our course facilitators will be available to review and grade your project. If you don't receive a passing grade in the first submission, you can re-submit your work again during the 1 week review period.

Live events

Final Project Submission Deadline

Event Date Spaces left
Exposing PI Data with the PI SQL Framework (Final Project Due) - Virtual Sept. 30, 2019, midnight -
Oct. 4, 2019, 4 p.m. PDT
79

Course Outline

  • Getting Started
  • Key Course Information
  • Course Grading Scheme
  • Final Project Submission Deadline
  • 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 Project
  • Final Project - Creating Queries for your own Application
  • Course Evaluation
  • How did it go?
  • Next Steps
  • Additional Resources