Query execution taking long time....

[Prabhith]

Hi All,

I am facing a strange issue in SAP BW Bex side(Environment 7.3SP4).

I have a query where we are doing some YTD ,MTD calculations.
The prompt/variable is mandatory fiscal year period(Customer exit), by default current fical year period 002.2013 will be shown to user.

When I execute the query with prompt value as 001.2013(or any other periods except current period),output is coming in 3 seconds.

But when I execute the query with prompt 002.2013(current period), the result is not coming atall.

Can some body help with a suitable solution.

I have never faced such an issue before..

Golive date is reaching fast...so expecting some quick response..

Many thanks in Advance

Prabhith

[Former Member]

Dear Prabhit,

Is there any changes to the design of the query happened recently,

what i mean is are there any navigational attribute with big MD object has been added/ a new processing type has been added as part of the query when drill down.

If nothing changes happened then u need to see as suggested by friends may be huge volume of data is the problem

Also in RSRT --> query name --> properties check optimized mode is 0 and also is the check box for use of selection for structured elements is checked and all setting for this query.

Kindly let us know if this can solve ur issue.

Thanks and Kind Regards,

Saisandeep

[Former Member]

Hi,

We have performance tuning concepts to improve Query Performance.

Try creating Aggregates,Compression, whichever satisfies the needs and try executing your query.

Hope this helps your query to improve its performance.

Regards,

Kavitha

[Prabhith]

Hi All,

Found a small work around today..

Initially I removed all the existing filters/restrictions from all the keyfigures.

Then I added back the restrictions one by one and executed the query. My basic intention here was to find the one KPI which is causing the issue.

After 3 or 4 KPI's, the query started hanging when I added back one KPI which had a YTD restriction. That made me feel like that particular KPI was the culprit.

I kept that KPI as such, but after that I dragged in the same KPI to the column once again, but this time without any YTD restriction.

Surprisingly, I was able to see the output now even with the current Fiscal year period.

I know that this is just a work around and I have no idea what has happened in the backend after my change.

I will keep this discussion open, expecting some better solution....

Br

Prabhith

[Former Member]

Dear Prabhakaran, In such a case, you can try using a lookup cube w/summary data. If still exit takes time to execute & populate, use a formula variable that copies to your KPI. Regards, Arun Bala

[Former Member]

there may be some request in uncompressed  status for the current period in the infoproviders,

else data volume may high,

else check variant in RSRT  debug mode analyze sql statements

else indexes defined in case of dso

last generate the report in RSRT once again and delete old abap........u can apply trace on query!!!

hope it help

regards:

satish

[Prabhith]

Hi All,

I don't have any aggregates or compression mechanisms in my info provider and no BI Accelerators (BIA) are been used.

RSRT -- Sql and debug options -- I need to check that ....

Br

Prabhith

[Former Member]

Does the cube has any partitioning...

[Former Member]

Hi,

Step 1 ) Compare the amount of data for the two months being processed.

Step 2) Run the  Execute + Debug option.

Br,

Arpit

[Prabhith]

Hi All,

Thanks for all your suggestions...

As always, I have built this query in a Multiprovider only.

But I have used the infoprovider restiction in the global filter of the query. Apart from that, there are many other filters which will ensure that the query output is minimum(as required)..

As you all know, its just May which means the 2nd period of the current Fiscal year. So the expected data  volume is also minumum.

Am worried wetr there is any SAP bug hidden behind this problem and SAP has some notes to solve it..

Br,

Prabhith

[Former Member]

Dear Prabhith,

Do you have any aggregates or compression mechanisms in the info provider which you are restricting at the query level. Chances are there aggregates are not rolled up or compression has not occured for the current period.

Since the issue is only with the current period, these might be the issues....

do check if you are missing any such things...

Thanks,

Nithesh.

[Former Member]

Hi Nithesh

Try to reduce the filter criterias to a minimum, meaning only your period filter should be active.

Execute this query version and let me know...

Regards

PC

[mohdshadab_shaikh]

Hi Prabhith,

The query is based on which info provider (multiprovider/cube/dso ..etc) if its multiprovider then you it would require complications since it would be taking data from many info provders.

Are you using BI Accelerators (BIA), if yes then you also need to check if BIA index were re-created or not after latest data loading.

If its possible for you to give the same selection in the info provider (if its only one) in the BW system and see how much data it is extracting and same way for previous periods. This would atleast clear this point that there is no issue with the amount of the data. Then we can focus on the query design side with its variables.

Hope this helps.

[RamanKorrapati]

Hi,

Please check data volume for current period, if data volume was huge, it may take time.

Try to execute bex report thru RSRT, hit Exexute+debug.

That would help you to find the cause.

Thanks

[Former Member]

Prabhith,

Try to place a break-point in cmod for the variable and check how the code responds.

Regards,

Venkatesh

[Former Member]

Hi Prabhith,

Try to run this query in RSRT with display SQL debug option. Check there value for Fiscal year period taking correctly or not.

Regards

Sunil