Building PI SQL Queries for the Real-Time Query Processing Engine

NEW - Create optimized PI SQL Client queries and transform existing PI OLEDB Enterprise queries into PI SQL Client queries

About this course

This course shows how to create optimized PI SQL Client queries as well as how to transform existing PI OLEDB Enterprise queries into PI SQL Client queries. You will compare performance and features of the aforementioned data providers on two sample AF databases, NuGreen and WindFarm. 

The course consists of the following parts: 

  1.  Get Familiar with PI SQL Client – in this part, you will configure drivers included in the PI SQL Client bundle and will establish a connection to RTQP Engine using PI SQL Commander Lite and DBVisualizer. 
  2.  Understand the New Data Model – in this part, you will learn the key concepts of the new data model. You will look at the model structure, the objects it contains, and how it differs from the PI OLEDB Enterprise data model. 
  3.  Build RTQP Engine Queries – in this part, you will learn principles of how to form queries against the new data model. 
  4.  Migrate Custom Application – in this part, you will migrate a C# console application from PI OLEDB Enterprise to PI SQL Client. 
  5.  Integrate PI SQL Client with Microsoft SQL Server Reporting Services – in this part, you will use PI SQL Client as a data source for a simple Reporting Services report. 

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

  • Explain how the PI SQL products evolved in time.
  • Compare architecture of older PI SQL products with PI SQL Client.
  • Configure drivers included in the PI SQL Client bundle.
  • Clarify how the RTQP Engine data model differs from the PI OLEDB Enterprise model and why the changes have been made.
  • Use PI SQL Commander Lite to browse the metadata and form SQL queries.
  • Migrate queries from PI OLEDB Enterprise data model into RTQP Engine data model.
  • Create a SQL Server Reporting Services report based on PI SQL Client queries.

Audience

This course is designed for users familiar with SQL language who want to learn more about the new generation of PI SQL products – PI SQL Client and Real-Time Query Processing Engine (RTQP Engine).

Level: Intermediate

Study time: 2 hours

Course Access: Unlimited 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

  • During this course, we will be making the following assumptions:
    • You are familiar with Relational Database Management Systems (RDBMS) and Structured Query Language (SQL).
    • You have at least basic knowledge of the PI System.
  • A computer that can access our YouTube content.

This Course Includes...

  • Videos, discussion opportunities 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
  • A sharable certificate of completion

Further Information

  • The course is accompanied by an Azure-deployed virtual environment and a workbook, which allows you to walk through the contents in detail at your own pace.
  • 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

Curriculum

  • Getting Started
  • Key Course Information
  • Course Grading Scheme
  • How to Navigate This Course
  • Offline Course Videos for Blocked YouTube Users
  • Course Workbook
  • Course Presentation
  • Training Cloud Environment
  • Cloud Environments Introduction
  • Cloud Environments Instructions
  • Launch Cloud Environment
  • Lesson 1 - Introduction
  • Getting Started (3:00)
  • [POLL] Who's from what industry
  • [DISCUSSION] Introductions
  • PI SQL Product Evolution (3:03)
  • Knowledge Check
  • Lesson 2 - Get Familiar with PI SQL Client
  • PI SQL Client ODBC (1:47)
  • PI SQL Client OLEDB (0:47)
  • PI SQL Client JDBC (1:27)
  • Questions on this part of the course?
  • Lesson 3 - Understand the New Data Model
  • One Data Model Instance Represents Just One Database (1:17)
  • De-normalization (1:31)
  • No Element Versioning (0:55)
  • Attribute Tables Contain Snapshot Values (0:44)
  • Entity-Relationship Diagram
  • Helper functions (2:23)
  • Table-Valued Function and Table Templates
  • User-Defined Objects (0:28)
  • User-Defined Template-Specific Data Models (2:50)
  • Knowledge Check
  • Questions on this part of the course?
  • Lesson 4 - Build RTQP Engine Queries
  • Request Just the Columns You Need (0:57)
  • Supported SQL Syntax
  • Forget OPTION (FORCE ORDER) (0:31)
  • Questions on this part of the course?
  • Lesson 5 - Migrate a Custom Application
  • Migrate Custom Application
  • Migrate Custom Application (1:07)
  • Questions on this part of the course?
  • Lesson 6 - Integrate PI SQL Client with Microsoft SQL Server Reporting Services
  • Introduction & Architecture (1:16)
  • Define the Report Data Source (0:34)
  • Explore the WindFarm AF Database (0:56)
  • Build the Query (1:50)
  • Build the Report (8:02)
  • Questions on this part of the course?
  • Next steps
  • Course Evaluation
  • Final Exam
  • Final Exam

About this course

This course shows how to create optimized PI SQL Client queries as well as how to transform existing PI OLEDB Enterprise queries into PI SQL Client queries. You will compare performance and features of the aforementioned data providers on two sample AF databases, NuGreen and WindFarm. 

The course consists of the following parts: 

  1.  Get Familiar with PI SQL Client – in this part, you will configure drivers included in the PI SQL Client bundle and will establish a connection to RTQP Engine using PI SQL Commander Lite and DBVisualizer. 
  2.  Understand the New Data Model – in this part, you will learn the key concepts of the new data model. You will look at the model structure, the objects it contains, and how it differs from the PI OLEDB Enterprise data model. 
  3.  Build RTQP Engine Queries – in this part, you will learn principles of how to form queries against the new data model. 
  4.  Migrate Custom Application – in this part, you will migrate a C# console application from PI OLEDB Enterprise to PI SQL Client. 
  5.  Integrate PI SQL Client with Microsoft SQL Server Reporting Services – in this part, you will use PI SQL Client as a data source for a simple Reporting Services report. 

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

  • Explain how the PI SQL products evolved in time.
  • Compare architecture of older PI SQL products with PI SQL Client.
  • Configure drivers included in the PI SQL Client bundle.
  • Clarify how the RTQP Engine data model differs from the PI OLEDB Enterprise model and why the changes have been made.
  • Use PI SQL Commander Lite to browse the metadata and form SQL queries.
  • Migrate queries from PI OLEDB Enterprise data model into RTQP Engine data model.
  • Create a SQL Server Reporting Services report based on PI SQL Client queries.

Audience

This course is designed for users familiar with SQL language who want to learn more about the new generation of PI SQL products – PI SQL Client and Real-Time Query Processing Engine (RTQP Engine).

Level: Intermediate

Study time: 2 hours

Course Access: Unlimited 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

  • During this course, we will be making the following assumptions:
    • You are familiar with Relational Database Management Systems (RDBMS) and Structured Query Language (SQL).
    • You have at least basic knowledge of the PI System.
  • A computer that can access our YouTube content.

This Course Includes...

  • Videos, discussion opportunities 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
  • A sharable certificate of completion

Further Information

  • The course is accompanied by an Azure-deployed virtual environment and a workbook, which allows you to walk through the contents in detail at your own pace.
  • 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

Curriculum

  • Getting Started
  • Key Course Information
  • Course Grading Scheme
  • How to Navigate This Course
  • Offline Course Videos for Blocked YouTube Users
  • Course Workbook
  • Course Presentation
  • Training Cloud Environment
  • Cloud Environments Introduction
  • Cloud Environments Instructions
  • Launch Cloud Environment
  • Lesson 1 - Introduction
  • Getting Started (3:00)
  • [POLL] Who's from what industry
  • [DISCUSSION] Introductions
  • PI SQL Product Evolution (3:03)
  • Knowledge Check
  • Lesson 2 - Get Familiar with PI SQL Client
  • PI SQL Client ODBC (1:47)
  • PI SQL Client OLEDB (0:47)
  • PI SQL Client JDBC (1:27)
  • Questions on this part of the course?
  • Lesson 3 - Understand the New Data Model
  • One Data Model Instance Represents Just One Database (1:17)
  • De-normalization (1:31)
  • No Element Versioning (0:55)
  • Attribute Tables Contain Snapshot Values (0:44)
  • Entity-Relationship Diagram
  • Helper functions (2:23)
  • Table-Valued Function and Table Templates
  • User-Defined Objects (0:28)
  • User-Defined Template-Specific Data Models (2:50)
  • Knowledge Check
  • Questions on this part of the course?
  • Lesson 4 - Build RTQP Engine Queries
  • Request Just the Columns You Need (0:57)
  • Supported SQL Syntax
  • Forget OPTION (FORCE ORDER) (0:31)
  • Questions on this part of the course?
  • Lesson 5 - Migrate a Custom Application
  • Migrate Custom Application
  • Migrate Custom Application (1:07)
  • Questions on this part of the course?
  • Lesson 6 - Integrate PI SQL Client with Microsoft SQL Server Reporting Services
  • Introduction & Architecture (1:16)
  • Define the Report Data Source (0:34)
  • Explore the WindFarm AF Database (0:56)
  • Build the Query (1:50)
  • Build the Report (8:02)
  • Questions on this part of the course?
  • Next steps
  • Course Evaluation
  • Final Exam
  • Final Exam