High Ram and CPU Consumption with small data

[Syndicate_Admin]

Hi Everybody,

I'm struggling with modelling my Excel file together with Powerquery. I need two "final" tables, one ist SalesData (Sales) the other one ist Amount of Customers in a shop (People). I have about 6 different sources with sales (SQL, API, CSV etc.) and 6 sources with poeple counting systems (different systems). The rows for sales data are about 1000 rows for 3 years (day aggregate) and the same for people counting. So the results returned are very few (in my opinion ;-)). It depends on the customers system which source I will take.

My first concept:

One Excel-sheet for each source of sales and one Excel-sheet for people and I finshes the job with formulas in Excel to merge the table finally. Some VBA Code decide which table has to refreshed. Everything gows fine, CPU usage was for 20 sec, RAM usage grows when refreshing, but stops by about 500-600 MB totally (Excel and Mashup.Container)

My second concept

After getting more different sources and after learning more about PQ I decided to build tables with "connection only" and "choose" the final tables via Powerquery. Becouse adding more tables let the workbook growing without need... So I did the following:

tableSales1 with steps

tableSales2 with steps

....

tableSales6 with steps

tableFinalSales is coded like  if SalesSystems 1 then tableSales 1 else if SalesSystem2 then tableSales ... etc.

same with tablePeople1 ... 6 and tableFinalPeople loaded to Excel.

The Final-Tables are connected/loaded to Excel.

With this concept the Refreshing time was not so much longer, but after refreshing the CPU Usage ist still very high for a long time (> 5 minutes) and the RAM is growing mostly over 3 GB.... which is to much for this small model... and some customers PC 😞

I did a lot of trials to reduce this, for example delet all formatting and tables in Excel, but it doesn't help... It seams even if there is a else if all tables are "touched and calculated" and it needs a lot of RAM. Finally I found a way which does use less memory and CPU

Every source I did a nested let in. For example for tableSales1:

let

source = SalesSystem1 (is a variable from Excel)

Decide = if Source = "Source1" then

let

step1

....

stepFinal

in

stepfinal

else DummySource (table with 0 rows)

in

source

With this concept I brought CPU and RAM usage down, but still much more then in Concept 1. And the maintanance is not comfortable becouse in the editor you can see only 1 step (the outer let in expression).

My question: Any other ideas for concept? Similar experiences that Usage of RAM and CPU ist growing with more connections, even they are not obviously used? I tested the final table with Result = Expression.Evaluat("tableSales1", #shared) but it's getting worse.... than the if else else if expression.

By the way - The steps in the different sources are easy, no merging/joining, simple DataTypes, Choose Columns etc., rename headers...

Any ideas what I can try out? Any suggestion is highly appreciated.

Thanks as lot

Steve

[Syndicate_Admin]

Hi, @gehe_bbe 

Here are some suggestions to address the performance and memory usage issues:

1. Consolidate data sources into a single source if possible.
2. Streamline and optimize Power Query steps for better performance.
3. Implement incremental refresh for partial data updates.
4. Ensure query folding is taking place where applicable.
5. Split complex queries into smaller, more manageable ones.
6. Monitor resource usage during data refresh.
7. Consider using Power BI Desktop for larger datasets and complex calculations.

If my assistance helps you in any way, appreciate the kudos. 👍

[Syndicate_Admin]

Hi rubayatyasim,

thank for the suggestion, but keep in mind:

- No change of the steps between Concept 1 and Concept 2 (but change in RAM/CPU usage)

- Very small data, so there ist no need vor incremental refresh

- PowerBI Desktop is no option, it's an Excel-Tool.

Regards Steve

[Syndicate_Admin]

You can check for these options

1. Optimize Power Query steps by removing redundancies and using efficient techniques.
2. Manage query dependencies to minimize redundant calculations.
3. Disable background data refresh in Power Query Options.
4. Consider splitting queries or workbooks for complex scenarios.
5. Monitor and optimize data types for improved performance.
6. Ensure sufficient system resources and close unnecessary applications.

if my assistance helps you in any way, appreciate the kudos. If solves your problem, accept the solution as the answer. 

Thanks

[Syndicate_Admin]

Adding some strategic buffer steps might help. Check out @ImkeF's speed/performance suggestions here:
https://www.thebiccountant.com/speedperformance-aspects/

It's hard to make specific suggestions without seeing the full query.