Pushing data from SQL to Data Hub via ADF

aj84
aj84
in Integrations and extensibility

Hi all,

I am currently creating an environment in ADF to push data from SQL to an Anaplan module which in our current toolset (Informatica) works perfectly via the Anaplan API connector but not so much via ADF having had to do the work manually given there is no connector.

Has anyone here done this and if so, do you have any hints/tricks/advice?

In short, I am using a lookup to query the table and create a JSON output which then pushes it to a web service containing the relevant auth headers and URL (in this case, the module) and this works fine with a little data.

The dataset in the first instance contains around 27000 rows and about 28 columns wide so the setup is to essentially break it down into chunks (because of ADF lookup and Anaplan restrictions):

  1. Get distinct list of cities and use ForEach container to push to Pipeline 2
  2. Pass this onto pipeline 2 to use and extract distinct list of departments using ForEach container to push to Pipeline 3
  3. Pass this onto pipeline 3 to use and extract distinct list of sales and push into Anaplan again via a ForEach container.

So as an example:
pipeline 1: London> pipeline2: departments for London > pipeline 3: Sales for departments passed from pipeline2
Once complete, repeat but this time for Manchester and so on.

The problem here is performance - because it is sending over JSON code to Anaplan 28 cols wide for each sale for each dept, for each city, it is taking an age even for around 500 rows of data let alone anything as much as 27k.

Questions:
1. Is there a better way of using the API to send data across to the data hub via ADF?


2. Appreciate its a lot of data and I am cutting it down as much as possible but there could be times where we have to do a full load so it needs to handle large amounts.

3. Note, there is limitations I am facing on the ADF side with lookups only handling 5000 rows or in my case, even when cut to say department level, I exceed the size of the lookup so the JSON is never formed.

4. Is there a better more fit for purpose toolset to do this?

Thanks in advance!

Answers

Categories