filter and restricted key figures

[Former Member]

Hello Gurus,

I am in a situation where I have to explain my manager why queries are not running faster. He told me that his queries were running faster until he moved the values in filter to his restricted key figures.In the queries all the elements in columns are restricted key figures.So can someone explain me if this will hinder the query performance and how? Also it would be great if you can give some major tips on what deterioates the query performance.Your answers will be well rewarded.

Regards,

Kal

[edwin_harpino · Accepted solution]

hi Kal,

restriction will take more process in retrieving data.

do you use any 'exclusion' in your restriction ? not recommended for performance.

from ods or infocube your query retrieve data ?

for ods, indexing to these filter characteristic normaly will boost the performance.

check your query 'read mode', “Read when navigating and expanding hierarchies” is recommended

some tips :

- Every Query should start with a relatively small result set; letthe user drill down to more detailed information

- ExclusionInclusion/Exclusion

Selections can include and exclude characteristic values.

Exclusion cannot access database indices.

Better use inclusion and avoid exclusion of char values

- Query on MultiProvider

Queries on MultiProviders usually access all underlying InfoProviders,

even if some cannot be hit as no key figures within the query definition

are contained in this InfoProvider

Use 0INFOPROVIDER to restrict the query to a selection of individual InfoProviders

- Other Query Design Tips

Cell calculation by means of the cell editor generates separate queries at query runtime

Be cautious with cell calculations

Customer-specific code is necessary for virtual key figures and characteristics

Check Code in Customer Exits

Read Mode For Queries. Usually “Read when navigating and expanding hierarchies” is recommended

- data modeling also impact to query performance

there are comprehensive docs for bw performance in this sdn forum and service.sap.com/bi. basicaly performance can be traced from database, olap and frontend.

tools for checking

- database : (ST05)RSRV,RSRT, RSRTRACE

- OLAP : ST05,ABAP Trace (SE30), RSRT,

- FrontEnd : IEMON, RSRT, RSRTRACE

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

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/afbad390-0201-0010-daa4-9ef...

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

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/4c0ab590-0201-0010-bd9a-833...

Message was edited by: A.H.P