Poor performance on BWA Only Cube loads

[Former Member]

Is anyone loading data to BWA Only cubes and seeing poor performance on the loads?  I'm talking taking 1-2 hours to load 1 million rows.  In our normal cubes, it takes about 5-10 min to load a million rows.  And if you have, what have you done to improve performance?

I'm trying to talk to SAP, but I'm still wading through initial "did you do a consistency check? Is it plugged in?" basic questions.

We have added loading text to an infoobject in the cube to build SIDs before we do our load, and that helped some.  Also set packet size in the DTP to about 400,000 (which I read elsewhere) to help with performance.  The lag time is really with the SID creation.  Someone else also recommended that we turn SID creation on during load on the Source DSO (it's set to the default "build SIDs during reporting" right now).

[Former Member · Accepted solution]

I did hear back from SAP--the guy found a data issue with information in the 0POSTAL_CD field.  Once I had fixed that, the loads are now taking about 1 to 1.5 hours to do about a million or so rows.  Which is not bad.  He also confirmed that doing a similar text load to an infoobject (that does not also get attributes loaded) first would also help the load with SID creation, etc.  And that turning on SIDs in the source DSO would not help the load to the BWA cube.

[Former Member]

Hi Tom,

Are you doing a lot in transformation to this BWA Only cube? I once tried to do hell lot of things while loading to Analytical Index in an APD and it took time. To reduce it, I implemented a DSO in between and did all transformations while loading to this DSO. Then 1:1 mapping into analytical index. This improved performance atleast by 50-60%. I think when you have BWA-only objects, the calculations go to your BWA server which is basically optimized for read operations and not able to do other functions as seemlessly ABAP server. SAP recommends similar process for HANA as well. Avoid passing datasets from one engine to another.

See if it makes sense in your case.

Regards,

Nikhil.

[Former Member]

We are not doing any manipulations other than cleaning up data as suggested by the SAP tech.  Every field is a direct source-to-target mapping, other than the cleanup being done on about a dozen fields. 

Now I've run into a problem that I know how to fix (via SAP), but it involves directly editing an RS table, which we can't do in our Production system.  I'm having to write an SM30 program to get it done so I can resume my loads.

For those that are interested, I am attending TechEd in October in Las Vegas and will be doing a presentation discussing using BWA Only cubes and the experiences I have run into with development and roll out to the users.

[former_member229426]

Dear Tom,

I found the name of your presentation (TechEd 2013) on the net and would kindly like to ask you if this material is available (or at least some part of it). I am very much interested how the implementation of SPOs affected you (administration, loading speed, query speed). I have a firm belief that SPO is a truly advanced technique, but I need to convince some people (and they would like to hear success stories from large clients).

Thank you very much in advance!!!

Laszlo

[Former Member]

Hello Tom,

Is your comparison of load performance between a BWA only cube and a normal cube correct? I would compare only if everything is same between the 2 loads like downward dataflow, kind of data, kind of tranformation, SID generation not yet done etc. But otherwise according to your statement that the lag is only due to SID generation, I woul assume that the normal cube would take same time (may be little less) compared to BWA. This is because for normal cube loads as well, SID generation will be a time consuming step.

[Former Member]

That's the answer I'm trying to get out of SAP--is it reasonable to expect the load time to be similar between the two?  My main issue is that I'm trying to load history to the cube, and to load 3 months of data takes about 14-16 hours.  The same 3 months of data to one of our normal cubes takes less than an hour.  And I'm loading from the same source DSO.  I have a ticket open with SAP that just went to 2nd level support asking if there's something I need to change, improve, etc., or if this is just "how it goes".