- Getting Started
-
How to Navigate This Course
-
Discussion Forum
-
Offline Course Videos for Blocked YouTube Users
-
Course Workbook
-
Course Presentation
- Training Cloud Environment
-
Launch Cloud Environment
- Lesson 1 - Introduction
-
Getting Started (3:00)
-
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)
- 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
- Lesson 4 - Build RTQP Engine Queries
-
Request Just the Columns You Need (0:57)
-
Supported SQL Syntax
-
Forget OPTION (FORCE ORDER) (0:31)
- Lesson 5 - Migrate a Custom Application
-
Migrate Custom Application
-
Migrate Custom Application (1:07)
- 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)
-
Next steps
-
Course Evaluation
- Final Exam
-
Final Exam
- Course Evaluation
-
How did it go?
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
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:
- 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.
- 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.
- Build RTQP Engine Queries – in this part, you will learn principles of how to form queries against the new data model.
- Migrate Custom Application – in this part, you will migrate a C# console application from PI OLEDB Enterprise to PI SQL Client.
- 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 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
- 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.
Software Requirements
- We expect the following software to be installed:
- PI Server 2018 SP3 patch 1
- PI SQL Data Access Server (RTQP Engine) 2018 SP3
- PI OLEDB Enterprise 2018
- PI SQL Client 2018 R2
- Microsoft Visual Studio Community Edition 2019
- Microsoft Reporting Services Projects Extension
- Microsoft SQL Server 2017
- SQL Server Reporting Services
- PI Server 2018 SP3 patch 1
- PDF Reader or equivalent is required to access the workbook and presentation provided.
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
- 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 experience working with the key concepts presented.
- There is a final quiz which you must pass to obtain a certificate of completion.
- Once you register for a course, you will have access to the course materials 24/7 on this website.
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
Course Material
- You can audit the full video lecture content right now on the OSIsoft Learning YouTube Channel
- Download Course Workbook