OData support for extended reporting

This section describes how to work with OData and ALM.

Overview

ALM supports OData (Open Data Protocol), the OASIS REST-based standard for consuming ALM REST APIs. You can use business intelligence (BI) tools that support OData to connect to ALM data and generate charts and reports. This enables you to visualize and analyze ALM data using extended reporting capabilities, beyond the functionality provided by ALM reports and graphs.

Here is the workflow of working with OData and ALM.

  1. Prepare for the OData connection, as specified in Prerequisites.

  2. Retrieve ALM data over OData using BI tools that support OData (such as Power BI and Tableau).

    For details, see Connect to ALM data from a BI tool.

  3. Process retrieved ALM data before analysis.

  4. Use the BI tool to generate reports and charts for the ALM data.

Back to top

Supported OData versions

ALM supports OData version 4.0.

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

Note: For a list of tools that support OData, go to OData Ecosystem and select Consumers.

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

Back to top

Supported ALM entity types

After connecting to an ALM project from a BI tool, you can retrieve the following project data:

  • Releases

  • Cycles

  • Requirements

  • Test folders

  • Tests

  • Test design steps

  • Test set folders

  • Test sets

  • Test instances

  • Test runs

  • Users

  • Defects

Back to top

Prerequisites

Before working with OData, gather the following connection information:

Prerequisite Details
Enable OData support

To enable the OData support, set the IS_ODATA_SERVICE_ENABLED parameter to Y.

For details, see ALM Site Parameters.

ALM's OData data source URL

ALM data can be accessed through a unique OData data source (or OData feed), allowing users to seamlessly connect to ALM data through different BI tools.

  • ALM's OData data sources follow the URL format of:
    https://<ALM_server>/qcbin/odatasvc/domains/<domain_name>/
    projects/<project_name>
    .

    For example, you use the following data source URL connect to connect to the DEFAULT/Demo project data.

    http://<your_ALM_server>/qcbin/odatasvc/domains/DEFAULT/projects/Demo

  • To include data from multiple projects, enter the data source URLs for these projects one by one.

Sign-in credentials

You use API keys to connect to an ALM OData data source. Complete the following for your ALM account:

  • Generate an API key for your ALM account. Note down the client ID and API key secret.

  • Enable OData for the API key.

For details, see Set up API key access.

Enable full name support

Required only if you want to retrieve users' full names.

By default, only usernames are retrieved for users. If you also want to obtain the full names, set the IS_ODATA_SHOW_FULL_NAME site parameter to Y.

Note: Consider your organization's policy before you enable the full name support.

Back to top

Authenticate

Authentication is required the first time you connect to an ALM OData data source from your BI tool.

Use the basic authentication option in your BI tool.

You authenticate with the client ID and API key secret associated with your ALM account.

Field Description
User name

Enter the Client ID.

Password

Enter the API key secret.

Select which level to apply these settings to

Varies on BI tools.

Specify the level where the username and password can be shared for authentication:

  • http://<your_ALM_server>/qcbin/odatasvc/domains/. You can use the username and password to connect to any projects of any domains in the specified server.

  • http://<your_ALM_server>/qcbin/odatasvc/domains/<domain_name>.You can use the username and password to connect to any projects under the specified domain.

Note: If your client ID or API key secret is no longer valid, the authentication fail. Edit or clear permissions to authenticate with your new credentials.

Back to top

Connect to ALM data from a BI tool

You can use any business intelligence tool that supports OData to connect to ALM data, such as Power BI, Tableau, and Excel.

Example: Connect to ALM data from Power BI

  1. Open Power BI. Select Home > Get Data > OData feed.

  2. In the OData feed dialog box, select Basic, enter your ALM OData data source URL, and click OK.

  3. Select Basic, and enter your sign-in credentials.

Example: Connect to ALM data from Tableau

  1. Open Tableau. From the side menu > Connect > To a Server, select More and then OData.

  2. In the Server field, enter your ALM OData data source URL.

  3. In the Authentication field, select Username and Password.

  4. Enter your sign-in credentials.

Example: Connect to ALM data using Excel

  1. Open an Excel worksheet. Select Data > Get Data > From Other Sources > From OData Feed.

  2. In the OData feed dialog box, select Basic, enter your ALM OData data source URL, and click OK.

  3. Select Basic, and enter your sign-in credentials.

Back to top

Process retrieved ALM data before analysis

This section lists some recommendations about processing ALM data before you work on them using your BI tool.

Filter data

If you want to work on narrowed-down ALM data, you can filter retrieved data by table columns of the following types:

  • ID. For example Defect ID.

  • Time. For example Actual Fix Time.

  • Date. For example Closing Date.

Expand data

When connecting to an ALM's OData data source with multiple tables, you can use the expand option on a table column to add additional columns from the related table. This helps you build queries across tables.

Note: The expand option is recommended for one-to-one entity relationships only.

For example, the Defects table contains references to several related tables, with the Releases table among them. You can use the expand option on the target_rel column of the Defects table to add more columns related to releases, for example, the name column. After the target_rel column is expanded, one more column named with target_rel.name is added in the Defects table.

Here is another example about expanding the detected_by_user column of the Defects table. Select full_name, and then the detected_by_user.full_name column is added.

Note: Whether you can add users' full names depends on whether the support for retrieving full names is enabled. For details, see Enable full name support.

Build entity relationships

Create relationships between tables to accurately calculate results and display the correct information in your analysis. This is especially necessary when you are dealing with ALM one-to-many, many-to-many, or multiple-level entity relationships.

Below is a relationship for test set folders, test sets, test instances, and tests.

Back to top