I'm looking for a complete summary on different ways to get data from PI to Power BI ? Like Data Archive or AF to Power BI with pros and cons
I can't give a complete summary, but I'll share what I know.
If you have access to the PI Integrator For Business Analytics, then you should use that according to this discussion. However, if your company doesn't already have it, it is probably not worth getting yet. It is a separate expense and is not included in the Enterprise Agreement.
Your next best bet is to install PI SQL DAS (RTQP Engine) on the PI Asset Framework server and query it using PI SQL Client. PI SQL Client is the successor to PI OLEDB Enterprise, is easier to use, easier to optimize, and runs lightning fast. However, it pulls data only through the PI Asset Framework, so you need that set up first.
I can't think of any time when you would want to use PI OLEDB Enterprise instead of PI SQL Client for retrieving time-series data. Maybe if you have a license for the former but not the latter?
If your organization doesn't use the PI Asset Framework, then PI OLEDB Provider will have to suffice. It allows you to write SQL queries that access data directly from PI Points. A lot of processing occurs on the client side, so you need to be careful with how you write your queries, otherwise they'll take forever to run.
I have a blog post that covers how to get started using the PI SQL products with Power Query. It's in the context of Excel, but the steps should be similar for Power BI.
Retrieving data ...