OWC 2003 - Problems with copy paste of large amount of data

[Lucas Campos]

Hi,
I'm using OWC 11 against an Analysis Services database.
I have a query that gets 11.000 rows and I'd like to copy it in order to
analyze in Excel.

Using OWC 2000, the usage of memory grows up to eat all my memory (physical
and virtual). I extended the virtual memory to 1GB of disk but I received a
message of out of memory (Task Manager says it was using more than 900MB of
virtual memory)

Now, with OWC 2003 the memory usage keeps low, but it has been processing
the copy command for more than 12 hours eating the CPU (100% all the time).
My OLAP server is not suffering, it has enough free memory and the CPU is
laughing. The problem seems to be at the client.
I know 11.000 rows is a big number, but 12 hours is too much.
Is there any thing (may be configuration) I can adjust?

Thanks a lot

Lucas C

 

[Wei-Dong Xu [MSFT]]

Hi Lucas,

It will be appreciated which office web component you use for this scenario. Does the long delay happen during retrieving the data from Analysis
service to PivotTable control?

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Lucas]

The delay happened when I make a Copy operation selecting all Pivot Table
Data (11.000 rows).
Here I explain a bit more.

I used to work with OWC 9 (2000) and the problem with this query is that it
consumes a lot of memory (all my memory) and finally the application fails
(it get more than 1GB of virtual memory) when I tried to make a Copy/Paste
operation in order to paste this data in Excel.
This was a problem but not the point of this Post.

Now, I'm using OWC 2003 and I'm trying to make the same query against the
same OLAP database. When I make Copy operation, memory doesn't grow to much
(just 70 MB), but it took more than one day to solve the operation and allow
me to Paste this data in Excel.

I now 11.000 rows (shown in the Pivot Table) is a big number, but 24 hours
seems to be excessive.
The Analysis services server doesn't seem to be affected in CPU or memory
usage.

Thanks a lot

Lucas C

Hi Lucas,

It will be appreciated which office web component you use for this

scenario. Does the long delay happen during retrieving the data from
Analysis

 

[Bluetooth]

Excel uses very different strategy when generating MDX query that runs
against OLAP server. So what happens when you copy "live" data from OWC to
exec is only the pivot table definition gets copied and then MDX is
re-executed.
The most notable difference is when you have a lot of empty tuples. OWC
first computes an axis as a session set, with empty ones filtered out and
then executes an MDX using ths (already computed) set. Generally this is
much faster than Excel's MDX.
Unfortunately the strategy used by OWC has some flaws (as we discovered
recently), so when you use custom rollup dimensions you may end up with
innacurate results in OWC, while the same pivot table computed by Excel is
fine (although slower).

The fundamental issue both in OWC and Excel is they do not use
NonEmptyCrossJoin, using CrossJoin instead, so when you nest two or more
dimensions on one axis, the intermediate results of CrossJoin operation are
huge and fill up client memory very quickly.

HTH
Szymon Slupik
CDN S.A.
Krakow, Poland

 

[Lucas]

The memory problem doesn't happened with OWC 11 (2003), but it take more
than one day to get Data copied in clipboard.
My Pivot Table has rows with data from two different dimensions (Producer
(100 rows) and Product (15000) ).
I have 11.000 rows with data (not empty cells).
Do you mean that when I make a Copy operation, OWC Joins them and retrieve
1.500.000 rows? (including empty topples) and this is the cause of memory
consumption and a DAY of delay?

Is there any thing to do?

We are not using " custom rollup dimensions ".

Thank you very much

LucasC

Excel uses very different strategy when generating MDX query that runs
against OLAP server. So what happens when you copy "live" data from OWC to
exec is only the pivot table definition gets copied and then MDX is
re-executed.
The most notable difference is when you have a lot of empty tuples. OWC
first computes an axis as a session set, with empty ones filtered out and
then executes an MDX using ths (already computed) set. Generally this is
much faster than Excel's MDX.
Unfortunately the strategy used by OWC has some flaws (as we discovered
recently), so when you use custom rollup dimensions you may end up with
innacurate results in OWC, while the same pivot table computed by Excel is
fine (although slower).

The fundamental issue both in OWC and Excel is they do not use
NonEmptyCrossJoin, using CrossJoin instead, so when you nest two or more
dimensions on one axis, the intermediate results of CrossJoin operation are
huge and fill up client memory very quickly.

HTH
Szymon Slupik
CDN S.A.
Krakow, Poland

The delay happened when I make a Copy operation selecting all Pivot Table
Data (11.000 rows).
Here I explain a bit more.

I used to work with OWC 9 (2000) and the problem with this query is that it
consumes a lot of memory (all my memory) and finally the application fails
(it get more than 1GB of virtual memory) when I tried to make a Copy/Paste
operation in order to paste this data in Excel.
This was a problem but not the point of this Post.

Now, I'm using OWC 2003 and I'm trying to make the same query against the
same OLAP database. When I make Copy operation, memory doesn't grow to much
(just 70 MB), but it took more than one day to solve the operation and allow
me to Paste this data in Excel.

I now 11.000 rows (shown in the Pivot Table) is a big number, but 24 hours
seems to be excessive.
The Analysis services server doesn't seem to be affected in CPU or memory
usage.

Thanks a lot

Lucas C



"Wei-Dong Xu [MSFT]" <[email protected]> escribió en el mensaje

Hi Lucas,

It will be appreciated which office web component you use for this

scenario. Does the long delay happen during retrieving the data from
Analysis

service to PivotTable control?

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Wei-Dong Xu [MSFT]]

Hi Lucas,

Thank you for replying!

So far as I know on this issue, I'd suggest you may set the "Large Level Threshold" property for the troubleshooting which may help some on this
issue. This property of SQL server analysis service determines the point at which a level is too large to be sent to the client application in a single
piece and the default value is 1000. You may set it to 5000, then test it.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Bluetooth]

OK I thought you press the "export to Excel" button on your OWC, which
exports "live" pivot table to Excel. This way you would be hitting Excel
bottleneck with inefficient.

If you copy your data via clipboard (Ctrl+A/select all, Ctrl+C/copy,
Ctrl+V/paste) it jjust copies the values, so nothing gets executed on the
Excel side.

But still what happens in OWC is CrossJoin instead of NonEmptyCrossJoin.
Whether this is executed on the client or on the server is up to the PTS
(Pivot Table Service, the OLAP client component) but can happen on the
client and its temporary resultset (1.500.000 rows) can eat up your memory.
Have seen this several times before.

You may try to play with Execution Location property and force the query to
execute on the server, as described here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnolap/html/largelev2.asp

HTH
Szymon Slupik, CDN S.A.
Krakow, Poland

The memory problem doesn't happened with OWC 11 (2003), but it take more
than one day to get Data copied in clipboard.
My Pivot Table has rows with data from two different dimensions (Producer
(100 rows) and Product (15000) ).
I have 11.000 rows with data (not empty cells).
Do you mean that when I make a Copy operation, OWC Joins them and retrieve
1.500.000 rows? (including empty topples) and this is the cause of memory
consumption and a DAY of delay?

Is there any thing to do?

We are not using " custom rollup dimensions ".

Thank you very much

LucasC

Excel uses very different strategy when generating MDX query that runs
against OLAP server. So what happens when you copy "live" data from OWC to
exec is only the pivot table definition gets copied and then MDX is
re-executed.
The most notable difference is when you have a lot of empty tuples. OWC
first computes an axis as a session set, with empty ones filtered out and
then executes an MDX using ths (already computed) set. Generally this is
much faster than Excel's MDX.
Unfortunately the strategy used by OWC has some flaws (as we discovered
recently), so when you use custom rollup dimensions you may end up with
innacurate results in OWC, while the same pivot table computed by Excel is
fine (although slower).

The fundamental issue both in OWC and Excel is they do not use
NonEmptyCrossJoin, using CrossJoin instead, so when you nest two or more
dimensions on one axis, the intermediate results of CrossJoin operation are
huge and fill up client memory very quickly.

HTH
Szymon Slupik
CDN S.A.
Krakow, Poland

The delay happened when I make a Copy operation selecting all Pivot Table
Data (11.000 rows).
Here I explain a bit more.

I used to work with OWC 9 (2000) and the problem with this query is

that
it

consumes a lot of memory (all my memory) and finally the application fails
(it get more than 1GB of virtual memory) when I tried to make a Copy/Paste
operation in order to paste this data in Excel.
This was a problem but not the point of this Post.

Now, I'm using OWC 2003 and I'm trying to make the same query against the
same OLAP database. When I make Copy operation, memory doesn't grow to much
(just 70 MB), but it took more than one day to solve the operation and allow
me to Paste this data in Excel.

I now 11.000 rows (shown in the Pivot Table) is a big number, but 24 hours
seems to be excessive.
The Analysis services server doesn't seem to be affected in CPU or memory
usage.

Thanks a lot

Lucas C



"Wei-Dong Xu [MSFT]" <[email protected]> escribió en el mensaje
Hi Lucas,

It will be appreciated which office web component you use for this
scenario. Does the long delay happen during retrieving the data from
Analysis
service to PivotTable control?

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no
rights.

 

[Lucas]

That's right I'm using copy/paste operation.

OWC 9 (2000), seems to consumes a lot of memory, but NOW OWC 11 (2003)
doesn't
The excesive time continue to be a problem.
Execution Location doesn't help because execution time is still huge and CPU
usage at 100% on the client machine is still a problem.
I didn't test with "Large Level Threshold" yet. Is this the property that
you can set in connection strings?

Is there any way to make this more efficient (just taking Non Empty Cross
Join)?

Thanks Wei-Dong Xu and Bluetooth

OK I thought you press the "export to Excel" button on your OWC, which
exports "live" pivot table to Excel. This way you would be hitting Excel
bottleneck with inefficient.

If you copy your data via clipboard (Ctrl+A/select all, Ctrl+C/copy,
Ctrl+V/paste) it jjust copies the values, so nothing gets executed on the
Excel side.

But still what happens in OWC is CrossJoin instead of NonEmptyCrossJoin.
Whether this is executed on the client or on the server is up to the PTS
(Pivot Table Service, the OLAP client component) but can happen on the
client and its temporary resultset (1.500.000 rows) can eat up your memory.
Have seen this several times before.

You may try to play with Execution Location property and force the query to
execute on the server, as described here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnolap/html/largelev2.asp

HTH
Szymon Slupik, CDN S.A.
Krakow, Poland

The memory problem doesn't happened with OWC 11 (2003), but it take more
than one day to get Data copied in clipboard.
My Pivot Table has rows with data from two different dimensions (Producer
(100 rows) and Product (15000) ).
I have 11.000 rows with data (not empty cells).
Do you mean that when I make a Copy operation, OWC Joins them and retrieve
1.500.000 rows? (including empty topples) and this is the cause of memory
consumption and a DAY of delay?

Is there any thing to do?

We are not using " custom rollup dimensions ".

Thank you very much

LucasC


OWC
to

Excel

is

fine (although slower).

The fundamental issue both in OWC and Excel is they do not use
NonEmptyCrossJoin, using CrossJoin instead, so when you nest two or more
dimensions on one axis, the intermediate results of CrossJoin

operation
are

huge and fill up client memory very quickly.

HTH
Szymon Slupik
CDN S.A.
Krakow, Poland


The delay happened when I make a Copy operation selecting all Pivot Table
Data (11.000 rows).
Here I explain a bit more.

I used to work with OWC 9 (2000) and the problem with this query is that
it
consumes a lot of memory (all my memory) and finally the application fails
(it get more than 1GB of virtual memory) when I tried to make a Copy/Paste
operation in order to paste this data in Excel.
This was a problem but not the point of this Post.

Now, I'm using OWC 2003 and I'm trying to make the same query

against
the

same OLAP database. When I make Copy operation, memory doesn't grow to
much
(just 70 MB), but it took more than one day to solve the operation and
allow
me to Paste this data in Excel.

I now 11.000 rows (shown in the Pivot Table) is a big number, but 24 hours
seems to be excessive.
The Analysis services server doesn't seem to be affected in CPU or memory
usage.

Thanks a lot

Lucas C



"Wei-Dong Xu [MSFT]" <[email protected]> escribió en el mensaje
Hi Lucas,

It will be appreciated which office web component you use for this
scenario. Does the long delay happen during retrieving the data from
Analysis
service to PivotTable control?

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no
rights.

 

[Bluetooth]

Lucas,

What I would do now is to check exactly what MDX gets generated, since we
still do not know what exactly happens when you perform copy to clipboard
operation. This would help us isolate if the problem is OWC or PTS/OLAP/MDX
bound.

To do this try to capture the MDX to a log file.
Somewhere in your code when OWC control is instantiated, you have the
connection string property. Add a log file to it, it should look like this:

<OBJECT class=ptexec classid="clsid:0002E552-0000-0000-C000-000000000046"
height=251
id=pt style="HEIGHT: 251px; LEFT: 0px; TOP: 0px; WIDTH: 502px" width=502>
<PARAM NAME="ConnectionString" VALUE='Data Source=localhost;Initial
Catalog="Foodmart 2000";Provider=MSOLAP.2;Client Cache Size=25;Log
File=C:\MDXOWC.LOG'>

Then have a look at the log file produced and take the MDX. There should be
three important sections. Two should begin with a creation of session sets
for the axis definitions:
CREATE SESSION SET [<your cube>].somerandomname AS
and then the select over the defined axis
SELECT NON EMPTY.....FROM [<your cube>]

Extract the mdx and paste it to the MDXBuilder application and observe your
time/processor/memory. If it is still way over your expectations, we should
switch to microsoft.public.sqlserver.olap newsgroup for resolution. If on
the other hand MDXBuilder will come up with expected results quickly, then
it means we have some internal OWC problem that should be further examined.

Currently there is no way to force OWC to use NonEmptyCrossJoin.
Unfortunately :-((((( I've had a long discussion on this with OWC team, this
is very important issue, but can't tell you more at this time (NDA).

HTH
Szymon Slupik, CDN S.A.
Krakow, Poland

That's right I'm using copy/paste operation.

OWC 9 (2000), seems to consumes a lot of memory, but NOW OWC 11 (2003)
doesn't
The excesive time continue to be a problem.
Execution Location doesn't help because execution time is still huge and CPU
usage at 100% on the client machine is still a problem.
I didn't test with "Large Level Threshold" yet. Is this the property that
you can set in connection strings?

Is there any way to make this more efficient (just taking Non Empty Cross
Join)?

Thanks Wei-Dong Xu and Bluetooth

OK I thought you press the "export to Excel" button on your OWC, which
exports "live" pivot table to Excel. This way you would be hitting Excel
bottleneck with inefficient.

If you copy your data via clipboard (Ctrl+A/select all, Ctrl+C/copy,
Ctrl+V/paste) it jjust copies the values, so nothing gets executed on the
Excel side.

But still what happens in OWC is CrossJoin instead of NonEmptyCrossJoin.
Whether this is executed on the client or on the server is up to the PTS
(Pivot Table Service, the OLAP client component) but can happen on the
client and its temporary resultset (1.500.000 rows) can eat up your memory.
Have seen this several times before.

You may try to play with Execution Location property and force the query to
execute on the server, as described here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnolap/html/largelev2.asp

HTH
Szymon Slupik, CDN S.A.
Krakow, Poland

out
and

this

is Excel is
that

grow

to

much
(just 70 MB), but it took more than one day to solve the operation and
allow
me to Paste this data in Excel.

I now 11.000 rows (shown in the Pivot Table) is a big number, but 24
hours
seems to be excessive.
The Analysis services server doesn't seem to be affected in CPU or
memory
usage.

Thanks a lot

Lucas C



"Wei-Dong Xu [MSFT]" <[email protected]> escribió en el
mensaje
Hi Lucas,

It will be appreciated which office web component you use for this
scenario. Does the long delay happen during retrieving the data from
Analysis
service to PivotTable control?

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no
rights.

 

[Lucas]

I get some part of the Log (nice trick), because I'd have to wait to much
time (days) to get a full one and because in 5 minutes it reached 2.5 MB of
hard disk space. All I have here is a lot of sentences as you described me
CREATE SESSION SET .... and SELECT NON EMPTY.....FROM
Most of them ara composed from other ones. Can we guess (dreaming a little)
that all that the OWC makes here (eating CPU for hours and hours) is to
complete a huge query?

I paste here the last part of the "cut" Log

IEXPLORE:2876 28/11/2003 02:15:25 p.m. MDX CREATE SESSION
SET [Sales by
Product].[{609EE91E-A381-49E2-A9F1-CFB572485309}Pivot1Axis1Set1070] AS
'
{
[{609EE91E-A381-49E2-A9F1-CFB572485309}Pivot1Axis1Set1069],
FILTER(
CROSSJOIN(
{ [Producer].[All].[XXXX S.A.]},
AddCalculatedMembers(
DESCENDANTS(
[Product].[Section].&[1].&[25].&[31].&[0].&[PPPPPP].&[OOOOOOO],
[Product].[Code MSP]
)
)
),
NOT ISEMPTY(([Measures].[Units]))
OR
NOT ISEMPTY(([Measures].[Importe Neto]))
OR
NOT ISEMPTY(([Measures].[UN Oferta]))
OR
NOT ISEMPTY(([Measures].[Importe Oferta]))
OR
NOT ISEMPTY(([Measures].[UN Faltantes]))
)
}
'

IEXPLORE:2876 28/11/2003 02:15:25 p.m. MDX SELECT
NON EMPTY [{609EE91E-A381-49E2-A9F1-CFB572485309}Pivot1Axis1Set1070]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
{
[Measures].[Units],
[Measures].[Importe Neto],
[Measures].[UN Oferta],
[Measures].[Importe Oferta],
[Measures].[UN Faltantes]
}
ON ROWS
FROM [Sales by Product]

Lucas,

What I would do now is to check exactly what MDX gets generated, since we
still do not know what exactly happens when you perform copy to clipboard
operation. This would help us isolate if the problem is OWC or PTS/OLAP/MDX
bound.

To do this try to capture the MDX to a log file.
Somewhere in your code when OWC control is instantiated, you have the
connection string property. Add a log file to it, it should look like this:

<OBJECT class=ptexec classid="clsid:0002E552-0000-0000-C000-000000000046"
height=251
id=pt style="HEIGHT: 251px; LEFT: 0px; TOP: 0px; WIDTH: 502px" width=502>
<PARAM NAME="ConnectionString" VALUE='Data Source=localhost;Initial
Catalog="Foodmart 2000";Provider=MSOLAP.2;Client Cache Size=25;Log
File=C:\MDXOWC.LOG'>

Then have a look at the log file produced and take the MDX. There should be
three important sections. Two should begin with a creation of session sets
for the axis definitions:
CREATE SESSION SET [<your cube>].somerandomname AS
and then the select over the defined axis
SELECT NON EMPTY.....FROM [<your cube>]

Extract the mdx and paste it to the MDXBuilder application and observe your
time/processor/memory. If it is still way over your expectations, we should
switch to microsoft.public.sqlserver.olap newsgroup for resolution. If on
the other hand MDXBuilder will come up with expected results quickly, then
it means we have some internal OWC problem that should be further examined.

Currently there is no way to force OWC to use NonEmptyCrossJoin.
Unfortunately :-((((( I've had a long discussion on this with OWC team, this
is very important issue, but can't tell you more at this time (NDA).

HTH
Szymon Slupik, CDN S.A.
Krakow, Poland

That's right I'm using copy/paste operation.

OWC 9 (2000), seems to consumes a lot of memory, but NOW OWC 11 (2003)
doesn't
The excesive time continue to be a problem.
Execution Location doesn't help because execution time is still huge and CPU
usage at 100% on the client machine is still a problem.
I didn't test with "Large Level Threshold" yet. Is this the property that
you can set in connection strings?

Is there any way to make this more efficient (just taking Non Empty Cross
Join)?

Thanks Wei-Dong Xu and Bluetooth


query
to

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnolap/html/largelev2.asp

from
OWC tuples.
OWC this or
more grow operation
and

but

24

hours
seems to be excessive.
The Analysis services server doesn't seem to be affected in CPU or
memory
usage.

Thanks a lot

Lucas C



"Wei-Dong Xu [MSFT]" <[email protected]> escribió en el
mensaje
Hi Lucas,

It will be appreciated which office web component you use for this
scenario. Does the long delay happen during retrieving the data from
Analysis
service to PivotTable control?

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and

confers
no

rights.

 

[Bluetooth]

Yes, I think this proves we have a non efficient query problem and it has
nothing to do with OWC itself (only the fact OWC constructs your inefficient
MDX). I've had a lot of problems with that. OLAP seems to be pretty smart,
but OWC implementation just kills it in many cases.

Have you tried to paste the queries into the MDXBuilder application to see
how long they execute? This would ultimately show if the query itself is the
real problem or there is something else on the OWC side. One thing that is
not clear to me is you say it takes very long time to execute Ctrl+C
command. My common sense tells me at the time you press Ctrl+C your query
should have already been executed... or may be OWC is re-executing it?
Anyway executing the queries in MDXBuilder would help pinpoint the problem.

Looking at the log file, there are clearly several things visible. One
important is the Filter(Crossjoin(...), NOT ISEMPTY() ) construct that is
definitely slow. Have a look at NonEmptyCrossJoin function description in
SQL Books On Line. They discuss very similar example and why the NECJ is
much more effective. I hope MS will finally enable NECJ in Office 12, since
current implementation in Office 10 and 11 just kills...

So if you finaly determine (by executing the queries within MDXBuilder) they
are the problem, turn to olap newsgroup for some hints on optimizing them.
We have no control over MDX syntax here (since it is OWC generated), but
setting some options like client cache, large level threshold and execution
location may help. Let's see what MDX experts will say. I subscribe to the
olap newsgroup as well, so we may continue the thread there, just focusing
more on the olap things.

Best
Szymon Slupik, CDN S.A.
Krakow, Poland

I get some part of the Log (nice trick), because I'd have to wait to much
time (days) to get a full one and because in 5 minutes it reached 2.5 MB of
hard disk space. All I have here is a lot of sentences as you described me
CREATE SESSION SET .... and SELECT NON EMPTY.....FROM
Most of them ara composed from other ones. Can we guess (dreaming a little)
that all that the OWC makes here (eating CPU for hours and hours) is to
complete a huge query?

I paste here the last part of the "cut" Log

IEXPLORE:2876 28/11/2003 02:15:25 p.m. MDX CREATE SESSION
SET [Sales by
Product].[{609EE91E-A381-49E2-A9F1-CFB572485309}Pivot1Axis1Set1070] AS
'
{
[{609EE91E-A381-49E2-A9F1-CFB572485309}Pivot1Axis1Set1069],
FILTER(
CROSSJOIN(
{ [Producer].[All].[XXXX S.A.]},
AddCalculatedMembers(
DESCENDANTS(
[Product].[Section].&[1].&[25].&[31].&[0].&[PPPPPP].&[OOOOOOO],
[Product].[Code MSP]
)
)
),
NOT ISEMPTY(([Measures].[Units]))
OR
NOT ISEMPTY(([Measures].[Importe Neto]))
OR
NOT ISEMPTY(([Measures].[UN Oferta]))
OR
NOT ISEMPTY(([Measures].[Importe Oferta]))
OR
NOT ISEMPTY(([Measures].[UN Faltantes]))
)
}
'

IEXPLORE:2876 28/11/2003 02:15:25 p.m. MDX SELECT
NON EMPTY [{609EE91E-A381-49E2-A9F1-CFB572485309}Pivot1Axis1Set1070]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
{
[Measures].[Units],
[Measures].[Importe Neto],
[Measures].[UN Oferta],
[Measures].[Importe Oferta],
[Measures].[UN Faltantes]
}
ON ROWS
FROM [Sales by Product]

Lucas,

What I would do now is to check exactly what MDX gets generated, since we
still do not know what exactly happens when you perform copy to clipboard
operation. This would help us isolate if the problem is OWC or PTS/OLAP/MDX
bound.

To do this try to capture the MDX to a log file.
Somewhere in your code when OWC control is instantiated, you have the
connection string property. Add a log file to it, it should look like this:

<OBJECT class=ptexec classid="clsid:0002E552-0000-0000-C000-000000000046"
height=251
id=pt style="HEIGHT: 251px; LEFT: 0px; TOP: 0px; WIDTH: 502px" width=502>
<PARAM NAME="ConnectionString" VALUE='Data Source=localhost;Initial
Catalog="Foodmart 2000";Provider=MSOLAP.2;Client Cache Size=25;Log
File=C:\MDXOWC.LOG'>

Then have a look at the log file produced and take the MDX. There should be
three important sections. Two should begin with a creation of session sets
for the axis definitions:
CREATE SESSION SET [<your cube>].somerandomname AS
and then the select over the defined axis
SELECT NON EMPTY.....FROM [<your cube>]

Extract the mdx and paste it to the MDXBuilder application and observe your
time/processor/memory. If it is still way over your expectations, we should
switch to microsoft.public.sqlserver.olap newsgroup for resolution. If on
the other hand MDXBuilder will come up with expected results quickly, then
it means we have some internal OWC problem that should be further examined.

Currently there is no way to force OWC to use NonEmptyCrossJoin.
Unfortunately :-((((( I've had a long discussion on this with OWC team, this
is very important issue, but can't tell you more at this time (NDA).

HTH
Szymon Slupik, CDN S.A.
Krakow, Poland

That's right I'm using copy/paste operation.

OWC 9 (2000), seems to consumes a lot of memory, but NOW OWC 11 (2003)
doesn't
The excesive time continue to be a problem.
Execution Location doesn't help because execution time is still huge

and
CPU

usage at 100% on the client machine is still a problem.
I didn't test with "Large Level Threshold" yet. Is this the property that
you can set in connection strings?

Is there any way to make this more efficient (just taking Non Empty Cross
Join)?

Thanks Wei-Dong Xu and Bluetooth


"Bluetooth" <[email protected]> escribió en el mensaje
OK I thought you press the "export to Excel" button on your OWC, which
exports "live" pivot table to Excel. This way you would be hitting Excel
bottleneck with inefficient.

If you copy your data via clipboard (Ctrl+A/select all, Ctrl+C/copy,
Ctrl+V/paste) it jjust copies the values, so nothing gets executed

on
the

Excel side.

But still what happens in OWC is CrossJoin instead of NonEmptyCrossJoin.
Whether this is executed on the client or on the server is up to the PTS
(Pivot Table Service, the OLAP client component) but can happen on the
client and its temporary resultset (1.500.000 rows) can eat up your
memory.
Have seen this several times before.

You may try to play with Execution Location property and force the query
to
execute on the server, as described here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnolap/html/largelev2.asp

take
more that
runs filtered
out query
is but

CPU

or

memory
usage.

Thanks a lot

Lucas C



"Wei-Dong Xu [MSFT]" <[email protected]> escribió en el
mensaje
Hi Lucas,

It will be appreciated which office web component you use

for
this

scenario. Does the long delay happen during retrieving the

data
from

Analysis
service to PivotTable control?

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers
no
rights.