OData support for extended reporting

ALM Octane supports OData (Open Data Protocol), the OASIS REST-based standard for accessing data. You can use OData to extend and improve reporting capabilities, above and beyond the functionality provided by ALM Octane dashboard widgets. This topic provides instructions for working with OData and ALM Octane.

Overview

This overview describes the use cases for using the OData standard to access ALM Octane.

You can retrieve ALM Octane data over OData with reporting and business intelligence tools.

You can use these reporting and business intelligence tools that support OData (such as Power BI) to generate reports and charts, instead of relying only on ALM Octane dashboard widgets.

Connect to ALM Octane's OData support from the application's interface.

Supported OData versions

Any tool that supports OData version is likely to work well with ALM Octane.

ALM Octane OData support has been tested with Power BI and Excel.

Caution: Tableau’s support for OData is limited. For example, Tableau's OData support currently does not include the $expand interactive query argument, which is used extensively for cross entity reporting. Consider using a different reporting or business intelligence tool.

For OData version support, download and install the Microsoft Power Query for Excel add-in.

For version details, see the Support Matrix.

Prerequisites

Ensure the following prerequisites are met, before you begin to work with OData:

Request that the site admin or the space admin activate basic authentication with the SUPPORTS_BASIC_AUTHENTICATION configuration parameter for each space. For details, see SUPPORTED_CI_PLUGIN_SDK_VERSION.
When ALM Octane is configured with SSO, configure OData integration using the API access keys. For details, see API access.
Ensure that Access OData permission is granted to the appropriate role. By default, permission is enabled, but if you have created custom roles, make sure this option is enabled. For details see General System Actions in Roles and permissions.

The ALM Octane server base URI

The ALM Octane server base URI for accessing ALM Octane data using OData is:

<https://server>/odata/v4/shared_spaces/<space_ID>/workspaces/<workspace_ID>/

Troubleshooting: If ALM Octane does not respond successfully to an OData consumer request, it might be because the base URI used to refer to ALM Octane is different than expected. Consider modifying ODATA_USE_SERVER_BASE_URL and SERVER_BASE_URL as described under Configuration parameters.

Server base URI for accessing data from multiple workspaces

You can access ALM Octane's data from multiple workspaces that exist within a shared space by modifying the base URI.

The URI should include:

ID type	Details
Workspace ID	A workspace whose ID is listed after /workspaces/ is considered the main workspace, and serves to provide metadata for the report, such as field names.
Cross-workspace ID	Workspaces whose IDs are listed after /cross-workspace/ are the source of data, and serve to provide the field values for the report.

Using the modified URI, you can:

Include data from all the workspaces within a shared space.
Include data from specific workspaces within a shared space.

Note: You can not include the data from workspaces that exist within an isolated space.

Use case	Details
Including data from all the workspaces	To include the data from all the workspaces that exist within a shared space, enter an asterisk () after /cross_workspace/. For example: <https://server>/odata/v4/shared_spaces/<1001>/workspaces/<1002>/cross_workspace/
Including data from specific workspaces	When including the data from specific workspaces, consider the following: The base URI should list all the workspaces' IDs whose data you need to include in the report. Separate each workspace ID with a comma. For example: <https://server>/odata/v4/shared_spaces/<1001>/workspaces/<1002>/cross_workspace/<1002>,<1003>. Only the data from workspaces whose IDs are mentioned after /cross-workspace/ is included in the report. It means that the main workspace's ID defined after /workspaces/ should also be defined after /cross_workspace/, otherwise the workspace data is ignored. Note: Private fields defined in specific workspaces, are not included in cross-workspace reports.

Use case

Details

Including data from all the workspaces

To include the data from all the workspaces that exist within a shared space, enter an asterisk (*) after /cross_workspace/.

For example: <https://server>/odata/v4/shared_spaces/<1001>/workspaces/<1002>/cross_workspace/*

Including data from specific workspaces

When including the data from specific workspaces, consider the following:

The base URI should list all the workspaces' IDs whose data you need to include in the report. Separate each workspace ID with a comma. For example: <https://server>/odata/v4/shared_spaces/<1001>/workspaces/<1002>/cross_workspace/<1002>,<1003>.
Only the data from workspaces whose IDs are mentioned after /cross-workspace/ is included in the report. It means that the main workspace's ID defined after /workspaces/ should also be defined after /cross_workspace/, otherwise the workspace data is ignored.

Note: Private fields defined in specific workspaces, are not included in cross-workspace reports.

Authenticating

To work with OData, review the prerequisites for basic authentication under Prerequisites, and then authenticate with basic authentication.

Caution: Activating basic authentication enables external systems to access ALM Octane using this authentication method, not just OData.

You can authenticate with either:

Your user name and password.
An API access key.

For details, see Basic authentication.

Tip: When working with basic authentication, on each successful authentication, ALM Octane includes the LWSSO_COOKIE_KEY cookie in the response. We recommend that you send the LWSSO_COOKIE_KEY cookie with each subsequent OData request for enhanced performance. For details, see the information about the LWSSO_COOKIE_KEY under Authentication.

Access ALM Octane data from a reporting or BI tool

Any reporting or business intelligence tool with OData support can integrate with ALM Octane, including Excel 2010 and later, Power BI, Power Query for Excel, and Power Pivot for Excel.

For a list of the tools that support OData, go to http://www.odata.org/ecosystem/ and select Consumers.

To troubleshooting issues with Microsoft applications, see Troubleshooting.

Templates

For an Excel template that helps you create a feature quality status report using OData, see https://marketplace.microfocus.com/appdelivery/content/odata-octane-excel-feature-quality-status-report.
For a Power BI template that you can deploy and import, see https://marketplace.microfocus.com/appdelivery/content/power-bi-desktop-template-for-octane-odata.

Scenario: Traceability report

This example demonstrates how to use Power BI to access ALM Octane data using OData. It shows, step by step, how to create a traceability report.

In Power BI, set up the data feed for ALM Octane with OData. For details, see Example: Access ALM Octane using Power BI.

Set up a basic feed using the ALM Octane URI.

Enter the user name and password for basic authentication and click Connect.
Now we have connected and can see all ALM Octane entities. Let's start creating our report.

To see only stories, click on stories and then Edit.

We now select the columns we want to see. Click Choose Columns.

For our report, let's select the ID, name, release, and tests columns.

Releases and tests are references to other entities. We can get more information about releases and tests by clicking on the right button for each column to expand them.

Click the button for the release. We would like the name of the release, so we select name.

Similarly, click the button for tests, and then select the name and ID attributes.

Let's filter for items in release 2.2.

Click Close & Apply. Here is the result so far.

Now create the graph with data we select.

From the right pane:

Select the values.
Select matrix visualizations.
Arrange the rows, columns and values.

We can also add the pie chart visualization.

Save and refresh the data.

Troubleshooting

When trying to access ALM Octane data from a Microsoft application, a message similar to "The server has closed the remote connection" displays, due to a Microsoft environment issue.

Problem:

When using OData with a secure ALM Octane server and Microsoft Excel (or other Microsoft applications, such as PowerBI), this error appears. This is because, by default, the Microsoft application does not support TLS1.2, which ALM Octane expects.

Workaround:

Modify your registry (using regedit.ext).

Set the SchUseStrongCrypto DWORD value in the following two registry keys to 1:

"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319"
"HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319"

If these keys do not exist, create them.

Restart the Microsoft application.