© 2021 AVEVA Group plc and its subsidiaries. All rights reserved.
AVEVA, the AVEVA logos and AVEVA product names are trademarks or registered trademarks of aveva group plc or its subsidiaries
in the United Kingdom and other countries. Other brands and products names are the trademarks of their respective companies.
AVEVA Group plc
High Cross, Madingley Road
Cambridge CB3 0HB, UK
Tel +44 (0)1223 556655
Fax +44 (0)1223 556666
aveva.com
PI DataLink: Basics
PI DataLink: Basics
PAGE 2 OF 52
Table of contents
1 INTRODUCTION .......................................................................................................................................5
1.1 BACKGROUND: PI SYSTEM SOFTWARE USED IN THIS CLASS..........................................................................6
2 PI DATALINK BASICS.................................................................................................................................6
2.1 WHAT IS PI DATALINK? ............................................................................................................................6
2.2 BACKGROUND: PI DATALINK FUNCTIONS...................................................................................................7
2.3 BA CKGRO UND : SEARCH FOR PI TAGS AND PI AF ATTRIBUTES IN PI DATALINK ................................................8
2.4 KNOWLEDGE CHECK: SEARCHING IN PI DATALINK ..................................................................................... 10
2.5 BACKGROUND: PI TIME ......................................................................................................................... 11
2.5.1 Fixed Time Syntax............................................................................................................................ 11
2.5.2 Reference Time Syntax..................................................................................................................... 12
2.5.3 Time Offset .................................................................................................................................... 13
2.5.4 Time Offset Syntax .......................................................................................................................... 13
2.5.5 Rules to Remember ......................................................................................................................... 14
2.6 KNOWLEDGE CHECK: PRACTICE PI TIME ................................................................................................... 14
3 SINGLE VALUE QUERIES ......................................................................................................................... 15
3.1 BA CKGRO UND : GET PI DATA WITH CURRENT AND ARCHIVE VALUE FUNCTIONS ........................................... 15
3.1.1 Obtain a Current Value..................................................................................................................... 15
3.1.2 Obtain an Archive Value ................................................................................................................... 16
3.2 HA NDS-ON: SINGLE VALUE QUERY .......................................................................................................... 17
3.3 HA NDS-ON: SINGLE VALUE QUERIES USING CELL REFERENCE ..................................................................... 18
3.4 KNOWLEDGE CHECK: TEMPERATURE AT A GLANCE ................................................................................... 19
3.5 BACKGROUND: RETRIEVAL MODE ........................................................................................................... 19
3.6 HA NDS-ON: ACTIVITY REPORT ................................................................................................................ 21
4 MULTIPLE VALUE QUERIES ..................................................................................................................... 21
4.1 BACKGROUND: GET PI DATA WITH COMPRESSED, SAMPLED, AND TIMED DATA FUNCTIONS ......................... 21
4.1.1 Retrieving Compressed Data ............................................................................................................. 22
4.1.2 Obtaining Sampled Data................................................................................................................... 23
PI DataLink: Basics
PAGE 3 OF 52
4.1.3 Timed Data..................................................................................................................................... 23
4.2 HA NDS-ON: ARCHIVED, SAMPLED, AND TIMED DATA FUNCTIONS .............................................................. 24
4.3 KNOWLEDGE CHECK: TANK ANALYSIS REPORT.......................................................................................... 25
4.4 BACKGROUND: ELEMENT RELATIVE PI DATALINK REPORTS ........................................................................ 26
4.5 HA NDS-ON: ELEMENT RELATIVE PI DATALINK REPORTS ............................................................................. 27
4.6 KNOWLEGDE CHECK: OPERATIONAL START UP ......................................................................................... 29
4.7 BACKGROUND: EXCEL ARRAYS IN PI DATALINK ......................................................................................... 30
4.7.1 Workflow prior to Dynamic Arrays – PI DataLink version 2019 and earlier................................................ 30
4.7.2 Workflow with Dynamic Arrays – PI DataLink version 2022.................................................................... 31
4.8 BACKGROUND: UNITS OF MEASURE AND DESCRIPTIONS AS CONTEXT IN REPORTS....................................... 32
5 MORE PI DATALINK FUNCTIONS............................................................................................................. 33
5.1 BA CKGRO UND : MORE PI DA TA LI NK FUNCTIO NS ....................................................................................... 33
5.1.1 Calculated Data............................................................................................................................... 33
5.2 HA NDS-ON: CALCULATED DATA .............................................................................................................. 34
5.3 BACKGROUND: CALCULATING TOTAL AND CONVERSION FACTOR ............................................................... 35
5.4 HA NDS-ON: CALCULATING THE TOTAL ..................................................................................................... 37
5.5 KNOWLEDGE CHECK: PRODUCTION SUMMARIES ...................................................................................... 37
5.6 BACKGROUND: USING TIME FILTERED DATA IN A PREVENTATIVE MAINTENANCE EXAMPLE .......................... 40
5.7 HA NDS-ON: CONTROLLER STATE............................................................................................................. 40
5.8 KNOWLEDGE CHECK: MIXING TANK LEVEL CONTROL................................................................................. 41
5.9 KNOWLEDGE CHECK: CONDITION BASED INSPECTION ............................................................................... 42
5.10 BACKGRO UND: FILTERED DA TA USING CUSTOM EXPRESSIONS................................................................ 43
5.11 BACKGROUND: EXCEL CONDITIONAL FORMATTING ............................................................................... 43
5.12 KNOWLEDGE CHECK: FILTERED DATA VALUE QUERIES ............................................................................ 44
5.13 KNOWLEDGE CHECK: PRODUCTION LEVEL REPORT ................................................................................ 45
6 EVENT-RELATED FEATURES OF PI DATALINK........................................................................................... 46
6.1 BACKGROUND: UNDERSTANDING THE EVENT EXPLORE AND COMPARE FUNCTIONS..................................... 46
PI DataLink: Basics
PAGE 4 OF 52
6.2 KNOWLEDGE CHECK: MONITORING DOWNTIME EVENTS........................................................................... 46
6.3 KNOWLEDGE CHECK: ANALYZING DOWNTIME EVENTS .............................................................................. 47
7 FUNCTIONS USING AN EXPRESSION ....................................................................................................... 49
7.1 BACKGROUND: FUNCTIONS USING AN EXPRESSION .................................................................................. 49
7.1.1 How is calculated data function different from PI expressions? .............................................................. 49
7.2 KNOWLEDGE CHECK: EXPRESSION QUERIES ............................................................................................. 51
7.3 KNOWLEDGE CHECK: MATERIAL BALANCE REPORT ................................................................................... 52
8 FINAL EXAM ........................................................................................................................................... 52
PI DataLink: Basics
PAGE 5 OF 52
1 Introduction
This course will teach you how to use PI DataLink to access your process data, create meaningful reports, and use them to
make key decisions about your plant performance. By the end of the course, you will be able to:
• Search for PI Tags and AF Attributes in PI DataLink
• Identify which PI DataLink function to use for your report
• Get current and historical PI data in PI DataLink
• Build reports with key KPIs
• Interpolate data with custom intervals
• Use calculated data
• Build reusable daily report
Throughout this class we will assume you have a basic knowledge of the PI System. If you are new to the PI System, we
encourage you to complete the PI System Basics class that is available for free on our Learning Page.
The course consists of text-based lessons, and video lectures. You are encouraged to follow along with all video lectures
using your Training Cloud Environment. Text-based lessons contain background theory, exercises, documentation, and
other useful information.
Lessons are categorized using the following key words:
1. Hands-On: These lessons contain hands-on exercises with video step-by-step walkthroughs. While we are
demonstrating how to use different functions of PI DataLink, you are encouraged to do the same on your virtual
environment. If you have access to your own data, feel free to use it instead. It will give you an amazing
opportunity to leave this course with some great ready-to-use reports for your organization.
2. Knowledge check: Exercises with step-by-step solutions to complete on your own using your virtual
environment, or quizzes to keep your skills sharp.
3. Background: Readings or videos containing useful background information to assist your understanding. These
lessons are optional but encouraged as they will give you a more well-rounded understanding of the features of
PI DataLink. Throughout these sections we will link out to reference documentation. You may be examined on
the content inside these references, but as the exam is open book, you're welcome to visit these references
during the exam.
In the next lesson we'll cover the Learning Cloud Environment used in this class.
Before reading this section, please refer to the following course YouTube video:
Course Introduction
PI DataLink: Basics
PAGE 6 OF 52
1.1 Background: PI System Software Used in this Class
SOFTWARE
VERSION
PI Data Archive 2018 SP3
3.4.430.460
PI AF Server 2018 SP3
2.10.6.195
PI DataLink 2019 SP1 Patch 1
5.5.2.0
2 PI DataLink Basics
2.1 What is PI DataLink?
PI DataLink is an OSIsoft add-in for Microsoft Excel. It lets you retrieve information from your PI System
directly onto a worksheet. Combined with the computational, graphic, and formatting capabilities of
Microsoft Excel, PI DataLink is a powerful tool for gathering, monitoring, analysing, and reporting PI System
data.
With PI DataLink, you can:
• Retrieve PI point values from a PI Data Archive server
• Retrieve attribute values from a PI Asset Framework (PI AF) server
• Retrieve system metadata to create a structured view of PI System data:
o PI point names and attributes
o PI AF attributes and elements
• Reference these items using PI DataLink functions to calculate and filter data
• Update retrieved values when the worksheet recalculates
It is important to understand the following nomenclature when working with PI DataLink and its data
retrieval functions.
Before reading this section, please refer to the following course YouTube video:
OSIsoft Learning: What is PI DataLink?
PI DataLink: Basics
PAGE 7 OF 52
Term Meaning
Data item
A PI Point name or AF attribute name, for which the PI DataLink function returns
property values.
Root path
The common path to specified data items within PI System. It is optional for PI
DataLink functions. Valid entries include:
Data Archive name if the data item is a PI Point.
AF server and database if the data item is an AF attribute. For a data item of AF
Attribute, root path can also include the name of parent elements.
Blank if the data item is on the default Data Archive or the default AF Server and
default database.
Output cell
The worksheet cell where the function writes the result.
If you select a cell before you open the function task pane, PI DataLink inserts the
selected cell into the output cell field.
References:
• For more information see "Introduction" in PI DataLink User Guide
2.2 Background: PI DataLink functions
With PI DataLink functions, you can query any Data Archive or AF server, apply calculations to retrieved
results and return values to worksheet cells. These functions return results in function arrays, which you can
recalculate to update values as needed. The below table summarizes the functions available in PI DataLink.
Query
Category
Function
Name
What It Returns
Single value
Current
Value
The current or most recent value
of a Data Item
Archive
Value
Value of a Data Item at a specified
time stamp
Computed value of a performance
equation at a specified time stamp
Multiple value
Compressed
Data
All the values of a Data Item for a
specified time period
A specific number of Data Item
values beginning at a certain time
Sampled
Data
Evenly spaced, interpolated values
for a Data Item over a regular
interval
PI DataLink: Basics
PAGE 8 OF 52
Evenly spaced, interpolated values
of a performance equation over a
regular interval.
Timed Data
Actual or interpolated sample
values for a Data Item at specified
time stamps
Values of a performance equation
computed at specified time
stamps
Calculation
Calculated
Data
One or more evenly spaced,
calculated values based on a Data
Item’s values and specified
calculation preferences
One or more evenly spaced,
calculated values based on an
evaluated performance equation
and specified calculation
preferences
Time
Filtered
The amount of time that a
performance equation evaluates
to true during a specified time
period
References:
• Documentation for the PI DataLink functions can be found here.
2.3 Background: Search for PI Tags and PI AF Attributes in PI DataLink
In this lesson you will learn how to:
• Search for PI Points in PI DataLink
• Find element and element attributes within the PI Asset Framework (AF) hierarchy in PI DataLink
Before reading this section, please refer to the following course YouTube video:
Search for PI Tags and PI AF Attributes in PI DataLink
PI DataLink: Basics
PAGE 9 OF 52
PI DataLink offers two ways to search for data items:
• Search tool
• Asset Filter Search function (this will be covered in a later section)
On first use, the tool starts at the Home
node, which shows all the Data Archives and
AF servers listed in Connection Manager. You
must limit the search to a single Data Archive
or single AF server and can limit the search
further to a single database on an AF server,
and then to specific elements and parent
attributes.
When your search scope is a
Data Archive, the top search
field is applied to the PI
Point Name, only. Set the
fields under Filters to
specify any additional
criteria the retrieved PI
Points must match.
When your scope is an AF Server or Database, the top search field is applied to AF Attribute name as well as
the name, description, and categories of the parent element.
PI DataLink: Basics
PAGE 10 OF 52
You can change columns displayed in the results pane by right clicking a column name in
the results pane and selecting which column you would like to be displayed.
References:
• Documentation for the PI DataLink search can be found here.
2.4 Knowledge check: Searching in PI DataLink
In this exercise, you will practice searching for PI Points and AF Attributes in PI DataLink.
Perform the following steps:
1. Open Microsoft Excel.
2. On the PI DataLink ribbon, select Search.
3. Select a Data Archive
a. Search for *temper*
b. Select an item and then OK.
4. Select the AF Server >
5. Search for flow*.
6. Choose all items in the list.
7. Select OK
You can use wildcard characters to augment your search, for example:
Use * to replace any number of characters:
Flow* = Flow_meter1, flow_meter2, flow_meter3, flowrate_pump1, flowrate_pump2
Use ? to replace one character:
Flow_meter? + flow_meter1, flow_meter2, low_meter3
PI DataLink: Basics
PAGE 11 OF 52
2.5 Background: PI Time
In PI there are two ways to specify time:
1. Fixed Time: An expression that signifies a specific date and time. Used when you want to save a
view of your PI System data for a specific time in history.
Example: A user is creating a report that analyses an equipment failure event which occurred on the
25
th
of July 2013 at 11 am, so 25-Jul-2013 11:00:00 AM
2. Reference Time: An expression that signifies a date and time relative to the current date and time.
Used when you want to create a dynamic view of your data, which can be used to view data in real-
time, or re-used on a periodic basis to create periodic reports.
Example: A user creates a report that summarizes weekly production totals. By using relative time
expressions, the user will be able to re-use this report every week, so define a start date of
“Monday” meaning start the report from last Monday.
Both Fixed Time and Reference Time can be used with Time Offsets; Time Offsets can be used alone.
2.5.1 Fixed Time Syntax
A fixed time expression is an expression which includes a date, and optionally a time.
When the time component is omitted, Midnight is assumed. And midnight occurs at the beginning of the
day, not the end.
Expression Meaning
5-jan-92 12:34
12:34 p.m. on January 5, 1992
25-sep-12
00:00:00 (midnight) on
September 25, 2012
The PI System interprets many different formats for fixed time. In the event of an ambiguous input, the
Windows Region and Language settings of the computer where the PI Visualization Tool is installed take
precedence.
Before reading this section, please refer to the following course YouTube video:
OSIsoft Learning: PI Time
PI DataLink: Basics
PAGE 12 OF 52
Note the following:
Expression
Region and
Language
Format
Meaning
1/5/2015 English (United States)
00:00:00 (midnight)
on January 5th 2015
1/5/2015 Rest of the world
00:00:00 (midnight)
on May 1st 2015
2.5.2 Reference Time Syntax
A reference-time abbreviation represents a time relative to the current time.
Abbreviation Meaning Reference time
* Now Current time
t today 00:00:00 (midnight) of the current day
y yesterday 00:00:00 (midnight) of the previous day
fri friday
00:00:00 (midnight) on the most recent
Friday
may may 00:00:00 (midnight) on the current day
in May of the current year
apr-15 april-15 00:00:00 (midnight) on the 15th day of
April in the current year
YYYY Year
00:00:00 (midnight) on the current day
and month in year YYYY
M-D or M/D
Or
D-M, D/M
USA
The world
00:00:00 (midnight) on the Dth day of
month M in the current year
15
00:00:00 (midnight) on the 15th day of
the current month
Use the first three letters as an abbreviation for any day of the week and any month of the year. For
example:
Expression Meaning
thu 00:00:00 (midnight) on the most recent Thursday
MAR
00:00:00 (midnight) on the current day in
March of the current year
PI DataLink: Basics
PAGE 13 OF 52
2.5.3 Time Offset
When specifying PI time use specific abbreviations that represent time units. These are used in constructing
Time Offsets as in the table.
Abbreviation Time Unit
s second
m minute
h hour
d day
mo month
y year
w week
Specify the abbreviation, the full time unit or the plural version of the time unit, such as s, second, or
seconds. Time offset is any of the time units with a valid value and a + or – sign included, e.g. +8h.
Time offsets can be used alone in a time field or come with a fixed time or reference-time abbreviation.
2.5.4 Time Offset Syntax
Reference Time or Fixed Time and Offset Expression
When included with a reference-time abbreviation or with a fixed time, a time offset adds or subtracts from
the specified time (indicated by either + or -) and a time unit with a value
Expression Meaning
*-1h One hour ago
t+8h 08:00:00 (8:00 a.m.) today
y-8h 16:00:00 (4:00 p.m.) the day before yesterday
mon+14.5h 14:30:00 (2:30 p.m.) last Monday
sat-1m 23:59:00 (11:59 p.m.) last Friday
1-jan-20 – 1d Midnight 31 December 2019
Time Offsets Used Alone
Entered alone in a time field, time offsets specify a time relative to an implied reference time. The implied
reference time depends on the field where you enter the expression:
• For a start time, the reference time is the current clock time.
• For an end time, the reference time is the start time.
PI DataLink: Basics
PAGE 14 OF 52
• For a single time stamp, the reference time is the current clock time.
Time field Expression Meaning
Start time -1d One day before the current clock time
(24 hours before the current clock
time)
End time +6h Six hours after the start time
End time -30m 30 minutes before the start time
Time stamp -15s
15 seconds before the current clock
time
2.5.5 Rules to Remember
Rule 1: You can only include a single time offset in an expression. Including multiple offsets can lead to
unpredictable results. For example, the following time expressions are not valid:
*+1d+4h
t-1d+12h
Rule 2: To define a time offset you must include a valid value with any time unit. Only for seconds, minutes, or hours,
you can specify a fractional value. You cannot specify fractional values for other time units.
Rule 3: A fixed timestamp consists of the fields of Year, Month, Day and Time (hours, minutes and seconds). If any of
these fields are not specified in the PI time expression, the following values will be assumed by default:
• If Time is not specified, then the default value would be Midnight.
• If Day is not specified, then the default value would be Current Day.
• If Month is not specified, then the default value would be Current Month.
• If Year is not specified, then the default value would be Current Year.
2.6 Knowledge check: Practice PI Time
The quiz in this course is taken online. Please check the course listing online for more details.
PI DataLink: Basics
PAGE 15 OF 52
3 Single value queries
3.1 Background: Get PI data with current and archive value functions
Most of the functions in PI DataLink can be done using these simple steps
• Select the Target cell
Data/Information will be inserted below and to the right of the target cell
• Choose the Function
Search/Snapshot Value or any other function
• Fill out the required parameters, or use cell references to make later editing easier
3.1.1 Obtain a Current Value
For the Current Value function no timestamp is required, as it is always assumed to be NOW (* in PI time or
=NOW() in Excel time).
You can retrieve PI data from any PI System currently connected to your client machine. You must specify:
1. Data item(s)
o Can specify 1 or more, explicitly or via a cell
reference
2. Output cell
o Any data currently in this cell will be
replaced.
PI DataLink: Basics
PAGE 16 OF 52
The result of this query will be the most current value and timestamp of the data item specified.
3.1.2 Obtain an Archive Value
The other PI DataLink function that returns a single value is the Archive Value. This function retrieves an
archived value at a specific timestamp.
You specify:
1. Data item(s)
o 1 or more
2. Time stamp
o Excel Time Format or
o PI Time Format
Note: Current Value is a volatile function: the function recalculates and updates values
whenever Excel calculates or recalculates any cell in the worksheet. To force an
immediate recalculation, press F9.
PI DataLink: Basics
PAGE 17 OF 52
The result being:
3.2 Hands-On: Single value query
Activity description
You would like to prepare a report that shows the current value of External Temperature, Pressure and Flow Rate of
Mixing Tank2. You are also interested in knowing the value of these measurements at 7 am yesterday.
Approach
Part I – Get Current Values
1. Open Excel and in cell A1 type Current Values as a heading.
2. Click on cell A2. Select Current Value function.
3. Click on the magnifying glass next to Data Item(s) box.
4. Search for the External Temperature of Mixing Tank2.
5. Select Time at Left and click OK.
6. Repeat these steps for Pressure and Flow Rate in cells A3 and A4, respectively.
Part II – Add Archive Values
1. In cell C1 type Archive Values as a heading.
2. Click on cell C2. Select Archive Value function.
3. Click on the magnifying glass next to Data Item(s) box.
4. Search for the External Temperature of Mixing Tank2.
5. Type Y+7h in the Time stamp box. What time stamp will be retrieved? ___________
6. Select Time at Left and click OK.
7. Repeat these steps for Pressure and Flow Rate in cells C3 and C4, respectively.
Part 3 – Do you think this is an efficient way to search for the data item every time a value is retrieved? Check out the
next lesson if you are unsure of the answer!
During this section, please refer to the following course YouTube video:
Single value query
PI DataLink: Basics
PAGE 18 OF 52
3.3 Hands-on: Single value queries using cell reference
Activity description
You would like to include the current value of External Temperature of Mixing Tank1 in our daily report. You are also
interested in the value of this tank’s Internal Temperature at 7 am today.
Approach
Part I – Build a Template
1. Open Excel and click on cell A2. Click on Search.
2. Select the AF Database of OSIsoft Plant. Type Ext*Temp* in the search box and click ‘Enter’. The External
Temperature of all four tanks should show up on the search results list.
3. Select the external temperature of Mixing Tank1 and click OK.
4. In cell B1 type Current Value. In cell D1 type Archive Value as headings.
5. In cell A4 type Timestamp, in B4 type t+7h. What time do you expect to see? ___________
Your report template will look similar to this:
Part II – Query PI Data
1. Select cell B2 (this will be your Output cell). Click on Current Value function.
2. Refer to cell A2 in the Data Item field and select Time at left. Click OK.
3. Select cell D2 (this will be your Output cell). Click on Archive Value function.
Refer to cell A2 in the Data Item field. In the Time stamp field refer to cell B4. Do not change any of the other options.
Select Time at left and click OK.
During this section, please refer to the following course YouTube video:
Single value queries using cell reference
PI DataLink: Basics
PAGE 19 OF 52
3.4 Knowledge check: Temperature at a glance
Activity description
You would like to include the current value of the External Temperature of all Tanks in your daily report. You are also
interested in knowing the value of the tanks’ Internal Temperature at midnight.
Approach
1. Open the file PI_DataLink_Exercises.xlsx.
2. Save it as <your initials>_ PI_DataLink_Exercises.xlsx then work on sheet Temperature at a Glance and fill in data
into all of the fields.
3. Use the Current Value and Archive Value queries to fill in the template. We should base our PI DataLink queries on
cell references whenever possible.
3.5 Background: Retrieval mode
When bringing the archive value to our report, we saw that a timestamp needs to be specified and the
archived value will be retrieved for that timestamp. There may or may not be a value archived at the specified
timestamp; how does PI DataLink decide just what data is retrieved?
You can decide the data retrieval behaviour by selecting different options from the Retrieval Mode drop down
list. The default option is Auto. Different modes will retrieve the value differently, as listed in the following
table:
Retrieval Mode Data retrieval behaviour
Auto (Default) Interpolates at the exact timestamp provided,
unless the data item is a step point, which would retrieve
the previous value.
Interpolated Always interpolates at the exact timestamp provided.
Previous, Next
Retrieves the previous or next compressed event from the
timestamp specified. If a compressed event does exist at
the exact timestamp, it is retrieved.
Previous Only,
Next Only
Same as Previous, Next, except it will ignore any event at
the exact timestamp specified.
Exact Time
Retrieves the value if and only if an archived value exists at
that exact timestamp (down to the second).
PI DataLink: Basics
PAGE 20 OF 52
The graph and table below exemplify the different retrieval modes.
Retrieval Mode Timestamp Value
Interpolated 13:00:00 1.8
Previous 12:30:00 2.5
Next 13:30:00 1
Exact No events found. No events found.
The next graph shows the difference between Auto and Interpolated for a step point and an analogue point.
PI DataLink: Basics
PAGE 21 OF 52
Auto Interpolated
Step OFF (Rate point) 13:00:00 - 2 13:00:00 - 2
Step ON 12:30:00 – 2.8 13:00:00 – 2.8
3.6 Hands-on: Activity report
Activity Description
Your manager needs a report they can open each afternoon to see the current values of KPI’s for Mixing
Tank2, which are:
• External Temperature
• Internal Temperature
• Level
• Flow Rate
Your manager needs to see the KPIs values at midnight today. Not trusting this new “PI Thing” your manager
wants to see archive values around midnight as well as be able to type in a timestamp and see if there is an
archive value at an exact time.
Approach
• Open <your initials>_PI_DataLink-Exercises.xlsx then work on sheet Activity Report and fill in the data in all
fields.
• Use the Current Value and Archive Value queries to fill in a template.
4 Multiple value queries
4.1 Background: Get PI data with compressed, sampled, and timed data
functions
There are three types of queries that return multiple values.
During this section, please refer to the following course YouTube video:
Activity report
PI DataLink: Basics
PAGE 22 OF 52
Compressed Data
Actual archived data and is more
properly termed Archived data, as
not all data may be compressed.
Sampled Data Evenly spaced interpolated data
Timed Data
Interpolates events to match existing
timestamps
Below is a graph of Compressed Data compared to Sampled Data. The Compressed Data function returns all
the data that had been archived for the data item of interest. Time intervals between the compressed data
values are uneven and depend on when the data was received and archived by the PI System.
You may need to know the value of a data item at specific timestamps, for example at the beginning of
every shift. Or you may need to compare the values of two different data items and having unevenly spaced
time intervals would not be that helpful. In these cases, using the Sampled Data function is helpful in
creating a PI DataLink report.
4.1.1 Retrieving Compressed Data
The Compressed Data function retrieves the actual archived data from the Data Archive.
You must specify:
• Data item(s)
o Can specify 1 or more
• Output cell
o Any data currently in this cell will be replaced
• Start Time and End Time.
Note: If you switch the timestamps for Start and End Times, the data will be returned in reverse order
PI DataLink: Basics
PAGE 23 OF 52
There are several optional checkboxes that will affect what is displayed, none of which are selected by
default:
• Hide count: When this box is unchecked, the top line of your results will return the number of events found within
that time range. When this box is checked, this count is not displayed and only the results are shown.
• Show time stamps: This checkbox determines if only the point values are returned, or if the corresponding
timestamp is also retrieved.
• Show Value Attributes and Show Annotations: will return additional fields if there are any annotations or quality
bits associated with the compressed events within the time range specified.
• Column and Row: This pair of radio buttons determine if the results are returned in columns or rows.
• The Number of Values option for this query behaves similarly, but rather than a
specific time range, you will specify a Start Time and the Number of Values that
you would like to retrieve. You can also check the box to have this go backwards,
rather than forwards in time.
4.1.2 Obtaining Sampled Data
Sampled Data retrieves evenly spaced interpolated values from the archive. You specify:
• Data item(s)
o Can specify 1 or more
• Output cell
o Any data currently in this cell will be replaced
• Start Time and End Time.
• Time Interval
o Used to divide the time range into discreet intervals.
4.1.3 Timed Data
The Timed Data function retrieves interpolated events to match existing timestamps. These time stamps are
already in your spreadsheet and may be as a result of a related data item query.
The Data item, Retrieval mode, and Output cell need to be specified. In this case, the times used to retrieve
events will be a range of cells containing timestamps. This is typically done by querying one point for events,
and then using those timestamps to find the values for other, related points. The graph below shows an
example of the External Temperature for Mixing Tank1 queried for Compressed data, then going to Mixing
Tank2 and getting the Compressed Data then the Timed Data matched to Mixing Tank1 External
temperature timestamps.
PI DataLink: Basics
PAGE 24 OF 52
4.2 Hands-on: Archived, sampled, and timed data functions
In this hands-on you would like to analyse the archived values of the External Temperature of Mixing Tank1 in your daily
report for the first two hours of every day. You would also like to see this temperature value every 10 minutes during
these two hours.
You are also interested in doing a comparison between Mixing Tank1 and Mixing Tank2 at the timestamps
where there is a value archived for external temperature of Mixing Tank1.
Approach
Part 1 – Build a Template
1. In Excel, create a Template for your report by typing Data Item, Start Time, End Time and Time Interval in cells
A1, A3, A4 and B5, respectively.
2. Using Search, find the attribute External Temperature for Mixing Tank1 and Mixing Tank2 and place them in cells
B1 and B2, respectively.
3. Type the Start Time of T (in cell B3), End Time of T+2h (in cell B4) and time interval of 10m (in cell B5).
During this section, please refer to the following course YouTube video:
Archived, sampled, and timed data functions
PI DataLink: Basics
PAGE 25 OF 52
Part 2 – Query PI Data
1. Obtain the compressed data for Mixing Tank1 External Temperature and list it in cell C3.
2. Do the same for Mixing Tank2 in cell G3.
3. Obtain the sampled data for Mixing Tank1 External Temperature in cell E3.
4. Comparing the timestamps of the compressed data for the two mixing tanks you notice they are not the same so
the comparison can’t be accurately done. To have a better comparison, use Timed Data function and get the
External Temperature of Mixing Tank2 at the timestamps in Column C.
4.3 Knowledge check: Tank analysis report
In this knowledge check you will practice extracting values and attributes from the PI System using the compressed and
sampled data functions.
Activity Description
Your task is to show the archived values of the Pressure of the two Mixing Tanks listed in your daily report for the past
24 hours. When retrieving the raw archived data for this process variable, you prefer to see one value each hour for the
past 24 hrs. You will use this report to do some analysis on this critical process variable of the mixing tanks.
Approach
1. Spend a few minutes and fill out the following table:
Data
Item
Start
Time
End
Time
Time
Interval
2. Open <your initials>_PI_DataLink-Exercises.xlsx then work on sheet Tank Analysis Report and fill in data for all of
the fields.
3. Use the Compressed Data and Sampled Data queries to fill in a template.
Bonus 1
Using Excel functions, modify your report such that you get one value at the top of each hour for the past 24 hours.
PI DataLink: Basics
PAGE 26 OF 52
Bonus 2
The Excel Insert tab in the Sparklines section has several options to show lines and columns as well as
additional options to format the axes and colors.
To use this functionality, first select the cell where you want to place the Sparkline. Then choose the Sparkline type. For
Data Range, select the cells with the data (just select the values, not the timestamps). Verify your output cell and choose
OK. The result is a small trend of your data, with an assumption of evenly spaced values over time.
4.4 Background: Element relative PI DataLink reports
PI DataLink promotes the reusability of PI DataLink reports and the creation of Element Relative PI DataLink
Reports.
This functionality is provided via Search.
You will need to:
• Set the Root path length to the mark before Name Only
• Insert root paths in: Drop-down list
resulting in:
Search for Assets by Filtering
The Element Relative search functionality is provided in PI DataLink through the Asset Filter search option.
The Asset Filter is helpful for searching the AF database for elements and filtering the returned elements by
the attribute values. Note that you must select an element template to filter elements if using attribute
values.
PI DataLink: Basics
PAGE 27 OF 52
The Asset Filter Search function returns assets that meet specified filter criteria. The returned assets can be
used in the worksheet as a function array which can update automatically, or as static values.
The filter criteria include:
• Element name
• Element template
• Element category
• Element description
• Value of attributes
Using the Asset Filter, you will have the option of returning the search results in a Drop-down list
which would be used in building reusable reports.
4.5 Hands-on: Element relative PI DataLink reports
Activity Description
You want to create a report to review the internal and external temperatures for all of the tanks.
Approach
We will follow two approaches in creating this report. The first approach is to use the Search functionality
while the second approach is to use the Asset Filter functionality.
Method 1: Using Search
1. Choose cell A1 in Excel
2. Select the AF Server > OSIsoft Plant > Production Area in Search.
3. Search for *TEMP*
You will notice that the results include all the attributes because their parent elements are based on the
template named “Generic Tank Template”.
Note: The Asset Filter option provides more options in filtering different assets and attributes when
creating an Element Relative Report. However, it requires a template for filtering the related assets.
During this section, please refer to the following course YouTube video:
Element relative PI DataLink reports
PI DataLink: Basics
PAGE 28 OF 52
4. Repeat the search for *TEMPER*
5. Select all eight (8) results using the <Shift> Click (or individually with Ctrl Click).
6. Use the Root path length slider set to the mark before Name Only.
7. Set ‘Insert root paths in:’ to Drop-down list.
8. Choose cell B2 and select the Current Value function.
9. Select the cells for Data item(s) and Root Path.
10. Now the Tank path can be modified with the drop-down list.
Method 2: Using Asset Filter
1. Add a new sheet.
a. Choose cell A2.
2. Click on Asset Filter from the Search section.
3. Under the Root path specify the path to the Production Area Element in the form of \\PISRV01\OSIsoft Plant.
4. Under the Element template, select “Generic Tank Template”.
5. Under the Attributes to display, select External Temperature and Internal Temperature.
6. Select Drop-down list.
7. Click Apply.
When working with Asset Filter and selecting the Drop-down list, the drop-
down list will appear in the cell above your Output cell. The selected Output
cell is where the selected attributes will start being listed.
PI DataLink: Basics
PAGE 29 OF 52
4.6 Knowlegde check: Operational start up
Activity Description
You want to determine if there is any deviation between start-up of operations for today compared to
yesterday, during the same period of time. Four tanks exist on your plant, and you want to be able to build
and reuse a single report for both tanks.
• Your daily shift starts at 6:30 am and the critical start up time is the first 2 hours after the start up.
• Gather the values for the external temperature for this period of time for today and yesterday
• Compare the two by calculating the ratio of the two days’ values (ideally, the ratio should stay as close as
possible to 1).
Approach
1. Spend a few minutes to fill out the following table:
Root Path
Data Item
Yesterday’s
Start Time
Yesterday’s
End Time
Today’s Start
Time
Today’s
End Time
Time Interval 4 Minutes
Excel
function for
Comparison
(ratio)
2. You will use the template provided in sheet Operational Start Up of the file <your initials>_PI_DataLink-
Exercises.xlsx.
3. Use both PI DataLink functions of Compressed and Sampled data in retrieving the values and doing the
comparison.
4. Which PI DataLink function, do you think, would be more suitable for the purpose of comparison of yesterdays
and today’s values?
PI DataLink: Basics
PAGE 30 OF 52
4.7 Background: Excel arrays in PI DataLink
4.7.1 Workflow prior to Dynamic Arrays – PI DataLink version 2019 and earlier
When using the PI DataLink product, end users may see this message below:
The Multiple Value functions just discussed actually return an array of values and timestamps. This array
cannot be modified cell by cell, but the entire array can be modified. These values can change in size based
on the point used, the time range specified, and exception and compression settings. Therefore, it can be
necessary to resize the array.
If too many values are returned, you will receive the message “Resize to show all values” at the bottom of
the array.
To resize an array is to right click anywhere in the array, and choose Recalculate (Resize) Function
During this section, please refer to the following course YouTube video:
https://www.youtube.com/watch?v=EabeWgcjjfQ&t=1s&ab_channel=OSIsoftLearning
PI DataLink: Basics
PAGE 31 OF 52
4.7.2 Workflow with Dynamic Arrays – PI DataLink version 2022
Starting with Office 2021 and Office 365 (January 2020 build or later), Microsoft introduced support for dynamic arrays.
Dynamic arrays can resize themselves if the data they contain updates. For instance, if a compressed data array shows
the events which occurred over the last day, and that number of events increases the next time, the spreadsheet opens,
and the dynamic array will resize itself without user interaction.
By default, DataLink continues to write legacy function arrays. However, where dynamic arrays are supported, the user
has the option to right-click on a legacy function array and convert to a dynamic array. After this conversion, any
subsequent modifications to the function through the task pane will write to the sheet as a dynamic array.
References:
• For more information see "Function arrays" in PI DataLink User Guide
PI DataLink: Basics
PAGE 32 OF 52
4.8 Background: Units of measure and descriptions as context in reports
It is possible to add Units of Measure (UOM) and Descriptions to reports. Doing so will add more context to
consumers. It is possible to retrieve these attributes, and any other attributes, for a data item using the
Properties function.
Different Properties will be shown depending on the Data Item listed. For both PI Points and AF Attributes,
the Units of Measure are shown as UOM in PI DataLink Properties list.
Data Archive
AF Server
PI DataLink: Basics
PAGE 33 OF 52
5 More PI DataLink functions
5.1 Background: More PI DataLink functions
In the previous lessons, we used different PI DataLink functions to access the raw data stored in PI Server.
However, you may not be interested in bringing all the raw data but instead apply calculations on these raw
values as they are retrieved from PI Server.
PI DataLink offers two ways to bring in calculated values:
1. Use different predefined calculation modes in PI DataLink as part of the Calculated Data function.
2. Use custom expressions in defining your desired calculation. The syntax used in these calculations is the same as
Performance Equations syntax which is explained later.
5.1.1 Calculated Data
The Calculated Data function returns a single calculated value or evenly spaced calculated values for a PI
Point or an AF Attribute. Calculation modes are:
• Total
• Minimum
• Maximum
• Standard deviation
• Range
• Average
• Count
• Mean
These summary calculations provide statistical information for data over a specified time period.
Selecting the Data Item radio button of the Calculated Data Function, you will see most of the fields are
similar to the other PI DataLink functions you have already worked with.
Some of the fields specific to this function are:
• Time interval: An optional field. If used, it allows the behaviour to be similar to the Sampled Data function and the
calculations will be done for the evenly spaced time intervals.
• Calculation mode: The available modes are the ones listed above.
• Show percent good: Checking this option displays the percentage of good data for the calculation time range. You
can use this to determine if you want use data that may not be 100% ‘good’.
Note: A Bad value for PI System is defined as any of the digital states from the System Digital State Set
in the Data Archive. Examples are I/O Timeout, No Data, Shutdown, No Sample, Intf Shut and Arc Off-
line. It makes no inferences about the Quality of the data in the plant.
PI DataLink: Basics
PAGE 34 OF 52
• Conversion Factor: If the calculation mode is set to Total, the conversion factor may need to be changed to a
number other than 1.
For time-weighted total calculations, use the Conversion Factor calculator. From the dropdown make a
selection based on the units of measure of the process variable. The required conversion factor number
will then be calculated and placed in the Conversion factor field.
When you click on the calculator icon next to the Conversion factor entry field you will get a popup that
lets you select the appropriate conversion factor:
5.2 Hands-on: Calculated data
Activity Description
We want to practice how to use Calculated Data and obtain the Average, Maximum and Minimum of Flow Rate for
Mixing Tank1 for the period of past two hours and for the past one day.
Approach
1. In Excel create a template with
a. Root Path: \\PISRV01\OSIsoft Plant\Production Area\Production Line1\Mixing Tank1
b. Data Item: Flow Rate
c. Start Time: *
During this section, please refer to the following course YouTube video:
Calculated data
PI DataLink: Basics
PAGE 35 OF 52
d. End Time: *-2h
2. Obtain the Average, Maximum and Minimum of Flow Rate for this tank and for the past one day.
3. Change the End Time to *-1d to see the PI Server recalculate these values.
5.3 Background: Calculating total and conversion factor
The conversion factor is a multiplier used to change a number from one unit of measure to another.
Ex: 1000 g per kg, 2.54 cm per inch, 24 hours per day, 1440 minutes per day
Since the Data Archive is not aware of engineering units, it assumes that rate points are in terms of units per
day. Typically, this assumption is inaccurate as many points are measured in terms of units per second, units
per minute, or units per hour. When using the Total function in Calculated Data, a conversion factor must
be used to correct PI's assumption that the data is in units per day. The conversion factor is equal to 1.0
when the source data are in units per day.
Actual Engineering Unit of the
Rate Point
Engineering Unit Assumed
by Data Archive
Conversion
Factor
units / day units / day 1
units / hour units / day 24
units / minute units / day 1440
units / second units / day 86400
Example: Flow rate is measure in litres per minute (l/m) and stored in the Data Archive. We are interested in
calculating total litres for an 8-hour period. Shown in the following illustration, flow rate is:
• 3 l/m for 3 minutes
• 5 l/m for 2 minutes
• 1 l/m for 3 minutes
PI DataLink: Basics
PAGE 36 OF 52
Total flow, is the area below the flow rate line (the area of the three rectangles added up).
The expected total flow would, therefore, be:
(3 l/m x 3 min) + (5 l/m x 2 min) + (1 l/m x 3 min) = 22 litres
Data Archive, however, assumes the unit of measure of the flow rate is gallons per day. Without applying
the conversion factor, the total flow calculated by Data Archive is returned as:
(3 litres per day x 3 min x 1 day/1440 min) + (5 litres per day x 2 min x 1 day/1440 min) + (1 litre per day x 3
min x 1 day/1440 min) = 0.01528 litres
The total computed by the Data Archive must be multiplied by the conversion factor of 1440 to get 22 litres:
0.01528 litres x 1440 = 22 litres
When the calculation mode is Total and part of the archived data within the range is bad, the reported value
is equal to the calculated total value divided by the fraction of the time period with good archived data. This
data normalization is equivalent to the assumption that for the bad data time range, the point value takes
on the average value of the entire range. However, this assumption may not be valid when a large fraction
of the time range contains bad data. Therefore, we recommend that you always look at the percent good
value before using the calculation result.
References:
• For more information see "Calculated Data" in
PI DataLink User Guide
PI DataLink: Basics
PAGE 37 OF 52
5.4 Hands-on: Calculating the total
Activity Description
We want to calculate the total Flow and the Average Flow Rate of Mixing Tank1 for yesterday. To know what conversion
factor to use, we need to bring the UOM of Flow Rate for Mixing Tank1.
Approach
1. In Excel create a template with
a. Root Path: \\PISRV01\OSIsoft Plant\Production Area\Production Line1\Mixing Tank1
b. Data Item: Flow Rate
c. Start Time: Y
d. End Time: T
2. Add a cell to your report template titled UOM and get this value for Flow Rate using Properties.
3. Add a cell to your report template titled Conversion Factor and write the appropriate value there.
4. Calculate the Average
5. Calculate the Total of Flow for the time range (don’t forget the Conversion Factor!)
6. Add the percent good.
5.5 Knowledge check: Production summaries
Activity Description
As the production manager, you want to create a report showing last week’s production statistics. You want
to display the following for the production from each day of the past week:
• Total
• Average
During this section, please refer to the following course YouTube video:
Calculating the total
PI DataLink: Basics
PAGE 38 OF 52
• Maximum
You also want to do the same calculations for the entire week.
PI DataLink: Basics
PAGE 39 OF 52
Approach
1. On the PI Server, the production is the sum of the productions from the two production lines and is stored as an
attribute named Production under the element of Production Area. It is defined as the sum of the flows of the
two Mixing Tanks in litres per minute.
2. Spend a few minutes and fill out the following table:
Root
Path
Data
item
Start
time:
End
time:
Time
interval:
UOM
3. You will use the template provided in sheet Production Summaries of the file <your initials>_PI_DataLink-
Exercises.xlsx. Use the values of your table in the provided template.
Note: When working with the weekly total, do not use the time interval. Only use it for the daily total.
Be sure to show the Start Time for the daily Calculations.
The percent good field is located to the right of an aggregate calculation, so use
with the Maximum.
PI DataLink: Basics
PAGE 40 OF 52
5.6 Background: Using time filtered data in a preventative maintenance
example
You have a Pump and would like to implement a Preventative Maintenance program. Firstly, you need to
determine the period that the pump was running. These total hours can then be checked against the
replacement period as indicated in the pump manufacturer’s datasheet; every 10,000 hours, for example.
If you have a PI Point recording the status of the pump (when it is ON or OFF) you can report your Preventative
Maintenance program by using the Time Filtered function. This function returns the amount of time that a
performance equation evaluates to true during a specified time period.
You need to specify the following fields for this function:
• Expression(s)
o Can specify 1 or more and it would follow the Performance Equation format. Data items permitted in
expressions are:
PI points
AF attributes that store data references to PI points
AF attributes that store constant values
• Start Time and End Time.
• Time Unit
o i.e., The pump was running for 5 seconds/minutes/hours/days
5.7 Hands-on: Controller state
Activity Description
We want to determine the amount of time a controller has been in the CASCADE state. The controller is a digital point,
named CDM158, and has five (5) discrete states. We are only concerned with the CASCADE state at this time.
Approach
1. In Excel create a label for the output cell Time in Cascade mode (hours).
2. Choose an empty output cell.
During this section, please refer to the following course YouTube video:
Controller state
PI DataLink: Basics
PAGE 41 OF 52
a. Select the Time Filtered function.
3. Use the following values for different fields:
a. Expression(s): ‘CDM158’ = “CASCADE”
b. Start Time: T-3d
c. End Time: T
d. Time Units: Hours
5.8 Knowledge check: Mixing tank level control
Activity Description
Mixing tank1 level going above 7 is an indication of malfunctioning of the pump. In our analysis report, we want to find
out for how many hours the pump was malfunctioning during yesterday.
Approach
1. In Excel create a report template similar to the screenshot at the end of the activity.
2. Select cell B1 and search for the Mixing Tank1 Level attribute.
3. Modify the expression and apply the PE syntax rules to ‘level’>7
4. Complete the report template with these values:
a. Start Time: T
b. End Time: Y
c. Time Units: Hours
5. Select the Time Filtered function and use the output cell as B6.
Note: The first ‘ in an Excel cell is used to signify that the cell content is text and that no Excel formulas
should be interpreted. To include an expression in a cell, you need to put a ‘ at the beginning of the
expression.
PI DataLink: Basics
PAGE 42 OF 52
5.9 Knowledge check: Condition based inspection
Activity Description
The pumps on our mixing and storage tanks need to be visually inspected every 4 months only if they have
been running for over 2,000 hours. As the person in charge of the Preventative Maintenance program, you
want to know the operating time for the pumps on the tanks.
A PI Point does not exist to determine if the pump is ON or OFF, but the pump only runs when the tank Level
is greater than 1.2. Find the amount of time the pumps on each of the tanks have been running for the past
4 months and find out if any of the pumps need to be inspected.
Approach
You will use the template provided in sheet Condition Based Inspection of the file <your
initials>_PI_DataLink-Exercises.xlsx. Get the total Operating Time of the pumps associated with tanks.
1. Use Asset Filter Search to bring the list of Assets without any attribute.
a. Specify the appropriate Root Path and Element Template in the search dialogue box and without selecting
any attributes, click Ok.
2. Use Time Filtered function to calculate the number of hours each pump was running.
PI DataLink: Basics
PAGE 43 OF 52
5.10 Background: Filtered data using custom expressions
When using different PI DataLink functions, we noticed an optional field of Filter Expression.
This option is available for the functions of:
• Compressed Data
• Sampled Data
• Calculated data
PI DataLink applies the filter expression to the raw data retrieved and only the values that make the
specified expression True, would be included. Therefore, for the two functions of Compressed Data and
Sampled Data, only the values that make the specified filter expression True would be listed in the output
cell. For the Calculated Data function, only the raw values that make the filter expression True, would be
considered in the calculations. With the use of Filter Expression, an option of Mark as filtered becomes
available for the Compressed and Sampled Data functions; if the Mark as filtered option is selected, a
Filtered status is returned for each group of values that does not satisfy the filter expression.
The Filter Expression is a performance equation and follows the formatting rules discussed in the previous
section.
References:
• For more information see "Expressions" in
PI DataLink User Guide.
5.11 Background: Excel conditional formatting
This topic is not specific to PI DataLink, but the Conditional Formatting found in Microsoft Excel can give
your PI DataLink reports a great perspective. It is useful for visually comparing values extracted from PI.
Conditional Formatting is found in the Home Ribbon in the Styles section. You can use this functionality to
provide highlighted maxima or minima, bar graph backgrounds, and many other visual cues about the data
being displayed.
To use this functionality, first highlight the group of cells you wish to format. Then click on the Conditional
Formatting button and choose a group and rule. For the Highlight Cells Rules and Top/Bottom Rules, you
will see a preview of the formatting once you configure the appropriate limits. For Data Bars, Colour Scales,
and Icon Sets, simply hover the mouse over each preconfigured option to preview its effect on the cells you
selected. Click the desired rule to select it.
Note: When using a cell reference for your Filter Expression, be sure that if the Filter Expression begins
with a timestamp or point name that you use two single quotes. The first single quote is used in Excel
to identify a text string, rather than a value, and will not be seen by the filter expression. Another
option is to enclose the entire filter expression in parenthesis, in which case the extra single quote is
not needed.
PI DataLink: Basics
PAGE 44 OF 52
If none of the preconfigured rules suit your needs, you can configure additional rules using Conditional
Formatting > New Rule. Multiple rules can be applied in configurable order using Conditional Formatting >
Manage Rules.
5.12 Knowledge check: Filtered data value queries
Activity Description
Retrieve data using the Compressed Data function and filter out all values below 150 because they are irrelevant to our
report.
Approach
Part 1
1. In Excel create a sheet with
a. Data Item: CDT158
b. Start Time: T
c. End Time: T-1d
d. Filter Expression: ‘‘CDT158’ > 150
2. Retrieve the Compressed Data and verify there are no values less than 150.
3. Values below 150 are unnecessary and we would not need to include them on our report.
Part 2
1. On a new sheet, create a sheet with
e. Tank: \\PISRV01\OSIsoft Plant\Production Area\Production Line1\Mixing Tank1
f. Process Variable: External Temperature
g. Start Time: T
h. End Time: T-1d
i. Filter Expression: ‘‘External Temperature’ > 200
2. Retrieve the Compressed Data and verify there are no
values less than 200; the filter expression removed
them.
Note: The MS Excel TODAY() function returns the serial number of the current date. The serial number
is the date-time code used by Microsoft Excel for date and time calculations. Microsoft Excel stores
dates as whole number of days starting at 1900. Dates and times are values and therefore can be
added, subtracted, and included in other calculations.
Syntax: =
TODAY()
Example: =
TODAY()- 10 + 16/24)
is today minus 10 days at 4 PM
PI DataLink: Basics
PAGE 45 OF 52
5.13 Knowledge check: Production level report
Activity Description
As a quality control manager, you want to examine the level of product in Mixing Tank1. Since this is an
analogue point, it is better to perform a Sampled Data query rather than a Compressed Data query.
You want to build a report for the level yesterday over 10-minute intervals. The report filters out mean tank
levels, which is defined as a level between 4 and 6.
Approach
1. Spend a few minutes and fill out the following table:
Root Path
Data Item
Start Time
End Time
Interval
Upper
Limit
Lower
Limit
Filter
Expression
2. You will use the template provided in sheet Production Level Report of the file <your initials>_PI_DataLink-
Exercises.xlsx.
Note: Are you familiar with the Excel function of CONCATENATE?
PI DataLink: Basics
PAGE 46 OF 52
6 Event-related features of PI DataLink
6.1 Background: Understanding the Event Explore and Compare
functions
PI DataLink allows browsing and comparing PI Events within Microsoft Excel.
These two Events functions return events that meet specified criteria in an AF database.
• Explore Events: this function returns one event per row and nests child events under parent events. This function
is useful to show child events under a parent event while preserving the hierarchy structure.
• Compare Events: this function returns one event per row but can return attributes from related events in that
same row. Specifically, to facilitate event comparison, the function can return attributes from child events or
parent events in the same row as the returned event. This function is useful to flatten the hierarchy to show a
particular child event that is common for each parent event.
6.2 Knowledge check: Monitoring downtime events
Activity Description
Operations manager needs a report that lists the amount of time that the tanks at your site had been down due to
mechanical issues (and not due to scheduled maintenance). Learn how to use event frames data in excel reports.
Approach
1. In Excel select an Output cell
2. Select Explore from the Events tab
3. Make the following selections:
a. Database:
\\PISRV01\OSIsoft Plant
b. Event name: *
c. Search start: *-1d
d. Search end: *
e. Event template: Downtime
f. Element name: *
4. Expand the More search options section. On the
Attribute value filters select the “Reason Code” for
the Attribute:
a. Reason Code = mechanical
PI DataLink: Basics
PAGE 47 OF 52
5. From Columns to display select Event name, Start time, End time, Duration, Primary element, Maximum External
Temperature, Maximum Internal Temperature, Reason Code and Temperature Difference.
6. Click Ok.
6.3 Knowledge check: Analyzing downtime events
Activity Description
The operations manager now needs a report that shows which downtime reason is most prevalent and a comparison
showing which tanks are the most problematic. He also would like to see information about the total production loss
from the tanks.
Approach
1. Use the template provided in sheets Downtime Raw Data and Evaluating Tank Downtime of the file <your
initials>_PI_DataLink-Exercises.xlsx.
2. Open the Downtime Raw Data Tab Select cell A7 as your Output cell
3. Select the Compare from the Events tab
4. Make the following selections:
PI DataLink: Basics
PAGE 48 OF 52
a. Database: \\PISRV01\OSIsoft Plant
b. Event name: *
c. Search start: *-1d
d. Search end: *
e. Event template: Downtime
f. Element name: *
5. From Columns to display select:
Event name, Start time, End time, Duration, Event Template,
Primary element, Event Duration, Maximum External Temperature,
Maximum Internal Temperature, Reason Code, Lost Production, and Temperature Difference.
6. Open the Evaluating Tank Downtime sheet.
7. Select the PivotTable under Comparing Reason Codes, then, from the Analyse tab, select Refresh
8. Select the PivotTable under Comparing Tanks, then, from the Analyze tab, select Refresh
9. Which Reason Code caused the most production loss overall? __________________
10. Which Reason Code caused the most production loss for the Storage tanks? __________________
11. Which Tank has caused the most production loss? __________________
PI DataLink: Basics
PAGE 49 OF 52
7 Functions using an expression
7.1 Background: Functions using an expression
As we saw earlier, some PI DataLink functions accept either a data item or an expression as input. These
functions have Data item and Expression options at the top of the task pane. So far, we only worked with
the Data Item option. In PI DataLink, expressions are performance equations that you can use to incorporate
mathematical operations and calculations based on PI System data items.
PI DataLink functions that can use Expressions (as well as Data Item) include:
• Archive Value function
• Sampled Data function
• Timed Data function
PI Expressions behave much the same way that PI PE do. They follow the same expression syntax and can
use one or many of the following Data items:
PI points
AF attributes that store data references to PI points
AF attributes that store constant values
The benefit to PI Expressions is that they only calculate on demand. However, the downside is that, unlike PI
PE, there is no history of these calculations stored on the PI Server, so it is more difficult to see historical
trends.
7.1.1 How is calculated data function different from PI expressions?
One difference between PI Calculated Data and PI Expressions is shown in an example below. The PI
Calculated Data computes the maximum of the point over each 1-day period for the last 7 days. The PI
Expression shown computes the maximum of the Sampled Data of point for the most recent 8 hours of
every 1-day period, for the last 7 days.
PI DataLink: Basics
PAGE 50 OF 52
The format for the PI Expression is:
TagMax('TagName','*-8h','*')
PI DataLink: Basics
PAGE 51 OF 52
Just like the PI Calculated Data, it would be configured with a start time of *-7d, an end time of *, and an interval of 1
day (1d). Keep in mind that the ‘*’ in the expression does not always go to the current time of the machine, the ‘*’ in
this case is represented by the right side of the 8-hour bar below. If the PI expression was changed to
TagMax('tag','*-1d','*'), the results of the PI Calculated Data and the PI Expression would be identical.
However, the PI Expression will contain an extra interval at the start time of the expression when compared directly with
PI Calculated Data
7.2 Knowledge check: Expression queries
Activity Description
We showed how we would be able to see the values of the Flow Rate of Mixing Tank2 for the past one day
at the top of each hour, using Sampled Data function.
Here, instead of bringing the raw values of the flow rate, we are interested in getting the difference
between the flow rate value and its average for the past day, and have it included in our Excel report.
Note: For simplicity, we will use PI Points in this example. You would be able to do this exercise using the AF
Attribute and the Concatenate function in Excel for building the expression.
Approach
1. Open Microsoft Excel.
2. Create a Template with:
a. Start Time: y
b. End Time: t
c. Time Interval: 1h
d. Expression: TagVal(‘VPSD.OSIsoftPlant.PL2.MXTK2:Flow Rate’)-TagAvg(‘VPSD.OSIsoftPlant.PL2.MXTK2:Flow
Rate’, ‘*-24h’,’*’)
3. Use Sampled Data function and use Expression in retrieving the data
PI DataLink: Basics
PAGE 52 OF 52
7.3 Knowledge check: Material balance report
Activity Description
Two products, named Product A and Product B are being mixed
in a tank, as shown in the following schematic.
The flow of Product A is stored in a PI Point named
VPSD.OSIsoftPlant.PL1.MXTK1:Flow Rate
and the flow of Product B is stored in a PI Point named
VPSD.OSIsoftPlant.PL2.MXTK2:Flow Rate.
You are interested in including the Percentage of Product A in
the mix, as it is a critical indicator. There is no PI Point storing
this value and you need to calculate it.
You are building this report for the past 7 days and would like to list this percentage for the period of every
2 hours.
Approach
1. Develop the expression for calculating the Percentage of Product A in the resulting mix.
2. Spend a few minutes and fill out the following table:
Expression
Start Time
End Time
Time
Interval
3. You will use the template provided in sheet Material Balance Report of the file <your initials>_PI_DataLink-
Exercises.xlsx.
4. How would you change the expression if one of the two Products was not flowing, and the associated point was
showing a bad value of “Shutdown”? (Hint: If Then Else)
8 Final Exam
The final exam in this course is taken online. Please check the course listing online for more details.
