I have been using Power BI BW connector to connect to composite provider and BEx query for a while now. I have run into issues where there is a memory overflow in SAP BW or time out error quiet often. This is typically due to the volume of data that needs to be retrieved. Here is my strategy to overcome this issue.
Let’s assume the BEx query takes start and end date as parameters. We want to bring 3 months of data into Power BI (approximately 3 million rows). So we set the start and end date to bring in all the data. However we run into memory overflow issues.
To overcome this, we can bring in small chunks of data recursively. E.g. we can bring in a weeks worth of data at a time. How do we do this….
First connect to the the BEx query like you would. Make this query a function with start and end date as parameters.
Now create another query that has two columns – start date and end date. Each row in this query has start and end date for a week. Using custom function invoke the function you created and pass the start and end date.
Power BI now will retrieve one week of data with each call and this is single threaded. So with each call the volume of data retrieved is smaller subset and the memory issue you used to run into earlier is resolved.