how to improve query performance when reporting on ods object?

[Former Member]

Hi,

Can anybody give me the answer, how to improve my query performance when reporting on ODS object?

Thanks in advance,

Ravi Alakuntla.

[Former Member · Accepted solution]

The two biggest DB tools are going to be seconday index(es) and table partitioning - they might be used separately or together. You can create secondary indexes form the BW Workbench, but would need to get a DBA involved in partitioning and ODS/DSO.

From the query side - depending on the query and the data involved, using the OLAP cache, runing the query once and storing the results to the Gloval OLAP cache might be even better solution. Pre-queries and value sets may also be useful options.

[Former Member · Accepted solution]

Hi Pizzaman,

I think you can create secondary indexes from Modelling workbench itself - so if we are sure wht all indexes would need to be created, then we would DBA be required?

Thanks in advance

Gaurav

[Former Member · Accepted solution]

You can create secondary indexes for ODS/DSO from the BW Wkbench (RSA1), but you need SE11 access for secondary indexes on dimension and master data tables, which is not usually available to BW staff.

You need to review your queries, concentrating on the ones that run the most and/or consume the most system resources. Collect Explain Plans to see what tables are accessed with full table scans that might benefit from secondary indexes.

Again, adding indexes without knowing if they will be used, when or how often, can waste a a lot of system resources. If you don't have much experience with them, I would recommend working with your DBA on a couple of frequently used queries to gain some experience.

[Former Member]

Another thing that can help is caching, compressiong requests perodically.

Ravi Thothadri

[Former Member]

Hi Ravi,

Check these links which may cater your requirement,

PDF on BW performance tuning,

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/cccad390-0201-0010-5093-fd9...

Regards,

Mani.