slow formatting in Bex

[Former Member]

Hi,

I am running a query with about 16000 records, the dimension table has almost same amount. I got really slow when it is formatting the excel sheet. It almost take 20 minites, the report is very basic report, no calculation,no condition/exception, is that normal? any idea?

Thanks

[Former Member · Accepted solution]

Hi,

Use Toce RSRT with option 'Execute + Debug' and also select Display Statastics data.

Now check the value for QTIMEDB.If is taking alomost same time of how much time taken by query to execute, then you need to work to increase the performance by Indexing on that Dimension and Define that as Line item dimension.

With rgds,

Anil Kuamr Sharma .P

[former_member184494]

Richard,

Welcome to SDN...

Run the query in RSRT and note the following.

OLAP time ( will be minimal since there is no processing as per your post)

DBTIME ( As Anil Kumar Sharma has mentioned )

To reduce DBTIME ..

1. Cache the query ( run it in the background using Reporting agent and turn on cache using RSRCACHE .. but then caching is not very effective for very large queries)

2. Create aggregates ( this way you can save time on accessing only query specific data )

3. Create secondary indexes on the relevant dimensions ( Again as previously mentioned )

4. See if you can keep most dimensions in the query in free characteristics and then allow the user to drill down ( If the report format is not chiselled in stone )

Arun

Assign points if found useful

[Former Member]

Thanks for reply.

When I run the report in RSRT, it is not too long to run, QTIMEOLAP = 25.089 QTIMEDB=1.47.

However in excel, it take long time for "formatting results area...".

Thanks

[Former Member]

We're having the same problem. It's not the database or OLAP time, it's the front-end, when the data's been transferred to Excel. If Bex Properties > 'Adjust formatting after data refresh' is turned off it runs instantly but the query's left unformatted (including decimals and %'s), and then running a macro to format runs much quicker than Bex to look after the formatting, but doing it via a macro isn't a nice solution because it needs to handle the column filtering users may do on the query.

Any other solutions?

Regards,

Julie

[Former Member]

Richard,

This happens, when you execute the query for the intial times in a System. Over the time, it should not take so long.

Hope this helps!

[Former Member]

maybe below links on query optimization could help..

/people/prakash.darji/blog/2006/01/26/query-optimization

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

/people/vikash.agrawal/blog/2006/04/26/in-what-147sequence148-olap-processor-look-for-query-relevant-data

cheers,

Vishvesh