Refresh all queries AfO performance

[former_member235200]

Hi,

If I have a workbook with more than one query, if I refresh them all, it feels like it is refreshing query by query but a little bit faster. For example, if one query takes me 5 seconds to refresh and the other also 5 seconds, when I refresh them all it will take me around 8 (instead of 10).

Even though it is faster, if I have more queries in a workbook, then this faster refresh is not enough and I''m really struggling with some good reports that we've built.

My question to you is if you know a way that the AfO would refresh all queries at the same time? Meaning that I would expect the refresh to take around the same time as the longest query I have in the workbook would take (eg. I have 5 queries in my file and the longest one takes me 20 seconds to refresh, then I would expect the "refresh all" to take around 20-25 sec.). Is there an option where I could improve the performance of my reports?

Thank you.

Rodrigo Mourinho

PS: I'm using AfO since 3 years now, currently with version 2.4.3. I've created a lot of reports and also read about how to create and use vba in the files, but if you have any tip to improve performance, I would be very happy to test it 🙂

[TammyPowlas]

Hi Rodrigo - any chance you could try upgrading to 2.6/2.7 with the same queries/workbook? On every release, SAP explains they try to improve performance.

[former_member235200]

Hi Tammy. thank you for your reply.

I've just installed 2.7 and start testing, but I'm struggling now with the fact that my formulas are not working with this new version. For example, if in my excel I had my Period linked to a cell and the cell is a number, now it doesn't work. I need to change to text in order for it to work again (which would be crazy if I would have to re-do all my files again).

Are you aware of this topic?

[TammyPowlas]

Rodrigo, I am not; perhaps the safest version for you is 2.6 with the latest SP; 2.7 is very new

[former_member235200]

Hi Tammy,

I've done a quick check with version 2.6. The good news are that my formulas work, but the timings are very very similar to version 2.4. Maybe the optimization comes from when we start a report from scratch (e.g. for version 2.7 they released an optimized formula query).

Regarding the question of this post, do you know if there's a way to refresh all queries at the same time. Does it even make sense?

Thank you very much for your support! I've been working on a lot of reports in the past years, but the performance is never great. I need to improve it somehow 😕

[gregor_dieckmann]

Hi,

I think the key word missing in this discussion is 'parallelization'. If you have more than one query in AO and refresh all of them then AO reads the result set of the queries again but not in parallel but sequentially. So e.g. having two queries Q1, Q2 with refresh time R1, R2, then R for both in one workbook might be only a little less than R1+R2 because some fix costs are not needed.

Regards,

Gregor

[former_member235200]

Hi Gregor,

Exactly, that's the topic.

Are you aware of this option for Analysis for Office? I've seen the RFC bundling, but honestly it doesn't change any timers.

Thank you.

Rodrigo Mourinho

[torsten_wirth]

I think with BW4 HANA there is a feature for parallel refresh. This is also possible with a direct HANA Connection. With BW 7.x this is not possible.

Regards

Torsten

[former_member151004]

Hi Rodrigo,

We have some best practices to analyse the performance on the following link: https://wiki.scn.sap.com/wiki/x/-wJQGg
I also will suggest you to record a profiling and check if the request and the result set are simultaneous.

Thanks,
Diego Ferrary