Copy large volume data from a Composite Provider to Azure Data Factory - delta load required, but no delta option in Open Hub, Data Transfer Process

Question

Copy large volume data from a Composite Provider to Azure Data Factory - delta load required, but no delta option in Open Hub, Data Transfer Process

Hi,

I am SAP BI Consultant at Price Waterhouse Coopers.

We are trying to load data from a Composite Provider to Azure Data Factory using Open Hub, copying over 2 million records, therefore, delta load will be necessary, but there is only a 'Full' extraction option available in the DTP, using Composite Provider as a Template.

Do you have any thoughts on how we should proceed? There is no material online that shows what we should do in our scenario, using a large Composite provider (only an Infocube as in https://learn.microsoft.com/en-us/azure/data-factory/load-sap-bw-data).

Best Regards,
Andreea Zota

MartinJaffer-MSFT 25,571 Reputation points Microsoft Employee

2020-09-28T04:54:13.81+00:00

Hello Andreea Zota and welcome to Microsoft Q&A.

While I would like to help you, I'm afraid you know more than I do about Composite Providers. I have reached out internally , but until I hear back, I am unable to say whether this is supported or even possible.

Can you tell me about the ways in which you can interact with the Composite Provider? Is it possible to maybe on the SAP side do a query or view which isolates the delta, and then have Data Factory pull the result?

Thank you for understanding,
Martin Jaffer
Andreea-Gabriela Zota 1 Reputation point

2020-09-28T08:47:20.337+00:00

Hi Martin,

First of all, thank you very much for looking into it and reaching out internally.

Unfortunatelly, we can't isolate the delta on the SAP side. We still need a way to automatically trigger the delta load, as loading daily this amount of data in full, is not an acceptable scenario.

Looking forward to new ideas from your side, on how we could achieve this.

Best Regards,
Andreea Zota
MartinJaffer-MSFT 25,571 Reputation points Microsoft Employee

2020-09-29T20:18:10.78+00:00

(Yesterday):
I have not received a reply yet, so I bumped/pinged/poked my internal thread.

Please do not raise your hopes too high. By my thinking, if SAP does not offer any delta options, then I do not think Data Factory will be able to provide one either.
That is why I was what other entry points there are for retrieving your data.

(Today):
The reply I received pointed to the same doc you did. If I understand correctly. The issue is that after creating Open Hub Destination (OHD) as Database Table and then when you attempt to create the Data Transfer Protocol (DTP), the desired Extraction Modes are unavailable. (My apologies if this seems obvious, but I do not have an environment to go look for myself.)

MartinJaffer-MSFT 25,571 Reputation points Microsoft Employee

2020-09-28T04:54:13.81+00:00

Hello Andreea Zota and welcome to Microsoft Q&A.

While I would like to help you, I'm afraid you know more than I do about Composite Providers. I have reached out internally , but until I hear back, I am unable to say whether this is supported or even possible.

Can you tell me about the ways in which you can interact with the Composite Provider? Is it possible to maybe on the SAP side do a query or view which isolates the delta, and then have Data Factory pull the result?

Thank you for understanding,
Martin Jaffer
Andreea-Gabriela Zota 1 Reputation point

2020-09-28T08:47:20.337+00:00

Hi Martin,

First of all, thank you very much for looking into it and reaching out internally.

Unfortunatelly, we can't isolate the delta on the SAP side. We still need a way to automatically trigger the delta load, as loading daily this amount of data in full, is not an acceptable scenario.

Looking forward to new ideas from your side, on how we could achieve this.

Best Regards,
Andreea Zota
MartinJaffer-MSFT 25,571 Reputation points Microsoft Employee

2020-09-29T20:18:10.78+00:00

(Yesterday):
I have not received a reply yet, so I bumped/pinged/poked my internal thread.

Please do not raise your hopes too high. By my thinking, if SAP does not offer any delta options, then I do not think Data Factory will be able to provide one either.
That is why I was what other entry points there are for retrieving your data.

(Today):
The reply I received pointed to the same doc you did. If I understand correctly. The issue is that after creating Open Hub Destination (OHD) as Database Table and then when you attempt to create the Data Transfer Protocol (DTP), the desired Extraction Modes are unavailable. (My apologies if this seems obvious, but I do not have an environment to go look for myself.)

Answer 1

MartinJaffer-MSFT 25,571 Microsoft Employee

I think I found the root cause. Delta for CompositeProviders is supported only under specific conditions. This is a restriction on the SAP side, preventing egress.

In delta mode, the DTP supports CompositeProviders (object type HCPR) as sources if they are made up entirely of InfoCubes or of DataStore objects (advanced) that are modeled like standard InfoCubes or standard DataStore objects (classic). The target of the DTP must be a DataStore object (advanced). A combination of InfoCubes and DataStore objects (advanced) as PartProviders of a CompositeProvider is not supported for DTPs in delta mode.

source

I found a blog detailing workarounds for various Composite Provider limitations. I am unsure whether item number 2 applies to this or not.

Andreea-Gabriela Zota 1 Reputation point

2020-10-01T14:58:40.997+00:00

Hi Martin,

Indeed, it doesn't seem to be possible to use the DTP in delta mode, when CompositeProvider is the source. I was hoping that we could use a different approach, to bring the delta to ADF, without using DTP, but I am not aware of such option.

But in a different scenario, when data from Calculation Views is pulled to Azure, how can we bring only the delta? As I mentioned, we extract millions of records, therefore, after a full load, we would like to only update the data.

Thank you,
Andreea

Copy large volume data from a Composite Provider to Azure Data Factory - delta load required, but no delta option in Open Hub, Data Transfer Process

1 answer

Activity