I need some suggestions/help with our drill through reports. We are using Hyperion 188.8.131.52 and the cube is ASO.
We have drill through reports set up in Essbase studio for drilling down from Essbase to Oracle database. It takes too long (like 30 mins for fetching 1000 records) and the query is simple.
What are the changes that we can do to bring down this time. Please advice.
By default Studio uses a lot of joins in the query. Often you can optimize the query using custom SQL to reduce the time . For example, I current have a client I'm working at where the drill through to EBS JE detail was taking a long time, but going to a vew that was optimized and removing the joins in the drill through report, it now runs in under 10 seconds.
Thanks for the quick reply. Even we are using custom query with couple of unavoidable joins between fact and dim tables. Is there any other settings that we can do to improve the performance ? Please advice.
work with your DBA giving him the query with the values you are passing in the $$dimension-value$$ columns and see if he can help you do further optimization and/or add indexes for performance. It is difficult to talk in generalities on something like this
We tried optimizing the drill through SQL query but actually when we run the directly in TOAD it takes *23 secs* but when we do drill through on the same intersection
it took more than 25 mins. Following is our query structure :
FROM "Table A" cp_594
INNER JOIN "Table B" cp_595 ON (cp_594.key = cp_595.key)
WHERE (Upper(cp_595.*"Dim1"*) in (select Upper(CHILD) from (SELECT * FROM DIM_TABLE_1 where CUBE = 'ALL') WHERE CONNECT_BY_ISLEAF = 1 START WITH PARENT = $$Dim1$$ CONNECT BY PRIOR CHILD = PARENT UNION ALL select Upper(CHILD) from DIM_TABLE_1 where CUBE = 'ALL' AND REPLACE('GL_'||CHILD, 'GL_IC_', 'IC_') = $$Dim1$$))
And ----same for 5 more dimensions
Can you suggest some improvement ? Please advice.