PI DataLink Exercises – version 4.6
Table of contents
Introduction 3
Exercise DL1 - Activity Report 5
Exercise DL2 - Operational Start Up 11
Exercise DL3 - Daily Production 13
Exercise DL4 - Off-spec Production 17
Exercise DL5 - Expression Calculation (Archive Value) 21
Exercise DL6 – Quality Control Report (Optional) 25
Exercise DL7 - Operating Time with Time Filtered 31
Exercise DL8 - Tank Level 35
Exercise DL9 - Advanced Calculated Data (Optional) 39
The information contained in this guide is confidential and may be subject to revision. No part of this publication may be reproduced or transmitted, in any form or by any means without prior permission of OSIsoft Canada. All rights reserved, OSIsoft ã 2008
Page 1
PI DataLink Exercises – version 4.6
Introduction
The following are the PI DataLink exercises. Some of the exercises cannot be completed unless you have completed the previous ones.
These exercises can be completed with either Microsoft Excel 2003 or Microsoft Excel 2007 using the PI DataLink version 4.0.3 and higher.
The exercises in this book are designed to be performed using live software. In order to perform the exercises in a self-study environment, you will have to have an installed and working PI System and the appropriate client applications. Some applications require tags, modules, and/or elements and attributes to be configured. If there are configurations required they will be described in detail before the exercise. Occasionally, sample data files are provided. Prerequisites are described for each exercise.
The exercises are designed to be accomplished using only the descriptions in the Approach sections. If you have trouble you can reference the Step-by-step Solutions at the end of each exercise. You should always try to complete the exercise from the Approach section first.
Properly licensed software is required. OSIsoft does not provide sample software or test environments for this tutorial. Any sample data files or configuration files referenced can be acquired as part of the zipped Training Materials package that can be downloaded from the OSIsoft technical support web site (http://support.osisoft.com). You will be required to register to validate customer status.
If you have any questions please contact the Training Department at .
The information contained in this guide is confidential and may be subject to revision. No part of this publication may be reproduced or transmitted, in any form or by any means without prior permission of OSIsoft Canada. All rights reserved, OSIsoft ã 2008
Page 1
PI DataLink Exercises – version 4.6
Exercise DL1 - Activity Report
Objectives
Learn to extract single values from the PI database using the following functions:
· Current value;
· Archive value;
· Tag attribute.
Problem Description
You need to build a basic activity report using data collected in your PI Server. You want this type of report to show values from the production plant floor before morning meeting. This report will show current values and ones from a specified time every morning.
Approach
We will use the Current Value, Archive Value and Tag Attributes features to fill in a template of an activity report. Remember that we should base our PI DataLink queries on cell references whenever possible. Open the file Exercise_DL1_Template.xls and fill in data into all of the fields. Note all of the tags used in this example have engineering units.
/ Try to do this exercise on your own before proceeding to the step-by-step solution.
Step-by-Step Solution
List
1. Open the file Exercise_DL1_Template.xls.
2. Select on the cell B7.
3. Click on PI ribbon > Tag Attributes.
4. In the task pane click in the Tagname(s) field.
5. Click and drag through cells A7 through A11 to select the cell range.
6. In the Attributes drop-down select descriptor.
7. Click on the OK button.
8. Select the cell C7.
9. Repeat Steps 3 – 7 instead selecting engunits in the Attributes drop-down.
10. Select on the cell D7.
11. Click on PI ribbon > Current Value.
12. In the task pane click in the Tagname(s) field.
13. Click and drag through cells A7 through A11 to select the cell range.
14. Select the time at left radio button.
15. Click on the OK button.
16. Select on the cell B19.
17. Click on PI ribbon > Archive Value.
18. In the task pane click in the Tagname(s) field.
19. Click and drag through cells A19 through A23 to select the cell range.
20. In the task pane click in the Timestamp field.
21. Select the cell D14.
22. Change the Retrieval Mode to interpolated.
23. Select the time at left radio button.
24. Click on the OK button.
25. Repeat steps 17 – 25 for the columns to the right, changing the Retrieval Mode to the appropriate selection from the heading (e.g., change the Retrieval Mode to previous for the Previous column, etc.).
Note 1
Notice that you can use the PI Datalink functions without the dialog box. Simply type in the formulas as follows:
Tag attribute:
Syntax =PITagAtt("tagname","NameOfAttributeYouWant","PIServer")
Example =PITagAtt($A$7,"descriptor",)or =PITagAtt("sinusoid","units",)
Current Value:
Syntax =PICurrVal("tagname",Outcode, "PIServer")
Example =PICurrVal($A$7,1,)
Archive value:
Syntax =PIArcVal("tagname","Date",Outcode, "PIServer","Mode")
Where mode = Interpolated or previous or next
Example =PIArcVal($A$7,$D$13,1,, "Interpolated")
Exact Time Value:
Syntax =PIExTimeVal("tagname","Date","PIServer")
Example =PIExTimeVal($A$7,$H$25,)
Note 2
We have seen that it is possible to enter a formula by entering one tag at a time. However it is possible to save time by selecting a range of cells instead of a single cell or using the copy/paste function of MS Excel. You must edit the formula and remove any unnecessary $character (absolute cell references) before using MS Excel copy/paste function.
Note 3
Note the impact of the point type and step attributes when you use archive value. For digital tags, the interpolated and previous values are equal and have the same timestamp because no interpolation is possible between states.
The information contained in this guide is confidential and may be subject to revision. No part of this publication may be reproduced or transmitted, in any form or by any means without prior permission of OSIsoft Canada. All rights reserved, OSIsoft ã 2008
Page 32
PI DataLink Exercises – version 4.6
Exercise DL2 - Operational Start Up
Objectives
· Learn to retrieve process data using the Compressed Data function.
Problem Description
You need to compare the values stored in the PI Server that relate to the startup of a piece of equipment in your facility. You want to determine if there is any deviation between startup operations examining today and comparing it to the same time period the day before.
The tag you want to use as a metric is CDT158. Your shift begins at 6:30 am in the morning and you want to gather the first 30 values beginning at that time for today and yesterday, and do some quick math to determine the deviation. A ten percent deviation in a positive or negative direction will be considered over / under range. All other outcomes will be considered nominal.
Approach
We need to create a spreadsheet that will show the first 30 values of the day for the tag CDT158. We also want to compare those values with the previous day’s starting 30 values. Open the file Exercise_DL2_Template.xls and fill in data into all of the fields. Both queries are using the Compressed Data (Start Time / Number) query. Use cell references for all of the dialog box fields. The formulas used to compare the two queries are found in the upper right portion of the spreadsheet. They both have a single quote inserted before the equal sign so MS Excel will treat them as strings and not formulas. You can copy and paste the contents and then remove the leading single quotes.
/ Try to do this exercise on your own before proceeding to the step-by-step solution.Step-by-Step Solution
List
1. Open the file Exercise_DL2_Template.xls.
2. Select cell A10.
3. Click on PI ribbon > Compressed Data.
4. In the task pane select the Number of Values radio button.
5. In the task pane click in the Tagname(s) field.
6. Click on cell B3.
7. In the task pane click in the Start Time field.
8. Click on cell B5.
9. In the task pane click in the Number of Values field.
10. Click on cell B6.
11. In the task pane select the Show Timestamps check box.
12. Click on the OK button.
13. Select on cell D10.
14. Repeat steps 4-13 but modify the Start Time field for the today’s start time (cell B4).
15. Select cell K3.
16. In the Formula Bar select all except the single quote at the beginning of the expression.
17. Paste the expression without the single quote into cell G10.
18. Propagate the result down to cell G39 by dragging the cell by the lower-right corner.
19. Select on cell K6.
20. In the Formula Bar select all except the single quote at the beginning of the expression.
21. Paste the expression without the single quote into cell I10.
22. Propagate the result down to cell I39 by dragging the cell’s handle at the lower-right corner.
The information contained in this guide is confidential and may be subject to revision. No part of this publication may be reproduced or transmitted, in any form or by any means without prior permission of OSIsoft Canada. All rights reserved, OSIsoft ã 2008
Page 32
PI DataLink Exercises – version 4.6
Exercise DL3 - Daily Production
Objectives
· Learn to extract calculated values using Calculated data function.
Problem Description
You are the production manager and want to create a report showing last week’s daily production totals. You want to display the total production, average, minimum and maximum and standard deviation for that week. Also, you would like to present this data on a daily basis.
Approach
We’ll use the built-in PI Calculated Data functions to show the above stats for the entire week (no time interval needs to be specified) and each day of the week (time interval of 24h).
/ Try to do this exercise on your own before proceeding to the step-by-step solution.Step-by-Step Solution
List
1. Open the file Exercise_DL3_Template.xls.
2. Select cell B16.
3. Click on PI ribbon > Calculated Data.
4. Make sure the PI Tag radio button is selected.
5. In the task pane click in the Tagname(s) field.
6. Click on cell B3.
7. In the task pane click in the Start Time field.
8. Click on cell B4.
9. In the task pane click in the End Time field.
10. Click on cell B5.
11. In the Calculation Mode drop-down select total.
12. For total calculation mode only enter 1440 in the Conversion Factor field. For all other calculation modes leave this field to one or 1.
13. Click on the OK button.
14. Repeat steps 3-14 for columns C through G using the appropriate selection for Calculation Mode (e.g., select Average in column C). In column G activate the show percent good check box.
15. Select cell A20.
16. Click on PI ribbon > Calculated Data.
17. In the task pane make sure the PI Tag radio button is selected.
18. In the task pane click in the Tagname(s) field.
19. Click on cell B3.
20. In the task pane click in the Start Time field.
21. Click on cell B4.
22. In the task pane click in the End Time field.
23. Click on cell B5.
24. In the task pane click in the Time Interval (optional) field.
25. Click on cell B7.
26. In the Calculation Mode drop-down select total.
27. For total calculation mode only enter 1440 in the Conversion Factor field. For all other Calculation Modes leave this field to one or 1.
28. In column A only activate the show end time check box in order to fill the Time stamp column.
29. Click on the OK button.
30. Repeat steps 16-30 for columns C through G using the appropriate selection for Calculation Mode (e.g., select Average in column C). In column G activate the show percent good check box.
The information contained in this guide is confidential and may be subject to revision. No part of this publication may be reproduced or transmitted, in any form or by any means without prior permission of OSIsoft Canada. All rights reserved, OSIsoft ã 2008
Page 32
PI DataLink Exercises – version 4.6
Exercise DL4 - Off-spec Production
Objectives
· Learn to apply filters to select the values to extract using the Filter Expression option of the Sampled data function.
Problem Description
As a quality control manager, you want to examine the production over the last 8 hours for a tag that does not report many values due to its lack of volatility and thus its high compression rate on the PI Sever. Since there are only a few values passing through the compression, it is better to perform a Sampled Data query rather than a Compressed Data query.
The tag you will use is SINUSOID and you want to build a report that will make it clear where the lowest 20% of values lie.
Approach
Use PI Datalink’s Sampled Data function to get evenly spaced data. Because we only want to see data that are outside of the norm, we will apply filters to show high values, low values, and a list including both.
Note 1
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.