- Getting Started
-
How to Navigate This Course
-
Discussion Forum
-
Offline Course Videos for Blocked YouTube Users
- Training Cloud Environments
-
Launch Cloud Environment
- Lesson 1 - Architecture and Product Selection
-
Overview of PI SQL Framework Architecture (11:52)
-
Which PI SQL Framework should I pick? (14:53)
-
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 (7:41)
-
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 (9:03)
-
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) Part 1
-
Solution: Write Transpose Queries for RTQP (PI SQL Client) Part 2
-
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 (10:01)
-
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 (12:34)
-
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 (12:56)
-
Knowledge Check: PI ODBC Driver Configuration
-
Solution: PI ODBC Driver Configuration
-
Install, configure and test PI JDBC Driver (11:23)
-
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 (13:47)
-
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 (9:48)
-
Knowledge Check: Making PI OLEDB Enterprise queries
-
Solution: Making PI OLEDB Enterprise queries
-
Create transpose functions to customize PI OLEDB Enterprise queries (11:01)
-
Knowledge Check: Writing PI OLEDB Enterprise Transpose Queries
-
Solution: Writing PI OLEDB Enterprise Transpose Queries
-
Optimize PI OLEDB Enterprise query performance (9:31)
- Lesson 7 - Guided Project
-
Introduction to guided project - Create a SSRS application (2:32)
-
Prepare custom database objects and queries (12:17)
-
Set up a MS SQL Server linked server (12:37)
-
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 (12:08)
-
Deploy an SSIS package to a SQL server (5:54)
-
Build and Deploy SSRS reports to showcase PI System data (9:48)
- Final Exam
-
Final Exam
- Course Evaluation
-
How did it go?
- Next Steps
-
Additional Resources
-
End of the course
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.
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: Unlimited access to all content except the Training Cloud Environment (TCE). You have 30 days access to the TCE starting on the day you access module section "Launch Cloud Environment".
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.
- For assistance in this topic, we recommend covering the first two sections of the Visualizing PI System Data with PI Vision course.
- 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
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 a PI System you can use the included Training Cloud Environment. Go to the Training Cloud Environment section at the end of the course to learn more about it and deploy your cloud environments.
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.
- This course is self-paced for your convenience. Thus, there are no live components to the course, nor are there required login hours. Please use the video lectures for instruction along with the course exercises to gain hands-on experience working with key concepts.
- There are 16 hands-on knowledge checks tailored to apply the knowledge you've learned in the video lectures.
- Once you register for a course, you will have access to the course materials 24/7 on this website.
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
Course Material
- You can audit the full video lecture content right now on the OSIsoft Learning YouTube Channel