Recommended approach for combining data from multiple odata api's and exposing it as a single entity

[geert-janklaps]

Hi,

For one of our customers (who's migrating to S/4HANA Cloud), I'm looking into migrating one of their existing on-premise tools to the cloud as a side-by-side extension. The tool combines: material master data (general, plant, sales org, accounting data), purchasing info record data, vendor master data, sales pricing conditions (+/- 8-9 different conditions).

In their on-premise system, I built the tool using ABAP CDS which allows them to select about 80000 records in a matter of seconds and display them on the screen for mass editing / exporting to excel. (each field in the output is filterable from the selection screen in this case, since it's a single CDS entity that's being queried)

For the cloud development I'm looking at CAP for sure as a backend service for the future Fiori app. Given the amount of data and the number of API calls I would need to put together the resulting entity (and the fact that almost every field needs to be filterable), I'm looking for some recommendations.

Some potential solutions I see:

1. Implement the CAP service and do all the required odata api calls (I don't think this will result in a performant solution and filtering would imply that for each call all data needs to be fetched before it can be filtered => not an option in my opinion)
2. Model as much as possible data into a custom CDS on S/4HANA Cloud (pricing data is not available using CDS's) and combine it with the remaining data using API calls (same issue with filtering, performance won't be great as well => not really an option, but better than option 1)
3. Build some replication solution to HANA in Cloud Platform and model the data in HANA views and expose them through CAP. (filtering issue solved, performance should be great => but additional work to build the replication solution)

Any advice on how to proceed with this solution? Are there SAP-provided real-time replication solutions available between S/4HANA Cloud and SAP HANA Cloud for this purpose? (I did find some information about HANA Smart Data Integration, but it's not really clear to me if this is an option S/4HANA Cloud and HANA Cloud and if it's possible to replicate e.g. custom views)

Best regards,

Geert-Jan Klaps

[david_kunz2]

Hi,

Regarding point 1):

Can't you pass the filters to the respective OData API call (per $filter option)? Then the filtering would happen in the source system and only the needed data would be transferred to your server.

Best regards,
David

[geert-janklaps]

Hi david.kunz2,

That's an option as well, although that would be quite a hassle I guess. Since you'd have to prioritize odata calls based on the entered filter parameters. If you'd do a fixed sequence for executing the odata calls this would potentially also result in quite a performance issue, e.g:

The user only enters a vendor number for filtering the result set and the fixed sequence of the odata calls is for example: material master, info record, vendor master data. That would result in fetching all the materials from the material master first and removing irrelevant materials from that result after e.g. the info record data is fetched (and filtered by the entered vendor number). (this example can be partially be solved by solution 2 of course)

I think it will even get more complex if price ranges are entered for one of the conditions that's shown on the screen, since price conditions are only exposed through API and require the right fields from e.g. the material master data to fetch the right prices based on the access sequence.

Best regards,

Geert-Jan

[david_kunz2]

Hi 8d8214c7f9734f45be69f95cc0d5aeee ,

Yes 'joins across services' should not be done, as it will result in bad performance. Maybe there is a way to expose a CDS view which does the joins already?

[geert-janklaps]

Hi david.kunz2,

Exactly what I was thinking. I think in this case 70-80% can be modelled in a custom CDS on S/4HANA Cloud, only problem will be the pricing conditions. For this one I'll for sure need some cross service joins, since this data is not available through a S/4HANA Cloud CDS but only through an odata API.

To somewhat overcome the filtering issue, I could disable filtering fields of the entity which are not available in the main odata service (based on the custom cds modelled on S/4HANA Cloud). At least that solution comes close to the functionality available on the on-premise version.

To get the full functionality available the only way would be some kind of replication to the HANA DB on Cloud Platform and model the data from there.

Best regards,

Geert-Jan

[gregorw]

Hi Geert-Jan,

right now I don't think there is any access possible to the HANA database below the S/4HANA Cloud which would allow some HANA based replication. But creating such a replication service in CAP is quite straigt forward. I've built a prototype that consists of two CAP services. One is the the replication controller the other is the target. The controller kicks off the initial replication by sending a message to the target. There the replication process starts requesting the data in a configurable packagesize via the OData Service. After the initial replication updates are triggered by events. There is one for the Conditions: SalesPricingConditionRecordEvents.

Hope that helps.

CU
Gregor

[geert-janklaps]

Hi Gregor,

Thanks for the input this valuable input, the solution you're proposing was kind of what I had in mind. Although the controller part is certainly a good addition to keep in mind for initial replication.

There does seem to be a way to replicate data from S/4HANA Cloud to a HANA DB on SCP (https://blogs.sap.com/2017/12/08/replicate-abap-cds-views-from-sap-s4hana-cloud-to-sap-cloud-platform/) using a SAP standard solution. (I'm going to dig into this one a little deeper, just to see what the options are here, since e.g. pricing conditions are not available through your proposed solution and the standard solution might be what's needed)

I think in general the only way to get everything up and running in a performant way the data needed for this solution would need to be present in the HANA DB.

Best regards,

Geert-Jan