Microsoft Power BI is a set of business analytics tools that connects hundreds of data sources and delivers powerful insights. By doing so, allows creating beautiful reports in a matter of seconds and publishing them online.
EPM Pulse for Project Online users have a possibility to build reports in Power BI using the data from Project Online. For this purpose, EPM Pulse OData API may be used as a source of Project Online data.
I. Entities
EPM Pulse OData API allows to get Project Online data for the following entities:
- Projects
- Resources
- Tasks
- Issues
- Risks
- Deliverables
- SharePoint List Items (SPListItems): includes data about items from all SharePoint lists configured for data synchronization. (for more details on SharePoint Lists synchronization please refer to this article - SharePoint Lists Synchronization)
II. Entity History
Besides providing the current data for the above listed entities, EPM Pulse OData API also allows getting historical data for each of these entities. EntityHistory is based on snapshots of data captured on a daily basis by EPM Pulse and can be used for audit and analysis:
- ProjectsHistory
- ResourcesHistory
- TasksHistory
- IssuesHistory
- RisksHistory
- DeliverablesHistory
- SharePoint Lists Items History (SPListItemsHistory): includes historical data about items from all synchronized SharePoint lists (for more details on SharePoint Lists synchronization please refer to this article - SharePoint Lists Synchronization)
III. Authentication
Authentication is required in order to access EPM Pulse OData API.
Please note: In order to access EPM Pulse OData API Admin role permission in EPM Pulse is required.
For third-party applications like Power BI or Excel we support Basic (login + password) authentication type, i.e. you will need to enter username and password that you use to sign in to EPM Pulse.
In case, you do not have a basic account, please register one, by sending an individual invitation as described in this article - Manage Users via individual invitations
⚠️ If either of integrated authorization modes - Office 365, Google or Microsoft - are used when you signing in to EPM Pulse, it would not be possible to establish connection between Power BI and EPM Pulse OData, since for such authentications user's login and password are not used explicitly. They use access tokens instead.
IV. Use EPM Pulse OData API in MS Power BI
The steps below should be taken to load Entity or EntityHistory data using OData Feed in Power BI:
Step 1 - Generate OData URL
By the steps below we will generate a URL by which Power BI will connect to EPM Pulse OData:
The URL should look as follows -
https://{location-us_or_eu_or_ca}-online.epmpulse.com/@{tenant_url}/_odata/{Connection_Name}1. Navigate to your EPM Pulse and click 'Settings' >> Data Management
2.1. In the opened page copy the URL of your EPM Pulse and paste it to any text editor (e.g. Notepad)
It should look as follows:
https://{location-us_or_eu_or_ca}-online.epmpulse.com/@{tenant_url}Example: https://us-online.epmpulse.com/@demotenant
2.2 Copy the name of your data connection from this page and paste it to the same text editor.
By default it is set to “Project Online”, but can be updated to another name if you click 'Manage' button.
Substitute any spaces in the Connection Name with %20.
Example - Project%20Online
3. Now generate the URL as shown in section 1 using copied elements. It will look as follows:
https://{location-us_or_eu_or_ca}-online.epmpulse.com/@{tenant_url}/_odata/{Connection_Name}Example: https://us-online.epmpulse.com/@demotenant/_odata/Project%20Online
Step 2 - Load data from OData to PowerBI
1. Run 'Power BI Desktop'.
2. Select Get Data option and choose OData Feed from the drop-down with data sources:
3.1. In the opened pop-out window leave the radio-button for Basic option.
3.2. Paste the URL generated in Step 1 into URL field.
Then click OK
Please note: Using the link, generated as described above, you will have possibility to load the data for all Entities. If you would like to load the data for some specific entity (for example, only Projects or only Tasks), the link must be updated as follows:
https://{location-us_or_eu_or_ca}-online.epmpulse.com/@{tenant url}/_odata/{Connection Name}/{Entity Name}For Example -
https://us-online.epmpulse.com/@demotenant/_odata/Project%20Online/Projects
⚠️ Such link may be used to load data for one specific Entity (for example, only Projects or only Tasks). It is not possible to generate the link to load Projects+Tasks+Resources, due to Power BI limitation.
4. The authentication window will appear. Select a Basic option and provide the User Name and Password you use to log in to EPM Pulse.
Then click Connect button.
5. The Navigator window will appear. Here you can choose the entities for which you would like to load the data by checking their checkboxes.
As the Entities are selected click the Load button:
6. It will take some time for Power BI Desktop to load the data for selected Entities:
7. After loading finishes, you can start using various tools provided by Power BI to work with this data, e.g. create different reports and visualizations.
