How to overcome SAP BW memory overflow issue

No comments

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.

BWRecursive1

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.

BWRecursive2

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.

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s