Issue with aggregates

[#1]

I am running into issue with performance on our query runs. I have tried creating aggregates..however based on how our queries are written(we are bringing back a whole bunch of data involving navigational attributes), I cannot get any of the queries to hit the aggregates. The only time I have had any kind of success is when I have my aggregates defined explicitly to include all the characteristics that are included in the query. We are dealing with about 5 million records in 0bpartner(the queries are brining back several navigational attributes for 0bpartner in addition to some other characteristics) and also have hierarchy based reporting....other than with using aggregates, what other ways can I try and speed up query performance...any tips or tricks that you folks can provide is much appreciated. Thanks

Dinesh

[former_member345199]

Hi,

This info may be helpful.

General tips

Using aggregates and compression.

Using less and complex cell definitions if possible.

1. Avoid using too many nav. attr

2. Avoid RKF and CKF

3. Many chars in row.

By using T-codes ST03 or ST03N

Go to transaction ST03 > switch to expert mode > from left side menu > and there in system load history and distribution for a particual day > check query execution time.

/people/andreas.vogel/blog/2007/04/08/statistical-records-part-4-how-to-read-st03n-datasets-from-db-in-nw2004

/people/andreas.vogel/blog/2007/03/16/how-to-read-st03n-datasets-from-db

Try table rsddstats to get the statistics

Using cache memoery will decrease the loading time of the report.

Run reporting agent at night and sending results to email.This will ensure use of OLAP cache. So later report execution will retrieve the result faster from the OLAP cache.

Also try

1. Use different parameters in ST03 to see the two important parameters aggregation ratio and records transferred to F/E to DB selected.

2. Use the program SAP_INFOCUBE_DESIGNS (Performance of BW infocubes) to see the aggregation ratio for the cube. If the cube does not appear in the list of this report, try to run RSRV checks on the cube and aggregates.

Go to SE38 > Run the program SAP_INFOCUBE_DESIGNS

It will shown dimension Vs Fact tables Size in percent.If you mean speed of queries on a cube as performance metric of cube,measure query runtime.

3. --- sign is the valuation of the aggregate. You can say -3 is the valuation of the aggregate design and usage. ++ means that its compression is good and access is also more (in effect, performance is good). If you check its compression ratio, it must be good. -- means the compression ratio is not so good and access is also not so good (performance is not so good).The more is the positives...more is useful the aggregate and more it satisfies the number of queries. The greater the number of minus signs, the worse the evaluation of the aggregate. The larger the number of plus signs, the better the evaluation of the aggregate.

if "-----" then it means it just an overhead. Aggregate can potentially be deleted and "+++++" means Aggregate is potentially very useful.

Refer.

http://help.sap.com/saphelp_nw70/helpdata/en/b8/23813b310c4a0ee10000000a114084/content.htm

http://help.sap.com/saphelp_nw70/helpdata/en/60/f0fb411e255f24e10000000a1550b0/frameset.htm

4. Run your query in RSRT and run the query in the debug mode. Select "Display Aggregates Found" and "Do not use cache" in the debug mode. This will tell you if it hit any aggregates while running. If it does not show any aggregates, you might want to redesign your aggregates for the query.

Also your query performance can depend upon criteria and since you have given selection only on one infoprovider...just check if you are selecting huge amount of data in the report

Check for the query read mode in RSRT.(whether its A,X or H)..advisable read mode is X.

5. In BI 7 statistics need to be activated for ST03 and BI admin cockpit to work.

By implementing BW Statistics Business Content - you need to install, feed data and through ready made reports which for analysis.

http://help.sap.com/saphelp_nw70/helpdata/en/26/4bc0417951d117e10000000a155106/frameset.htm

/people/vikash.agrawal/blog/2006/04/17/query-performance-150-is-aggregates-the-way-out-for-me

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/1955ba90-0201-0010-d3aa-8b2...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/ce7fb368-0601-0010-64ba-fad...

http://help.sap.com/saphelp_nw04/helpdata/en/c1/0dbf65e04311d286d6006008b32e84/frameset.htm

You can go to T-Code DB20 which gives you all the performance related information like

Partitions

Databases

Schemas

Buffer Pools

Tablespaces etc

use tool RSDDK_CHECK_AGGREGATE in se38 to check for the corrupt aggregates

If aggregates contain incorrect data, you must regenerate them.

Note 646402 - Programs for checking aggregates (as of BW 3.0B SP15)

Check SE11 > table RSDDAGGRDIR . You can find the last callup in the table.

Generate Report in RSRT  

http://help.sap.com/saphelp_nw04/helpdata/en/74/e8caaea70d7a41b03dc82637ae0fa5/frameset.htm

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/cccad390-0201-0010-5093-fd9ec815...

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/4c0ab590-0201-0010-bd9a-8332d8b4...

Achieving BI Query Performance Building Business Intelligence

http://www.dmreview.com/issues/20051001/1038109-1.html

ODS Query Performance  

OLAP: Cache Monitor 

http://help.sap.com/saphelp_nw2004s/helpdata/en/41/b987eb1443534ba78a793f4beed9d5/frameset.htm

Cube Performance

/thread/785462

Keep the small dimensions together

Keep line item dimensions wherever needed

Group related characteristics into one dimension only

Remove high cardinality dimensions

Dimension tables have a huge impact on InfoCube performance. The star schema design works best when the database can assume minimal records in the dimension tables and larger volumes in the fact table.

Each dimension should be of approximately equal size and that the file size of each dimension should not make up more than 10 percent of the associated fact table.

You may have performance issues when DIM tables are bigger than the FACT tables (degenerative dimension). This can happen if you regularly delete data from the cube, because the data is only deleted in the fact-tables and not in the dimensions.

You can clean-up the dimensions in transaction RSRV with the check -> elementary tests -> transaction data -> Entries not used in the dimension of an InfoCube

Hope this helps.

Thanks,

JituK

[Former Member]

Thank you for the tips..I will try out some of these

[Former Member]

Hi,

First of all you need to find out wherey your query is taking time.

Is it at database level/OLAP time or frontend time

u can check it using following tools

use RSRT,RSRV,st05...

accordingly we can proceed to enhance the query perofmance

Cheers,

Swapna.G

[Former Member]

HI,

Partatining and Query Read mode will help to improve query performencence in addition to aggregates.

Thanks and Regards,

Kotesh