/
ODATA Connectivity for Clarity on AWS

ODATA Connectivity for Clarity on AWS

Owned by Jared Hodgson (Unlicensed)
Last updated: Sept 11, 2024 by Lalitha Kothur
10 min read

Clarity SaaS on AWS provides an enterprise-grade secure ODATA connection for 3rd party tools to query small amounts of live Clarity data. This may be used to connect tools such as Microsoft PowerBI, Tableau or Excel to the Clarity data warehouse schema. The Rego Support team does not specifically support 3rd party applications but provides technical support for the ODATA connectivity protocol. The process of querying data using ODATA requires detailed knowledge of the Clarity database schema and is not meant to replace the supported reporting tools within Clarity. Rego has a team of Technical Consultants to provide training, development and guidance for designing queries, dashboards and reports using ODATA - you may reach out to Rego to request a consulting engagement.

The ODATA protocol is intended to transmit small amounts of data, the protocol is not intended for large data dumps. For large data extracts, please use the Rego Data Extractor or other tools. The information below is provided for expert users of Clarity and ODATA. Please only submit Rego Support tickets if there is a failure of the metadata refresh or ODATA connectivity.

ODATA connectivity is influenced by networking and firewall disruption. If testing connectivity using Microsoft Excel fails to connect, try from different machines and try from different networks or disconnect from VPN.

 

COMPONENTS

Objects:

  • Rego ODATA Extension: The purpose of the Data Extract object is to hold the queries to connect to transaction database tables.

Processes:

  • Rego ODATA Refresh: This process will refresh the metadata, an alternative to the out-of-the-box job “Refresh Data Warehouse ODATA Model”.

  • Rego Validate ODATA Extension: This process will validate the customer created queries based on transaction database tables in the “Rego ODATA Extension” object. This process also refreshes the transactional metadata if the query is valid.

Groups:

  • Rego ODATA Users: Members of this group will be able to consume an ODATA feed through any OData supported client tool.

  • Rego ODATA Administrators: Members of this group will be able to consume an ODATA feed through any ODATA supported client tool. The members can also create “Rego ODATA Extension” object entries with custom queries to connect to transactional database.

 

CONFIGURE & SCHEDULE THE ODATA METADATA REFRESH PROCESS

The Rego ODATA Refresh Process will refresh the metadata required for the ODATA feeds. By default, it uses the “admin” user to perform the refresh. You can modify the default administrator user by following the below instructions.

  • In the Classic UI, navigate to the Administration Tab -> Data Administration section -> Processes

  • Search for the Process Name “Rego ODATA Refresh”

  • Click on the process and go to Start Step and Click on “script” actions

  • Go to the Custom Script Parameters tab and provide any Clarity admin user name. This user must be part of either “Rego ODATA Users” or “Rego ODATA Administrators” groups so that they have the required privileges to refresh the metadata.

  • Save the changes

  • Ensure the process is activated. If process is not active, go to the Process Validation tab and click on “Validate All and Activate” to activate the process

  • To schedule the process, navigate to Jobs in the Home tab of Classic

  • Search on Job Type as “Execute a Process” and click on the job

  • In the parameters section, select “Rego ODATA Refresh” in the Process ID list. Ensure “Allow only once running instance” is checked. Change the Job Name to “Rego ODATA Refresh” for easier identification.

  • Select the schedule in the “When” section. For eg: In the below screenshot, the job is selected to be executed every data at 23:00 hrs. Ensure that you schedule this job after the Load Datawarehouse job execution.

  • Submit the job. After the next execution, the ODATA solution will be ready for usage.

 

CONFIGURE AND ACTIVATE THE ODATA EXTENSION

The Rego ODATA Extension process that validates the underlying SQL queries uses “admin” user to perform the validation actions. To change the default admin user and/or to activate the process, follow the below instructions.

  • In the Classic UI, navigate to the Administration Tab -> Data Administration section -> Processes

  • Search for the Process Name “Rego Validate ODATA Extension”

  • Click on the process and go to the Steps tab. Click on the Step “Validate Query”

  • Click on “Validate Query” action

  • Go to the Custom Script Parameters tab and provide any Clarity admin username. This user must be part of “Rego OData Administrators” group.

  • Save the changes

  • Go to Process Validation tab and click on “Validate All and Activate” to validate and activate the process

 

ACCESSING THE ODATA ENDPOINTS

When accessing ODATA, first test Microsoft Excel using the ODATA v4 endpoint. Connecting to ODATA may require your network administrators to allow the connection.

 

ODATA URL format:

 

For example, if your organization name is “biggreenit”, the endpoints for the DEV non-production environment will be as below:

 

For example, if your organization name is “biggreenit”, the endpoints for the PRODUCTION environment will be as below. Production URLs will not have the environment name:

 

CREDENTIALS:

Rego OData allows uses both basic authentication and Clarity API Keys to authenticate users to access the OData environment. Syntax to be used is as follows:

  • Basic Authentication:

    • Username: <Clarity Username>

    • Password: <Password defined in Clarity>

  • API Keys

    • Username : <API Client ID>|<Clarity Username>

    • Password: <API Key>

Note:

  • ODATA does not use Single Sign on or Cognito password. Basic authentication or API keys are used for authentication.

  • All the rules associated with API Keys such as key expiration will be applied.

  • Existing Odata access right is still needed to access Odata.

  • Administrators will need to enable APIs in their environment and create an API Client that end-users should use to generate API keys.

  • When refreshing non-production from a production environment the API keys for the non-production environment will be overwritten with the production values as the API keys are stored in the database.

Once connected the tables will be available to retrieve data:


In the future, if you wish to reset the password for the Clarity account that authenticates the ODATA connection to Clarity, follow this guidance:

  • Whenever the ODATA service account password is expired in Clarity it must be reset from Clarity Administration (Clarity Administration-> Organization → Resources) and not from the “Forgot Password” link on the login screen of NON-SSO URLs.

  • When the password is changed in Clarity (not SSO, but on Clarity Administration-> Organization → Resources), the new password will come into effect immediately. Password change has no dependency on the Refresh ODATA job or any other sync process as authentication directly happens against the ODATA URL.

  • If Power BI is unable to connect to ODATA with the new password, it is because of caching that is done at the Power BI client side. Once the Clarity password is changed, please make sure to clear the caches in Power BI. The user needs to manually change the Clarity password for the respective ODATA client connection after each password rotation.

 

ADVANCED CREATION OF CUSTOM ODATA EXTENSION QUERIES

This advanced usage of ODATA is intended for use by senior Rego consultants. Customer usage of this feature may cause unexpected load on the core Clarity database, causing instability or performance issues. Customers are required to engage in a consulting arrangement with Rego to develop custom ODATA queries.

To create custom ODATA feeds based on the Clarity core transactional tables, follow the below instructions:

  • Login to Clarity as a user in the Rego OData Administrators group

  • In the Classic UI, navigate to the Home Tab -> Rego OData Extension List custom object, typically found under the “Custom Objects” menu option

  • Click on “Rego OData Extension”

  • Fill out the form as per the details mentioned in the table below. There is a 4,000 character limit on the SQL Query.

 

 

 

 

ODATA Query Code

Provide the unique identifier for the feed in “ODATA Query Code”. The code should start with an alphabetic letter, can contain only alphanumeric digits with no spaces or other special characters except underscore.

Short Description

Description of the feed details

SQL Query

a SELECT query to execute for the feed. If all fields of a table are required, use “SELECT * from <table_name>”

IMPORTANT NOTE: Avoid using “;” (semi colon) in the queries as this is a reserved character

Query Primary Keys Alias

Each OData feed entry must have a primary key. It can be combination of multiple keys. In such case, use a comma to separate the key aliases

Query Status

Can have a status as New, Valid, Invalid. Only Valid queries will be available for the ODATA feed

IMPORTANT NOTE: If the query takes more than 15 seconds to execute, then status is set to invalid. Such queries hit the hard threshold limits of AWS architecture and will adversely impact the main Clarity transactional database.

Error

If the query or the primary key details provided are invalid, then Query Status will be set to invalid, and the respective error reason will be displayed in this field.

Active

When inactive, the fields: short description, SQL Query, Query Primary Keys Alias will be editable and can be changed. To validate the query, Check the Active field and save the changes. This will auto trigger the process “Rego Validate OData Extension” which will perform all the validations. If validations are successful, the status is set to valid. Otherwise, the status is marked as invalid, and reasons are mentioned under the Error field

  • Activated and validated entries will be locked to avoid for further editing. To edit, user must first deactivate the entry. Then perform the required changes, activate, and save the form. This will trigger the process that will revalidate the changes. Please refresh the page to ensure “Query Status = Valid”

  • All the validated ODATA extension object entries should now be accessible in the ODATA endpoint. There is no need to refresh the data warehouse, as the queries are not using the data warehouse. There is no need to execute the job “Rego ODATA Refresh”, as the custom ODATA extension queries do not rely on metadata.

  • When accessing the custom ODATA extension queries, please note the change in the URL to add /ppmapi. Please confirm your connectivity success to the standard ODATA tables before attempting to connect to the custom ODATA extension queries.

    ADVANCED Endpoint for custom queries created in Rego OData Extension object: https://odata-<environment>.<customer>.moventus.com/odata/ppmapi

 

  • Example of the results available in a 3rd party ODATA client:

 

TROUBLESHOOTING

Connectivity:

  • When accessing ODATA, always test connectivity with Microsoft Excel to the ODATA v4 endpoint. If the connection works in Excel but not other tools, please contract the 3rd party developer for support of their tool. Rego technical troubleshooting support is provided exclusively for connections from Microsoft Excel to ODATA v4 as a baseline. Connecting to ODATA may require your network administrators to allow the connection through your VPN or firewall, contact your internal network team for connectivity support.

Caching:

  • ODATA connections are cached by Microsoft Excel and other products. When encountering authentication or other unexpected errors, clear the caches then recreate the connection. In Microsoft Excel, the cache of connections us under Data > Get Data > Data Source Settings. Delete cached connections and recreate the ODATA feed connection.

Errors:

  • “Unable to resolve the type name <TableName> to an EdmType”. This error indicates a problem connecting to the base endpoint. Always setup ODATA feed connections to connect to the base URL endpoint such as /api2, /api4, or /ppmapi. Do not configure ODATA feed URLs to connect directly to a table, such as /ppmapi/TableName. If you encounter the EdmType error, delete the cached connections in Microsoft Excel and recreate the connection using the base URL endpoint.