cancel
Showing results for 
Search instead for 
Did you mean: 

Deactivate Hierarchy symbols in excel

Former Member
0 Kudos

Does anyone know how to remove the display of the Hierarchy symbols within bex analyser.

Our problem is that we have a large hierarchy which is expanded by another hierarchy, in total about 2000 rows and 9 columns. Within ST03N it can be seen that 98% of approximate 600sec processing time is occupied by the front end. The front end PC is appropriately spec'd ~ less than 3 months old and is consistent with other users.

From what I can tell this performance is related to the display of the many hierarchy symbols in excel. So I would like to know if I could remove them. I am aware that they can be changed, however no real performance improvement is noticed.

We are on BW 3.0B SP20.

Thanks

James

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

What Front End Patch level are you at?

Found Note 779784 that indicates some improvements were made in 3.0 FEP15. I'm at FEP9 now, so I'm going to test the suggested interim fix of copying the sapbex.xla from FEP 15. I'll see what, if anything it does.

Former Member
0 Kudos

Actually copied the sapbex.xla from FEP 16. Haven't had a chance to completely validate everything, but the fontend time dropped fomr about 1680 seconds to 290.

So assuming everything else checks out and no new problems created - this looks good.

Former Member
0 Kudos

Pizzaman, how did you change the default shapes?

You might try the little routine below. Only need to run this once for a given workbook (will not affect any other workbooks than the one this is run on). It will reduce shapes in that workbook to basically ... nothing.

I have not tested this on anything as big as the situation the two of you are facing, but I have tested it and it appears to work.

I have one other thought about what might be affecting your response time. Do you have any "live" equations in Excel? These definitely affect query performance. To test the effect, change Excel calculation to Manual before refreshing the query.

Sub reduceShapesToNothing()

Dim ws As Worksheet, sh As Shape

Set ws = Sheets("SAPBEXqueries")

For Each sh In ws.Shapes

myName = sh.Name

sh.Delete

Set sh = ActiveSheet.Shapes.AddLine(1, 1, 1, 1)

sh.Name = myName

Next sh

End Sub

Hope this helps.

- Pete

Former Member
0 Kudos

As far as changing the symbol, nothing fancy, just using the option on the menu:

Business Explorer / Format / Define Symbol for / Expand hierarchical

I used a simpler graphic with more contrast - actually liked it better. I can't find the default symbols on my PC, so I'm guessing they may get sent from th eserver along with the data.

I did not see if I could save these symbols as part of a template so I don't know if you can permanently change the shape that is used or just for the duration of the session. I did like the one I used so I might pursue it further - it was pilfered from a BW Help Screen on hierarchies, and was a black box with a white arrow. I thought it made the hierarchy easier to read.

I did test turning off the automatic calculation just to be sure, but that did not make a difference. The sapbex.xla from the 30B FEP 16 reduced the front-end time by 75 - 80%. Some of the remaining timem I'm sure is hierarchy display, but the network roundtrips contribute to it as well, so it may be as good as it gets until we get the the web.

Former Member
0 Kudos

Thanks for the explanation.

The symbols are stored on the (very) hidden query repository sheet in the workbook. The name of sheet is "SAPBEXqueries".

If you delete the symbols from the workbook, they get copied in again. Not from the server, from the copy of the analyzer (sapbex.xla) on your PC.

The little bit of Visual Basic code in my earlier posting simply replaces all of the shapes (I think there are 6 of them) that are stored on the (very) hidden sheet with new shapes that have a width of 0 and a height of 0.

Let me know if it works for you.

- Pete

Former Member
0 Kudos

Thanks for the good suggestions guys.

We are running front end 3.5 patch 0, about to go to patch 5. Will test this shortly and see if any different.

Peter, the "very" hidden sheets, how do I display them. I can see them in the VBA editor, but not in excel.

I have given your code a try, but what would I expect to see happen. It doesn't seem to delete the symbols before or after refreshing the query. When I manually ("F5">>Special>>Objects) delete the objects they are returned upon refresh.

I have tried running this in a workbook and as just a locally managed query embedded in excel (ie not saved on server as a workbook.)

I have also tried switching off automatic calculation with no real difference.

Former Member
0 Kudos

Frontend patch 5 for 3.5 massively improves the performance. I believe this is equivalent to the previously mention patch for 3.0. This is sort of covered in OSS note 852700.

Thanks for the help everybody.

Former Member
0 Kudos

Hi James,

To see the (very) hidden sheets, I refer you to an earlier posting. I have copied part of that posting below and provide a link if you want to see the entire posting.

What would you expect to see happen ... when you run the routine above you will see nothing immediately ... unless you have made the query repository sheet visible ... in which case you will see that the 6 shape objects on the repository sheet have disappeared. When you refresh the query, though, you should see no hierarchy symbols on the query sheet.

You are correct that if you manually delete the object ... either on the query sheet or on the hidden repository sheet ... the symbols will reappear (in both places) after query refresh. That is why in the code shown above I create new objects (with zero height and zero width)with the same names.

Following is from a posting dated June 3, 2005 ...

To view the query repo sheet, the first hurdle is making it visible. To do this, go to the VB Editor and either:

1) select the Microsoft Excel Object that is named (SAPBEXqueries); double click to select; launch the Properties window (press F4); change the Visible property to Visible

2) use the following VB code:

Sub ShowSheets()

Dim ws As Worksheet

If ActiveWorkbook Is Nothing Then Exit Sub

For Each ws In ActiveWorkbook.Worksheets

If Left(ws.Name, 6) = "SAPBEX" Then

ws.Visible = True

ws.Select

ws.Cells.WrapText = False

End If

Next ws

End Sub

- Pete

Former Member
0 Kudos

Peter,

Bloody brilliant help.

Managed to get it all to work, however I did have to make sure that I selected the SAPBEXqueries sheet before running the macro to reduce the symbols.

Anyway have now managed to successfully run a query with over 25,000 rows and 12 columns, ie over 300,000 cells. Previously this could not be run but now runs in less than 600seconds, which the client is pretty happy about.

Thanks again.

Answers (2)

Answers (2)

Former Member
0 Kudos

James -

I'm struggling with the exact same problem. I have 7,000 rows with 36 KFs. A fairly large result, but I've got the query tuned well on the DB side, and now have almost all frontend time to deal with - almost 1,700 seconds. I've tested with formatting, automatic column width adjustment, zero suppression, etc, and it is evident that the hierarchy is the culprit.

I tried replacing the symbol with a much smaller graphic (25 bytes) and that didn't make any difference. What I haven't determined is if problem is with the underlying SAP VB code that formats the cells in the worksheet, or Excel funcitonality for inserting a graphic.

Not sure that there is much I can do if it is either of those as I don't really want to customize the VB. I haven't found anthing on SDN or elsewhere that seems to address the problem. I'm going to see if I can find anything on the Excel side.

If you learn anything, please post back here. I've got this thread marked for watching.

Former Member
0 Kudos

Hi

9*2.000 = 18.000 cells this will take time no matter what you do. Have you tried to run the query from the WEB? The web should be much faster when we are talking transfer to the frontend.

Regards

Kristian

Former Member
0 Kudos

Yes the WEB is a lot faster, however the requirement is for excel as the data is feeding another database. The other database relies upon the hierarchy node keys, so open hub is not an option either. I have tried disabling all formatting from query properties, however this still does not remove the hierarchy symbols.

Whether the query is 1 column or 9 columns, it doesn't make much difference either - thus my conclusion that it was related to the hierarchy symbols.

Former Member
0 Kudos

OK. If you are to feed another DB you could also try to use transaction RSCRM_BAPI to fill a flat table and then make the extract from here. This solotuion would also make it more easy to automate the process. There is a how to paper on that in the how to area of SDN.

With Kind Regards

Kristian