I would like to confirm that Excel Add-in is the best tool for collecting large amounts of data for use in third-party tools, or if we have some more suitable tool.
When you mention large amounts of data, could you give us a estimate of how many events/tag ? What is the format that you want the data in? Which third party tools are you looking at?
In general you should also look at reducing expensive archive queries to the PI Data Archive.
The recommended tool for csv data is PI DataLink, which is a Microsoft Excel add-in that lets you retrieve information from your PI System directly into a worksheet where you can view, report, and analyze PI System data. PI DataLink directly integrates your PI Server data with Microsoft Excel so you can easily analyze all your operational data using the powerful analytic features of your spreadsheets.
Note: Opening .csv files using MS Excel may run into specifications and limits set by Microsoft. For 2016 version this is 1,048,576 rows.
Another option to explore is PI Integrator for Business Analytics presents PI System data perfectly suited to business intelligence tools including but not limited to Tableau, Tibco Spotfire, QlikView, and Microsoft Power BI for reporting and analytics. Business Intelligence (BI) client tools offer the ability to run retrospective analyses on a much larger set of your real-time PI System data. The data can be cleansed using a variety of filters and enhanced with asset, event, and time context from the PI System. The resultant PI Views are immediately usable in BI tools without further modification.
If for some reasons you are unable to use PI DataLink, which is preferred over any third party tools, you can take take a look at Extracting large event counts from the PI Data Archive blog and see if the custom utility mentioned in it helps.
Very good. Tomorrow I'll discover how large the dataset will be and ir I'll need something other than Excel.
Retrieving data ...