Power BI Report with SSAS Connection Fails to Refresh (Timeout Issue)

Hi Xarris23 

 The following is AI generated and unverified, but I hope it helps.

Root Cause Summary

Your refresh error (DM_GWPipeline_Gateway_MashupDataAccessError) with the message "not enough memory to complete this operation" is likely due to Power BI generating a large mashup query when all SSAS tables are imported together in one step. This can cause:

• Excessive cross joins during query folding.
• Memory exhaustion on the gateway or SSAS server.
• Timeouts, even if the dataset is relatively small.

This behavior has been observed by other users as well, especially when complex transformations or calculated columns are involved. [community....rosoft.com]

🧠 Why It Happens

When you import all tables at once from SSAS:

• Power BI may attempt to optimize the query by combining multiple table requests into a single mashup.
• This can result in inefficient query plans, especially if relationships are complex or involve calculated columns.
• The mashup engine (Power Query) can consume significant memory, especially during scheduled refreshes via the gateway.

This is not officially documented by Microsoft, but several experts and community threads confirm this behavior. [purplefrog...ystems.com]

✅ Recommended Workarounds

Since your workaround (importing tables individually) works well, here are some best practices and options to consider:

1. Split the Import Queries

• Import each table separately in Power BI.
• Recreate relationships manually in the Power BI model.
• Avoid referencing other queries in transformations (which can trigger mashup folding).

2. Use CommandTimeout in M Code

You can explicitly set a timeout in the AnalysisServices.Database() function like this:

AnalysisServices.Database("your_server", "your_model", [CommandTimeout=#duration(0,0,10,0)])

This sets a 10-minute timeout and can help avoid premature cancellations. [darren.gosbell.com]

3. Optimize Gateway and Server Resources

• Ensure the on-premises gateway is up-to-date and has sufficient memory.
• Monitor SSAS server memory usage during refresh.
• Consider dedicated gateway VM if shared with other services. [community....rosoft.com]

4. Incremental Refresh (if applicable)

If your data changes incrementally, consider using incremental refresh to reduce load. This requires a datetime column and proper partitioning setup.

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

Hi,

Based on my previous experiences and tests, I would suggest considering followings. The importance is from most to least.

1. Do not follow the guidance of Power BI to form a big wide table. For example, if there are 1 fact table and 3 dimensional tables. If you follow the guidance of PBI and select the desired fields, the result is one big table. So keep the structure in PBI as it in SSAS.

2. Refresh model by refresh data only. If refresh schema and data at the same time, it will query the SSAS twice actually. 

3. Use self-defined query instead for each table. As shown below, choose DAX query over MDX. DAX is modern. 

4. If you would like to, use SQL Server Profiler to see what is happening in SSAS.

Best,

Dale