cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SarathMohan
Helper II
Helper II

Suggestion on doing ETL jobs in CDS

Hi Team,

 

Just want to get some suggestions on  which tools or leverage OOB tools for data movement to CDS on daily basis as scheduled tasks.

The requirement is there are multiple excel sheets  in one drive locations and they have to be imported to CDS , its not direct import of data its basically 50 k records of which there can be new records also there are scemarios which we need to compare the whole set of records in existing CDS entity to the excel data and see if the same records exists or not if not delete the record from CDS.

 

I am able to achieve this using SSIS but I am also looking for other options as well in Azure. Power Automate is not fitting into this requirement as there are many comparisons and its time consuming. Other option I have checked is using data flows but as per my knowledge its not for ETL jobs.

 

Thank you

Regards,

Sarath Mohan

9 REPLIES 9
EricRegnier
Super User
Super User

Hi @SarathMohan,

The newish out-of-the-box approach with low-code/no-code is with Dataflows. It allows you to do the whole ETL and schedule the runs. Here's the Microsoft documentation on Dataflows which would be a good start: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-and-use-dataflows

Hope this helps!

Not sure if this works as we need to load from multiple locations and CDS can be accessed only through web api and has a limit on 500 records. Tried Dataflow and it works better if we want to just pull data from source to destination . Transformation between multiple tables through power query in data flow didn't find it better for my scenario.

 

Sarath Mohan

Yeah you can't have one for all locations, but can you have a Dataflow per location? Dataflow also supports APIs, and you would be able to transform multiple tables in PowerQuery. Curious what's the specific issue in PowerQuery? Otherwise the other popular cloud low-code solution would be Logic Apps which gives you a bit more flexibility than Power Automate.

Thanks for the response, My scenario requires all the data to be pulled from CDS this is not possinle with web api request as it allows only 5000 records for each request.  Logic app also will not serve the purpose as we cannot load the data into a temporary location for faster processing, which will help only helps in iterating one by records etc. Which will be a time consuming job. 

 

Regards,

Sarath Mohan

Thanks for clarifying. Since your pulling from CDS and Logic Apps/Power Automate wouldn’t be a viable option. Non-custom options are limited, you can look at the Data Export Service (https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database) but this is more to replicating data or SSIS (with optionally Kingswaysoft).

All the best!

Thanks for clarifying. Since your pulling from CDS and Logic Apps/Power Automate wouldn’t be a viable option. Non-custom options are limited, you can look at the Data Export Service (https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database) but this is more to replicating data or SSIS (with optionally Kingswaysoft)

All the best!

Yes thats correct, I have tried to do this with SSIS package but it becomes on premise mode. Which customers don't prefer trying to find an alternative. Checked with Azure Data factory but it doesn't have similar options as we have for SSIS.

 

Regards, Sarath 

 

v-xida-msft
Community Support
Community Support

Hi @SarathMohan ,

Based on the needs that you mentioned, I agree with @EricRegnier 's thought almost. I think the Data flow in PowerApps could achieve your needs.

 

Within the Data flow, you could consider merge multiple tables into a single one table using the "Merge Query" option in Power Query Editor:

8.JPG

 

More details about the usage of Power Query in Data flow, please check the following article:

https://docs.microsoft.com/en-us/power-query/power-query-quickstart-using-power-bi

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

My scenario is slightly different how we can  extract more than 5k record from CDS through web api connector in Power Query. I need to load all the tables and do the transformation . Which means I need to load around max 50 k records from cds for processing.

basically processing is between three different sources Two excel sheets and existing CDS records and do transformations between the records one by one. Not sure how to achieve this.

 

Regards

Sarath Mohan

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,708)