cancel
Showing results for 
Search instead for 
Did you mean: 

Performance on Analysis for Microsoft Office

Former Member
0 Kudos

Hi,

I'm working on Analysis for Microsoft Office, Version 1.4.0.2528.

I'm getting data from a DSO in BW. When reading that and inserting the results into the Analysis, it takes a couple of long minutes, while running the same query in BW, with transaction RSRT, it is giving me the results immediately.

Any tips on what to look at to improve the Analysis performance?

Thanks,

Miriam

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Miriam,

I would firstly recommend you have applied all the backend notes. You can find more on this here.

http://scn.sap.com/thread/3374277

You should follow standard best practices for BEx queries. These are some things we found;

If you have a hierarchy as a row. The initial query runs much faster if you expand to a couple of levels only when compared to having it expanded to the lowest level. Which would make sense as excel does not need to process all the cells. We have had to opted for a Citrix Solution in area’s with low bandwith. One other obvious tip is to only return what you need and have light queries for this that restricts the data on the queries.

We also found the performance on the member selector poor with large hierarchy we are currently looking at using authorization to improve this.

Hope it helps

Regards

Answers (4)

Answers (4)

Former Member
0 Kudos

Hi All,

We are also in process of implimenting AO over a Hana Database.

The Hierarchys built in Hana Information Models (CALC View) doesnt perform well in AO.

We tried accessing the same data in Lumira and works much faster.

So seems like AO for Office is definately the bottle neck.

Would some one have any pointers that i can look into.

Regards

Yogesh Sharma

Former Member
0 Kudos

Hi Everyone,

You may want to look at below link:

My colleague found this and this answered some questions about the disparity between RSRT and SAP AO behavior. Some times changes do not take effect on SAP AO side, while main reason maybe precedence of settings. But some users save workbooks on their local and reuse them by refreshing the workbook. This may cause old configurations being used on SAP AO side and you would see different behavior while comparing.

The solution would be to reset the data source, but this cause loss of formatting. I am using AO 2.3 - latest at this point in time.

Former Member
0 Kudos

Hi everyone,

concerning the impact for formatting when resetting datasource, the only way we use here is to create a view of your query. Now, all the formatting changes you make (using AFO formatting and not directly with formatting Excel) will be kept with the view (if you save the view save of course).

I also ha a problem with a characteristic that I wanted to displayed with Key and text by default and that kept displaying only text even if I had changed the option in the Query Designer. The solution was to tick the option "Rest Datasource on workbook opening" in the technical panel in the Analysis Excel Tab.

I think taht sometimes the workbook saves some query properies internally and does change refresh them unless we specifies it.

Hope this will help you.

We are running on AO 2.3

torsten_wirth
Participant
0 Kudos

This is the official version when it is necessary to reset the DS:

http://scn.sap.com/docs/DOC-59966

eric_gilbert
Explorer
0 Kudos

Any update on this??
We are in the process of moving users from the old BEx 3.5 to Analysis. Migration is fairly easy except the numerous complaints about the performance difference. (We are stuck migrating BEFORE we upgrade from BW 7.01 since 7.3/7.4 has no support for the aged BEx front end). We are hoping to be able to tell the users that the performance issues in Analysis will go away after we perform the upgrade to BW 7.4...

Former Member
0 Kudos

Hallo Eric,

We are facing same issue what you experienced with our users as well. In 3.5 worksbooks run very quick and AO taking very long time and sometimes excel is hanged out.Still having performance issues for BW 7.31 version powered with HANA. So performance issue not completly related to BW backend version .

simon_eiblmeier
Explorer
0 Kudos

Hallo,

I have the same problem in our environment. The "old" 3.5 Workbooks are by far (about 5times) faster then the new AO Workbooks. By now I haven't  found a reason, nor a solution..

Former Member
0 Kudos

Hi Miriam,

I'm wondering if you saw any remarkable progress with your performance issues?

We're currently using AO 1.4.5 Hotfix 1 (mid-Feb.) and in an example query, which has a single selection variable Input, execution in RSRT takes less than 5 seconds.

In contrast, when we open the variable screen in AO, this alone takes 30 seconds, plus about 45 seconds until the result is displayed in Excel.

I'm wondering why you can see the process "CL_SQL_STATEMENT==============CP" running about 40 seconds (SM66), whereas in RSRT the SELECT obviously takes hardly 3 seconds?!

FYI: The result of this query is exactly one single charachteristic, with two lines of material numbers (key). So, the actual data transfer is definitely not more than some 20 KBytes.

Any comments appreciated.

Martin

Former Member
0 Kudos

Hi Martin,

one reason can be the number of attributes in you one single characteristic.

How many do you have? (nav and non-nav)

Regards

Thorsten

Former Member
0 Kudos

Hi Thorsten,

the characteristic has a compounding to the source system info (which is not entered in the selection variable), plus it has 80 attributes (30 navi, 50 display).

But why should the SQL statement take > 10 times longer via AO?

Regards, Martin

Former Member
0 Kudos

Hi Martin

I just had the experience a couple of times that attributes are having a negative impact on the performance

The reason for that is the data access and the data which is initially fetched by AO.

Once I had a very positive effect with SAP Note 1144979.

To find out the real reason I need more information from the RSRT or the BICS statistics recorded in AO...


Regards

Thorsten

Former Member
0 Kudos

Hi Thorsten,

unfortunately the note is only applicable to BW 7.00 and we are already on 7.31.

I checked the SQL Statement in RSRT ... and it is hilariously small:

Requested Attributes (SELECT)

OUR__ATTRIBUTE_sid  INTO S____6962

Key Figures

__numoffacttablerows Aggregation 02 INTO Z____026

Constraints

Name: $$keydate$$  Value: 20140304

Join Conditions (FROM)

Query Entries (WHERE)

f: 0000 l: 0requid_sid o: LE c:  v1: 13491090 v2:

f: 0000 l: OUR__ATTRIBUTE_key o: EQ c:  v1: 0390922670000 v2:

f: 0000  AND

Order by

attr: OUR__ATTRIBUTE_sid sort: 1 (ascending)

... what should take > 40 seconds here for AO???

Do you mean I should open an OSS for support?

BR, Martin

Former Member
0 Kudos

Hi Martin

if you record a rstt trace, which step is the high consuming?

Regards

Thorsten

Former Member
0 Kudos

Hi Thorsten,

RSTT was a good idea. Here the results.


First an example of a pure "Refresh" of the datasource without variable Screen:

Program moduleRuntime
RSAO_BICS_SESSION_INITIALIZE516.405
BICS_CONS_SET_GET_SESSION_PROP25.653
BICS_PROV_OPEN1.127.188
BICS_PROV_VAR_GET_VARIABLES125.762
BICS_PROV_SUBMIT_VARIABLES85.724
BICS_PROV_GET_INITIAL_STATE29.509
BICS_PROV_SET_STATE6.477
BICS_PROV_GET_RESULT_SET492.904.849 = 492 Seconds
RSBOLAP_BICS_STATISTIC_INFO79.823

This is ok, because the result is a list of 1,04 Mio. records.

Program moduleRuntime
BICS_PROV_OPEN11.754
BICS_PROV_VAR_GET_VARIABLES60.394
BICS_PROV_VARIANT_GET_CATALOG4.939
BICS_PROV_GET_MEMBERS47.454
BICS_PROV_VAR_SET_VARIABLES82.254.484 = 82 Seconds
BICS_PROV_SUBMIT_VARIABLES81.717.506 = 81 Seconds
BICS_PROV_GET_INITIAL_STATE27.466
BICS_PROV_SET_STATE7.415
BICS_PROV_GET_RESULT_SET3.026.526 = 3 Seconds
RSBOLAP_BICS_STATISTIC_INFO55.691

Here we have a result of 2 records.

But it looks like there is really an issue around variable screen and submit!

Thanks, Martin

Former Member
0 Kudos

Small update:

The first run with Variable Screen usage has been executed with this registry setting = true:  http://scn.sap.com/thread/3433366

When deleting this entry again, the runtime improves by about 55% ... nevertheless it is too high!!!

BICS_PROV_OPEN9.357
BICS_PROV_VAR_GET_VARIABLES49.620
BICS_PROV_VARIANT_GET_CATALOG4.288
BICS_PROV_GET_MEMBERS84.444
BICS_PROV_VAR_SET_VARIABLES42.085.689
BICS_PROV_SUBMIT_VARIABLES54.629.788
BICS_PROV_GET_INITIAL_STATE24.371
BICS_PROV_SET_STATE6.926
BICS_PROV_GET_RESULT_SET125.752
RSBOLAP_BICS_STATISTIC_INFO11.403
Former Member
0 Kudos

Hi Martin,

I am just guessing whats the reason behind the times

The following things might have an impact:

- variable exit

- a huge list of single selection instead of range selection

What is your selection? Do you have exits?


Regards


Thorsten

Former Member
0 Kudos

Hi Thorsten,

I really appreciate that you are trying to help me, but I guess we'll open an OSS.

We have a normal Characteristic Variable with Selection Option and optional Manual Input.

There is no Exit behind and I'm selecting one single material no. with its key.

Like said, the variable prompt plus query execution is done in RSRT in less than 5 seconds... with exactly the same example Input.

Thanks anyway,

Martin

Former Member
0 Kudos

hi Martin

yes, open an OSS message 🙂

the support will be able to help you

Regards

Thorsten

0 Kudos

Dear Martin,

any News regarding your OSS message?

We are facing the same issue.

Thanks & BR,

Andreas

Former Member
0 Kudos

Hi Andreas,

I could not find a note anymore... I guess we did not open one.

Today I would say the problem is that the entered characteristics are checked against all the master data keys, which for Material numbers is around 25 Mio in our system.

I can't tell you why AO takes so much longer to do this than RSRT, but we got used to it, and don't expect any improvement in the future.

Best regards,

Martin

IngoH
Active Contributor
0 Kudos

Hello Miriam,

please also take a look at note 1774343

regards

Ingo Hilgefort, SAP

Former Member
0 Kudos

Hi Michael, Hi Ingo,

Thanks for taking the time to answer to me.

I was checking the notes both of you mentioned and some of them are telling me to update to SP6 (I'm with SP5 now).

I tried applying some of the corresponding notes without noticing improvements. We are going for the SP6 next week and I will check again.

Thanks again,

Miriam

IngoH
Active Contributor
0 Kudos

Hello Miriam,

you should go to the latest version 1.4 for sure

regards

Ingo Hilgefort