OData Setup (Legacy)

Last updated
Save as PDF

To get the most value out of the data that your organization generates and consumes inside of PPM Pro every day, PPM Pro supports an “Open Data Protocol” or “OData” feed. With robust coverage of database-level fields, this mechanism will facilitate reporting and analytics workflows across any data warehouse, data lake, or business intelligence ecosystem that can consume OData feeds. The PPM Pro feed currently supports OData version 4, to include (but not limited to) support of Power BI, Tableau, Microsoft Excel, and others. NOTE: If you are using Tableau 10.0.1 and above, you will follow a slightly different set of instructions to retrieve your data via a Web Data Connector.

Below are the PPM Pro OData feed setup steps. These steps are in two parts: 1) identifying the data fields to access, and 2) accessing the data. For more information, see OData Webinar Recording.

NOTE

This uses legacy dashboards only. To connect OData feed with the new reporting and dashboards, See 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.

Create one or more List 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 a Target 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.
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 Dashboard 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.
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. Ensure the "Expires on" date is set to accommodate your OData access timeframe, for example, a maximum of 1 year out.
3. If one or more of the reports on your Dashboard is so large that it will not display/only the download to Excel option link is shown, make sure the "Allow viewers to export dashboard data to Excel" checkbox is selected.
4. 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.
5. Configure a publishing schedule to run sufficiently for accessing your data. The OData feed will pull the last published dashboard.
Capture the Dashboard ID as it will be needed for the OData feed URL.
1. Select the Help menu > Developer Tools > ID Inspector.
2. Select the Dashboard row in your Dashboards grid.
3. The ID should appear within the ID Inspector, note it down as this will be needed for Accessing Data (steps below).

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('dashboardID@yourSubdomain')

where "dashboardID" is the Dashboard 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.

OAuth token modal.png

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 Dashboard ID from step 4 above.
- 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.

BI/Reporting Tool	Problem Encountered	Things to Try
Any tool	Need to bring in current data, not just last published data.	Within PPM Pro: Click on the Publish Options icon, . Within the resulting "Update Options for Published Dashboard" dialog, change one of your parameters, e.g., change the "Expires on" date, or, deselect then reselect the "Allow viewers to export dashboard data to Excel" checkbox. Click Publish. Note that publishing actions do not change the Dashboard ID, it will always be the same (so the URL will not change just because a dashboard is republished). The 3 steps above are much faster than selecting to Unpublish and then repeating the Publish steps. Thanks to one of our customer Community posters for this tip, you know who you are! If you have a strong need to republish dashboards outside of the regularly scheduled publish time or find yourself constantly republishing, please contact Support and ask about alternative methods.
Any tool	Your original attempt to access OData did not succeed and/or you changed your dashboard, login credentials, or some other access information and the data is not updated.	Clear your credentials in your BI/Reporting tool: In PowerBI, select top left menu option "Options and settings", then "Data source settings", and in resulting modal select "Clear Permissions"/"Clear Permissions" (can clear for "Data sources in current file" and "Global permissions" as appropriate). In PowerBI, select File > Options and Settings > Options > Data Load > Clear Cache. In Excel (Power Query), select Data > Get Data > Data source settings, and in resulting modal select "Clear Permissions"/"Clear Permissions" (can clear for "Data sources in current file" and "Global permissions" as appropriate). In Excel 2016 (non-Power Query), select "New Query" menu option "Data source settings", and in resulting modal select "Clear Permissions"/"Clear Permissions" (can clear for "Data sources in current file" and "Global permissions" as appropriate).
Any tool	500 error:	This may be due to a number of reasons. Double-check to ensure all of the reports on the dashboard are List reports; Crosstab and other report output types do not work with the OData feed. Double-check to ensure your Report field titles do NOT contain special characters such as "!", "%", ":", etc. or HTML. If one or more do, edit the report and ensure a "Display Name" is used for each Selected Field that does not have these characters (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). Then edit your Dashboard so that the Report will be refreshed and republish the Dashboard. Double-check to ensure your Report field titles do NOT have "Entity" as their titles. If one or more do, edit the report and provide a "Display Name" that has more than just "Entity" (e.g., change to "Entity Type"). Then edit your Dashboard so that the Report will be refreshed and republish the Dashboard. Double-check to ensure there is not more than one report of the same category on your dashboard. If there are, remove reports of the same category until only one is left and republish your 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 Dashboard ID as needed. Double-check to ensure the dashboard is actually published. Double-check that the Dashboard is published by the Owner; publishing a linked dashboard will cause problems with the OData access. Double-check to ensure the published Dashboard ID in the URL is correct. For Excel 2016, instead of going through the "Get External Data" function, use "New Query" function, From Other Sources/From OData Feed. If you are using a version before Excel 2016, add the PowerQuery module which as of March 1, 2018, can be downloaded from: https://www.microsoft.com/en-us/down....aspx?id=39379 If none of the above is the case, there may be a conflict with the Dashboard. Copy the Dashboard, Publish the copy, and try to access again with the new Dashboard ID from the Copy.
Any tool	401 error:	This error usually occurs when the credentials do not authenticate due to incorrect Username/Password or attempting OData access from a restricted IP address. Double-check that your login credentials are correct, that you have entered your personal PPM Pro Username for the login user and your personal OAuth Token for the login Password (not your own user password). If your company has the IP Restriction feature turned on (if you see an "IP Restriction" checkbox on the PPM Pro dashboard Publish Options modal), check to see or ask your PPM Pro Administrator to see if your IP address is included in the allowed range of IP addresses.
Any tool	404 error:	This error usually occurs when the attempt to reach a URL fails and may be due to a couple of reasons. Double-check that your subdomain is correct. The subdomain is the first part of the URL you use when logging in to PPM Pro. For example, if you log in to https://myCompany.innotas.com, then yourSubdomain is "myCompany". Double-check to ensure "InnotasODataService/Entities" in the URL, or for Tableau, "InnotasOdataService/WebConnector/InnotasWebConnector.html", have correct capitalization. Double-check to ensure nothing is missing from the URL and nothing is misspelled. If you copied and pasted your URL into the BI/Reporting tool field, type over and replace the pasted apostrophes ' ' (in case by pasting it changed the apostrophes in some way). Double-check to ensure your Report field titles do NOT contain HTML. If one or more do, edit the report and ensure a "Display Name" is used for each Selected Field that does not have HTML (the presence of the HTML can cause the OData access to fail). Then edit your Dashboard so that the Report will be refreshed and republish the Dashboard.
Any tool	Need to bring in data from two reports that are of the same category.	Add each report to a separate dashboard (which still can include other reports of different categories). You can bring in additional dashboards with your OData feed by substituting the Dashboard ID within the URL.
Excel (Power Query), Power BI	Not all of the expected data columns are appearing.	When entering the OData feed information, select "Advanced" and then the "Include open type columns" checkbox. Use the Basic tab and when entering the login credentials, select the level with the full URL on "Select which level to apply these settings to".
Excel	After the data table appears, unable to or not sure how to expand all of the individual data elements.	In Workbook Queries, click (or right click) on the query and select to "Edit". Click on the "Table" link in the Fields column for the report entity you want to expand. Click on the right hand button in the "More Columns" column title (this column appears after clicking on the "Table" link, the button looks like two arrows). Ensure that all of the columns you want to bring in have their checkboxes selected and click OK. NOTE that if a field label had a special character, its corresponding column likely is not selected. Click "Close and Load" to exit editing. If this is a large amount of data, it may take a bit of time.
Excel, possibly other tools	Dates, money, and other special formatted data comes in as string data.	OData sends data in string format. Many tools, such as PowerBI, will attempt to automatically detect the appropriate format and convert accordingly. Other tools, such as Excel, may not detect the data format or do so correctly (PowerQuery for Excel might do a better job of doing this). You will need to format this data manually in the BI/Reporting tool, e.g., in Excel and PowerBI, there is a Transform/Detect Data Type option and also a Transform/Data Type selection option to change a column of data to a particular format.
Power BI, possibly other tools	A report field that is multi-select displays the data in a single cell separated by commas. Need to separate out the individual multi-select values.	Transform the data when it gets into Power BI. There is a function that can split the column into multiple rows using the ‘*Split Column’* function. This Power BI Community article on it may be helpful. The example uses a semicolon, but the same would apply for a comma in our example. Other reporting/BI tools may have similar functions. https://community.powerbi.com/t5/Desktop/How-to-split-the-the-Column-into-Multiple-rows/m-p/255014#M113552 For example, one customer has one or more sponsors for a Request. If multiple sponsors are selected in a multi-select list they are separated by a comma. The customer created a new copy of the query that contains the data in Power BI and deleted all the columns except the Request ID and the Sponsors. They then selected the Sponsors column and Split. In Advanced, they selected By Rows instead of Columns. Now, instead of one row with the ID as “123” and the Sponsor as “<sponsor 1>, <sponsor 2>” it has two rows with both having the same ID but each row now only has one sponsor. They then created relationships in the Query Editor between the queries – the original Sponsor List, Sponsors, and Requests.
Excel, possibly other tools	Columns do not expand, just spins	Try tweaking the URL to include a capital "D" in InnotasODataService, for example, "https://odata.ppmpro.com/InnotasODa...vice/Entities(‘9999999999@domain’)". Bizarre, we know, but one of the participants found that this solved the column expansion and access.
Tableau	Tableau does not recognize the version of the web data connector API error:	Check your version of Tableau and ensure it is 10.0.1 or higher. Their version 10.0.0 had a bug for their web data connector capabilities that was patched in 10.0.1.
SQL (and possibly MySQL)	Unable to expand/open more columns after generating a table.	When entering https://odata...../Entities(dashboard@subdomain), the OData service returns a list of keys that specify all of the available types like project… and on the next column, there is the link of a table that you can expand. Power BI handles this and generates the next OData call for each, which is as follows. https://odata...../Entities(key)/Fields which returns the data expanded that you are familiar with. Creating this URL may help. Another URL to try is https://odata.innotas.com/InnotasOdataService/Fields('dashboardID@domain').