cancel
Showing results for 
Search instead for 
Did you mean: 

Drill down problem

Former Member
0 Kudos

Hi

I have a query where in the rows I have a 36 month structure. In the columns I have 5 keyfigures and these are populated by cell formulas.

My query works fine when in the selection screen values are selected. Selection variables are many like cocode, plant, profit center, sales Org, division, customer. After the query result comes, i am able to use the drill down for all characteristics except for customer.

If I start the drill down for customer, the query stalls for more than 30 minutes and sometimes closes with time out?

How to make the drill down by customers work? Any suggestion on aggregates or any other tweaking of query?

The query worked fine in the Development system where the number of customers are not much.

Any suggestions welcomed.

Ram

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Its worth looking at the query stats to confirm where the time is being spent - is it front-end time, OLAP time, DB time?

If it is DB time - yes, review you aggregates. You might also have your DBA review the queries and see if any additional indices on master data or dimension tables might help.

You could look at splitting the data up into separate years and use a multiprovider, to take advantage of the parallel processing. Since the query goes after 3 yrs of data, this would be a good application, but that is a significant retooling effort, so explore all your other options first.

Former Member
0 Kudos

Hi

I checked the statistics through ST03 and found that maximum time is OLAP time. Now I am able to drill down to customer level after a couple of filters. Still the OLAP time is almost 85% when I drill down to customer level. What should I do to reduce the OLAP time?

Ram

Former Member
0 Kudos

My guess is you have a lot of customers. Is customer in a line-item dimension? The one thing you may want to consider is using VBA or Table Interface for your cell level calculations. If you do that instead of the cell editor, it's not working on retrieving information from multiple times for each cell and the cell level calculations are happening post query run. This will probably involve a quite a bit of VBA to handle the multiple drilldown scenarios, but will help performance quite a bit.

Former Member
0 Kudos

Hi Prakash

Yes The number of customers are many but i can not use VBA since the queries are to be run in portal and VBA code does not run in portal.

I already have some aggregates and probably will have to try new combinations. Since there are about 12 selection criteria, i told the client to narrow down to a small sizable number of customers before trying the drill down. The problem is because of the 36 month structure with cell formula with a number of if else conditions.

Ram