Unanswered

Exporting large amounts of data

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi All,

I would like to export data from one of our marketing systems (via API) to a data source that I can then connect to from PowerBI

This will be a daily export and the amount of data can be up to 100k records.

Due to the large amount of data I prefer not to do this with an apply to each and also not sure what the best DB solution for this is.

I'm thinking could I just dump the data as a CSV file to Azure blob storage and then connect PowerBI from there? If so how does PowerBI connect to multiple CSVs? As I can see in PowerBI you must choose a specific CSV to work from when choosing Blob storage as your data source.

Bottom line, I'm trying to avoid the need to set up a dedicated DB here and webservice to manage the process.

Looking for all and any advice here

Thanks

Sam

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (0)

Brad_Groux 4,556 on at

Like (1)

Report
Copy link

Link copied!

With that amount of data I'd never recommend CSV or non-table storage. Power BI would crawl the more data you would add. With that said, Azure Cosmos DB and Azure Tables are good options for ease of use and cost effectiveness.

As far as apply to each, that's your only option - that is how records are created. See Understand data operations - Power Automate | Microsoft Docs for more details. With that said, you can do this rather quickly with Concurrency Controls and Degree of Parallelism settings.

If you could provide an expanded screenshot of your Flow and steps, your Flow run history, and of any detailed error messages you're receiving we could likely better assist you. Also, for the best results, you may want to review How to write a good forum post.

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION ☑️. If you find this reply helpful, please consider giving it a LIKE.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thanks @Brad_Groux. I've had some discussions with the business owners and it seems they want the data to be saved in an existing SQL (on-prem) DB. Is there any quicker way to push large amounts of data to SQL rather than just an apply to each?

Was this reply helpful? Yes No
Jronash 827 on at

Like (1)

Report
Copy link

Link copied!

One thing to keep in mind is that Automate has a limit of 5,000 loop iterations for an Apply to Each block. A premium account gets you a 100,000 limit.

Was this reply helpful? Yes No
Brad_Groux 4,556 on at

Like (0)

Report
Copy link

Link copied!

@Anonymous, a condition with an apply to each is how the platform works. Power Automate is a no-code/low-code solution, if you want a more programmatic approach, Azure Logic Apps may be a better fit.

I discuss the difference between the two in this blog post - Why My Future is Serverless in the Microsoft Cloud | by Brad Groux | MSFT Engineer.

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION ☑️. If you find this reply helpful, please consider giving it a LIKE.

Was this reply helpful? Yes No
takolota1 4,978 Moderator on at

Like (0)

Report
Copy link

Link copied!

If anyone is facing something similar where you are trying to avoid extra maintenance, skill requirements, etc. & you don’t expect your datasets to be more than a couple hundred thousand rows, then you can try some batch Excel options:
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Create-Update-and-Upsert-Excel/td-p/1624706

Was this reply helpful? Yes No
takolota1 4,978 Moderator on at

Like (0)

Report
Copy link

Link copied!

You could also use this batch SQL set-up to send chunks of at least several thousands of records to SQL at a time:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/SQL-Batch-Create-Read-Update-and-Delete/td-p/1715338

Was this reply helpful? Yes No