cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Solution Sage
Solution Sage

Re: Suggestion on doing ETL jobs in CDS

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!

Highlighted
Helper II
Helper II

Re: Suggestion on doing ETL jobs in CDS

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

Highlighted
Solution Sage
Solution Sage

Re: Suggestion on doing ETL jobs in CDS

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.

Highlighted
Helper II
Helper II

Re: Suggestion on doing ETL jobs in CDS

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

Highlighted
Solution Sage
Solution Sage

Re: Suggestion on doing ETL jobs in CDS

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!

Highlighted
Solution Sage
Solution Sage

Re: Suggestion on doing ETL jobs in CDS

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!

Highlighted
Helper II
Helper II

Re: Suggestion on doing ETL jobs in CDS

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 

 

Highlighted
Community Support
Community Support

Re: Suggestion on doing ETL jobs in CDS

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.
Highlighted
Helper II
Helper II

Re: Suggestion on doing ETL jobs in CDS

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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Users online (8,424)