OData API query limitation due to missing table foreign keys. Is it possible to add foreign keys on some DWH tables?

Products

• Clarity PPM SaaS

Issue/Introduction

Users are using OData API to query data from DWH.  However, some of the queries do not work because the table joins return an error. 

It is assumed that there are missing foreign keys in the tables to make the join (using the $expand syntax) work.  Is it possible to add more foreign keys on some tables in the DWH schema in SaaS?

https://<reporting_host>.ondemand.ca.com/api/odata4/cppm1234ndatasourceV4/DWH_INV_INVESTMENT?$expand=DWH_INV_OBS_MAPPING

 

the error:

"error": {

        "code": null,

        "message": "The property 'DWH_INV_OBS_MAPPING', used in a query expression, is not defined in type 'dwh_inv_investment'."

    }

 

Environment

Release : 16.1.0

Resolution

It is not possible to add more foreign keys beyond what is present in the Data Warehouse out of the box.

By design, not all tables in the Data Warehouse have foreign keys.