PIVOT Data Export Performance Problems

[Velociraptor]

I need to be able to use the OWC PivotTable control as a general Pivoting
engine (I am using SQL 2000 and Access projects don't have a crosstab query
capability). The format of the PivotTable component is completely unusable
for reports (and forms IMHO) so I need raw pivoted data to supply reports
that have been formatted in a reasonable way. I have finally managed to work
out how to do this programmatically:

1) Open an Access form that contains a PivotTable component
2) Programmatically setup the data source, the row headings, columns and
data fields
3) Issue PivotTable.ActiveView.COPY to copy the contents of the crosstab
view to the clipboard
4) Create an OWC SpreadSheet component
5) Paste the clipboard into the Spreadsheet
6) Write the Spreadsheet as CSV to a file
7) Read the CSV file and parse it into a table

All this because Microsoft don't give us a simple means of extracting the
data from the PivotTable. (Come on MS - an ADO.Recordset property for
PivotTable would do the trick!)

My problem is that step 3 TAKES FOREVER.Copying a view that has just 8
columns and less than 200 rows to the clipboard takes more than a minute on
a twin 1.7GHz Dell with 1.5GB RAM! This is less than 5K of raw data. I need
to extract data with more than 10,000 rows. The performance of the PivotData
control in generating the crosstab is exemplary, why is the extraction so
slow.

Dows anybody have any ideas as to how I can extract data quickly from a a
PivotTable component?

Paul

 

[Velociraptor]

Should have said: OWC version 11.

 

[Wei-Dong Xu [MSFT]]

Hi Velociraptor,

Based on my experience on this issue, there is no copy method for
PivotTable.ActiveView from OWC11 or OWC10. The ActiveView is a property of
PivotTable whose type is PivotView class. So far as I konw, PivotView class
has no copy method. It will be appreciated you can send me your codes so
that I can test them for you.

Furthermore, if the copy method gives a slow performance, I'd suggset you
can save the data of PivotTable to xml and then parse the xml to the table.
You will need to create one temp xml file on the disk. Besides, XML5 can
give you a very good performance on this in office 2003.

Please feel free to let me know if you have any 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.

 

[Velociraptor]

Thanks Wei-Dong

Unfortunately your suggestion for exporting to XML does not work. The
XMLdata property of the PivotTable object encodes information about the
schema, and does not contain the raw data. As far as I know this is the only
XML property or method available.

Sorry about the confusion regarding the copy method - my fault. In my
original post, the code for copying pivoted data to clipboard should have
been:

PivotTableObject.Copy PivotTableObject.ActiveView

Issuing this command after you have supplied the row, column and data fields
copies the pivoted results to the clipboard in a manner that allows it to be
pasted into a spreadsheet. As far as I know, this is the only way of getting
at the raw data, but, as noted previously, the Copy method is extremely
slow.

I cannot understand why Microsoft have made it almost impossible to get at
the data in the PivotTable control in an efficient way. I can
programmatically recover data from individual data columns and rows in the
pivot results but this method is, again, unbelievably slow. (About two cells
per second on a twin 1.7GHz system). Microsoft seem to have made an
assumption that the PivotTable component is an adequate way of of producing
and displaying crosstabulated data. The production is fast enough and is not
in question but the display of the data is not suitable for reports. Also,
this approach prevents post-processing of crosstabulated data.

What is needed is a mechanism that allows the PivotTable component to expose
an ADODB Recordset object that contains the pivoted data and/or supply a
method that writes pivoted data to a table. This would allow post-processing
of results and simple contruction of reports, forms and queries that mimic
the capability available in native Access JET database crosstab queries.

This has been a problem for programmers migrating from JET to SQL 2000 for
some time. Microsoft have gone almost all the way with the OWC PivotTable
object but seem to have stumbled at the last hurdle. Lest you think that I
am exaggerating the problem, type [Crosstab "SQL Server"] into Google you
will get more than 1500 hits with many developers mentioning and complaining
about this issue.

If you feel that it would be helpful, I will forward my code to you - please
let me know.

Kind regards

Paul Shearing
Software Development Services
Caterham, England

 

[Wei-Dong Xu [MSFT]]

Hi Velociraptor,

Thank you for replying and the detailed information about this issue!

From my understanding to your issue now, you can build your own XML file for the active view if the performance of Copy method is very slow for
you. I'd sugget you can build the intermediate xml file with the help from the KB article 294782, then spreadsheet can import the XML and display
the data. In this way, you can also directly parse the xml file to csv for futher processing.

Though this article focuses on how to get the data to html, you can easily design the xml file and rewrite the code to produce xml following the
direction of this KB. Please go to:
294782 HOWTO: Extract Cell Aggregate Values From the Office XP PivotTable
http://support.microsoft.com/?id=294782

Please feel free to let me know if you have any 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.

--------------------

From: "Velociraptor" <[email protected]>
Newsgroups: microsoft.public.office.developer.web.components
References: <[email protected]> <[email protected]> <RFJAGBJlDHA.2464 @cpmsftngxa06.phx.gbl>
Subject: Re: PIVOT Data Export Performance Problems
Date: Fri, 17 Oct 2003 13:53:50 +0100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Lines: 52
Message-ID: <[email protected]>
NNTP-Posting-Host: userdf016.dsl.pipex.com
X-Trace: 1066395230 news.dial.pipex.com 256 62.190.229.16
X-Complaints-To: (e-mail address removed)
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!130.59.10.21.MISMATCH!irazu.switch.ch!switch.ch! news.tele.dk!news.tele.dk!small.news.tele.dk!lnewsoutpeer01.lnd.ops.eu.uu.net!lnewsinpeer01.lnd.ops.eu.uu.net!lnewspost00.lnd.ops.eu.uu.net!
emea.uu.net!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.office.developer.web.components:7261
X-Tomcat-NG: microsoft.public.office.developer.web.components

Thanks Wei-Dong

Unfortunately your suggestion for exporting to XML does not work. The
XMLdata property of the PivotTable object encodes information about the
schema, and does not contain the raw data. As far as I know this is the only
XML property or method available.

Sorry about the confusion regarding the copy method - my fault. In my
original post, the code for copying pivoted data to clipboard should have
been:

PivotTableObject.Copy PivotTableObject.ActiveView

Issuing this command after you have supplied the row, column and data fields
copies the pivoted results to the clipboard in a manner that allows it to be
pasted into a spreadsheet. As far as I know, this is the only way of getting
at the raw data, but, as noted previously, the Copy method is extremely
slow.

I cannot understand why Microsoft have made it almost impossible to get at
the data in the PivotTable control in an efficient way. I can
programmatically recover data from individual data columns and rows in the
pivot results but this method is, again, unbelievably slow. (About two cells
per second on a twin 1.7GHz system). Microsoft seem to have made an
assumption that the PivotTable component is an adequate way of of producing
and displaying crosstabulated data. The production is fast enough and is not
in question but the display of the data is not suitable for reports. Also,
this approach prevents post-processing of crosstabulated data.

What is needed is a mechanism that allows the PivotTable component to expose
an ADODB Recordset object that contains the pivoted data and/or supply a
method that writes pivoted data to a table. This would allow post-processing
of results and simple contruction of reports, forms and queries that mimic
the capability available in native Access JET database crosstab queries.

This has been a problem for programmers migrating from JET to SQL 2000 for
some time. Microsoft have gone almost all the way with the OWC PivotTable
object but seem to have stumbled at the last hurdle. Lest you think that I
am exaggerating the problem, type [Crosstab "SQL Server"] into Google you
will get more than 1500 hits with many developers mentioning and complaining
about this issue.

If you feel that it would be helpful, I will forward my code to you - please
let me know.

Kind regards

Paul Shearing
Software Development Services
Caterham, England

 

[Velociraptor]

Wei-Dong

Many thanks for your reply.

I have used the information in KB article 294782 to derive a solution that
bypasses the production of the spreadsheet altogether and writes data
directly from the Pivot control to a temporary table.

Although this works and is significantly faster than the Copy to clipboard
mechanism, it is still very slow. The maximum that I can get out of the
control is ten rows of pivoted data per second. (This is with only five
columns per row, Twin 1.7GHz xeons and loads of RAM). For small datasets it
is ok but for larger ones this solution will simply take too long. I believe
that Microsoft need to look at this urgently. 3rd-party components such as
the AGS Crosstab for SQL server perform the same crosstab operation and
write my entire test data to a table in less than one second.

Thanks again

Paul

Hi Velociraptor,

Thank you for replying and the detailed information about this issue!

From my understanding to your issue now, you can build your own XML file

for the active view if the performance of Copy method is very slow for

you. I'd sugget you can build the intermediate xml file with the help from

the KB article 294782, then spreadsheet can import the XML and display

the data. In this way, you can also directly parse the xml file to csv for futher processing.

Though this article focuses on how to get the data to html, you can easily

design the xml file and rewrite the code to produce xml following the

direction of this KB. Please go to:
294782 HOWTO: Extract Cell Aggregate Values From the Office XP PivotTable
http://support.microsoft.com/?id=294782

Please feel free to let me know if you have any 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.

@cpmsftngxa06.phx.gbl>
Subject: Re: PIVOT Data Export Performance Problems
Date: Fri, 17 Oct 2003 13:53:50 +0100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Lines: 52
Message-ID: <[email protected]>
NNTP-Posting-Host: userdf016.dsl.pipex.com
X-Trace: 1066395230 news.dial.pipex.com 256 62.190.229.16
X-Complaints-To: (e-mail address removed)
Path:

cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!130.59.10.21.MISMATCH!irazu.switch.ch!switch.ch!news.tele.dk!news.tele.dk!small.news.tele.dk!lnewsoutpeer01.lnd.ops.eu.uu.ne
t!lnewsinpeer01.lnd.ops.eu.uu.net!lnewspost00.lnd.ops.eu.uu.net!

emea.uu.net!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.office.developer.web.components:7261
X-Tomcat-NG: microsoft.public.office.developer.web.components

Thanks Wei-Dong

Unfortunately your suggestion for exporting to XML does not work. The
XMLdata property of the PivotTable object encodes information about the
schema, and does not contain the raw data. As far as I know this is the only
XML property or method available.

Sorry about the confusion regarding the copy method - my fault. In my
original post, the code for copying pivoted data to clipboard should have
been:

PivotTableObject.Copy PivotTableObject.ActiveView

Issuing this command after you have supplied the row, column and data fields
copies the pivoted results to the clipboard in a manner that allows it to be
pasted into a spreadsheet. As far as I know, this is the only way of getting
at the raw data, but, as noted previously, the Copy method is extremely
slow.

I cannot understand why Microsoft have made it almost impossible to get at
the data in the PivotTable control in an efficient way. I can
programmatically recover data from individual data columns and rows in the
pivot results but this method is, again, unbelievably slow. (About two cells
per second on a twin 1.7GHz system). Microsoft seem to have made an
assumption that the PivotTable component is an adequate way of of producing
and displaying crosstabulated data. The production is fast enough and is not
in question but the display of the data is not suitable for reports. Also,
this approach prevents post-processing of crosstabulated data.

What is needed is a mechanism that allows the PivotTable component to expose
an ADODB Recordset object that contains the pivoted data and/or supply a
method that writes pivoted data to a table. This would allow post-processing
of results and simple contruction of reports, forms and queries that mimic
the capability available in native Access JET database crosstab queries.

This has been a problem for programmers migrating from JET to SQL 2000 for
some time. Microsoft have gone almost all the way with the OWC PivotTable
object but seem to have stumbled at the last hurdle. Lest you think that I
am exaggerating the problem, type [Crosstab "SQL Server"] into Google you
will get more than 1500 hits with many developers mentioning and complaining
about this issue.

If you feel that it would be helpful, I will forward my code to you - please
let me know.

Kind regards

Paul Shearing
Software Development Services
Caterham, England

 

[Wei-Dong Xu [MSFT]]

Hi Velociraptor,

Thank you for replying and the detailed information about the performance of PivotTable!

I think this is a great idea for a future product enhancement. I'd recommend that you forward the recommendation to the Microsoft Wish Program:

1. World Wide Web - To send a comment or suggestion via the Web, use one of the following methods:
Visit the following Microsoft Product Feedback Web site
http://register.microsoft.com/mswish/suggestion.asp
and then complete and submit the form.

2. E-mail - To send comments or suggestions via e-mail, use the following Microsoft Wish Program e-mail address, (e-mail address removed).

3. FAX - To send comments or suggestions via FAX, use the following Microsoft FAX number, (425) 936-7329.
NOTE : Address the FAX to the attention of the Microsoft Wish Program.

4. US Mail - To send comments or suggestions via US Mail, use the following Microsoft mailing address:

Microsoft Corporation
Attn. Microsoft Wish Program
One Microsoft Way
Redmond, WA 98052-6399

Don't hesitate to let me know if you have any questions.

Thank you once more 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.
--------------------
MORE INFORMATION
Each product suggestion is read by a member of our product feedback team, classified for easy access, and routed to the product or service team
to drive Microsoft product and/or service improvements. Because we receive an abundance of suggestions (over 69,000 suggestions a year!) we
can't guarantee that each request makes it into a final product or service. But we can tell you that each suggestion has been received and is
being reviewed by the team that is most capable of addressing it.