Power automate Limitations on CDATA Cloud

Live Connectivity CData Connect Cloud
, , ,
1

I am currently working on a Power Automate flow that fetches transaction line data from Exact Online using the CDATA Cloud connector. The goal is to group the data based on GLAccountCode while retrieving the minimum (LeastFinancialYear) and maximum (HighestFinancialYear) financial years. Below is the SQL query being executed:

SELECT GLAccountCode, MIN(FinancialYear) AS LeastFinancialYear,  
MAX(FinancialYear) AS HighestFinancialYear  
FROM [ExactOnline1].[3243703].[TransactionLines]  
GROUP BY GLAccountCode;

Since this query processes a large dataset, it takes a considerable amount of time to execute. However, Power Automate has a 2-minute timeout limitation for SQL queries, causing the flow to fail before retrieving the results.

I would appreciate any guidance on optimizing the query or any best practices to handle large dataset processing efficiently within Power Automate. Additionally, if there are alternative approaches—such as running the query in batches, using stored procedures, or leveraging data pre-aggregation techniques—please advise on how best to implement them.

Looking forward to your insights.

2

Hi Muthu,

When dealing with large tables of data, and especially when your data/BI tool has limitations like the one you described, we’d recommend making use of Connect Cloud’s data caching feature. When configured, this feature will pull the data in intervals into a self-hosted PostgreSQL database, which is then accessed instead of via the Exact Online API for queries.

This will help with avoiding both the limitations of the Exact Online API in addition to the timeout associated with Power Automate.

You can read more about the data caching feature in the Connect Cloud documentation here:

If your Connect Cloud account doesn’t already have this feature, you can contact our sales team at sales@cdata.com to discuss adding this functionality.