Unable to load historical data in PowerBi from SQL Server

[pranjaljain99]

I am trying to create a PowerBI dashboard (75 columns and about 25 M rows) coming from more than 25 tables in a SQL Server using a view. Once I load that data into a pbix file, I can configure incremental refresh which refreshes it for last 3 months.

The error which I receive at the end of the entire data load which happens in about 30 minutes:

Failed to save modifications to the server.
Error returned: 'OLE DB or ODBC error: [DataSource.Error]
An error happened while reading data from the provider: 'Invalid operation. The connection is closed.'

I've also tried to break the view into 3 separate views (each fetching about 9M records) which works fine but now when I try to create a new table and do a union of these 3 datasets, it throws an error

There is not enough memory to complete the operation in PowerBI

Resources: Power Bi Desktop | Azure SQL server serverless Gen5

Does anyone have some workaround for this?

[Pragati11]

Hi @pranjaljain99 ,

1. What version of Power BI Desktop are you using?
2. Have you tried clearing Power BI cache and then trying?

Thanks,

Pragati

[pranjaljain99]

Hi Pragati, Thanks for your response. 

I am using the latest version. No further updates are available. Version: 2.95.804.0 64-bit (July 2021)

Yes, I've cleared the cache and tried it too. 

[v-kkf-msft]

Hi @pranjaljain99 ,

I see that you have posted on another forum: https://stackoverflow.com/questions/68591108/unable-to-load-historical-data-in-powerbi-from-sql-serv... 

Has your problem been solved? If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[pranjaljain99]

Dear Winniz,

Thanks for your response. Yes, I am trying all those steps one by one. No success yet. Will keep you posted once I am done with all of them. Thanks

-Pranjal