on 06-27-2013 3:19 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
This is the official version when it is necessary to reset the DS:
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 .
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
Hi Thorsten,
RSTT was a good idea. Here the results.
First an example of a pure "Refresh" of the datasource without variable Screen:
Program module | Runtime |
RSAO_BICS_SESSION_INITIALIZE | 516.405 |
BICS_CONS_SET_GET_SESSION_PROP | 25.653 |
BICS_PROV_OPEN | 1.127.188 |
BICS_PROV_VAR_GET_VARIABLES | 125.762 |
BICS_PROV_SUBMIT_VARIABLES | 85.724 |
BICS_PROV_GET_INITIAL_STATE | 29.509 |
BICS_PROV_SET_STATE | 6.477 |
BICS_PROV_GET_RESULT_SET | 492.904.849 = 492 Seconds |
RSBOLAP_BICS_STATISTIC_INFO | 79.823 |
This is ok, because the result is a list of 1,04 Mio. records.
Program module | Runtime |
BICS_PROV_OPEN | 11.754 |
BICS_PROV_VAR_GET_VARIABLES | 60.394 |
BICS_PROV_VARIANT_GET_CATALOG | 4.939 |
BICS_PROV_GET_MEMBERS | 47.454 |
BICS_PROV_VAR_SET_VARIABLES | 82.254.484 = 82 Seconds |
BICS_PROV_SUBMIT_VARIABLES | 81.717.506 = 81 Seconds |
BICS_PROV_GET_INITIAL_STATE | 27.466 |
BICS_PROV_SET_STATE | 7.415 |
BICS_PROV_GET_RESULT_SET | 3.026.526 = 3 Seconds |
RSBOLAP_BICS_STATISTIC_INFO | 55.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
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_OPEN | 9.357 |
BICS_PROV_VAR_GET_VARIABLES | 49.620 |
BICS_PROV_VARIANT_GET_CATALOG | 4.288 |
BICS_PROV_GET_MEMBERS | 84.444 |
BICS_PROV_VAR_SET_VARIABLES | 42.085.689 |
BICS_PROV_SUBMIT_VARIABLES | 54.629.788 |
BICS_PROV_GET_INITIAL_STATE | 24.371 |
BICS_PROV_SET_STATE | 6.926 |
BICS_PROV_GET_RESULT_SET | 125.752 |
RSBOLAP_BICS_STATISTIC_INFO | 11.403 |
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
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
Hello Miriam,
please also take a look at note 1774343
regards
Ingo Hilgefort, SAP
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.