# Power automate Limitations on CDATA Cloud

**URL:** <https://community.cdata.com/t/power-automate-limitations-on-cdata-cloud/1052>\
**Category:** CData Connect Cloud\
**Tags:** sql, exact-online, question, performance\
**Created:** [March 13, 2025, 1:22pm UTC](https://community.cdata.com/t/power-automate-limitations-on-cdata-cloud/1052 "2025-03-13T13:22:52Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![muthuEOL](https://avatars.discourse-cdn.com/v4/letter/m/cc9497/32.png) [@muthuEOL](https://community.cdata.com/u/muthuEOL)\
**Post date:** [March 13, 2025, 1:22pm UTC](https://community.cdata.com/t/power-automate-limitations-on-cdata-cloud/1052/1 "2025-03-13T13:22:53Z")

</div>

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, &nbsp;  
MAX(FinancialYear) AS HighestFinancialYear &nbsp;  
FROM [ExactOnline1].[3243703].[TransactionLines] &nbsp;  
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.

---

<div class="post-metadata">

**Author:** ![AlexS](https://avatars.discourse-cdn.com/v4/letter/a/2acd7d/32.png) [@AlexS](https://community.cdata.com/u/AlexS)\
**Post date:** [March 14, 2025, 8:18pm UTC](https://community.cdata.com/t/power-automate-limitations-on-cdata-cloud/1052/2 "2025-03-14T20:18:09Z")

</div>

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:

> **[Jobs - CData](https://docs.cloud.cdata.com/en/Jobs)**
>
> The Jobs page enables you to manage and run various Connect AI jobs. From this page, you can manage either Caching or Scheduled Queries.

> **[Caching - CData](https://docs.cloud.cdata.com/en/Caching)**
>
> Caching allows you to fetch cached data from a database instead of retrieving live data. This can improve performance of data connections, since some drivers can be slow retrieving live data.

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