Upon request, Rego will setup an ODATA connection into your Clarity instance. This may be used to connect tools such as Microsoft PowerBI, Tableau or Excel to the Clarity data warehouse schema. PowerBI is the only certified and tested tool for this configuration of ODATA, Rego SaaS Support does not specifically support these tools, but supports the ODATA connectivity protocol. The process of querying data using ODATA requires detailed knowledge of the Clarity data warehouse dataset 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 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.
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 in a browser or Microsoft Excel. Connecting to ODATA may require your network administrators to allow the connection.
ODATA URL format:
ODATA v2: https://odata-<environment>.<customer>.moventus.com/odata/api2
ODATA v4: https://odata-<environment>.<customer>.moventus.com/odata/api4
ADVANCED Endpoint for custom queries created in Rego OData Extension object: https://odata-<environment>.<customer>.moventus.com/odata/ppmapi
For example, if your organization name is “biggreenit”, the endpoints for the DEV non-production environment will be as below:
ODATA v2: https://odata-dev.biggreenit.moventus.com/odata/api2
ODATA v4: https://odata-dev.biggreenit.moventus.com/odata/api4
ADVANCED Endpoint for custom queries created in Rego OData Extension object: https://odata-dev.biggreenit.moventus.com/odata/ppmapi
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:
ADVANCED Endpoint for custom queries created in Rego OData Extension object: https://odata.biggreenit.moventus.com/odata/ppmapi
CREDENTIALS:
ODATA does not use Single Sign On, or any other custom authentication. Basic authentication is used. The Clarity username and password will be used to connect to ODATA.
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 out of the PPM/Transaction Databases 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
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>” |
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.
All the validated ODATA extension object entries should now be accessible in the ODATA endpoint.
