piPointList.InterpolatedValues Powershell export to csv, per column the values of the PIPoint

Do you know which specific parts are slow?  Line 52 makes a lazy bulk data call.  Line 55 transforms the lazy call into a very active call. 

After Line 55, I see no OSIsoft calls, so any slow downs are with your code or your network.

Also, the PowerShell Add-Member calls most likely use Reflection, and are generally considered sluggish by their very nature.  The most general bit of advice to speed this up is to use Hash tables or Dictionaries.

Hi Ralph,

The answer given by Rick pretty much sums it up. To validate what calls are slow, you can run an AFSDK trace against PowerShell to determine which calls are Slow. Moreover, the issue might in located in powershell interop between the reflected assembly. However AFSDK trace would make it more clear if there is any room for improvement on the server side. 

It has indeed to do that we use an array instead of a dictionary. Below part is really slow. But for us it's not clear how to use a dictionary with the correct AFSDK funcions, to speed this up.

foreach ($result in $listResultsarr) {
if ($overall_counter -gt 0 -and (!($resultsMap | Get-Member | where { $_.membertype -eq "Noteproperty"} ).name.contains($result.PIPoint.Name))) {
$resultsMap | Add-Member -MemberType NoteProperty -Name "$($result.PIPoint.Name)" -Value $null
"Added column for $($result.PIPoint.Name)"
$columncounter++
$loop_counter = 0
if ($columncounter -eq 1) {
$loop_size = $overall_counter
} 
}
if ($columncounter -eq 0) {
$resultsMap += [PSCustomObject]@{"Timestamp"="$($result.Timestamp.LocalTime)";"$($result.PIPoint.Name)"="$($result.Value)";}
}
else {
$row = $loop_counter
$resultsMap[$row]."$($result.PIPoint.Name)"="$($result.Value)"
}
Write-Progress -Activity "Creating object with PI Values...." -Status "$([math]::Round(($overall_counter / $listResultsarr.count) * 100))% Complete:" -PercentComplete (($overall_counter / $listResultsarr.count) * 100)
$loop_counter++
$overall_counter++
}
Write-Output "Processed $($overall_counter) values in $((Get-Date).Subtract($start_time).Seconds) second(s)"
}

I've changed the loop to this and used a Hash tables or Dictionaries. But now I only have the timestamps as a result, because I use $output.Timestamp, but I've no clue to add the values of the PIPoints here without getting these kind of values in the CSV; "System.Object[]" and "OSIsoft.AF.Asset.AFValues"

$resultsMap = @{}

try
{
 [Collections.Generic.IEnumerable[OSIsoft.AF.Asset.AFValues]] $listResults = $piPointList.InterpolatedValues($timeRange, $timeSpan, "",$true, $config) 
 
 $i=0
 foreach ($pointResult in $listResults) {
 if ($i -eq 0) {
 $resultsMap.add("Timestamp", $pointresult.Timestamp)
 $i++
 }
 else {
 $resultsMap.add($i, $pointresult)
 $i++
 }
 }

 
}

catch 
{
 Write-Host $_
}
 

$output = @()
$output += New-Object PSObject -Property $resultsMap
$output.Timestamp | Select-object localtime | Export-Csv -Delimiter ";" -Path $myDestinationForData -NoTypeInformation

You are getting closer.  While your performance issue is entirely with PowerShell and not AF SDK, let's take a step back to examine things in AF SDK that can help.

The order of tags in a PIPointList can be fairly well determined. If you wanted to have a tight control over the specific ordering, I would recommend a case-insensitive Dictionary keyed by String with a value of PIPoint.  The String key would be the case-insensitive tag name.  I know how to do this in C# but not too well with PowerShell.

An InterpolatedValues call may return collections of AFValues in a certain order, but there are 2 things we know.  One, for enumeration of the result, the entire AFValues list for that enumeration will only apply to one specific PIPoint.  And two, since it's interpolated values over a range, then each AFValues list will have matching timestamps at their respective index within the AFValues list.

So rather than a dictionary keyed by string, you may need 2 dictionaries.  The first would be of your output order of tag, or specifically the case-insensitive dictionary keyed by the Tag Name, with the value being the PIPoint.  The next dictionary would be a transformation of the results from InterpolatedValues, and it would be keyed by PIPoint with the value being the AFValues list.  

You might be able to not have the 2nd dictionary, and instead have a specific array or list of AFValues, where a given index of the list refers to a specific PIPoint.  What you would need to tie together in your code is how to populate the list of AFValues so that the given index refers to the correct tag.  Another nice property to understand is that you could check the first value in the AFValues collection to grab the .PIPoint property, and therefore its name.

And all of this jumping through hoops is because you may want a specific order, and all of this is also the reason for the degraded performance.  If you would be happier just to know you've dumped all the data, but maybe "Tag 2" comes before "Tag 1", you wouldn't need dictionaries but would need the array or list of AFValues as mentioned in the immediately preceding paragraph.

Thanks for all the help. The performane is now really good. I think I'm almost there, if I change this line of code

$output.Timestamp | Select-object localtime | Export-Csv -Delimiter ";" -Path $myDestinationForData -NoTypeInformation

to this

$output.1 | Select-object value | Export-Csv -Delimiter ";" -Path $myDestinationForData -NoTypeInformation

I've got the values of the first tag in a csv, and if I change the "1" into a "2", I've get the values of the second tag in a csv.

But I have no clue how I loop through $output. and get the columns after eachother... Someone has an idea?

Some more information to maybe make it more clear. If I run below, as mentioned in this link .

$output.GetEnumerator() 

I get this result in powershell;

13 : {27,83895, 27,47229, 27,27062, 27,65563...}
12 : {0,0183332, 0,0183332, 0,0183332, 0,0183332...}
11 : {-0,0144444, -0,0144444, -0,0144444, -0,0144444...}
10 : {7,305275, 7,493052, 7,381108, 7,568886...}
9 : {0, 0, 0, 0...}
8 : {2,899999, 2,684999, 3,155994, 2,988257...}
7 : {55,44197, 55,93599, 55,41597, 55,38997...}
6 : {1015,44, 1015,44, 1015,44, 1015,44...}
5 : {43,46998, 43,66999, 43,98998, 44,18999...}
4 : {18,04999, 18,02999, 18,02999, 17,92997...}
3 : {-25,75318, -25,75318, -25,75318, -25,75318...}
2 : {-0,1025543, -0,1025543, -0,1025543, -0,1025543...}
1 : {99,97687, 99,97697, 99,97709, 99,9772...}
Timestamp : {1-6-2020 00:00:00, 1-6-2020 00:05:00, 1-6-2020 00:10:00, 1-6-2020 00:15:00...}

The GetEnumerator() is one way to do it.  An alternative is to use PowerShell's for ForEach loop, which calls the enumerator.

Again, if order is not that critical, the simplest way to do it is to transform the output from InterpolatedValues from an IEnumerable[AFValues] to either a List[AFValues] or an AFValues[] array, i.e use a simple ToList or ToArray.  Each index of that list/array refers to one PIPoint, which you can get from the 0 index of the respective AFValues.

You can play around with how you want the timestamp to appear, or later work on an order, but here's one way to jump dump everything to a file:

x

#Load the AF SDK assembly through reflection

[Reflection.Assembly]::LoadWithPartialName("OSIsoft.AFSDK") | Out-Null

# Connect to the default PI Data Archive
[OSIsoft.AF.PI.PIServers] $piSrvs = New-Object OSIsoft.AF.PI.PIServers
[OSIsoft.AF.PI.PIServer] $piSrv = $piSrvs.DefaultPIServer

# Define the time range for data retrieval
$startTimeString = "2020-10-13T00:00:00"
$endTimeString = "2020-10-14T00:00:00"
[OSIsoft.AF.Time.AFTime]$pistarttime = [OSIsoft.AF.Time.AFTime]::Parse($startTimeString)
[OSIsoft.AF.Time.AFTime]$piendtime = [OSIsoft.AF.Time.AFTime]::Parse($endTimeString)

# Define the time range and span interval for data retrieval
[OSIsoft.AF.Time.AFTimeRange] $timeRange = New-Object OSIsoft.AF.Time.AFTimeRange($pistarttime, $piendtime) 
[OSIsoft.AF.Time.AFTimeSpan] $timeSpan = New-Object OSIsoft.AF.Time.AFTimeSpan([TimeSpan]::FromMinutes(5))

[System.Diagnostics.Stopwatch]$stopWatch = New-Object System.Diagnostics.Stopwatch

$OutputFolder = "C:\Temp\"
$Timestamp=get-date -uFormat "%m%d%Y%H%M%s" 
$NewFileName = "Export"+ $Timestamp +".csv" 
$myDestinationForData = "$($OutputFolder)$($NewFileName)"

[string[]]$namesArray = "SINUSOID", "CDT158"

[void]$stopWatch.Restart()
[OSIsoft.AF.PI.PIPointList]$piPointList = New-Object OSIsoft.AF.PI.PIPointList 
$piPointList.AddRange( [OSIsoft.AF.PI.PIPoint]::FindPIPoints($piSrv, $namesArray, $null) );
[void]$stopWatch.Stop()
Write-Output "FindPIPoints took: $($stopWatch.Elapsed.TotalSeconds) second(s)"

[OSIsoft.AF.PI.PIPagingConfiguration] $config = New-Object OSIsoft.AF.PI.PIPagingConfiguration([OSIsoft.AF.PI.PIPageType]::TagCount, 100)

[String]$separator = ";" # could be a Comma, Semi-Colon, or Tab among other things
[System.Text.StringBuilder]$builder = New-Object System.Text.StringBuilder

try
{
 [void]$stopWatch.Restart()
 [OSIsoft.AF.Asset.AFValues[]] $listResults = ($piPointList.InterpolatedValues($timeRange, $timeSpan, "",$true, $config) ) 
 [void]$stopWatch.Stop()
 Write-Output "PI InterpolatedValues took: $($stopWatch.Elapsed.TotalSeconds) second(s)"

 $tagCount = $listResults.Length
 $eventCount = $listResults[0].Count # i.e. count of returned timestamps

 [void]$stopWatch.Restart()

 # Header row
 [void]$builder.Append("Timestamp")
 for ($i = 0; $i -lt $tagCount; $i++)
 {
 $tagName = $listResults[$i][0].PIPoint.Name
 [void]$builder.Append($separator + $tagName)
 }
 [void]$builder.AppendLine("")

 # Detail rows
 for ($eventIndex = 0; $eventIndex -lt $eventCount; $eventIndex++)
 {
 [void]$builder.Append($listResults[0][$eventIndex].Timestamp.LocalTime.ToString("yyyy-MM-ddTHH:mmK"))
 for ($i = 0; $i -lt $tagCount; $i++)
 {
 $piValue = $listResults[$i][$eventIndex].Value.ToString()
 [void]$builder.Append($separator + $piValue)
 } 
 [void]$builder.AppendLine("")
 }

 [void]$stopWatch.Stop()
 Write-Output "StringBuilder took: $($stopWatch.Elapsed.TotalSeconds) second(s)"
}
catch 
{
 Write-Host $_
}

[void]$stopWatch.Start()
[String]$builder | Out-File -FilePath $myDestinationForData 
[void]$stopWatch.Stop()

Write-Output "Create Out File took: $($stopWatch.Elapsed.TotalSeconds) second(s)"

In the name of variety, I am doing a lot of things differently.

• I use a Stopwatch object for timing calls.
• My time strings follow an ISO-8601 format, and are first parsed into AFTime objects before moving onto the AFTimeRange.
• I set the AFTimeSpan quickly with a TimeSpan.FromMinutes(5) call.
• I combined the FindPIPoints and assign to a PIPointList into one call.
• I combined the InterpolatedValues call with transforming it to an array in one call.
• The $separator variable allows me to change the column delimiter from a comma to perhaps a tab or a semi-colon.
• I do not output as a CSV now, but instead just perform a flat out file dump.

Hi Ralph, from the code you have shared it seems as though you are not accessing the value for this PI Point. To do so you would need to call $pointresult.Value which contains the actual value for this point. You may find this link of use for this issue. Moreover, you might want to consider using a dictionary with a key being the PI Point such as:

Dictionary<PIPoint, AFValues> resultsMap = new Dictionary<PIPoint, AFValues>();
IEnumerable<AFValues> listResults = piPoints.RecordedValues(timeRange, AFBoundaryType.Inside, null, false, config);
EventsRead = 0;
foreach (AFValues pointResults in listResults)
{ 
resultsMap[pointResults.PIPoint] = pointResults; 
EventsRead += pointResults.Count();
}

This being said, you may also want to have a look at the following link for further insight on how to use listdatamethods.