Connect PI with Power BI
Hello, I have been trying to connect PI with Power BI but I have not been able to establish a connection. Can you guide me on how to do this?
I have read the article: "Get Project Insight Data into Power BI using REST" but in the step where I need to enter the "api-token" I dont find this option in the system.
Let me know if you have been able to do this connection for one specific Project.
Thanks!
-
Official comment
Hi Alejandra!
Connecting PI® to PowerBI is a very powerful analysis and visualization practice and I highly recommend this where PI does not provide what you need out of the box. FYI, you can do this exact same set of steps in Excel too, if desired. Here's how you do it:
Step 1: Connect Power BI to PI®
Open Power BI Desktop application (If you don't have the desktop version, download for free here). Once open, click the Get Data button on the Ribbon. When the dialog opens, type web, select it from the list, and click the Connect button.
Step 2: Configure the PI Connection
Next, paste the API end-point you wish to connect to ("active user list" shown). Include any parameters if you are using search or model properties on the query string. You will also need to place the header api-token (all lowercase) with a corresponding key (that you generate from your webapp). If you do not have a key, please contact your system administrator.
Step 3: Convert JSON Records to Data Table
Once the connection is made, you will receive a list of Record items, each representing the contents between each set of curly braces in the JSON result. Make sure the column is selected and click To Table.
Step 4: Remove Extra Record Information
When the To Table dialog appears, select Truncate exta columns from the "How to handle extra columns" drop-down.
Step 5: Load Desired Columns
Select the desired columns from the subsequent modal. You may need to click the Load more link to see all the columns returned from the query. Once all the columns are chosen, click OK.
Step 6: Transform Data
Once the data returns to Power BI as a data table, you can further make adjustments to your dataset so that future imports take on all the rules and policies you place around clean data for your reports. To modify values in a column, select the column head. From there, you can manage any transformations from the available choices in the Ribbon or right-clicking the column head, including: adjusting types, merging records, default sorting, trimming rows, replacing values, renaming columns, and more. As you make changes, you will see transformational steps on the right. (If you make a mistake, simply click the "x" next to the step, or jump to that part of the instruction set to see how the data forms.) When the data is formed the way you'd like it, click Close & Apply. You can go back and change your data transformation rules at any time by clicking Edit Queries.
Step 7: Select Your Data
Once the visual editor returns, you can select which fields you'd like to visualize by choosing your visualization item (table, pie chart, bar chart, etc) and then selecting the values on the right.
Hope that helps!
Kevin -
Thanks for your comment Kevin!! I have one issue with this procedure. I cannot go through step 2 since I cannot select the option "api-token". I have all the URL and the REST API but I cannot put it in the connection parameters:
Do you know if it is a problema with the versión or do I have to install something else?
Let me know your comments.
Regards,
AL
0 -
Hi Alejandra,
Instead of selecting from the drop-down you can type in "api-token" in the HTTP request header parameters and then copy and paste your api token in the next box. Let me know if that works for you.
0 -
Just type it in--per Alex above. Make sure it's all lowercase:
api-token
😎
Have a great day!
0
Please sign in to leave a comment.
Comments
4 comments