6 Replies Latest reply: Feb 8, 2013 6:20 PM by Andy1785 RSS

    Drill Through reports takes too long

    Andy1785
      Hi all,

      I need some suggestions/help with our drill through reports. We are using Hyperion 11.1.1.3 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.

      Thanks.
        • 1. Re: Drill Through reports takes too long
          GlennS_3
          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.
          • 2. Re: Drill Through reports takes too long
            Andy1785
            Hi Glenn,

            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.

            Thanks
            • 3. Re: Drill Through reports takes too long
              GlennS_3
              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
              • 4. Re: Drill Through reports takes too long
                Andy1785
                Hi Glenn,

                Thanks again. Let me talk to DBA's if that does'nt help i'll post the query and try to provide more information.
                Appreciate your help.

                Thanks
                • 5. Re: Drill Through reports takes too long
                  Andy1785
                  Hi Glenn,

                  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 :

                  (SELECT *
                  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.

                  Thanks