— Jozef Aerts 2012/07/05 20:16
There has been some confusion about the use of the 'OrderNumber' in 'StudyEventRef', 'FormRef', 'ItemGroupRef' and 'ItemRef' in CDISC ODM. The specification (ODM v.1.3.1) states for example for 'OrderNumber' in 'StudyEventRef':
“The OrderNumbers provide an ordering on the StudyEventDefs for use whenever a list of StudyEventDefs is presented to a user. They do not imply anything about event scheduling, time ordering, or data correctness.”
As the specification states, 'OrderNumber' should not be used for scheduling, i.e. not for providing an order in which visits (StudyEvents) need to be executed. This should be defined using the new SDM-XML standard, or just by the natural order in which the 'StudyEventRef' elements appear within the 'Protocol' element. Similary, 'OrderNumber' in 'FormRef' should not be used to define in which order forms should be used within a visit (StudyEvent)
I admit that the sentence “for use whenever a list of StudyEventDefs is presented to a user” is confusing. It might suggest that 'OrderNumber' is about display order, and that it would change the (natural) order within the parent element when the list is displayed, e.g. by a stylesheet. This is however not correct.
I was confused myself about 'OrderNumber' until my good friend Lex Jansen (now at SAS) gave me a tip: Consider the case that a receiving system needs to store an incoming ODM file containing a study design in a relational database. So it generates a table “StudyEvents”, a table “Forms”, etc.. In the table “StudyEvents” it generates a row for each planned StudyEvent. So for each StudyEvent, there is a row in the table. Now we all know that in a relational database, the order of the rows is not important, and that when executing a query, the result table might give the rows in another order than in which they were originally inserted. This means that the natural order from the XML file that generated the table is lost.
In order to prevent such a loss, the 'OrderNumber' attribute was introduced. So when reading an ODM file with metadata, generating a row in a database for each StudyEvent, a value for the order is assigned, and the column 'OrderNumber' is filled. When then tring to re-generate the ODM file from the database table, the SQL query used is (for example):
SELECT * FROM STUDYEVENTS ORDER BY ORDERNUMBER;
and from that the XML is generated. When done so, the order of the 'StudyEventRef's in the result XML is the same as in the original XML file.
My proposal is to change the text in the (next version of) the specification to something like: “The sole purpose of the OrderNumber” attribute is to allow relational databases to store and to reconstruct the order of the XML elements 'StudyEventRef', 'FormRef', ItemGroupRef' and 'ItemRef' within their parent element.”
Or some better description of course …
Some might not like such a statement, as it points to a weakness of relational databases when storing information coming from XML files (where order does matter!). And of course: you don't have this problem at all if you use a native XML database!