BW Query Performance

[Former Member]

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,

[anindya_bose · Accepted solution]

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