CF Multi-tenant apps - Can we create and link multiple HDIs upon subscription to a tenant

Dear Experts,

We are in the process of migrating our Cloud Apps hosted in NEO to CF and consider the below scenario.

App AA has its’ own database. Common data such as Master data and Order data gets replicated to a common database named CORE. CORE DB is used by other Cloud Apps as well.

The App AA DB and CORE DB are linked using Synonyms and a granter service. All the database related joint queries works as expected in standard mode in CF (non-multitenant). We use separate MTAs to develop and deploy App AA and CORE.

Now, we want to make this a multi-tenant aware app. So, upon subscription we want the Service Manager to create HDI for each tenant. Also want to link the AA tenant HDI to CORE tenant HDI take place as depicted below.

App-router-> Service -> HDI_AA_tenant1 -> HDI_CORE_tenant1

-> HDI_AA_tenant2 -> HDI_CORE_tenant2

-> HDI_AA_tenant(n) -> HDI_CORE_tenant(n)

We tried this scenario following the YouTube SAP HANA Academy video series in https://www.youtube.com/playlist?list=PLkzo92owKnVx3Sh0nemX8GoSNzJGfsWJM.

We use the SAP Cloud Application Model (CAP) to develop the above multi-tenant application. But we could not succeed so for.

We would like to hear from experts, whether this has been tried and tested as a scenario in SAP BTP multi-tenant application development?

Whether this is a possible scenario to achieve in SAP CF multi-tenant implementation or any suggestion on how to achieve our goal.

Thanks and regards,

Manoj