900k rows/records - not able to download in excel output

[goms12]

Hi

Power BI report builder output has 900k rows of output in which not able to download into excel. we are using databricks as datasource and report is running fine and giving output within 15 mins having 10k pages. but when we click on export to excel its taking more time and finally timeout error or session expired error. is there any alternate way to overcome this issue?

Appericiate your help on this

Thanks

[v-jianpeng-msft]

Hi, @goms12 

I wish you all the best. Previously we have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

Jianpeng Li

[v-jianpeng-msft]

Thank you lbendlin 

Hi, @goms12

You're connecting to databricks in Power BI Report Builder and you're experiencing timeout issues while exporting.
Report builder doesn't store data and sends queries to databricks. During the presentation phase, you spent 15min. When exporting to Excel, it usually takes longer: Report builder sends the query time, Report builder writes to excel time. During this process, Databricksk may time out due to reasons such as query pending.

If you're using the Power Query connector, the following diagram looks like:

This connector results in longer waits. The main reason is when the Power Query Online service is called.
You can try using ODBC or JDBC to connect to Databricks to avoid timeouts caused by waiting for other middleware.

Solved: Connect Report Builder to Databricks (direct; not ... - Microsoft Fabric Community

If you are using the ODBC driver to connect to Databricks, then you can try adjusting STATEMENT_TIMEOUT settings, the following article will help you:

Solved: Re: SQL Warehouse Timeout / Prevent Long Running Q... - Databricks Community - 71636

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[goms12]

we tried with local installation of SIMBA SPARK ODBC driver. but when we publish it to service which is not working and its looking for gateway setup which we don't want to do since both are in cloud databricks and powerbi why do we need to setup gateway?

[v-jianpeng-msft]

Hi, @goms12 

I wish you all the best. Previously we have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

[v-jianpeng-msft]

Hi, @goms12 

Thank you for your previous reply. You mentioned using Cloud Databricks and Power BI Service. We recommend that you try publishing the report to Power BI Service and then export to Excel. This may help you better manage and analyze your data.

Follow these steps:

1.Create and save your report in Power BI Report Builder.

2.Publish the report to Power BI Service.
3.Open the report in Power BI Service and select the Export option to export the report as an Excel file.

Calling Power Query Online in Power BI Service is much faster than calling in the local report builder, just to rule out the impact of Power Query Online on-premises.

If you encounter any problems during the operation, please feel free to contact us. We will be happy to support you further.

Best Regards

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Do you need to use paginated reports to output to Excel?  Might be easier to connect Excel directly to the semantic model.

[goms12]

if then how the user will use the paramters selection?