I've recieved numerous requests to create a process to extract data from PI into SQL Server where it could be more easily reported on for some users.
Basically they are seeking a Data Mart of select PI Data that could be consumed by SSAS. I've seen a white paper on this where it was suggested to use PI-OLEDB or AF. We don't have AF at the moment so that leaves me with OLEDB. I'm wondering if anyone has done something along these lines and if there are any lessons to be learned? Some of the challenges I see up front are:
1. Load queries to load a lot of historical data. We have alot of data in PI and loading any significant amount into SQL would be very time consuming.
2. How to handle updates to data that may change after the initial load or incremental updates.
3. How to model time series pi data in a relational model for use in SQL - likely SSAS.
Has anyone else been getting these requests? How have you solved it? Thanks in advance for any comments!