I have a BEx query that when I try to bring in two hierarchies it is spending forever on the OLAP level. It selects the records from the database in 30 seconds. It then loops through an autogenerated program millions of times to aggregate to the hierarchy level and then renders 500 cells in rsrt.
This process takes 16 minutes and it reads 3 millions lines from the database. The problem I have is why does this happen and how can I help improve it? If I use EPM which uses MDX on the same query and tables it runs in 1 minute.
This seems to be how OLAP processes the data. It is running quickly but it is parsing a lot of data to aggregate two hierarchies.
Hi,
It is difficult to try to come into conclusions here, just based with these information. It would be required to review in more details. I suggest you to record a ST12 trace with your team and review it afterwards to see where the performance is happening.
Also, run the query and check the statistics, read KBA 1707239 - Analyze Query Performance with Statistics Data in RSRT and verify KBAs 1681396 - Query Performance, 1879725 - Query Memory Consumption.
Thanks,
Diego Seben