[SIP] Improve pivot chart with automatic update and much better export

[phohomi]

Please make sure you are familiar with the SIP process documented
here. The SIP will be numbered by a committer upon acceptance.

[SIP] Proposal for ...<title>

Motivation

Improve Pivot 2: we only use this chart, and we come from saiku (+ mondrian) which has been discontinued. So here is some of our experience.

Proposed Changes

Update the pivot chart automatically:

Add an automatic update option to the pivot chart and switch it on by default please. It is a much better user experience to see the results of each change that the user makes to the query right away. The "update chart" button and "your chart is not up to date" banner make you perform extra clicks, add visual clutter, and makes you explore the data in less incremental steps because you'd want to avoid to click on update chart after each and every change you make.
In case the current behaviour would have been chosen in order to limit the number of queries executed in the background: by making it optional, and/or giving a suggestion based on the time the query takes (which is monitored already) to switch the setting off if the dataset doesn't have subsecond latency.

Everybody uses Microsoft Excel: make export to excel nice and easy

Export to excel should be easy. It currently requires 3 clicks "..." > download > "export to pivoted csv". The excel current export is not the pivoted version of the data but the unaggregated source data. So a user has to create the pivot table again in excel, so the only alternative is to use the csv file: which is not meant for normal computer users. The only know excel, so please stop forcing this strange file onto the user. Also Excel doesn't play nice with CSV files, it requires your computer to be correctly configured (wich decimal sign is used on the system?). Finally, if there are multiple attributes in the rows, they are concatenated in the exported CSV file into one column. So the data is not well preserved. A code starting with zeroes is turned into a number, so that the leading zeroes are lost,...
Our users have been used to exporting to excel simply by pressing a single button in Saiku.

So please offer an export to excel:

• which doesn't require 3 clicks,
• gives the user the pivoted data that he just built and expects, and
• preserves the pivoted data as its displayed in the browser: so no concatenation of the row columns, no conversion of numerical strings to numbers wich causes it to lose leading zeroes, ...

If it must be an open standard, use the libre office spreadsheet format, that will already be a huge improvement over CSV.

The Apache Kylin project has incorporated Mondrian into its code base to support XMLA and support pivoting form excel, because the recognised that everybody uses excel, power bi, etc. (For people who have read the Mondrian in Action book, Julian Hyde is among the contributors of the mdx-kylin repo on github) However, kylin is not end-user oriented, and we are currently not planning to start using xmla in excel directly.

New or Changed Public Interfaces

New dependencies

Migration Plan and Compatibility

Rejected Alternatives

[rusackas]

Hi there. While we feel your pain and appreciate your willingness to participate in the improvement of Superset, I don't think this qualifies as a SIP. A SIP should be an implementation plan, rather than a group of feature requests. If you want to open these feature requests as GitHub Discussions, you're more than welcome to do so. If you want to implement these changes or propose their design in technical detail, please revise this Issue and we can revisit it.

[phohomi]

Hi,

No I do not; that technical detail is not for me, but I can summarise it though.

Automatic chart update: this one is easy

1. add a setting,
2. if the setting is on, and the query has been changed, then run it instead of showing an "your chart is not up to date" banner.
3. test, release, enjoy

Better Export:

1. create an export to a modern file format (Excel/LibreOffice format) which normal people can use, and that contains the pivoted data exactly like the user sees it in front of him. This is simply "What You See Is What You Get" which is not a new principle.
2. add an easy to find button for this export so that it is not hidden 3 levels deep in a menu, surrounded by many export options the user probably doesn't want to use because they are not WYSIWYG.
3. test, release, enjoy

If you are just going to close this, then you will need to keep closing such requests, because they will keep coming. You know more then I do, so open the correct topics.

There are discussions and issues started in 2022 which you can easily find. If you look at them, you can even see that the export to pivoted csv has gotten worse: row attribute columns were not concatenated in the past.

• Feature Request: Modify CSV export for Tables and Pivot Tables to more closely resembled rendered data in these charts #19705
• Feature Request: Modify CSV export for Tables and Pivot Tables to more closely resembled rendered data in these charts #23374
• When exporting to CSV with the 'Pivoted' option, all row fields are concatenated into one column in Excel. Is this a known issue? #21854
• Data sorting is getting changed in CSV file than report data when using Export CSV option [BUG]  #21337

Fix the root problem: make a new export, and don't invest time in fixing something that is broken: CSV is not a good export format.