Hi! I'm running into a problem while trying to pull a large data set
back from Analysis Services 2000 to SQL Server 2000 via a linked
server (OLAP OLEDB driver).

When returning a rowset of ~ 100,000 records, everything performs fine
and the results are returned in about 5 minutes. However, when I add
dimensions to increase the granularity (basically to produce a detail
report – i.e. each returned row corresponds to one row from the fact
table) to bring the total record count to ~ 700,000, the query runs
for several hours (or just a few minutes some times) and then quits
with a "The provider ran out of memory" error. One strange thing is
that the MSOLAP process only consumes the CPU for ~ 10 minutes right
at the beginning, and the rest of the time is consumed by the SQL
Server process.

So, the question is, am I just trying to pull back too much data? If
so, what can I do to get around this problem? I need to produce
reports at this granularity level, and the data is not permanently
warehoused anywhere besides the cube itself. Is the problem SQL
Server?

The server in question is fairly well equipped: 4 x 1.4Ghz PIII Xeon,
8GB RAM, Windows 2000 Advanced SP4, SQL Server 2000 Enterprise SP3,
and Analysis Services SP3.

Any help is *much* appreciated!

Thanks,
Chris

Anyone have any thoughts on this? I did some more research, and I
have tracked the problem back the the OLAP for OLEDB provider. It
appears that adding dimensions, rather than increasing the rowcount,
causes the reduction in performance. I believe this is because
dimension levels are returned as TEXT (ADO Type: Binary) to the
calling app, and this is a tough type to cast to a VARCHAR (i.e.
String).

I also played around with using ADOMD to process the resultset, hoping
that the provider would not have to process/cast levels I am not using
(which is most of them, of course), but it yielded identical
performance.

George, are you out there?

Thanks,
Chris

chris (AT) networkpipe (DOT) com (Chris Aiken) wrote in message news:<694b31c6.0311121741.2484daa3 (AT) posting (DOT) google.com>...