Apache OpenOffice Wiki - User contributions [en]

User Experience/Proposal DataPilot byIBM

2009-06-25T08:45:18Z

Helenyue: /* Summary */

{{User Experience Community}}

= DataPilot UX Design Proposal by IBM =

__TOC__

== Summary ==
Symphony team start to develop/enhance DataPilot feature since Symphony 1.1, and still working to improve it. To improve user experience to use DataPilot, we designed&developed new user interface in the releases, including:
<ul>
<li> New DataPilot table: Show a predefined new table to users avoid the meaningless table which have nothing.</li>
<li>DataPilot panel: Users can work with fields there and update the changes automatically.</li>
<li>Filter panel: The filter panel make users can filter items of fields directly in the DataPilot table. Some useful functions are added here, e.g. search items, show only the current item. Users can also set to show "Top n" items in this panel.</li>
<li>Dialogs: Improve the information architecture and UI controls of the dialogs, e.g. field options, sort.</li>
<li>Editing and reading mode: Provide two modes for the different scenarios: edit the DataPilot table and show the table to others.</li>
</ul>

== New DataPilot table ==
When users create a new DataPilot table, before they add any fields to table, a predefined table will be there. The table give users some clues about the areas of the table and how to start the work.

{| class="prettytable"
| [[Image:Symphony NewDataPilotTable.gif‎]]

|}

== DataPilot panel ==
The DataPilot panel will list all fields from the source data. The field will turn to be a button when users hover on it. Users can drag&drop the fields to the areas to assign them. Users can also modify the sequences or remove the fields via drag&drop in the panel.
{| class="prettytable"
| [[Image:Symphony HoverOnField.gif]]

|}

The changes on DataPilot panel will be updated automatically, so that users can get the result immediately. Users can also disable it and manually update by themselves when there are large amount of data.
{| class="prettytable"
| [[Image:Symphony EditDataPilotTable.gif‎‎]]

|}

== Filter panel ==
Users can show or hide items via the filer panel which can be accessed directly on the table. "Top n" is integrated on this panel so that users can show the top/bottom n items quickly. 
If users hide any items(or set Top n) on a filed, the filter button beside the filed within table will turn to a special style, so that users can know which items have been filtered quickly.

{| class="prettytable"
| [[Image:Symphony DataPilot Filter.gif‎]]

|}

Users can locate to certain item quickly via the search function of the panel. It is useful when there are lots of items of a field. They can also show/hide certain item quickly when they just want to make a single selection there. 

{| class="prettytable"
| [[Image:Symphony DataPilot Filter 2.gif‎]]

|}

== Dialogs ==
The information architecture and UI controls of the dialogs have been improved, e.g. field options, sort.

{| class="prettytable"
| [[Image:Symphony DataPilot Dialog.gif‎]]

|}

== Editing and reading mode ==
When users focus on the DataPilot table, it will be editing mode and users can get the DataPilot panel and fields buttons within table automatically. Users can modify the table conveniently via these UI. When users focus out of the table, it will be reading mode and the DataPilot panel and fields buttons within table will be hided automatically, so that users can show the DataPilot table to others clearly.

Editing mode: DataPilot panel and field buttons within table will be shown automatically.
{| class="prettytable"
| [[Image:Symphony EditDataPilotTable.gif‎‎]]

|}

Reading mode: DataPilot panel and field buttons within table will be hided automatically.
{| class="prettytable"
| [[Image:Symphony DataPilot TableReadingMode.gif‎‎‎]]

|}

Calc/Proposal DataPilot byIBM

2009-06-23T15:16:00Z

Helenyue:

= DataPilot Performance Enhancement I =

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 50%"

|- align="left"
| colspan="2" bgcolor="#cccccc" | '''Specification Status'''
|-
| width="150" | '''Author''' || width="150" |[[User:wangxum|Wang Xu Ming]], [[User:helenyue|Helen Yue]]
|-
| width="150" | '''Last Change''' || width ="150"|See wiki history
|-
|}
== Background==
DataPilot is a critical function to Spreadsheet users. In Symphony 1.2 and 1.3 release, IBM Lotus Symphony Spreadsheet team developed the new UI and some new features for DataPilot based on OpenOffice 1.1 code base and also with some code merged from OpenOffice 2.4.

With our new UI, users can drag and drop to generate the DataPilot table based on the page/column/row/data fields, and change the structure easily. Furthermore, the function like DataPilot usually will process thousands of data records. Performance is one of our major focus.

During the development, test team found that there was serious performance problem when user create or update a DataPilot table. This specification will describe what performance issues we have found, and the enhancement we did on the algorithm that created and output a DataPilot table. Although the code base (2.4 and 3.1) is different, the solution should also apply to the new code base.

We are currently merging the code to OpenOffice.org 3.1 code base and will continue the enhancement work.

== Test Result ==

'''Low performance when update a datapilot table'''

We defined 6 scenario, and test it to a sample DataPilot table which have 5000 rows as data source.

Test environment: Hardware: IBM T30 CPU: 2.4 GHz Memory:1.0 GB Operation System: Window XP SP2

Below table is the test result to OpenOffice 3.0.0. Scenario 3 and 4 showed big delays to generate the table, while another office product can complete all the scenarios within 3 seconds.

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 70%"
|-
| width="150" bgcolor="#dddddd" | '''Test Scenario'''|| width="150" bgcolor="#dddddd"|'''Open Office 3.0.0'''
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action: - Add Product to Row
|| 3.15s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:- Product ID to Data
|| 3.06s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product to Row
|| 25.28s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product ID to Data
|| 44.21s
|-
| Page: 1 Column: 2 Row: 2 Data: 3
Action:- Add SalesRep to Data
|| 6.28s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:-Change the function of Revenue from Sum to Max
|| 6.03s
|-
|}
'''Crash'''

Insert two field into row area ( Each field have about 1000 members ),causes freezing and crash.

== Code Analysis ==
First, create a complex layout DataPilot table, and use rational quantify create a report for its performance.

[[Image:quantify report.jpg]]

From above table, we can see the top three "F time (% of Focus)" functions:

* new
* OutputDevice::DrawLine
* SfxItemSet::==

Then we scan and debug the code, get three root causes for the performance issue:

'''Allocate a lot of abundant data'''

For a simple datapilot table:

[[Image:simple dptable.jpg]]

Member A1 in L1 field will create an array for all members {B1,B2,B3}. But only B1 is visible and valid.

'''Allocate too much memories'''

Every member's data is stored in a big structure.

'''Set too many times of border styles for output area'''

Some borders are set twice or more.

== Solution Description ==

'''Data Source buffer'''

A document stored a source buffer array. Every table have a buffer id. The datapilot table can use the same id if they have same data source.

In the buffer, the members of a field can be identified by an id( the sorted index ).

Then in the output table's algorithm the ScDPItemData structure is replaced by an id.

'''Only allocate visible member'''

'''Enhance the algorithm of setting border style'''

Calc/Proposal DataPilot byIBM

2009-06-23T15:11:06Z

Helenyue: /* Solution */

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 50%"

|- align="left"
| colspan="2" bgcolor="#cccccc" | '''Specification Status'''
|-
| width="150" | '''Author''' || width="150" |[[User:wangxum|Wang Xu Ming]], [[User:helenyue|Helen Yue]]
|-
| width="150" | '''Last Change''' || width ="150"|See wiki history
|-
|}
== Background==
DataPilot is a critical function to Spreadsheet users. In Symphony 1.2 and 1.3 release, IBM Lotus Symphony Spreadsheet team developed the new UI and some new features for DataPilot based on OpenOffice 1.1 code base and also with some code merged from OpenOffice 2.4.

With our new UI, users can drag and drop to generate the DataPilot table based on the page/column/row/data fields, and change the structure easily. Furthermore, the function like DataPilot usually will process thousands of data records. Performance is one of our major focus.

During the development, test team found that there was serious performance problem when user create or update a DataPilot table. This specification will describe what performance issues we have found, and the enhancement we did on the algorithm that created and output a DataPilot table. Although the code base (2.4 and 3.1) is different, the solution should also apply to the new code base.

We are currently merging the code to OpenOffice.org 3.1 code base and will continue the enhancement work.

== Test Result ==

'''Low performance when update a datapilot table'''

We defined 6 scenario, and test it to a sample DataPilot table which have 5000 rows as data source.

Test environment: Hardware: IBM T30 CPU: 2.4 GHz Memory:1.0 GB Operation System: Window XP SP2

Below table is the test result to OpenOffice 3.0.0. Scenario 3 and 4 showed big delays to generate the table, while another office product can complete all the scenarios within 3 seconds.

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 70%"
|-
| width="150" bgcolor="#dddddd" | '''Test Scenario'''|| width="150" bgcolor="#dddddd"|'''Open Office 3.0.0'''
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action: - Add Product to Row
|| 3.15s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:- Product ID to Data
|| 3.06s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product to Row
|| 25.28s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product ID to Data
|| 44.21s
|-
| Page: 1 Column: 2 Row: 2 Data: 3
Action:- Add SalesRep to Data
|| 6.28s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:-Change the function of Revenue from Sum to Max
|| 6.03s
|-
|}
'''Crash'''

Insert two field into row area ( Each field have about 1000 members ),causes freezing and crash.

== Code Analysis ==
First, create a complex layout DataPilot table, and use rational quantify create a report for its performance.

[[Image:quantify report.jpg]]

From above table, we can see the top three "F time (% of Focus)" functions:

* new
* OutputDevice::DrawLine
* SfxItemSet::==

Then we scan and debug the code, get three root causes for the performance issue:

'''Allocate a lot of abundant data'''

For a simple datapilot table:

[[Image:simple dptable.jpg]]

Member A1 in L1 field will create an array for all members {B1,B2,B3}. But only B1 is visible and valid.

'''Allocate too much memories'''

Every member's data is stored in a big structure.

'''Set too many times of border styles for output area'''

Some borders are set twice or more.

== Solution Description ==

'''Data Source buffer'''

A document stored a source buffer array. Every table have a buffer id. The datapilot table can use the same id if they have same data source.

In the buffer, the members of a field can be identified by an id( the sorted index ).

Then in the output table's algorithm the ScDPItemData structure is replaced by an id.

'''Only allocate visible member'''

'''Enhance the algorithm of setting border style'''

Calc/Proposal DataPilot byIBM

2009-06-23T15:10:03Z

Helenyue: /* Analysis */

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 50%"

|- align="left"
| colspan="2" bgcolor="#cccccc" | '''Specification Status'''
|-
| width="150" | '''Author''' || width="150" |[[User:wangxum|Wang Xu Ming]], [[User:helenyue|Helen Yue]]
|-
| width="150" | '''Last Change''' || width ="150"|See wiki history
|-
|}
== Background==
DataPilot is a critical function to Spreadsheet users. In Symphony 1.2 and 1.3 release, IBM Lotus Symphony Spreadsheet team developed the new UI and some new features for DataPilot based on OpenOffice 1.1 code base and also with some code merged from OpenOffice 2.4.

With our new UI, users can drag and drop to generate the DataPilot table based on the page/column/row/data fields, and change the structure easily. Furthermore, the function like DataPilot usually will process thousands of data records. Performance is one of our major focus.

During the development, test team found that there was serious performance problem when user create or update a DataPilot table. This specification will describe what performance issues we have found, and the enhancement we did on the algorithm that created and output a DataPilot table. Although the code base (2.4 and 3.1) is different, the solution should also apply to the new code base.

We are currently merging the code to OpenOffice.org 3.1 code base and will continue the enhancement work.

== Test Result ==

'''Low performance when update a datapilot table'''

We defined 6 scenario, and test it to a sample DataPilot table which have 5000 rows as data source.

Test environment: Hardware: IBM T30 CPU: 2.4 GHz Memory:1.0 GB Operation System: Window XP SP2

Below table is the test result to OpenOffice 3.0.0. Scenario 3 and 4 showed big delays to generate the table, while another office product can complete all the scenarios within 3 seconds.

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 70%"
|-
| width="150" bgcolor="#dddddd" | '''Test Scenario'''|| width="150" bgcolor="#dddddd"|'''Open Office 3.0.0'''
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action: - Add Product to Row
|| 3.15s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:- Product ID to Data
|| 3.06s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product to Row
|| 25.28s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product ID to Data
|| 44.21s
|-
| Page: 1 Column: 2 Row: 2 Data: 3
Action:- Add SalesRep to Data
|| 6.28s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:-Change the function of Revenue from Sum to Max
|| 6.03s
|-
|}
'''Crash'''

Insert two field into row area ( Each field have about 1000 members ),causes freezing and crash.

== Code Analysis ==
First, create a complex layout DataPilot table, and use rational quantify create a report for its performance.

[[Image:quantify report.jpg]]

From above table, we can see the top three "F time (% of Focus)" functions:

* new
* OutputDevice::DrawLine
* SfxItemSet::==

Then we scan and debug the code, get three root causes for the performance issue:

'''Allocate a lot of abundant data'''

For a simple datapilot table:

[[Image:simple dptable.jpg]]

Member A1 in L1 field will create an array for all members {B1,B2,B3}. But only B1 is visible and valid.

'''Allocate too much memories'''

Every member's data is stored in a big structure.

'''Set too many times of border styles for output area'''

Some borders are set twice or more.

== Solution ==

'''Data Source buffer'''

A document stored a source buffer array. Every table have a buffer id. The datapilot table can use the same id if they have same data source.

In the buffer, the members of a field can be identified by an id( the sorted index ).

Then in the output table's algorithm the ScDPItemData structure is replaced by an id.

'''Only allocate visible member'''

'''Enhance the algorithm of setting border style'''

Calc/Proposal DataPilot byIBM

2009-06-23T15:09:27Z

Helenyue:

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 50%"

|- align="left"
| colspan="2" bgcolor="#cccccc" | '''Specification Status'''
|-
| width="150" | '''Author''' || width="150" |[[User:wangxum|Wang Xu Ming]], [[User:helenyue|Helen Yue]]
|-
| width="150" | '''Last Change''' || width ="150"|See wiki history
|-
|}
== Background==
DataPilot is a critical function to Spreadsheet users. In Symphony 1.2 and 1.3 release, IBM Lotus Symphony Spreadsheet team developed the new UI and some new features for DataPilot based on OpenOffice 1.1 code base and also with some code merged from OpenOffice 2.4.

With our new UI, users can drag and drop to generate the DataPilot table based on the page/column/row/data fields, and change the structure easily. Furthermore, the function like DataPilot usually will process thousands of data records. Performance is one of our major focus.

During the development, test team found that there was serious performance problem when user create or update a DataPilot table. This specification will describe what performance issues we have found, and the enhancement we did on the algorithm that created and output a DataPilot table. Although the code base (2.4 and 3.1) is different, the solution should also apply to the new code base.

We are currently merging the code to OpenOffice.org 3.1 code base and will continue the enhancement work.

== Test Result ==

'''Low performance when update a datapilot table'''

We defined 6 scenario, and test it to a sample DataPilot table which have 5000 rows as data source.

Test environment: Hardware: IBM T30 CPU: 2.4 GHz Memory:1.0 GB Operation System: Window XP SP2

Below table is the test result to OpenOffice 3.0.0. Scenario 3 and 4 showed big delays to generate the table, while another office product can complete all the scenarios within 3 seconds.

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 70%"
|-
| width="150" bgcolor="#dddddd" | '''Test Scenario'''|| width="150" bgcolor="#dddddd"|'''Open Office 3.0.0'''
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action: - Add Product to Row
|| 3.15s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:- Product ID to Data
|| 3.06s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product to Row
|| 25.28s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product ID to Data
|| 44.21s
|-
| Page: 1 Column: 2 Row: 2 Data: 3
Action:- Add SalesRep to Data
|| 6.28s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:-Change the function of Revenue from Sum to Max
|| 6.03s
|-
|}
'''Crash'''

Insert two field into row area ( Each field have about 1000 members ),causes freezing and crash.

== Analysis ==
First, create a complex layout DataPilot table, and use rational quantify create a report for its performance.

[[Image:quantify report.jpg]]

From above table, we can see the top three "F time (% of Focus)" functions:

* new
* OutputDevice::DrawLine
* SfxItemSet::==

Then we scan and debug the code, get three root causes for the performance issue:

'''Allocate a lot of abundant data'''

For a simple datapilot table:

[[Image:simple dptable.jpg]]

Member A1 in L1 field will create an array for all members {B1,B2,B3}. But only B1 is visible and valid.

'''Allocate too much memories'''

Every member's data is stored in a big structure.

'''Set too many times of border styles for output area'''

Some borders are set twice or more.

== Solution ==

'''Data Source buffer'''

A document stored a source buffer array. Every table have a buffer id. The datapilot table can use the same id if they have same data source.

In the buffer, the members of a field can be identified by an id( the sorted index ).

Then in the output table's algorithm the ScDPItemData structure is replaced by an id.

'''Only allocate visible member'''

'''Enhance the algorithm of setting border style'''

Calc/Proposal DataPilot byIBM

2009-06-23T15:08:14Z

Helenyue: /* Analyzing result */

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 50%"

|- align="left"
| colspan="2" bgcolor="#cccccc" | '''Specification Status'''
|-
| width="150" | '''Author''' || width="150" |[[User:wangxum|Wang Xu Ming]]
|-
| width="150" | '''Last Change''' || width ="150"|See wiki history
|-
|}
== Background==
DataPilot is a critical function to Spreadsheet users. In Symphony 1.2 and 1.3 release, IBM Lotus Symphony Spreadsheet team developed the new UI and some new features for DataPilot based on OpenOffice 1.1 code base and also with some code merged from OpenOffice 2.4.

With our new UI, users can drag and drop to generate the DataPilot table based on the page/column/row/data fields, and change the structure easily. Furthermore, the function like DataPilot usually will process thousands of data records. Performance is one of our major focus.

During the development, test team found that there was serious performance problem when user create or update a DataPilot table. This specification will describe what performance issues we have found, and the enhancement we did on the algorithm that created and output a DataPilot table. Although the code base (2.4 and 3.1) is different, the solution should also apply to the new code base.

We are currently merging the code to OpenOffice.org 3.1 code base and will continue the enhancement work.

== Test Result ==

'''Low performance when update a datapilot table'''

We defined 6 scenario, and test it to a sample DataPilot table which have 5000 rows as data source.

Test environment: Hardware: IBM T30 CPU: 2.4 GHz Memory:1.0 GB Operation System: Window XP SP2

Below table is the test result to OpenOffice 3.0.0. Scenario 3 and 4 showed big delays to generate the table, while another office product can complete all the scenarios within 3 seconds.

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 70%"
|-
| width="150" bgcolor="#dddddd" | '''Test Scenario'''|| width="150" bgcolor="#dddddd"|'''Open Office 3.0.0'''
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action: - Add Product to Row
|| 3.15s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:- Product ID to Data
|| 3.06s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product to Row
|| 25.28s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product ID to Data
|| 44.21s
|-
| Page: 1 Column: 2 Row: 2 Data: 3
Action:- Add SalesRep to Data
|| 6.28s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:-Change the function of Revenue from Sum to Max
|| 6.03s
|-
|}
'''Crash'''

Insert two field into row area ( Each field have about 1000 members ),causes freezing and crash.

== Analysis ==
First, create a complex layout DataPilot table, and use rational quantify create a report for its performance.

[[Image:quantify report.jpg]]

From above table, we can see the top three "F time (% of Focus)" functions:

* new
* OutputDevice::DrawLine
* SfxItemSet::==

Then we scan and debug the code, get three root causes for the performance issue:

'''Allocate a lot of abundant data'''

For a simple datapilot table:

[[Image:simple dptable.jpg]]

Member A1 in L1 field will create an array for all members {B1,B2,B3}. But only B1 is visible and valid.

'''Allocate too much memories'''

Every member's data is stored in a big structure.

'''Set too many times of border styles for output area'''

Some borders are set twice or more.

== Solution ==

'''Data Source buffer'''

A document stored a source buffer array. Every table have a buffer id. The datapilot table can use the same id if they have same data source.

In the buffer, the members of a field can be identified by an id( the sorted index ).

Then in the output table's algorithm the ScDPItemData structure is replaced by an id.

'''Only allocate visible member'''

'''Enhance the algorithm of setting border style'''

Calc/Proposal DataPilot byIBM

2009-06-23T15:06:26Z

Helenyue: /* Test Data */

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 50%"

|- align="left"
| colspan="2" bgcolor="#cccccc" | '''Specification Status'''
|-
| width="150" | '''Author''' || width="150" |[[User:wangxum|Wang Xu Ming]]
|-
| width="150" | '''Last Change''' || width ="150"|See wiki history
|-
|}
== Background==
DataPilot is a critical function to Spreadsheet users. In Symphony 1.2 and 1.3 release, IBM Lotus Symphony Spreadsheet team developed the new UI and some new features for DataPilot based on OpenOffice 1.1 code base and also with some code merged from OpenOffice 2.4.

With our new UI, users can drag and drop to generate the DataPilot table based on the page/column/row/data fields, and change the structure easily. Furthermore, the function like DataPilot usually will process thousands of data records. Performance is one of our major focus.

During the development, test team found that there was serious performance problem when user create or update a DataPilot table. This specification will describe what performance issues we have found, and the enhancement we did on the algorithm that created and output a DataPilot table. Although the code base (2.4 and 3.1) is different, the solution should also apply to the new code base.

We are currently merging the code to OpenOffice.org 3.1 code base and will continue the enhancement work.

== Test Result ==

'''Low performance when update a datapilot table'''

We defined 6 scenario, and test it to a sample DataPilot table which have 5000 rows as data source.

Test environment: Hardware: IBM T30 CPU: 2.4 GHz Memory:1.0 GB Operation System: Window XP SP2

Below table is the test result to OpenOffice 3.0.0. Scenario 3 and 4 showed big delays to generate the table, while another office product can complete all the scenarios within 3 seconds.

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 70%"
|-
| width="150" bgcolor="#dddddd" | '''Test Scenario'''|| width="150" bgcolor="#dddddd"|'''Open Office 3.0.0'''
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action: - Add Product to Row
|| 3.15s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:- Product ID to Data
|| 3.06s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product to Row
|| 25.28s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product ID to Data
|| 44.21s
|-
| Page: 1 Column: 2 Row: 2 Data: 3
Action:- Add SalesRep to Data
|| 6.28s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:-Change the function of Revenue from Sum to Max
|| 6.03s
|-
|}
'''Crash'''

Insert two field into row area ( Each field have about 1000 members ),causes freezing and crash.

== Analyzing result ==
First, create a complex layout DataPilot table, and use rational quantify create a report for its performance.

[[Image:quantify report.jpg]]

From above table, we can see the top three "F time (% of Focus)" functions:

* new
* OutputDevice::DrawLine
* SfxItemSet::==

Then scan and debug the code, get three cause:

'''Allocate a lot of abundant data'''

For a simple datapilot table:

[[Image:simple dptable.jpg]]

Member A1 in L1 field will create an array for all members {B1,B2,B3}. But only B1 is visible and valid.

'''Allocate too much memories'''

Every member's data is stored in a big structure.

'''Set too many times of border styles for output area'''

Some borders are set twice or more.

== Solution ==

'''Data Source buffer'''

A document stored a source buffer array. Every table have a buffer id. The datapilot table can use the same id if they have same data source.

In the buffer, the members of a field can be identified by an id( the sorted index ).

Then in the output table's algorithm the ScDPItemData structure is replaced by an id.

'''Only allocate visible member'''

'''Enhance the algorithm of setting border style'''

Calc/Proposal DataPilot byIBM

2009-06-23T15:05:59Z

Helenyue: /* Problem Description */

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 50%"

|- align="left"
| colspan="2" bgcolor="#cccccc" | '''Specification Status'''
|-
| width="150" | '''Author''' || width="150" |[[User:wangxum|Wang Xu Ming]]
|-
| width="150" | '''Last Change''' || width ="150"|See wiki history
|-
|}
== Background==
DataPilot is a critical function to Spreadsheet users. In Symphony 1.2 and 1.3 release, IBM Lotus Symphony Spreadsheet team developed the new UI and some new features for DataPilot based on OpenOffice 1.1 code base and also with some code merged from OpenOffice 2.4.

With our new UI, users can drag and drop to generate the DataPilot table based on the page/column/row/data fields, and change the structure easily. Furthermore, the function like DataPilot usually will process thousands of data records. Performance is one of our major focus.

During the development, test team found that there was serious performance problem when user create or update a DataPilot table. This specification will describe what performance issues we have found, and the enhancement we did on the algorithm that created and output a DataPilot table. Although the code base (2.4 and 3.1) is different, the solution should also apply to the new code base.

We are currently merging the code to OpenOffice.org 3.1 code base and will continue the enhancement work.

== Test Data ==

'''Low performance when update a datapilot table'''

We defined 6 scenario, and test it to a sample DataPilot table which have 5000 rows as data source.

Test environment: Hardware: IBM T30 CPU: 2.4 GHz Memory:1.0 GB Operation System: Window XP SP2

Below table is the test result to OpenOffice 3.0.0. Scenario 3 and 4 showed big delays to generate the table, while another office product can complete all the scenarios within 3 seconds.

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 70%"
|-
| width="150" bgcolor="#dddddd" | '''Test Scenario'''|| width="150" bgcolor="#dddddd"|'''Open Office 3.0.0'''
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action: - Add Product to Row
|| 3.15s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:- Product ID to Data
|| 3.06s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product to Row
|| 25.28s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product ID to Data
|| 44.21s
|-
| Page: 1 Column: 2 Row: 2 Data: 3
Action:- Add SalesRep to Data
|| 6.28s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:-Change the function of Revenue from Sum to Max
|| 6.03s
|-
|}
'''Crash'''

Insert two field into row area ( Each field have about 1000 members ),causes freezing and crash.

== Analyzing result ==
First, create a complex layout DataPilot table, and use rational quantify create a report for its performance.

[[Image:quantify report.jpg]]

From above table, we can see the top three "F time (% of Focus)" functions:

* new
* OutputDevice::DrawLine
* SfxItemSet::==

Then scan and debug the code, get three cause:

'''Allocate a lot of abundant data'''

For a simple datapilot table:

[[Image:simple dptable.jpg]]

Member A1 in L1 field will create an array for all members {B1,B2,B3}. But only B1 is visible and valid.

'''Allocate too much memories'''

Every member's data is stored in a big structure.

'''Set too many times of border styles for output area'''

Some borders are set twice or more.

== Solution ==

'''Data Source buffer'''

A document stored a source buffer array. Every table have a buffer id. The datapilot table can use the same id if they have same data source.

In the buffer, the members of a field can be identified by an id( the sorted index ).

Then in the output table's algorithm the ScDPItemData structure is replaced by an id.

'''Only allocate visible member'''

'''Enhance the algorithm of setting border style'''

Calc/Proposal DataPilot byIBM

2009-06-23T14:59:43Z

Helenyue: /* Background */

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 50%"

|- align="left"
| colspan="2" bgcolor="#cccccc" | '''Specification Status'''
|-
| width="150" | '''Author''' || width="150" |[[User:wangxum|Wang Xu Ming]]
|-
| width="150" | '''Last Change''' || width ="150"|See wiki history
|-
|}
== Background==
DataPilot is a critical function to Spreadsheet users. In Symphony 1.2 and 1.3 release, IBM Lotus Symphony Spreadsheet team developed the new UI and some new features for DataPilot based on OpenOffice 1.1 code base and also with some code merged from OpenOffice 2.4.

With our new UI, users can drag and drop to generate the DataPilot table based on the page/column/row/data fields, and change the structure easily. Furthermore, the function like DataPilot usually will process thousands of data records. Performance is one of our major focus.

During the development, test team found that there was serious performance problem when user create or update a DataPilot table. This specification will describe what performance issues we have found, and the enhancement we did on the algorithm that created and output a DataPilot table. Although the code base (2.4 and 3.1) is different, the solution should also apply to the new code base.

We are currently merging the code to OpenOffice.org 3.1 code base and will continue the enhancement work.

== Problem Description ==

'''Low performance when update a datapilot table'''

Test team tested several operations to a sample DataPilot table which have 5000 rows data source.

Test environment: Hardware: IBM T30 CPU: 2.4 GHz Memory:1.0 GB Operation System: Window XP SP2

Below table is the test result to OpenOffice 3.0.0:

{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; border: 1px #cccccc solid; border-collapse: collapse; width: 50%"
|-
| width="150" bgcolor="#dddddd" | '''Test Scenario'''|| width="150" bgcolor="#dddddd"|'''Open Office 3.0.0'''
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action: - Add Product to Row
|| 3.15s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:- Product ID to Data
|| 3.06s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product to Row
|| 25.28s
|-
| Page: 1 Column: 3 Row: 3 Data: 1
Action:- Add Product ID to Data
|| 44.21s
|-
| Page: 1 Column: 2 Row: 2 Data: 3
Action:- Add SalesRep to Data
|| 6.28s
|-
| Page: 1 Column: 2 Row: 1 Data: 1
Action:-Change the function of Revenue from Sum to Max
|| 6.03s
|-
|}
'''Crash'''

Insert two field into row area ( Each field have about 1000 members ),causes freezing and crash.

== Analyzing result ==
First, create a complex layout DataPilot table, and use rational quantify create a report for its performance.

[[Image:quantify report.jpg]]

From above table, we can see the top three "F time (% of Focus)" functions:

* new
* OutputDevice::DrawLine
* SfxItemSet::==

Then scan and debug the code, get three cause:

'''Allocate a lot of abundant data'''

For a simple datapilot table:

[[Image:simple dptable.jpg]]

Member A1 in L1 field will create an array for all members {B1,B2,B3}. But only B1 is visible and valid.

'''Allocate too much memories'''

Every member's data is stored in a big structure.

'''Set too many times of border styles for output area'''

Some borders are set twice or more.

== Solution ==

'''Data Source buffer'''

A document stored a source buffer array. Every table have a buffer id. The datapilot table can use the same id if they have same data source.

In the buffer, the members of a field can be identified by an id( the sorted index ).

Then in the output table's algorithm the ScDPItemData structure is replaced by an id.

'''Only allocate visible member'''

'''Enhance the algorithm of setting border style'''

Calc/Proposal DataPilot byIBM

2009-06-19T09:25:40Z

Helenyue: New page: Background Problem Description Solution

Background

Problem Description

Solution

User Experience/Proroposal DataPilot byIBM

2009-06-12T08:46:22Z

Helenyue: New page: This is a placeholder for DataPilot by IBM

This is a placeholder for DataPilot by IBM