OData in Portfolio

Also see OData Endpoints.

The Portfolio OData API is a standard-based REST API that allows users to get data and results of their portfolio from Excel, any OData compatible client programs, or their own program. It is possible to connect these applications directly to the OData URL, and the data accessible to users is subject to Portfolio security.

This documentation assumes that API users are familiar with accessing OData APIs, and familiar with all the concepts and features Portfolio provides. OData Basic Tutorial would be the best place for beginners to learn.

https://www.odata.org/getting-started/basic-tutorial/

For reference, OData (Open Data Protocol) is a standard protocol that defines a set of best practices for building and consuming RESTful API’s. Web clients can publish and edit resources, identified using URLs and defined in a data model, using simple HTTP messages.

From User Options in Portfolio, OData user connections can be maintained for data exports to database.

Managing an OData Link in Portfolio

OData access is managed from the OData menu on the Portfolio Home Page.

OData Password

The OData User Account is global for all Portfolios and a password will be created for the user currently logged into the application. By default, the password will be created with a lifespan of 7 days; acceptable values are from 1 through 365 days. Specify a Lifespan and use Create OData Password to configure the account.

The configuration allows users to generate a password, which can be copied and used in external OData compatible applications to complete OData connections.

Click image to expand or minimize.

User: The user currently logged into Portfolio. This is for information only and cannot be edited.

OData URL: The URL for the OData database is configured by a Portfolio Administrator. The URL is displayed but cannot be edited. Use Copy if the URL will be required input in an external application. The URL Copy is available at any time.

OData username: OData username required on log-in in external applications.

OData Password: The OData Password is generated automatically and masked from the user. To use the password in external tools, the user must Copy the password and save it if required.

Password Copy is only available one time – when the password is generated.

Expires On: Based on the Lifespan, the date the account will expire. Prior to the Expiry date, the password is indicated as Active.

Created On: The data the account was created

Test OData Password: Test that the OData user is valid.

  1. Copy the OData password
  2. Paste it into the Test Login text box.
  3. Use Test to confirm the account is valid.

A successful Test results in a green check on the Test button.

Copy is only available when the password is created. When the user leaves the dialogue it is no longer available. If the password will be required in external applications, save it to an accessible file.

Click image to expand or minimize.

When the user leaves the OData User Account page the masked password is no longer available for Copy. In the case where a password is needed and was not initially recorded, the user will have to use Create New for a new password so that it can be copied. This process generates a new password, so existing connections will need to be refreshed.

Click image to expand or minimize.

OData Exports

Saved passwords can be validated using Test to make sure the correct string has been saved. If an invalid password is tested the result is a red indicator on the Test button.

OData Exports

Users can access OData Export details in (Portfolio > User Options> OData Exports).

OData Exports will be listed in a grid, and can be locked or deleted.

Exports are visible from the User Options page, but Portfolio Administrators have higher rights than non-Administrators:

  • Admins are able to see exports for all users; non-Admins can only see their own.
  • Admins are able to delete exports by all users; Non-Admins can only delete their own exports.
  • Admins have the ability to lock any selected exports; non-Admins are not be able to delete exports locked by an Admin.
  • Locked exports are not deleted.

Calc Version is included in the grid for cross-reference in external OData linked tools. The export ID is listed by Portfolio:Scenario.

Obsolete? is True if an export is updated by a manual selection or new optimization details.

Scenario Contributions

The OData database is updated automatically with Portfolio data. However, to download Scenario Contributions Results through OData, users must export Opportunity Contributions to the OData database.

  1. Select a Scenario.
  2. Access the Scenario Selection actions on the Scenario Selections tab.
  3. Select Export Opportunity Contributions.
  4. Select Database as the Export Destination.

Click image to expand or minimize.

This exports the contributions data to the OData database where it is accessible from other applications via an OData connection.

  • The database is the one specified under OData URL in Account Settings.
  • Information about the export is available in Notifications.

Connecting via Excel

While this might not be a long-term solution, value can be achieved through initially accessing data via Excel to allow for interrogation and identification of data detail within an OData implementation.

To connect via Excel

  1. Within the external application (Excel), click Data > Get Data > From Other Sources > From OData Feed.

    2. Click image to expand or minimize.

  2. Paste in the OData URL, available to Copy from the OData User Account under Portfolio > Account Settings described above.
  3. Use Basic authentication.
  4. The Portfolio user currently logged into Portfolio is the OData user. This should not be edited. Paste in the OData Password, available to Copy from the OData User Account under Portfolio > Account Settings described above. Select Connect.

    5. Click image to expand or minimize.

  5. Click image to expand or minimize.
  6. Select which data to download. The application will connect to Portfolio and display the specified data. Select Transform Data to modify the data returned by the OData query. This opens the Power Query Editor.

    7. Click image to expand or minimize.

The query can be modified in the Power Query Editor.

Connecting via PowerBI Desktop

  1. Within the external application (PowerBI Desktop), click Home tab > Get Data > OData Feed

    2. Click image to expand or minimize.

  2. Using Basic authentication, paste in the OData URL, available to Copy from the OData User Account under Portfolio > Account Settings described above.

    3. Click image to expand or minimize.

  3. Use Basic authentication.
  4. The Portfolio user currently logged into Portfolio is the OData user. This should not be edited. Paste in the OData Password, available to Copy from the OData User Account under Portfolio > Account Settings described above. Select Connect.

    5. Click image to expand or minimize.

  5. Select which data to download. The application will connect to Portfolio and display the specified data. Select Edit to modify the data returned by the OData query. This opens the Query Editor.

    6. Click image to expand or minimize.

The query can be modified in the Power Query Editor.

Click image to expand or minimize.

Connecting via Spotfire

  1. Within the external application (TIBCO Spotfire), click Connect to.
  2. Select OData from the data source list
  3. Select New Connection to connect to the OData url.
  4. Enter information per Managing an OData Link in Portfolio.
  5. Click Connect.

The tables available for use in Spotfire are those prefixed with All…

Import data and configure visualizations per Spotfire documentation.