I have an issue using drillthough functionality from AS to an Oracle database.

Drillthrough works fine in most situations. However, where a specific
Parent-Child dimension is included, drillthroughs at certain levels cause an
error.

If I use the Oracle OLEDB provider, I get "Connection To Server Lost" and
the OLAP service actaullay stops and has to be restarted.

If I use the Microsoft provider for Oracle, I get "Provider Error", but the
OLAP Service is not halted.

If I import the data from Oracle into SQL Server, and build the cube from
that, I get no Drillthrough problems.

The problem always occurs with certain dimension members at certain levels,
but never occurs with others. My gut feeling is that it is something to do
with the number of levels down to the base data, as it is a ragged hierarchy.

The only other slightly unusual thing about the dimension is it has non-leaf
data.

Can anyone shed any light?

Using the Microsoft provider on XP, I get a more informative message, which
makes the problem clear:

Unable to drill through. Data Source Provider error. ORA-01795: maximum
number of expressions in a list is 1000.

The parent-child dimension involved has over 7000 members. I guess what
happens is that the MDX DRILLTHROUGH query is converted to a standard SQL
query that uses an IN clause to list all the participating members including
the selected one and below. If these number more than 1000, Oracle throws an
error.

This is a massive limitation for us, as it means we can't use Drill Through
to Oracle at all. The parent-child dimension represents the organizational
structure, and is present in almost every query.

I'm sure other people must've hit this problem. Has anyone found a solution?

"Dave Morrow" wrote: