cancel
Showing results for 
Search instead for 
Did you mean: 

BW Query Performance

Former Member
0 Kudos
62

Hello,

BW queries are running slow on our system. Upon further checking , we found that underline infocube has last 5 years of data and customer wants to keep those historical data - we are on Bw 7.3 version so probably design change to use SPO would be benefial but it development and implementaion would certainly takes time and customer do not want to wait so long.

We also checked this queris load in ST03N and found there is no need to for an aggregate since Sel/Tranfer ration is below 10 although database time is pretty high. Would Filling OLAP cache would help here ?.

How I could find what all selection data users have had entered so we can create a suitable aggregate - base on query design - one thing we noticed that query should always has 0CALDAY on selection screen because it is made mandatory in query design - there are other 5 fields on selection screen hence we are not sure whether aggregate would help here ?.

Also we does have BWA but it is alomost 70% occupied so adding cube on BWA would not help either - we already checked cubes on BWA and we can not remove existsing cube from BWA.

Any advise on pathforward ?.

Thank you,

Accepted Solutions (1)

Accepted Solutions (1)

anindya_bose
Active Contributor
0 Kudos

Hi ...You can consider these points.

1. Creating Aggregates with Calyear selection.. Like an aggregate with current 2 years data. This would be smaller than Cube .

2. While defining aggregates Calday should be a part of your aggr obviously. Check Charac in KF restriction and Charac on rows.   You can leave charac in Free Charac pane from aggregate definition.

3.  If you include let's say customer in aggregate definition, then there is no point keeping customer group outside.. Because customer grp in this case would not increase the number of records in aggregate.

4.  You can have a separate cube for historical data. If you have query on MultiProvider you can provide a selection on 0INFOPROV to select current or historical cube.

5. You can go for DB partition.

6. Whatever you do , you would not get BWA like performance. Best performance you can get from creating a current and historical cube and put the current cube on BWA.. Here you can save some memory in BWA plus  get excellent runtime.

Cheers

Anindya

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi,

You can go for Partitioning.

Transfer the data from your base cube to others In such a way say that your one cube contain one or two year of data.

Maintain the aggregates.

Hope this will helps.

Regards,

Former Member
0 Kudos

Thanks to all.

former_member186445
Active Contributor
0 Kudos

refresh the statistics of the cube if not yet done.

partition the cube.

0 Kudos

For accelerating the performance we can cache the query each time the infocube gets updated , i know this is old method but works well .

Create a query cache and run it each time the cube is updated this will accelerate the performance definitely.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/f048c590-31a4-2c10-8599-bd01fabb9...

The above link provides the steps.

Also use the query properties selection.

4) persistent cache across each application server. once you run the query without the cache you will find the runtime statistics under cache monitor- blob.

We did this to optimize the run time or a 180 sec query to 35 sec with this method.

Sainath Kumar

Former Member
0 Kudos

Hello

If your Cubes are not Compressed. you can go for Cube Compression.

And said above..you do the aggregates based on time

check if the data load is taking care of Creating index for the cube. if no.. then its better to create index ( drop/Build) in the process chains

check with the Basis if they has done the REORG of that Table.

Sachin