4 Replies Latest reply: Jan 16, 2013 1:25 PM by name_and_address_withheld RSS

    Drill Through Report Limit

    User305031
      Hello,

      I read few posts about Oracle having a 1000 member limit in IN clause. I wanted to know the following -

      1. Is that an Oracle RDBMS limit? Won't we encounter the error if we have SQL server as the back-end?
      2. Does the IN clause need to have > 1000 level 0 members to get the error? The reason I ask is I built a sample cube, recursive hierarchy, through studio and pulled a report containing more than 1500 records. I have to say the report was for a specific member having different period, amount values.

      It looks like there is an alternative to this by modifying the system generated SQL.

      Please answer my queries above.

      Thank you!
        • 1. Re: Drill Through Report Limit
          GlennS_3
          872230 wrote:
          Hello,

          I read few posts about Oracle having a 1000 member limit in IN clause. I wanted to know the following -

          1. Is that an Oracle RDBMS limit? Won't we encounter the error if we have SQL server as the back-end?
          This limit is in Essbase Studio not the RDBMS. When trying to build the SQL statement, it fails if it trys to put more than 100 members into the in clause

          2. Does the IN clause need to have > 1000 level 0 members to get the error? The reason I ask is I built a sample cube, recursive hierarchy, through studio and pulled a report containing more than 1500 records. I have to say the report was for a specific member having different period, amount values.
          Yes, but only if you are querying an upper level member that has more than 100 level 0 descendants. It does not matter that there are 10000s of level zero members in the cube, just what the qurey is trying to use. so if you have a parent that has 50 level 0 children you are fine. if you pull a parent with 200 level zero children you will have issues

          It looks like there is an alternative to this by modifying the system generated SQL.
          I've not found it yet. It always tries to create the in clause and when it hits 100 fails. The only thing I've been able to get to work is to limit the dimension to level 0 members or level 1 if I know none of them has more than 100 children
          • 2. Re: Drill Through Report Limit
            User305031
            Glenn,

            Thank you for responding. I posed the same question to Oracle support and here is what they had to say.

            "" Essbase studio don't have limitation,But it might be caused by the sql limitation as for recursive hierarchy, all members under the specified level will be included in "in" clause. It is impossible that there's no limitation imposed by sql. So, the 1000 member limitation might be from there. ""

            Here is the post wherein timtow mentions about Oracle limit - Issue with Drill Through Reports in Essbase Studio.
            I'm getting confused now as to which product has the limit and how to overcome it.


            Below is the alternate method I mentioned in my original post although I haven't implemented it..
            http://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit

            In your response, I'm assuming you are talking about 1000 members and not 100.

            Thank you!
            • 3. Re: Drill Through Report Limit
              GlennS_3
              Sorry, it was early morning for me when I posted, yes I meant 1000 not 100. I ran into the limit at a client and it seemed it was Essbase Studio trowing the error, but I was not able to prove it one way or the other. I'll have to look at the SQL you provided to figure out how it works
              • 4. Re: Drill Through Report Limit
                name_and_address_withheld
                Could it depend on the driver?