I need to extract 3 year data from Data Archive for 2300 tags. Is there a recommended way to perform this extraction, lets say to get this data in a text file, or through sql using PI System Data Access.
I would suggest using the OSIsoft Powershell Tools. Having performed similar gigantic extractions I have some suggestions on how to proceed as well. First, you have to perform this on a server where the OSIsoft powershell library is installed (typically installed on AF, and PI Servers). You can run 'Get-Command -Module OSIsoft.Powershell' to activate it, and then run 'Get-Module' to make sure it is there. Next run a test set to see how long one months worth of data for 2300 tags will take.The time it takes to perform a one month extract multiplied by 36 months will give you a rough estimate of how long it will take to run your three year extract. Next I suggest you perform this on a server that is not primary to other operations to help avoid other critical data tools from timing out.. I would also suggest performing these extracts on smaller sets of time ranges such as three month quarters, and compile the data on the other end because excel can only handle a million rows of data. The following image is an example of the output.
Attached is a PowerShell script you can use to start.
Thank you James,
I will start extracting data and let you know how did it go.
How could I export the data timestamps in local time?
I assume your need to export timestamps to your local time is because the pi server stores timestamps in UTC.
You could try a "fudge factor" with the $startTime, and $endTime variables. In my case we operate at UTC -6, which is USA Mountain Standard Time. So to account for the variance I subtracted 6 hours from the $startTime, and from the $endTime. This preserves the speed of the 'Export-Csv' method in my script. If you are not sure of your variance you can use this handy reference found here: Time Zone Map. So for a start time of July 1st, 2019 at midnight in USA Mountain Standard Time I set $startTime = "6/30/2019 18:00:00". My output then reflects the timestamps from midnight July 1st, 2019 in Excel.
I worked out an alternative script, which I attached (version '05'), which utilizes System.IO.Streamwriter method to write the individual converted timestamps and values to csv files. We have used both scripts in our operations, but the 'Export-Csv' is faster.
Retrieving data ...