OData Setup (R2D2)

Identifying Data Fields

The OData feed uses the PPM Pro List report and dashboard functionality as a way to identify what fields to access with OData. Therefore, start by creating one or more List reports that contain the data you would like to access, add these reports to a dashboard, and publish it.

1. Create one or more List Basic reports where the Selected Fields correspond to the data you want to access. NOTE: OData brings all field information across as plain text. Format information such as alignment, bold, HTML, etc., is lost. Many BI/Reporting tools will attempt to detect the format of date and money fields, but you may need to adjust these and other field formats as desired once the data is in your tool.
   1. Use the scope of Organization to give you the widest data access. With wider data access, you can use your BI or Reporting tool to do the filtering for you, but note that it does increase access and refresh time over lower level targets.
   2. Ensure that any entities that you report on do not use special characters, such as "#", "%", " ' ", ":", etc., or HTML in their titles. For example, if you want to create a report based on "Issues/Risks", you will need to enter a Support case to change it to remove the "/". Some Reporting/BI tools such as PowerBI that uses SSAS do not allow entity titles to have special characters.
   3. Only add fields that you want to access. Avoid adding fields that you are never going to look at to promote faster refresh and data access times.
   4. Ensure none of the fields have special characters or any punctuation, such as "#", "%", " ' ", ":", etc., or HTML in their title. Some Reporting/BI tools such as PowerBI that uses SSAS do not allow field titles to have special characters. If you have any Available Field titles that include special characters, please either change their titles within Admin to avoid potential errors or edit the Report field to have a Display Name without special characters and edit/republish the Dashboard, particularly if you are using PowerBI; use only alpha and numeric characters in their titles.
   5. Ensure none of the fields just have the word "Entity" as their title. When we first deployed the OData service we used this term to identify the entity for fields and unintentionally caused problems for fields that have this title. We will change this with our next deployment, but in the mean time if you have any report fields that just show as "Entity", please either change their titles within Admin to avoid potential errors or edit the Report field to have a Display Name with more than just "Entity" (such as changing to "Entity Type") and edit/republish the Dashboard.
   6. Do NOT select any Available Fields from "Children" folder. We cannot send hierarchical data, only a flat table, so "Children" fields may cause errors. To access "Children" data, create a separate List report where the desired fields are not "Children" and make sure the relationship is mapped correctly to the parent entity within your Reporting/BI tool.
   7. DO enter a Display Name for each field, ensuring that it does not use any special characters, such as "#", "%", " ' ", ":", etc. If you do not enter a Display Name, the report column title will use both the Object title and the Field title separated by a colon in the column title, e.g., "Project: Title". The presence of the colon can cause the OData access to fail.
   8. Do NOT configure any other Selected Fields parameters (Max Width, Horizontal Alignment, Vertical Alignment, Aggregate, Drilldown, Drilldown Target, Display Data As, Export Data/Color, Hide), Sorting, or List Options. Doing so could negatively affect the generated table relationships. The BI/Reporting tool should allow you to configure these as you like once you have the data.
   9. Do NOT use Group By, as some tools will introduce blank rows or may not be able to handle.
   10. There is no need to set Visibility, as the OData feed does not use this information.
2. Create a top level Dashboard containing the List report(s); do not create entity level dashboards.
   1. Add the List reports containing the data you want to access. There is no need to worry about how the reports are arranged, as the OData feed does not use this information.
   2. Do NOT add more than one report of the same category to the dashboard. If you need to bring in another report of the same category, create another dashboard and add it there. You can bring in additional dashboards with your OData feed by substituting the Publishing ID as needed.
   3. Configure Target, Filter or other scope parameters on the Component tab as needed. Certain report sources, such as Timesheet, require this. We recommend using Filters and other scope parameters to define/limit what data is accessed; if there is data that will never be used, then filter it out to promote faster refresh and access times.
   4. There is no need to set Display Name, Size, or Visibility, as the OData feed does not use this information.
   5. NOTE: It is recommended that the dashboards created for OData access only be used for OData access; do not access OData for dashboards that are viewed and used by other users or as part of your regular business needs.
3. Run and publish the Dashboard.
   1. Ensure that the Dashboard is published by the Owner; publishing a linked dashboard will cause problems with the OData access.
   2. Although the OData feed does not use the User authentication information, we recommend that you set up standard authentication (for example, "Require user to be logged in to PPM Pro") to protect from unauthorized access to your dashboard.
   3. Configure a publishing schedule to run sufficiently for accessing your data. The OData feed will pull the last published dashboard.
4. Capture the Publishing ID as it will be needed for the OData feed URL. Publishing ID is the last string of the URL of Published Dashboard.
   1. Select the Dashboard > Right Click > Manage Publishing
   2. Select the last part of the string of the URL after /. See highlighted sample Publishing ID: https://test.ppmpro.com/dashboard/vbxHHffSSrjvAKXeeshS 
   3. For users coming from Legacy Reports, Publishing ID replaces the Dashboard ID while keeping all other processes same.

Accessing Data: All tools EXCEPT Tableau

To access data from BI/Reporting tools other than Tableau, you will need to do both of the following:

• Enter the URL for your OData feed within your BI/Reporting tool. To do this, find the function/location and enter the following OData feed URL (note that this URL is case sensitive):

   https://odata.ppmpro.com/InnotasOdataService/Entities('publishingID@yourSubdomain') 

where "publishingID" is the Publishing ID from step 4 above, and, "yourSubdomain" is the first part of the URL you use when logging in to PPM Pro. For example, if you log in to https://myCompany.ppmpro.com, then yourSubdomain is "myCompany". It is case sensitive, so make sure you have the correct upper and lower case characters.

• Find the login function/location in your BI/Reporting tool and enter the relevant credentials:
  • Use your personal PPM Pro Username for the login user and your personal OAuth Token for the login Password (not your own user password). To generate your personal OAuth Token, within PPM Pro in the upper right user dropdown menu select “Edit Preferences”. In the resulting modal (shown below), select the OAuth Token tab. Click the “Generate Token” button and copy the token that appears (in the example below the OAuth Token is “fb8c6547-063e-479d-bec5-36acf3a62885”). Use this token, including hyphens, as the password.

Accessing Data: Tableau 10.0.1 +

To access data from Tableau 10.0.1 and higher versions, you will need to do all of the following:

• Access the Web Data Connector. Go to the Tableau "Connect" view and select To a Server "Web Data Connector". In the resulting Web Data Connector "Enter your web data connector URL here", enter this URL (note that this URL is case sensitive) and hit Return/Enter:

  https://odata.ppmpro.com/InnotasOdataService/WebConnector/InnotasWebConnector.html

• You will see a "PPM Pro Data Connector" dialog where you need to enter identifying information for your data and login credentials.
  • Enter the Publishing ID from step 4 above in the Dashboard ID field.
  • Enter your Subdomain, which is the first part of the URL you use when logging in to PPM Pro. For example, if you log in to https://myCompany.ppmpro.com, then yourSubdomain is "myCompany". It is case sensitive, so make sure you have the correct upper and lower case characters.
  • Enter a Username:
    • Enter your personal PPM Pro Username for the login user (note that the username is case sensitive).
  • Enter a Password:
    • Enter your personal OAuth Token for the login password (not your own user password). To generate your personal OAuth Token, within PPM Pro in the upper right user dropdown menu select “Edit Preferences”. In the resulting modal (shown below), select the OAuth Token tab. Click the “Generate Token” button and copy the token that appears (in the example below the OAuth Token is “fb8c6547-063e-479d-bec5-36acf3a62885”). Use this token, including hyphens, as the password.

• Click "Connect".

After performing the above, Tableau should show relevant Tables on the left side which you can drag and drop to the top middle section and then click "Update Now" to pull in the data. All data comes in as string data type, so some work may be needed to change the formats so that you can display and aggregate as you like when building Tableau reports.

Trouble Shooting & Tips

Completing all of the above relevant setup steps should enable you to use your BI/Reporting tool to access the PPM Pro OData feed! PPM Pro does not have in-house expertise in all the various BI/Reporting tools that accept OData, however if you experience difficulties making the connection or if the data isn't showing up, please submit a PPM Pro Support case. Below are some trouble shooting and tips that we have gathered.