Standards-Based Interoperability

re: Standards-Based Interoperability

Jomar Silva — Fri, 05 Jun 2009 19:05:17 GMT

Hi Doug,

Thanks for asking :]

When do you will release a fix for the formulas issues on SP2 (there's a lot of people asking me about it in Brazil... And I mean "important" people).

Best,

Jomar

re: Standards-Based Interoperability

dmahugh — Fri, 05 Jun 2009 19:39:02 GMT

Hi Jomar,

For information on our approach to formula support in SP2's implementation of ODF 1.1, see these posts:

http://blogs.msdn.com/dmahugh/archive/2009/05/05/odf-spreadsheet-interoperability.aspx

http://blogs.msdn.com/dmahugh/archive/2009/05/09/1-2-1.aspx

As covered there, we'll be looking closely at Open Formula when it is approved and published. Personally, I think Open Formula looks very promising, and it will be good to have a published standard for formulas in future versions of ODF.

Did you have any thoughts on the topics covered in the blog post above? I'd be interested in knowing, for example, whether or not you agree that standards conformance is important for enabling standards-based interoperability.

re: Standards-Based Interoperability

Dennis E. Hamilton — Fri, 05 Jun 2009 21:35:43 GMT

Short answer: I do agree that standards conformance is the foundation for standards-based interoperability. Conformance is not enough, as we are reminded in many different ways, but a standard (evolved and improved as reality demands) is the proper foundation for resolving interoperabilty.

Longer story:In the case of Excel 2007 handling of table-cell formulas, there is a complicated tension and it would be good that it was better understood, whether or not people agree with how is was resolved in Excel 2007 ODF support.

It seems to me that the Be Predictable principle and the Fail Hard principle is in a tug-of-war with the Principle of least astonishment and the Fail Quietly/Softly principle. (That last tractor-puller was a surprise arrival in this match.)

The predictability is great for Excel 2007 ODF spreadsheets brought back into or interchange with Excel 2007 ODF. It also strikes me that there is no question that the Excel approach qualifies under the definition of ODF and is even further supported by thise formulas being drawn from a documented and open international standard. (Bugs we will ignore on both sides of this equation)

On the other hand, there is no question that users of other products are massively surprised by (1) their spreadsheets having their formulas lost when interchanged with Excel 2007 and (2) not being able to handle the formulase received in Excel 2007's ODF output.

Whether this is something that Microsoft is supposed to fix, as in Jomar Silva's view, is not that obvious to me.

I recall in my own experience how hard we worked to avoid solving problems that were not of our own creation. (We avoided dealing with authentication and authorization in one agreement for precisely that reason, rather than attempt yet-another private solution).

Here the problem is that the prevalent ODF spreadsheet implementations tend to use the same private namespace for an unstandardized formula-and-functions scheme. It is unclear whether those implementations conform with each other and whether there is any way to tell.

It's my impression that the Fail Hard decision was the chosen alternative over attempting to accept these other unstandardized formulas and have errors that would be unnoticed in the results or that, when noticed, would be inexplicable and difficult to resolve.

As long as I am creating my own fantasy about the state of affairs, it is also worth noting that when you already have a spreadsheet formula system that you know works, using that in the first Excel ODF support has a certain economy and helps one be confident in that initial implementation. It might even be a demonstration of the least-that-could-possible-work agility principle, and the future will determine whether the "but no simpler" line was crossed.

It is very unclear to me how much it would take to become confident in addressing interoperability with the uses of no-namespace and unstandardized namespaces and whether there is any reason to do that when the long-awaited OpenFormula addition to ODF is expected real-soon-now. Although there are those who think interoperability (with which anointed non-standardized implementation?) should have been a no-brainer, I speculate that the uncertain opportunity cost of pursuing that might have been frightening, especially with OpenFormula lurking in the wings and certain implementations claiming existing support for an unratified ODF 1.2.

I cannot fault the Microsoft approach as incorrect, and it is far too early to declare it to be unsuccessful. I was at the year-ago DII meeting where the guiding principles were announced and their application to spreadsheet formulas described. I applauded the principles and understood the reasoning for formulas. How this would impact various groups of users and non-users (who still want to interoperate) of Office 2007 did not surface in my consciousness. I little intelligence (pun intended) on that level of consideration.

re: Standards-Based Interoperability

Franco Merletti — Sat, 06 Jun 2009 01:25:16 GMT

Hi Doug, nice post ( and nice illustrations ).

Can you tell us about the advance of the fix in ODF formula handling in Office 2007 ( you know, the "square-bracket-thing" that prevents any real interoperability between spreadsheets generated by Microsoft(TM) Office 2007 and the Rest Of The World(TM) ODF spreadsheets ).

Thanks in advance.

re: Standards-Based Interoperability

A Nonymous — Sat, 06 Jun 2009 03:03:14 GMT

There is a problem with your diagrams, in how theory maps to reality.

Remember: It is not about applications, it is about file formats.

There may be N (large value of N, >> 5) applications, but regardless of how big N is, the question is, are there any "islands" within which interoperabity (or more specifically, file format implementation compatibility, or standard interpretation, or whatever you want to call it) is "perfect", i.e. known to be good, and for which round-trip open/close preserves the complete contents of the XML encoding of the file?

The question then reduces to, "How many such 'islands' are there?" If the number is small, then the theoretical argument is just that - theoretical. If the number is small, the question is, is it small enough to justify the effort to achieve the compatiblity work, and to do so reliably? (Hint - the answer is: very few islands, like less than 5, and yes, the work is more than justified.)

But all of this ignores alternative approaches, which hit more of the "bullet points" in your Guiding Priniciples.

Are those Principles in order of importance?

If you are informed of an approach which goes further down the list, and in fact hits all but the second last one, would there not be a compelling argument that that would be a better approach?

The gist of the alternative method of preserving everything in an ODF spreadsheet without risking introducing errors (because of different ideas of what 1+2 results in, for instance), is to make the cell contents on imported cells used in foreign namespace formulas read-only (e.g. FOOBAR:=formula(foo,bar) means that "foo" and "bar" are protected from being modified, by default.)

The problem of interpreting formulas is only encountered when formulas are interpreted.

In reading in a spreadsheet, both the formulas AND THE LAST RESULTING VALUES are present. The previous values are by definition "right". If the input to the formula doesn't change, e.g. by being protected against being changed by the user, then the formula NEVER needs to be re-interpreted.

This would mean:

- ODF 1.1 is supported

- the results are predictable (a first for MS ;-))

- user intent is preserved

- visual fidelity is preserved

And, as a bonus, the foreign namespace formulas *and values* survive a round-trip completely intact.

Q.E.D.

So, what do you think of this proposal?

Do you not anticipate a demand for this sort of functionality in a spreadsheet application, claiming to "do" ODF 1.1?

Name Withheld

re: Standards-Based Interoperability

dmahugh — Sat, 06 Jun 2009 03:55:10 GMT

@Dennis, the tractor-pull analogy is right on. The principles are indeed often in tension/opposition with one another on certain details.

There’s always room for people to debate a given approach is more or less astonishing than another approach, of course. But our Excel program managers felt strongly, based on their long experience with real customer issues, that if you open a spreadsheet someone sends you and see different results from the calculation than the sender saw that would be much worse experience than if you saw the same results they sent, but you could not easily recalculate them. With the current state of ODF 1.1 spreadsheet interop there is just no easy way for someone to know if they are seeing the same calculation results that someone else saw. And the astonishment could be severe if, for example, you accept someone’s bid to remodel your kitchen for $10,000 and then later they tell you that in their favorite spreadsheet application the total came to $15,000.

Thanks for your comment, Franco. Regarding formulas, note the responses to Jomar and Dennis above.

I think it's important that we all remember there is NO published standard for ODF spreadsheet formulas yet. Nor is there any de-facto standard that everyone agrees on. In truth, among all of the other ODF implementations real-world complex spreadsheets don’t interoperate very well either. The only way an implementer can try to make ODF spreadsheet interop work today (and by work I mean not just for trivial cases, but really work reliably for real-world complex spreadsheets) is by the “spaghetti diagram" method, with all of the complexity and risk of bugs that entails. No implementer we know of has attempted that, and the very point of my post was to explain why we don’t think this is a good approach, and why we think the standards-based approach is better.

In the case of spreadsheet formulas, help is on the way -- OpenFormula is under development for use with ODF 1.2. In the meantime, we should not pretend that all was well with spreadsheet interop before SP2 came along. And (in my opinion) we would be collectively better off to spend our energies on solving the problem instead of complaining about it.

FYI, I’d like to keep this thread on-topic around the concepts covered in this post, as outlined in my comment policy. There are already 118 comments on the two threads about formulas that I linked to above, so I’ll only be letting more comments on that topic appear here if they truly add something new to the discussion that has not already come up in those threads.

re: Standards-Based Interoperability

AndréR — Sat, 06 Jun 2009 16:20:31 GMT

I appreciate the post, very good, because it raises these aspects which are often overlooked. Visually I would rather frame it in terms of convergence, a spiral.

You stressed the need to keep a strong compatibility with legacy formats in DIS 29500. So how does your interoperability process function when the competitor in your diagram above is your past product? How is it possible to "get it right" rather than to support 1983 legacy bugs? Y2K was as you know a strict standard compliance bug.

A format lock-in often apllies not only to the implementation but the "corpus of existing documents". The deliberate choice to implement formula totally different from competitors ("Nor is there any de-facto standard that everyone agrees on."), what will that imply for the future: No openformula because the MS-ODF legacy has to be supported? Or incompatibility of your old formats because of the literal approach?

I doubt someone would ever find a magic bullet to interoperability and user satisfaction. It is more kneading to converge. For convergence commercial and public pressure seems helpful, and of course a GATT style process to make that happen.

In terms of visual fidelity NLnet and Opendoc Society prepare an interesting project: http://www.officeshots.org/

re: Standards-Based Interoperability

L. — Sun, 07 Jun 2009 03:21:53 GMT

Still, as a user, I have to say that discarding formulas in spreadsheets *sucks*, and it goes a long way towards "astonishing" (as in "least astonishment") in a very negative way anyone who is not aware of this limitation.

OpenFormula is still under development, but it's already used in a major implementation of ODF (OpenOffice), so it's not likely to evolve much in incompatible ways. There are ways to handle such a situation. You could have implemented it, and provided a service pack if changes do happen. Or if it really bothered you, then you could have included a checkbox & warning to enable/disable it according to the user's needs. It would not have been the first time Microsoft implemented a standard before its finalization (e.g. the IE team is already implementing parts of HTML5), if it's beneficial to the user.

re: Standards-Based Interoperability

hAl — Mon, 08 Jun 2009 12:43:07 GMT

Still no committee draft on OpenFormula spec in OASIS.

Will OASIS even allow ODF 1.2 to continue in the standardization proces when it is relying heavily on a specifcation still in draft form ?

re: Standards-Based Interoperability

dmahugh — Tue, 09 Jun 2009 04:29:36 GMT

@A Nonymous,

The concept of an implementation round-tripping content that it doesn't understand is something we considered during initial planning of our ODF implementation. It was also one of the topics raised during the roundtable discussions at the DII event in Redmond last July.

The problem is that if you don't understand a piece of content, then you can't reliably know whether other changes you've made in areas that you do understand may have invalidated the content. Consider, for example, a spreadsheet like this:

Suppose we open that spreadsheet in an implementation that round-trips content it doesn't understand, and we insert a header row. What happens?

The cells all shift down, but the formula gets round-tripped without a change. So when you open the modified spreadsheet back in the original application, you see a different result than it had before:

@Andre, you're right, things get very messy when you have to deal with not only multiple products, but multiple versions of each one.

re: Standards-Based Interoperability

A Nonymous — Tue, 09 Jun 2009 05:34:26 GMT

If your application can understand cell addressing (you know, the "atoms" of spreadsheets, duh), then the question can be reduced to:

Can we process relativistic moves (like inserting lines, columns, etc), by blindly manipulating the cell addresses, while leaving the formula results untouched?

The answer is yes.

The issue of round-tripping is not nearly so complicated as you make it sound, when the interpretation (of cells *values* and *formulas*) is factored out.

Adding a line, shifting rows, means updating the formula (from "=SUM(B1:B3)" to "=SUM(B2:B4)", while literally not touching the displayed result.

If the original cell B2, which was shifted to B3, had in fact been the text-formatted "2" instead of the integer '2', there still be no need to *calculate* the formula result - since it is a foreign namespace formula.

The underlying engine for formula triggers would need to be smart enough to know that cells are being "tracked", rather than a formula literally being modified. Caveat developer.

Name Withheld

re: Standards-Based Interoperability

Mitch 74 — Tue, 09 Jun 2009 15:42:05 GMT

@dmahugh: technically, the choice to discard formulas and keep only the latest known good result is valid; however, there is no question that formulas etc. get destroyed on save if you open an ODF spreadsheet made with OOo Calc in MS Excel.

On the other hand, other spreadsheets seem to largely fall to another solution: formula is kept, but not interpreted, and preceded with its namespace. You'll tell me, this destroys visual fidelity (by default) because instead of the last known value, you now have a string the spreadsheet can't compute.

Stop me if I'm wrong though, the last known good value is saved in the file, can be read, and is "merely squashed" by the spreadsheet application.

Now, and THAT would have probably prevented a LOT of criticism, didn't you add a small alert box saying:

"This file contains formulas in a format that Excel can't parse."

Choice 1 "discard formulas, only keep values"

Choice 2 "discard values, display formulas for manual editing"

Check box "remember my choice"

text link to help page explaining the reasons and solutions

And that would have been ALL: have your cake, and eat it too. Frankly, how hard would it have been?

re: Standards-Based Interoperability

Bart Hanssens — Tue, 09 Jun 2009 16:13:41 GMT

> In my next post, I’ll cover our testing strategy and methodology in more detail. What else would you like to know about how Office approaches document format interoperability?

Doug: could you shed some light if and how the MS Office-team and the ODF-converter team deal with interop testing between 2007sp2 and the plug-in?

Since MS provides technical and architectural guidance for the OpenXML/ODF converter project, I assume both teams somehow share the same methodology on testing two different products, so it could be helpful for other ODF-developers as well...

Thanks,

Bart

re: Standards-Based Interoperability

dmahugh — Tue, 09 Jun 2009 20:04:01 GMT

@A Nonymous, this sounds potentially fragile to me, because the consumer would not be taking full responsibility for the integrity of the content, but would still be modifying certain aspects of it.

@Mitch, your suggestion is essentially a combination of various approaches used in existing implementations. Have you suggested that the current versions of Symphony (1.2) and OpenOffice.org (3.1) should implement this multiple-choice approach as well?

In any event, I think the best path forward is for all of us to focus on making Open Formula as interoperable as possible. The wide variety of approaches that people have suggested indicates a need for an agreed-upon standard in this area.

@Bart, that's a good question, I'll plan to address it in my next post.

re: Standards-Based Interoperability

Mitch 74 — Wed, 10 Jun 2009 12:08:12 GMT

@Doug: yup, they should. The fact that they don't is no reason not to one-up them, is it? You could, like, innovate... If they do imitate you, then interoperability wins anyway!

Thing is, their approach (keep the formula) may allow a patient user to re-write a new formula based on the old one and get his (proper) results (in essence, to recreate user intent). Agreed, it's far from being the best solution, but there's no actual loss: result can be found again without external reference.

Current Excel system's would mean that a spreadsheet named LifeTheUniverseAndEverything.odc woud merely contain 42 in Excel - the question would be scrapped. And them Vogons are not patient.

Now, another (non-interactive, but maybe more workflow-disruptive) solution would be a new error message: #NAMESPACE:42 (namespace is not know; last known good value is 42), which would be technically correct (error:can't parse the formula), informative (error caused by unknown namespace), non-destructive (formula and namespace appear in edit mode, like in other spreadsheet programs, and last good value is kept after error message) and forward looking (the day that namespace is supported, formula can be computed again).

Or both solutions, I don't know? It could also be added to ODF 1.2 (how to manage formulas from different namespaces), and it doesn't prevent OpenFormula's development.

Of course, it would mean some careful thinking: how does one work with a spreadsheet that uses several formula namespaces? Although the only thing taken into account by a formula is the 'last known good value', there could be problems such as floating point precision (some may accept double 32-bit float precision, others be limited to single 32-bit precisions, others may use 256-bit precision...), or dates (oxml-f may accept 1902-02-29, odf-of certainly won't) to work out...

I do think those questions should be addressed as soon as possible; destroying a formula ain't a good solution, converting it from one format to another isn't either (ceil() and floor() in MS Office for example are mathematically wrong on negative values; a parameter exists in oooc and OF to deal with import, but there's no way to export floor() or ceil from those namespaces to MS formulas correctly), and merely keeping the last known good value... well, if last known good value is 256-bit precision and is read on a spreadsheet program that has single 32-bit precision, then if Excel's solution is used, not only will you lose the formula, you'll also lose the actual value (loss of precision).

I dunno, maybe I'm overlooking something, what do you think?

re: Standards-Based Interoperability

Mitch 74 — Wed, 10 Jun 2009 16:52:07 GMT

@Doug: it's the second time I try to post this one...

So what? Should you limit your software to a level of service equivalent to other suites? How about, innovation?

The current solution that Excel uses has one slight problem.

Take the file named LifeTheUniverseAndEverything.odt, by author: Deep Thought; it contains a very complex set of oooc formulas.

Excel only keeps 42.

If you'd rather 'bypass' the prompt I indicated in the first comment, then use: #NAMESPACE:42 as returned value.

- it keeps the original formula and namespace in formula space (for manual porting)

- it keeps the computed value in displayed results

- error message is explicit

Rationale: since all formulas are disabled anyway, marking all of them as such won't create a cascade effect (except if there are several formula namespaces used in the spreadsheet - doubtful).

Ah - of course, that removes 'visual fidelity'. But then you wouldn't lose the equation behind the '42' result.

And them Vogons ain't patient.

re: Standards-Based Interoperability

A Nonymous — Wed, 10 Jun 2009 17:12:46 GMT

Um, "potentially fragile"?

Which part of this is fragile?

(I will refrain from commenting on this comment appearing to be an application of "fear, uncertainty, and doubt".)

Presuming of course, that the original suggestion, making the formulas and input cells READ-ONLY, means the integrity of THAT content is unimpeachable.

And I am presuming that the application (excel) is already doing these other things (tracking cell relocation, and cell modification), already.

If relocation is handled (it is), that is most of the battle.

If it is NOT the case that excel relies exclusively on modifications of either input values or formulas, to trigger formula calculation, then excel is grossly flawed. If it IS the case (that it relies exclusively), then the rest of the battle is won.

My questions are:

- Whose call at Microsoft is it, to do this or not do this?

- If this was considered and discarded, I fail to see the rationale for doing so. Can you elaborate on that decision?

- Whose call at Microsoft is it, to revisit the question of using this method to support foreign namespaces in read-only, round-trip proof ways?

Name Withheld

Testing Office’s ODF Implementation

Doug Mahugh — Mon, 15 Jun 2009 07:32:21 GMT

In this blog post, I’m going to cover some of the details of how we approached the challenges of testing

Internationale ODF bijeenkomst in Den Haag

voortgang — Mon, 15 Jun 2009 18:30:34 GMT

Microsoft neemt vandaag en morgen (15 en 16 juni 2009) deel aan een ODF bijeenkomst in Den Haag. Het

[Open XML] Liens de la semaine 15/06/09

Julien Chable — Fri, 19 Jun 2009 16:42:40 GMT

Voici les derniers posts concernant Open XML et l’implémentation d’ODF dans Office 2007 SP2. De très