PI Web API Data Connector for Power BI

Blog Post created by rborges Employee on Oct 23, 2018


Lately, I've been experimenting with Microsoft Power BI and I'm impressed by how mature the tool is. The application not only supports a myriad of data sources but now there is even a Power Query SDK that allows developers to write their own Data Connectors. Of course, the majority of my experiments uses data from PI Points and AF Attributes and, because Power BI is very SQL oriented, I end up using PI OLEDB Enterprise most of the time. But let's face it: writing a query can be tricky and not a required skill for most Power BI users. So I decided to create a simple PI Web API Data Connector for Power BI. The reason I decided to use PI Web API is that the main use-case for the Data Connector is "Create a business analyst friendly view for a REST API". Also, there's no reason to install additional data providers.


Important Notes

Keep in mind that this tool is meant to help in small experiments where a PI and Power BI user wants to get some business intelligence done on PI data quickly. For production environment use cases, where there is a serious need for shaping data views, or where scale is of importance we highly recommend the use of PI Integrator for Business Analytics . Also, in order to avoid confusions, let me be clear that this is not a PI Connector. Microsoft named these extensions to Power BI "Data Connectors" and they are not related to our PI Connector products.


The custom Data Connector is a Power BI beta feature, so it may break with the release of newer versions. I will do my best to keep it update but, please, leave a comment if there's something not working.  It's also limited by the Power BI capabilities, that means it currently only supports basic and anonymous authentication for web requests. If the lack of Kerberos support is a no-go for you, please refer to this article on how to use PI OLEDB Enterprise.



If you are using the latest version of the Power BI (October 2018), you should enable custom data connectors by going to File / Options and Settings / Options / Security and then lowering the security for Data Extensions to Allow any extension to load.


2018-10-17 10_48_09-.png


For older versions of the Power BI, you have to enable Custom data connectors. It's under File / Options and Settings / Options / Preview features / Custom data connectors.


2018-06-28 09_12_56-.png


This should automatically create a [My Documents]\Power BI Desktop\Custom Connectors folder. If it's not there, you can create it by yourself. Finally, download the file (PIWebAPIConnector.mez) at the end of this article, extract from the zip and manually place it there. If you have Power BI instance running, you need to restart it before using this connector. Keep in mind that custom data connectors were introduced in April 2018, so versions before that will not be able to use this extension.


Hot to use it

You first have to add a new data source by clicking Get Data / More and finding the PI Web API Data Connector under Services.


2018-10-17 10_59_21-.png

Once you click the Connect button, a warning will pop up to remember this is a preview connector. Once you acknowledge it, a form will be presented and you must fill it with the appropriate data:


2018-10-17 11_09_35-.png


Here's a short description of the parameters:


ParameterDescriptionAllowed Values
PI Web API ServerThe URL to where the PI Web API instance is located.A valid URL: https://server/piwebapi
Retrieval MethodThe method to get your data.Recorded, Interpolated
ShapeThe shape of the table. List is a row for every data entry, while Transposed is a column for every data path.List, Transposed
Data PathThe full path of a PI Point or an AF Element. Multiple values are allowed separated by a semicolon (;).\\PIServer\Sinusoid;\\AFServer\DB\Element|Attribute
Start TimeThe timestamp at the beginning of the data set you are retrieving.A PI TIme or a timestamp: *-1d, 2018-01-01
End TimeThe timestamp at the end of the data set you are retrieving.A PI TIme or a timestamp: *-1d, 2018-01-01
IntervalOnly necessary for interpolated. The frequency on which the data will be interpolated.A valid Time-Interval: 10m,


Once you fill the form with what you need, you hit the OK button and you may be asked for credentials. It's important to mention that, for the time being, there's no support for any other authentication method than anonymous and basic.


2018-10-17 11_10_43-Untitled - Power BI Desktop.png


After selecting the appropriate login method, a preview of the data is shown:


2018-10-17 11_15_11-.png


If it seems all right, click Load and a new query result will be added to your data.


List vs Transposed

In order to cover the basic usage of data, I'm offering two different ways to present the query result. The first one is List, where you can get data and some important attributes that can be useful from time to time:


     2018-10-17 11_23_41-Untitled - Power BI Desktop.png


If you don't want the metadata and are only interested in the data, you may find Transposed to be a tad more useful as I already present the data transposed for you:


     2018-10-17 11_26_25-Untitled - Power BI Desktop.png


Note that for Digital data points I only bring the string value when using transposed. And that's it. Now you can use the data the way you want!


2018-06-28 10_39_53-Untitled - Power BI Desktop.png


The Code

The GitHub repository for this project is available here. If you want to extend the code I'm providing and have not worked with the M language before, I strongly suggest you watch this live-coding session. Keep in mind that it's a functional language that excels in ETL scenarios, but is not yet fully mature, so expect some quirkiness like the mandatory variable declaration for each instruction, lack of flow control and no support for unit testing.


Final Considerations

I hope this tool can make it easier for you to quickly load PI System data into your Power BI dashboard for small projects and use cases. Keep in mind that this extension is not designed for production environments nor it's a full-featured ETL tool. If you need something more powerful and able to handle huge amounts of data, please consider using the PI Integrator for Business Analytics. If you have questions, suggestions or a bug report, please leave a comment.