Aw: Re: Archetype relational mapping - a practical openEHR persistence solution

[Karsten Hilbert]

> If a database has a field-type XML, then I expect it does something special 
> with
> that field that justifies the fieldtypename.

Oh, it does, it offers validation of the XML, AFAICT.

Karsten Hilbert

___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Bert Verhees]

If a database has a field-type XML, then I expect it does something 
special with that field that justifies the fieldtypename.

Especially I expect that from Postgres, because they mostly do good things.

Bert

On 16-02-16 11:47, Thomas Beale wrote:



On 14/02/2016 23:25, Bert Verhees wrote:


One doesn't know what software really does. One must distinguish what 
software seems to do and what it really does.


Storing XML really as XML means, storing a lot of redundant 
information. I don't know, but I cannot believe postgress really 
stores the full tag names,  even when they occur thousand times. I 
would be really disappointed if they do.


Bert



storing a text serialisation of an object structure in a database 
never makes sense when you think about it. Storing a /binary/ 
serialisation is a normal 'blob' approach, and if you are not doing 
blobs or partial blobs, then you are doing transparent representation, 
in which case things like XML or JSON don't come into it - they only 
make sense as one kind of generated view on data.


You might implement blobs using a zipped text format, but in a serious 
scalable implementation that would surely have to be the least 
efficient of viable approaches.


I can't think of any circumstance where a DB would actually store its 
information as XML instance text (other than where some column value 
happened to be an XMLstring, i.e. the XML is just data).


- thomas


___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org


___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Thomas Beale]

On 16/02/2016 11:26, Seref Arikan wrote:
The document oriented view of the domain has no problem with storing 
XML text in the DB, because the implementations are built against that 
view. The clinical care focused use cases require developers to focus 
on reading & writing documents, so there is rarely a requirement to 
read across documents, because clinicians would not be able to consume 
all that information at once (except averages or other aggregate 
values they're interested in)


So developers mostly deal with put document/get document type of 
requirements and simply having an XML field (or json for that matter) 
makes things easy for them. Hence, it makes sense to them.


right - but that's treating a document as an opaque data item, i.e. just 
like a String, that happens to be the value of some field in the overall 
schema.


Old blog post 
 
on the difference between blobs and documents...


- thomas
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Seref Arikan]

You're right, it does not read in the way I wanted it to. Let me try to
clarify:

Clinical care is strongly single patient centric, and in most cases this
puts a limit on the amount of data that does not push the limits of
computational power too much. Developers can afford to fetch multiple
documents and go across them if needed without requiring the DB to perform
the same operation, hence having to process XML (or json) So you can get
away with treating the relational database as a document store and
performance problems do not easily reveal themselves.

There will be cases which this won't work (for a single patient's care),
but it is only when the requirement to produce time series, aggregates etc
becomes too expensive to do outside of the DB. Even then, most
implementations would pre-define these and populate them during data entry
in advance, and still get away with not having to dissect XML at the db
level.

So my argument is more about being able to not to process XML using the
relational database than the necessity of clinical data that spans multiple
documents, which can be summarized as "developers are lazy"

Does it make sense now?

On Tue, Feb 16, 2016 at 11:33 AM, Ian McNicoll  wrote:

> Hi Seref,
>
> "so there is rarely a requirement to read across documents, because
> clinicians would not be able to consume all that information at once
> (except averages or other aggregate values they're interested in)"
>
> I don't think that is true in any other than the most trivial
> implementations. The ability to query and pull granular data across
> documents, for single and multiple patients is a critical requirement for
> anty EPR/EHR.
>
> or perhaps I misunderstood?
>
> Ian
>
>
> Dr Ian McNicoll
> mobile +44 (0)775 209 7859
> office +44 (0)1536 414994
> skype: ianmcnicoll
> email: i...@freshehr.com
> twitter: @ianmcnicoll
>
> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
> Director, freshEHR Clinical Informatics Ltd.
> Director, HANDIHealth CIC
> Hon. Senior Research Associate, CHIME, UCL
>
> On 16 February 2016 at 11:26, Seref Arikan <
> serefari...@kurumsalteknoloji.com> wrote:
>
>> The document oriented view of the domain has no problem with storing XML
>> text in the DB, because the implementations are built against that view.
>> The clinical care focused use cases require developers to focus on reading
>> & writing documents, so there is rarely a requirement to read across
>> documents, because clinicians would not be able to consume all that
>> information at once (except averages or other aggregate values they're
>> interested in)
>>
>> So developers mostly deal with put document/get document type of
>> requirements and simply having an XML field (or json for that matter) makes
>> things easy for them. Hence, it makes sense to them. Database vendors have
>> their own reasons for supporting direct storage of XML and they deal with
>> obvious disadvantages behind the scenes (google: postgres xml toast). The
>> price of storage keeps falling faster than all the other components,which
>> also helps justify this approach.
>>
>> I suggest a coding exercise for representing XML documents in a
>> relational database with writes and reads to see what a joy it is to
>> rebuild an XML document in comparison to just reading it directly from a
>> column :)
>>
>> Some DBs store XML using tables behind the scenes to save the developers
>> from this pain, but they usually rely on schemas to do that (cough **rcl*)
>> and it becomes another joyful activity to use that feature.
>>
>> So all I'm saying is: there is a price for everything, and in some cases
>> having xml sitting in the db is not as bad as it sounds
>>
>> On Tue, Feb 16, 2016 at 10:47 AM, Thomas Beale 
>> wrote:
>>
>>>
>>>
>>> On 14/02/2016 23:25, Bert Verhees wrote:
>>>
>>> One doesn't know what software really does. One must distinguish what
>>> software seems to do and what it really does.
>>>
>>> Storing XML really as XML means, storing a lot of redundant information.
>>> I don't know, but I cannot believe postgress really stores the full tag
>>> names,  even when they occur thousand times. I would be really disappointed
>>> if they do.
>>>
>>> Bert
>>>
>>>
>>> storing a text serialisation of an object structure in a database never
>>> makes sense when you think about it. Storing a *binary* serialisation
>>> is a normal 'blob' approach, and if you are not doing blobs or partial
>>> blobs, then you are doing transparent representation, in which case things
>>> like XML or JSON don't come into it - they only make sense as one kind of
>>> generated view on data.
>>>
>>> You might implement blobs using a zipped text format, but in a serious
>>> scalable implementation that would surely have to be the least efficient of
>>> viable approaches.
>>>
>>> I can't think of any circumstance where a DB would actually store its
>>> information as XML instance text (other than where some column value
>>> 

Re: Archetype relational mapping - a practical openEHR persistence solution

[Ian McNicoll]

Hi Seref,

"so there is rarely a requirement to read across documents, because
clinicians would not be able to consume all that information at once
(except averages or other aggregate values they're interested in)"

I don't think that is true in any other than the most trivial
implementations. The ability to query and pull granular data across
documents, for single and multiple patients is a critical requirement for
anty EPR/EHR.

or perhaps I misunderstood?

Ian


Dr Ian McNicoll
mobile +44 (0)775 209 7859
office +44 (0)1536 414994
skype: ianmcnicoll
email: i...@freshehr.com
twitter: @ianmcnicoll

Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
Director, freshEHR Clinical Informatics Ltd.
Director, HANDIHealth CIC
Hon. Senior Research Associate, CHIME, UCL

On 16 February 2016 at 11:26, Seref Arikan <
serefari...@kurumsalteknoloji.com> wrote:

> The document oriented view of the domain has no problem with storing XML
> text in the DB, because the implementations are built against that view.
> The clinical care focused use cases require developers to focus on reading
> & writing documents, so there is rarely a requirement to read across
> documents, because clinicians would not be able to consume all that
> information at once (except averages or other aggregate values they're
> interested in)
>
> So developers mostly deal with put document/get document type of
> requirements and simply having an XML field (or json for that matter) makes
> things easy for them. Hence, it makes sense to them. Database vendors have
> their own reasons for supporting direct storage of XML and they deal with
> obvious disadvantages behind the scenes (google: postgres xml toast). The
> price of storage keeps falling faster than all the other components,which
> also helps justify this approach.
>
> I suggest a coding exercise for representing XML documents in a relational
> database with writes and reads to see what a joy it is to rebuild an XML
> document in comparison to just reading it directly from a column :)
>
> Some DBs store XML using tables behind the scenes to save the developers
> from this pain, but they usually rely on schemas to do that (cough **rcl*)
> and it becomes another joyful activity to use that feature.
>
> So all I'm saying is: there is a price for everything, and in some cases
> having xml sitting in the db is not as bad as it sounds
>
> On Tue, Feb 16, 2016 at 10:47 AM, Thomas Beale 
> wrote:
>
>>
>>
>> On 14/02/2016 23:25, Bert Verhees wrote:
>>
>> One doesn't know what software really does. One must distinguish what
>> software seems to do and what it really does.
>>
>> Storing XML really as XML means, storing a lot of redundant information.
>> I don't know, but I cannot believe postgress really stores the full tag
>> names,  even when they occur thousand times. I would be really disappointed
>> if they do.
>>
>> Bert
>>
>>
>> storing a text serialisation of an object structure in a database never
>> makes sense when you think about it. Storing a *binary* serialisation is
>> a normal 'blob' approach, and if you are not doing blobs or partial blobs,
>> then you are doing transparent representation, in which case things like
>> XML or JSON don't come into it - they only make sense as one kind of
>> generated view on data.
>>
>> You might implement blobs using a zipped text format, but in a serious
>> scalable implementation that would surely have to be the least efficient of
>> viable approaches.
>>
>> I can't think of any circumstance where a DB would actually store its
>> information as XML instance text (other than where some column value
>> happened to be an XMLstring, i.e. the XML is just data).
>>
>> - thomas
>>
>> ___
>> openEHR-technical mailing list
>> openEHR-technical@lists.openehr.org
>>
>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>
>
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Seref Arikan]

The document oriented view of the domain has no problem with storing XML
text in the DB, because the implementations are built against that view.
The clinical care focused use cases require developers to focus on reading
& writing documents, so there is rarely a requirement to read across
documents, because clinicians would not be able to consume all that
information at once (except averages or other aggregate values they're
interested in)

So developers mostly deal with put document/get document type of
requirements and simply having an XML field (or json for that matter) makes
things easy for them. Hence, it makes sense to them. Database vendors have
their own reasons for supporting direct storage of XML and they deal with
obvious disadvantages behind the scenes (google: postgres xml toast). The
price of storage keeps falling faster than all the other components,which
also helps justify this approach.

I suggest a coding exercise for representing XML documents in a relational
database with writes and reads to see what a joy it is to rebuild an XML
document in comparison to just reading it directly from a column :)

Some DBs store XML using tables behind the scenes to save the developers
from this pain, but they usually rely on schemas to do that (cough **rcl*)
and it becomes another joyful activity to use that feature.

So all I'm saying is: there is a price for everything, and in some cases
having xml sitting in the db is not as bad as it sounds

On Tue, Feb 16, 2016 at 10:47 AM, Thomas Beale 
wrote:

>
>
> On 14/02/2016 23:25, Bert Verhees wrote:
>
> One doesn't know what software really does. One must distinguish what
> software seems to do and what it really does.
>
> Storing XML really as XML means, storing a lot of redundant information. I
> don't know, but I cannot believe postgress really stores the full tag
> names,  even when they occur thousand times. I would be really disappointed
> if they do.
>
> Bert
>
>
> storing a text serialisation of an object structure in a database never
> makes sense when you think about it. Storing a *binary* serialisation is
> a normal 'blob' approach, and if you are not doing blobs or partial blobs,
> then you are doing transparent representation, in which case things like
> XML or JSON don't come into it - they only make sense as one kind of
> generated view on data.
>
> You might implement blobs using a zipped text format, but in a serious
> scalable implementation that would surely have to be the least efficient of
> viable approaches.
>
> I can't think of any circumstance where a DB would actually store its
> information as XML instance text (other than where some column value
> happened to be an XMLstring, i.e. the XML is just data).
>
> - thomas
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Thomas Beale]

On 14/02/2016 23:25, Bert Verhees wrote:


One doesn't know what software really does. One must distinguish what 
software seems to do and what it really does.


Storing XML really as XML means, storing a lot of redundant 
information. I don't know, but I cannot believe postgress really 
stores the full tag names,  even when they occur thousand times. I 
would be really disappointed if they do.


Bert



storing a text serialisation of an object structure in a database never 
makes sense when you think about it. Storing a /binary/ serialisation is 
a normal 'blob' approach, and if you are not doing blobs or partial 
blobs, then you are doing transparent representation, in which case 
things like XML or JSON don't come into it - they only make sense as one 
kind of generated view on data.


You might implement blobs using a zipped text format, but in a serious 
scalable implementation that would surely have to be the least efficient 
of viable approaches.


I can't think of any circumstance where a DB would actually store its 
information as XML instance text (other than where some column value 
happened to be an XMLstring, i.e. the XML is just data).


- thomas
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Bert Verhees]

One doesn't know what software really does. One must distinguish what
software seems to do and what it really does.

Storing XML really as XML means, storing a lot of redundant information. I
don't know, but I cannot believe postgress really stores the full tag
names,  even when they occur thousand times. I would be really disappointed
if they do.

Bert
Op 14 feb. 2016 17:39 schreef "Karsten Hilbert" :

> On Sun, Feb 14, 2016 at 12:01:55PM +0100, Bert Verhees wrote:
>
> > I don't believe that XML-databases actually store XML. Oracle, for
> example,
> > breaks it up in a relational structure. But I don't know the internals of
> > others well. The worst solution, however for storing XML would be really
> > storing XML.
>
> For what it is worth, here is what PostgreSQL does by default:
>
> http://www.postgresql.org/docs/current/static/datatype-xml.html
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Karsten Hilbert]

On Sun, Feb 14, 2016 at 12:01:55PM +0100, Bert Verhees wrote:

> I don't believe that XML-databases actually store XML. Oracle, for example,
> breaks it up in a relational structure. But I don't know the internals of
> others well. The worst solution, however for storing XML would be really
> storing XML.

For what it is worth, here is what PostgreSQL does by default:

http://www.postgresql.org/docs/current/static/datatype-xml.html

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Bert Verhees]

Thanks,

I always forget it is weekend, as independent developer, there ain't no 
such thing as a day of.


I agree that mixing query-languages is ugly, and I don't see the necessity.

But maybe, you will explain that next week

Bert

On 13-02-16 17:46, Birger Haarbrandt wrote:

Hi Bert,

I will post some more thoughts on these things after the weekend :) To 
just give a quick answer: imo it's important to have a flexible data 
format like the one I2B2 uses (roughly said EAV) to mix openEHR data 
with non-openEHR data. Making analysis on XML documents/databaes might 
prevent integration of other sources (and even if its possible like in 
SQL Server or Oracle, queries that mix XQuery and SQL might become 
pretty ugly. And I see no good way to provide a drag & drop interface 
to researchers/physicians to make queries).


Cheers,

Birger

Von meinem iPad gesendet


Am 13.02.2016 um 16:24 schrieb Bert Verhees >:



No comments, on the other hand it is Saturday

I had left out some necessary technical details.
I will possible build it and then have possible the fastest 
two-level-modeling engine in the world, which will, of course, also 
support OpenEHR.

So it is not really bad that this happens.

When it is ready, I will make an announcement.

Best regards and enjoy the weekend.
Bert Verhees


On 12-02-16 20:49, Bert Verhees wrote:

On 12-02-16 18:26, Erik Sundvall wrote:
if you are experimenting with open source native XML DBs for 
openEHR, it preformed well for "clinical" patient-specific querying 
even though all xml databases we tested were not suitable for ad 
hoc epidemiological population queries (without query specific 
indexing)



A very interesting paper. I have some first opinions on that. But 
first I need to explain what I think about the matter.
I have not prepared the story below, so there may be things which I 
write to fast. See it as provisional view, not as a hard opinion.

-
There are relational database-configurations for OLAP and for OLTP. 
The combination is hard to find. There are reasons. This is a 
classic problem.


You need specific indexes for data-mining (OLAP), and for every 
extra data-mining query you need more indexes, especially if you 
don't have time to wait a night for the result. Those extra indexes 
stand in the way for transactional processing (OLTP) because they 
need to be updated, and that is unnecessary burden for the 
OLTP-processes, as longer as the database exist, the burden becomes 
heavier.

That is why OLAP and OLTP are not often combined in one configuration.

So many professional databases have extra features for OLAP, I 
worked, years ago with Oracle for a big Dutch television company, 
and my main job was to create indexes for marketing purposes.
We ran those unusual queries during the night and stored the result 
in special tables, Oracle called them "materialized views".
The day after, those views were processed in analyzing software, 
like SPSS, and after that, thrown away.


It was a database with 900,000 persons in it, and every person had a 
lot of history of web-history, personal interests, etc.
"How much interest does a person have for opera, and is he able to 
pay for opera, is it worth to call him for a ticket-offer, we cannot 
call 900,000 persons"
These were complex queries based on things the customer bought, 
television programs he was interested in, web-activities.

That was the kind of thing they did with the database.

So this could well be compared with a medical database, regarding to 
size and complexity.


The same difficulties count for XML databases. That is why XML 
databases have also features for creating extra indexes.
Oracle, by the way, if it knows the structure of XML (via XSD), it 
breaks, underwater, XML into relational data, and store it in a 
relational database. It also converts XQuery to SQL.
In this way, it has the weakness and advantages of a relational 
database, and it needs the extra indexes for unusual queries, but on 
the developer view it is XML.

---
Comparing XML and relational, for OpenEHR, I favor XML, because it 
can easily reflect the structures which need to be stored. It makes 
the data-processing layer less complex. There is a lot of tooling 
around XML, XML-schema to make the database-structure known to 
Oracle, Schematron to validate against archetypes. This is very 
matured software, and therefor the complexity is solved years ago, 
and well tested. It is hidden complexity, and matured hidden 
complexity is no problem.


And if you want to do data-mining, like epidemiological research, 
and you have the time to plan the research, then the classical 
database, XML or RDB, is OK.


In my opinion, there is not often a real need for adhoc data-mining 
(epidemiological research) queries, with result in a few minutes. 
They are always planned, and creating the indexes and storing the 
result in  "materialized views" are part of the 

Re: Archetype relational mapping - a practical openEHR persistence solution

[Birger Haarbrandt]

Even as a research associate or independent developer, its sometimes good to 
take a break from work :) I hope I didn't miss a point in your e-mail and hope 
that I understood corretly: when you store openEHR-data using XML technologies, 
you will have to use XML technologies to query that data. To my understanding, 
also Oracle will need xpath/xquery when you store XML data using the XML 
datatyped column. If you want to combine this data with other data that isn't 
based on XML, you will have to convert the external data to XML or mix query 
languages in the mentioned data bases. 

In short: It makes sense to not use the openEHR CDR for such queries but export 
it to something more suited for such purpose. This does not need to be a 
classical Data Warehouse (it has only limited use for the questions clinicians 
have) but certainly XML would not be my first choice for the analytics layer.

Birger


Von meinem iPad gesendet

> Am 13.02.2016 um 17:56 schrieb Bert Verhees :
> 
> Thanks,
> 
> I always forget it is weekend, as independent developer, there ain't no such 
> thing as a day of.
> 
> I agree that mixing query-languages is ugly, and I don't see the necessity.
> 
> But maybe, you will explain that next week
> 
> Bert
> 
>> On 13-02-16 17:46, Birger Haarbrandt wrote:
>> Hi Bert,
>> 
>> I will post some more thoughts on these things after the weekend :) To just 
>> give a quick answer: imo it's important to have a flexible data format like 
>> the one I2B2 uses (roughly said EAV) to mix openEHR data with non-openEHR 
>> data. Making analysis on XML documents/databaes might prevent integration of 
>> other sources (and even if its possible like in SQL Server or Oracle, 
>> queries that mix XQuery and SQL might become pretty ugly. And I see no good 
>> way to provide a drag & drop interface to researchers/physicians to make 
>> queries).
>> 
>> Cheers,
>> 
>> Birger 
>> 
>> Von meinem iPad gesendet
>> 
>> 
>> Am 13.02.2016 um 16:24 schrieb Bert Verhees :
>> 
>>> No comments, on the other hand it is Saturday
>>> 
>>> I had left out some necessary technical details.
>>> I will possible build it and then have possible the fastest 
>>> two-level-modeling engine in the world, which will, of course, also support 
>>> OpenEHR.
>>> So it is not really bad that this happens.
>>> 
>>> When it is ready, I will make an announcement.
>>> 
>>> Best regards and enjoy the weekend.
>>> Bert Verhees
>>> 
>>> 
 On 12-02-16 20:49, Bert Verhees wrote:
> On 12-02-16 18:26, Erik Sundvall wrote:
> if you are experimenting with open source native XML DBs for openEHR, it 
> preformed well for "clinical" patient-specific querying even though all 
> xml databases we tested were not suitable for ad hoc epidemiological 
> population queries (without query specific indexing)
 
 
 A very interesting paper. I have some first opinions on that. But first I 
 need to explain what I think about the matter.
 I have not prepared the story below, so there may be things which I write 
 to fast. See it as provisional view, not as a hard opinion.
 -
 There are relational database-configurations for OLAP and for OLTP. The 
 combination is hard to find. There are reasons. This is a classic problem.
 
 You need specific indexes for data-mining (OLAP), and for every extra 
 data-mining query you need more indexes, especially if you don't have time 
 to wait a night for the result. Those extra indexes stand in the way for 
 transactional processing (OLTP) because they need to be updated, and that 
 is unnecessary burden for the OLTP-processes, as longer as the database 
 exist, the burden becomes heavier. 
 That is why OLAP and OLTP are not often combined in one configuration.
 
 So many professional databases have extra features for OLAP, I worked, 
 years ago with Oracle for a big Dutch television company, and my main job 
 was to create indexes for marketing purposes.
 We ran those unusual queries during the night and stored the result in 
 special tables, Oracle called them "materialized views". 
 The day after, those views were processed in analyzing software, like 
 SPSS, and after that, thrown away.
 
 It was a database with 900,000 persons in it, and every person had a lot 
 of history of web-history, personal interests, etc.
 "How much interest does a person have for opera, and is he able to pay for 
 opera, is it worth to call him for a ticket-offer, we cannot call 900,000 
 persons"
 These were complex queries based on things the customer bought, television 
 programs he was interested in, web-activities.
 That was the kind of thing they did with the database.
 
 So this could well be compared with a medical database, regarding to size 
 and complexity.
 
 The same difficulties count for XML databases. That is 

Re: Archetype relational mapping - a practical openEHR persistence solution

[Bert Verhees]

On 13-02-16 18:13, Birger Haarbrandt wrote:
Even as a research associate or independent developer, its sometimes 
good to take a break from work :) I hope I didn't miss a point in your 
e-mail and hope that I understood corretly: when you store 
openEHR-data using XML technologies, you will have to use XML 
technologies to query that data. To my understanding, also Oracle will 
need xpath/xquery when you store XML data using the XML datatyped 
column. If you want to combine this data with other data that isn't 
based on XML, you will have to convert the external data to XML or mix 
query languages in the mentioned data bases.


I don't understand the point you make. The leafnodes in an archetype are 
always constraints on primitives, and XML can represent all types of 
primitives.


Everything that is not a leafnode is a structure, the structure is 
object oriented and easy to convert to XML, where the tag-names are the 
attribute-names, and the type-names are the typenames from the RM-XSD. 
The structure which is represented in XML is also easy to bring back to 
an object oriented structure.


It is just a notation, nothing else.

There are even standard java-classes (I thought in  JAXB)  which support 
this kind of things.


AQL is easy to convert to XQuery, but maybe I would prefer XQuery, and 
it is, for a non technical person possible to use a drag and drop 
interface which represents the archetypepaths.


I am missing your point, or better sad, we are not on the same track of 
understanding


Best regards
Bert




In short: It makes sense to not use the openEHR CDR for such queries 
but export it to something more suited for such purpose. This does not 
need to be a classical Data Warehouse (it has only limited use for the 
questions clinicians have) but certainly XML would not be my first 
choice for the analytics layer.


Birger


Von meinem iPad gesendet

Am 13.02.2016 um 17:56 schrieb Bert Verhees >:



Thanks,

I always forget it is weekend, as independent developer, there ain't 
no such thing as a day of.


I agree that mixing query-languages is ugly, and I don't see the 
necessity.


But maybe, you will explain that next week

Bert

On 13-02-16 17:46, Birger Haarbrandt wrote:

Hi Bert,

I will post some more thoughts on these things after the weekend :) 
To just give a quick answer: imo it's important to have a flexible 
data format like the one I2B2 uses (roughly said EAV) to mix openEHR 
data with non-openEHR data. Making analysis on XML 
documents/databaes might prevent integration of other sources (and 
even if its possible like in SQL Server or Oracle, queries that mix 
XQuery and SQL might become pretty ugly. And I see no good way to 
provide a drag & drop interface to researchers/physicians to make 
queries).


Cheers,

Birger

Von meinem iPad gesendet


Am 13.02.2016 um 16:24 schrieb Bert Verhees :


No comments, on the other hand it is Saturday

I had left out some necessary technical details.
I will possible build it and then have possible the fastest 
two-level-modeling engine in the world, which will, of course, also 
support OpenEHR.

So it is not really bad that this happens.

When it is ready, I will make an announcement.

Best regards and enjoy the weekend.
Bert Verhees


On 12-02-16 20:49, Bert Verhees wrote:

On 12-02-16 18:26, Erik Sundvall wrote:
if you are experimenting with open source native XML DBs for 
openEHR, it preformed well for "clinical" patient-specific 
querying even though all xml databases we tested were not 
suitable for ad hoc epidemiological population queries (without 
query specific indexing)



A very interesting paper. I have some first opinions on that. But 
first I need to explain what I think about the matter.
I have not prepared the story below, so there may be things which 
I write to fast. See it as provisional view, not as a hard opinion.

-
There are relational database-configurations for OLAP and for 
OLTP. The combination is hard to find. There are reasons. This is 
a classic problem.


You need specific indexes for data-mining (OLAP), and for every 
extra data-mining query you need more indexes, especially if you 
don't have time to wait a night for the result. Those extra 
indexes stand in the way for transactional processing (OLTP) 
because they need to be updated, and that is unnecessary burden 
for the OLTP-processes, as longer as the database exist, the 
burden becomes heavier.

That is why OLAP and OLTP are not often combined in one configuration.

So many professional databases have extra features for OLAP, I 
worked, years ago with Oracle for a big Dutch television company, 
and my main job was to create indexes for marketing purposes.
We ran those unusual queries during the night and stored the 
result in special tables, Oracle called them "materialized views".
The day after, those views were processed in analyzing software, 
like SPSS, and after 

Re: Archetype relational mapping - a practical openEHR persistence solution

[Seref Arikan]

Thanks,
My feelings for postgresql are well known, it is a great piece of code.
Final question, do you support AQL with your current design?

On Tue, Jan 26, 2016 at 10:40 AM, Jan-Marc Verlinden <
jan-m...@medvision360.com> wrote:

> Hi Seref,
>
> We tested with lots of queries, not with large datasets (tens of
> thousands) within the system itself. But given the nature of the "normal"
> relational DB schema and queries, this should be no problem at all. It's
> good old postgres..:-).
>
> Jan-Marc
>
> Op di 26 jan. 2016 om 10:53 schreef Seref Arikan <
> serefari...@kurumsalteknoloji.com>:
>
>> Hi Jan-Marc,
>>
>> To clarify: when you say huge: do you mean that the result set is huge,
>> or the the amount of existing data is huge? Are you able to comment on how
>> query performance changes/stays the same when the result set size begins to
>> grow?
>>
>> On Tue, Jan 26, 2016 at 9:38 AM, Jan-Marc Verlinden <
>> jan-m...@medvision360.com> wrote:
>>
>>> I believe your paper is about performance of an openEHR based system
>>> with a relational database. It does not argues if this is the right
>>> approach. And specifically on the performance we could not agree more...:-).
>>>
>>> In the past year we worked on three different versions, all completely
>>> openEHR compliant. We compared them, so I believe we are entitled to
>>> discuss. Let's see:
>>>
>>>1. Our first version was Java based with a postgres DB, everything
>>>stored as path/values.
>>>Every query would take about a second. We did not even try complex
>>>queries..:-). Also the GUI side did not know what to do with the 
>>> pathvalues.
>>>2. Second version was completely XML based, lots of Java with an
>>>Exist DB. This version can be found in GitHub.
>>>Results: a single query over 900 records took 100ms. But the scary
>>>part was that performance exploded linear, so 9 would take about 10
>>>seconds, even for the most simple (already indexed and optimized) query.
>>>3. Our current stack converts archetypes to an Object model and
>>>persists this model to postgres. Indeed this needs some fancy tricks
>>>(@Thomas), but it's doable...:-).
>>>Performance is comparable with the findings in the paper, even with
>>>huge queries. Performance is steady at about 1 to 3ms per query. No
>>>optimization done yet, is not yet needed but could/will make it even 
>>> faster.
>>>
>>> Cheers, Jan-Marc
>>>
>>> Op di 26 jan. 2016 om 09:55 schreef 吕旭东 :
>>>
 Dear all,

 I just found there are lots of comments on our recent paper. Thanks for
 all these comments, I will read them and reply later.

 @Shinji, Of course the slides could be shared.

 Regards
 Xudong

 2016-01-26 10:44 GMT+08:00 Shinji KOBAYASHI :

> Hi Ian and all,
>
> We, openEHR Japan had an unconference with Dr Lu and he gave us a
> presentation for us about this research.
> I will ask him if the slides would be shareble.
>
> Shinji KOBAYASHI
>
> 2016-01-25 22:04 GMT+09:00 Ian McNicoll :
>
>> Interesting paper from China
>>
>>
>> http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
>>
>> Ian
>>
>> Dr Ian McNicoll
>> mobile +44 (0)775 209 7859
>> office +44 (0)1536 414994
>> skype: ianmcnicoll
>> email: i...@freshehr.com
>> twitter: @ianmcnicoll
>>
>> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
>> Director, freshEHR Clinical Informatics Ltd.
>> Director, HANDIHealth CIC
>> Hon. Senior Research Associate, CHIME, UCL
>>
>> ___
>> openEHR-technical mailing list
>> openEHR-technical@lists.openehr.org
>>
>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>
>
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>

 ___
 openEHR-technical mailing list
 openEHR-technical@lists.openehr.org

 http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>>
>>> --
>>>
>>> Jan-Marc Verlinden
>>> MedVision (mobile)
>>>
>>> *MedVision BV*
>>> Aagje Dekenkade 71
>>> 2251 ZV, Voorschoten
>>> www.medvision360.com
>>>
>>> This e-mail message is intended exclusively for the addressee(s). Please
>>> inform us immediately if you are not the addressee.
>>>
>>
>>> ___
>>> openEHR-technical mailing list
>>> openEHR-technical@lists.openehr.org
>>>
>>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>>
>> ___
>> openEHR-technical mailing 

Re: Archetype relational mapping - a practical openEHR persistence solution

[Thomas Beale]

I have not performance tested my own implementation (although I did 
actually build one over a decade ago). But the Informix path system was 
real and performant (they used a hierachical number node code approach 
e.g. 1.2.3, 1.2.3.1.2 etc). There are others I have read about as well, 
but I'd have to spend a bit of time to track them down, not having 
looked at it for a few years.


The reason why it can be fast is that you don't have many columns and 
essentially no joins, and you can do extremely efficient matching on 
number spaces to determine the 'type' of something (i.e. whether it is a 
systolic BP or whatever) /before/ you decompress the value (a 
DV_QUANTITY). Then you do value comparison (e.g. bp > 160 or whatever) 
as a second step.


Before doing any of this, you apply basic AQL logic that cuts out whole 
parts of the DB because their values are not inside (AQL CONTAINS; XML 
'//') the required kind of Composition.


If you have run an actual study of path-based performance and found it 
not to be working, it would be very interesting to know the details. It 
may be that with some adjustments you can significantly change the 
performance characteristic.


- thomas

On 26/01/2016 10:43, Jan-Marc Verlinden wrote:

Hi Thomas,

/_this is also what I would expect. Path-based storage does rely on 
very smart ways to figure match terms in a query to paths of course._/


Did you test, or is this theoretical?

Jan-Marc



___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Seref Arikan]

Thanks for the answers Jan-Marc.



On Tue, Jan 26, 2016 at 11:55 AM, Jan-Marc Verlinden <
jan-m...@medvision360.com> wrote:

> Dear Seref,
>
> At moment we do not support AQL, mainly because none of our current
> partners/developers are asking for it. We have discussed using AQL
> internally several (lots of-) times, but also with for instance Ian.
>
> If this changes, so if there is a need for AQL, we could easily support
> it. Its just a matter of development. In that case our architecture is to
> convert AQL to SQL, and go from there.
>
> Regards, Jan-Marc
>
> Op di 26 jan. 2016 om 12:26 schreef Seref Arikan <
> serefari...@kurumsalteknoloji.com>:
>
>> Thanks,
>> My feelings for postgresql are well known, it is a great piece of code.
>> Final question, do you support AQL with your current design?
>>
>> On Tue, Jan 26, 2016 at 10:40 AM, Jan-Marc Verlinden <
>> jan-m...@medvision360.com> wrote:
>>
>>> Hi Seref,
>>>
>>> We tested with lots of queries, not with large datasets (tens of
>>> thousands) within the system itself. But given the nature of the "normal"
>>> relational DB schema and queries, this should be no problem at all. It's
>>> good old postgres..:-).
>>>
>>> Jan-Marc
>>>
>>> Op di 26 jan. 2016 om 10:53 schreef Seref Arikan <
>>> serefari...@kurumsalteknoloji.com>:
>>>
 Hi Jan-Marc,

 To clarify: when you say huge: do you mean that the result set is huge,
 or the the amount of existing data is huge? Are you able to comment on how
 query performance changes/stays the same when the result set size begins to
 grow?

 On Tue, Jan 26, 2016 at 9:38 AM, Jan-Marc Verlinden <
 jan-m...@medvision360.com> wrote:

> I believe your paper is about performance of an openEHR based system
> with a relational database. It does not argues if this is the right
> approach. And specifically on the performance we could not agree 
> more...:-).
>
> In the past year we worked on three different versions, all completely
> openEHR compliant. We compared them, so I believe we are entitled to
> discuss. Let's see:
>
>1. Our first version was Java based with a postgres DB, everything
>stored as path/values.
>Every query would take about a second. We did not even try complex
>queries..:-). Also the GUI side did not know what to do with the 
> pathvalues.
>2. Second version was completely XML based, lots of Java with an
>Exist DB. This version can be found in GitHub.
>Results: a single query over 900 records took 100ms. But the scary
>part was that performance exploded linear, so 9 would take about 10
>seconds, even for the most simple (already indexed and optimized) 
> query.
>3. Our current stack converts archetypes to an Object model and
>persists this model to postgres. Indeed this needs some fancy tricks
>(@Thomas), but it's doable...:-).
>Performance is comparable with the findings in the paper, even
>with huge queries. Performance is steady at about 1 to 3ms per query. 
> No
>optimization done yet, is not yet needed but could/will make it even 
> faster.
>
> Cheers, Jan-Marc
>
> Op di 26 jan. 2016 om 09:55 schreef 吕旭东 :
>
>> Dear all,
>>
>> I just found there are lots of comments on our recent paper. Thanks
>> for all these comments, I will read them and reply later.
>>
>> @Shinji, Of course the slides could be shared.
>>
>> Regards
>> Xudong
>>
>> 2016-01-26 10:44 GMT+08:00 Shinji KOBAYASHI :
>>
>>> Hi Ian and all,
>>>
>>> We, openEHR Japan had an unconference with Dr Lu and he gave us a
>>> presentation for us about this research.
>>> I will ask him if the slides would be shareble.
>>>
>>> Shinji KOBAYASHI
>>>
>>> 2016-01-25 22:04 GMT+09:00 Ian McNicoll :
>>>
 Interesting paper from China


 http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0

 Ian

 Dr Ian McNicoll
 mobile +44 (0)775 209 7859
 office +44 (0)1536 414994
 skype: ianmcnicoll
 email: i...@freshehr.com
 twitter: @ianmcnicoll

 Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
 Director, freshEHR Clinical Informatics Ltd.
 Director, HANDIHealth CIC
 Hon. Senior Research Associate, CHIME, UCL

 ___
 openEHR-technical mailing list
 openEHR-technical@lists.openehr.org

 http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

>>>
>>>
>>> ___
>>> openEHR-technical mailing list
>>> openEHR-technical@lists.openehr.org
>>>

Re: Archetype relational mapping - a practical openEHR persistence solution

[吕旭东]

We haven't convert RM to relational model, it will cause lots of JOIN
operation and low performance. Actually, we using "storage template", which
is a specialization of archetype for storage purpose, and is generated
automatically according to existing multiple templates (If more templates
are added, the "storage template" will be updated, but in real case, it
will not be updated too often).

Best Regards
Xudong

2016-01-26 1:42 GMT+08:00 Bert Verhees :

> Another problem is you have to convert your object oriented model (which
> RM is) to a relational model, which becomes complex in converting
> templates/aql to SQL. I have been that way. More then five years ago I left
> it. It is difficult doable, if you want a full featured openehr kernel. I
> would never recommend going this way, unless someone has a really smart
> idea.
>
> It can work for a light featured openehr light derived application model.
>
> Best regards
> Bert
> Op 25 jan. 2016 15:26 schreef "pazospa...@hotmail.com" <
> pazospa...@hotmail.com>:
>
>> I talked about this approach with a colleague from China during MEDINFO.
>> The problem is your schema grows with your archetypes. Also, that storing
>> data from many templates that don't use all the fields in the archetype,
>> will generate sparse tables (lots of null columns). I told him it was
>> easier to do an ORM from the IM, because the schema doesn't change and
>> allows to store data from any archetype/template. But they already have a
>> system working this way.
>>
>>
>> Sent from my LG Mobile
>>
>> -- Original message--
>>
>> *From: *Ian McNicoll
>>
>> *Date: *Mon, Jan 25, 2016 10:06
>>
>> *To: *For openEHR technical discussions;
>>
>> *Subject:*Archetype relational mapping - a practical openEHR persistence
>> solution
>> Interesting paper from China
>>
>>
>> http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
>>
>> Ian
>>
>> Dr Ian McNicoll
>> mobile +44 (0)775 209 7859 <+44%20(0)775%20209%207859>
>> office +44 (0)1536 414994 <+44%20(0)1536%20414994>
>> skype: ianmcnicoll
>> email: i...@freshehr.com
>> twitter: @ianmcnicoll
>>
>> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
>> Director, freshEHR Clinical Informatics Ltd.
>> Director, HANDIHealth CIC
>> Hon. Senior Research Associate, CHIME, UCL
>>
>> ___
>> openEHR-technical mailing list
>> openEHR-technical@lists.openehr.org
>>
>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Jan-Marc Verlinden]

Hi Thomas,

*this is also what I would expect. Path-based storage does rely on very
smart ways to figure match terms in a query to paths of course.*

Did you test, or is this theoretical?

Jan-Marc

Op di 26 jan. 2016 om 11:36 schreef Thomas Beale :

>
>
> On 26/01/2016 09:51, Bert Verhees wrote:
>
> On 26-01-16 10:38, Jan-Marc Verlinden wrote:
>
>
>- Our first version was Java based with a postgres DB, everything
>stored as path/values.
>Every query would take about a second. We did not even try complex
>queries..:-). Also the GUI side did not know what to do with the 
> pathvalues.
>
> Hi Jan-Marc,
>
> There where some problems handling the path/values, most problems were
> based on giving a semantic meaning to the paths.
> Storing path and an according a value is very, very quick. I asked
> database specialists, and they say this is the best way to go until
> billions of records.
>
>
> this is also what I would expect. Path-based storage does rely on very
> smart ways to figure match terms in a query to paths of course. There are
> some tricks to use here. For example, the path to systolic BP DV_QUANTITY
> node from the archetype is
>
> /data[id2|history|]/events[id7|any
> event|]/data[id4]/items[id5|Systolic|]/value
>
> In the whole of CKM there are probably about 7,000 'interesting' leaf
> paths (if you assume that you crunch DATA_VALUE subtypes into little
> blobs). That's a tiny number. Assume that when they've modelled everything
> in medicine (outside of genomics and proteomics) that we have 50,000 such
> 'paths of interest'. That's a very small number. These paths can be mapped
> in smart ways to a 64-but number space so that finding out if a specific
> query term is in some EHR is very quick. When you include a coded list of
> archetype ids in the mix, I think querying can be made extremly quick.
>
> The devil is in the details. Various large DBs used path-based approachs
> in the past, Informix was one.
>
>
>
> Also easy to migrate to another database, for clustering or other reasons.
>
> But there are some problems to solve, which were harder to solve five
> years ago.
>
> One problem is the GUI builders, they are looking at a difficult to
> understand database-approach, and also easy to create errors in, hard to
> debug.
> They need JSON to write their datasets in.
>
> The other problem is querying. As long as it are predefined queries, you
> can do anything, but then you are no different from an old monolithic
> system.
> But writing new templates heavily relies on on the fly query building
>
> There are however, some technological progresses, also in the open source
> domain.
>
> The path/value storage could come to a better life again with help of
> ANTLR, which can help to interpret AQL for this purpose. I even think this
> is promising.
>
> Let engineers read the Definitive ANTLR4 Reference by Terence Parr, and
> read it with path/values in the back of the mind. Both the GUI problem as
> the query problem can be solved.
>
> It should be worth the spent time and the price of the book ;-)
>
>
> It is.
>
>
> - thomas
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

-- 

Jan-Marc Verlinden
MedVision (mobile)

-- 
*MedVision BV*
Aagje Dekenkade 71
2251 ZV, Voorschoten
www.medvision360.com

This e-mail message is intended exclusively for the addressee(s). Please 
inform us immediately if you are not the addressee. 
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Bert Verhees]

On 26-01-16 10:15, 吕旭东 wrote:
We haven't convert RM to relational model, it will cause lots of JOIN 
operation and low performance. Actually, we using "storage template", 
which is a specialization of archetype for storage purpose, and is 
generated automatically according to existing multiple templates (If 
more templates are added, the "storage template" will be updated, but 
in real case, it will not be updated too often).


So, if I understand, it is an -on the fly- optimization. So to say if in 
an archetype something is not there, then there is no storage needed to 
store that, no query needed to handle that (because it is not there). So 
you store every archetype with its own optimization in an own structure?

You optimize the RM for a specific archetype

If I understand it well, this is a very original approach.
Excuse me for reading your document to quick. I will now read it more 
carefully


Thank you very much for sharing.
Bert



Best Regards
Xudong

2016-01-26 1:42 GMT+08:00 Bert Verhees >:


Another problem is you have to convert your object oriented model
(which RM is) to a relational model, which becomes complex in
converting templates/aql to SQL. I have been that way. More then
five years ago I left it. It is difficult doable, if you want a
full featured openehr kernel. I would never recommend going this
way, unless someone has a really smart idea.

It can work for a light featured openehr light derived application
model.

Best regards
Bert

Op 25 jan. 2016 15:26 schreef "pazospa...@hotmail.com
" >:

I talked about this approach with a colleague from China
during MEDINFO. The problem is your schema grows with your
archetypes. Also, that storing data from many templates that
don't use all the fields in the archetype, will generate
sparse tables (lots of null columns). I told him it was easier
to do an ORM from the IM, because the schema doesn't change
and allows to store data from any archetype/template. But they
already have a system working this way.


Sent from my LG Mobile

-- Original message--

*From: *Ian McNicoll

*Date: *Mon, Jan 25, 2016 10:06

*To: *For openEHR technical discussions;

*Subject:*Archetype relational mapping - a practical openEHR
persistence solution

Interesting paper from China


http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0

Ian

Dr Ian McNicoll
mobile +44 (0)775 209 7859 
office +44 (0)1536 414994 
skype: ianmcnicoll
email: i...@freshehr.com 
twitter: @ianmcnicoll

Co-Chair, openEHR Foundation ian.mcnic...@openehr.org

Director, freshEHR Clinical Informatics Ltd.
Director, HANDIHealth CIC
Hon. Senior Research Associate, CHIME, UCL

___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org


http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org


___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org


http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org




___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org


___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Seref Arikan]

Hi Jan-Marc,

To clarify: when you say huge: do you mean that the result set is huge, or
the the amount of existing data is huge? Are you able to comment on how
query performance changes/stays the same when the result set size begins to
grow?

On Tue, Jan 26, 2016 at 9:38 AM, Jan-Marc Verlinden <
jan-m...@medvision360.com> wrote:

> I believe your paper is about performance of an openEHR based system with
> a relational database. It does not argues if this is the right approach.
> And specifically on the performance we could not agree more...:-).
>
> In the past year we worked on three different versions, all completely
> openEHR compliant. We compared them, so I believe we are entitled to
> discuss. Let's see:
>
>1. Our first version was Java based with a postgres DB, everything
>stored as path/values.
>Every query would take about a second. We did not even try complex
>queries..:-). Also the GUI side did not know what to do with the 
> pathvalues.
>2. Second version was completely XML based, lots of Java with an Exist
>DB. This version can be found in GitHub.
>Results: a single query over 900 records took 100ms. But the scary
>part was that performance exploded linear, so 9 would take about 10
>seconds, even for the most simple (already indexed and optimized) query.
>3. Our current stack converts archetypes to an Object model and
>persists this model to postgres. Indeed this needs some fancy tricks
>(@Thomas), but it's doable...:-).
>Performance is comparable with the findings in the paper, even with
>huge queries. Performance is steady at about 1 to 3ms per query. No
>optimization done yet, is not yet needed but could/will make it even 
> faster.
>
> Cheers, Jan-Marc
>
> Op di 26 jan. 2016 om 09:55 schreef 吕旭东 :
>
>> Dear all,
>>
>> I just found there are lots of comments on our recent paper. Thanks for
>> all these comments, I will read them and reply later.
>>
>> @Shinji, Of course the slides could be shared.
>>
>> Regards
>> Xudong
>>
>> 2016-01-26 10:44 GMT+08:00 Shinji KOBAYASHI :
>>
>>> Hi Ian and all,
>>>
>>> We, openEHR Japan had an unconference with Dr Lu and he gave us a
>>> presentation for us about this research.
>>> I will ask him if the slides would be shareble.
>>>
>>> Shinji KOBAYASHI
>>>
>>> 2016-01-25 22:04 GMT+09:00 Ian McNicoll :
>>>
 Interesting paper from China


 http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0

 Ian

 Dr Ian McNicoll
 mobile +44 (0)775 209 7859
 office +44 (0)1536 414994
 skype: ianmcnicoll
 email: i...@freshehr.com
 twitter: @ianmcnicoll

 Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
 Director, freshEHR Clinical Informatics Ltd.
 Director, HANDIHealth CIC
 Hon. Senior Research Associate, CHIME, UCL

 ___
 openEHR-technical mailing list
 openEHR-technical@lists.openehr.org

 http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

>>>
>>>
>>> ___
>>> openEHR-technical mailing list
>>> openEHR-technical@lists.openehr.org
>>>
>>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>>
>>
>> ___
>> openEHR-technical mailing list
>> openEHR-technical@lists.openehr.org
>>
>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
> --
>
> Jan-Marc Verlinden
> MedVision (mobile)
>
> *MedVision BV*
> Aagje Dekenkade 71
> 2251 ZV, Voorschoten
> www.medvision360.com
>
> This e-mail message is intended exclusively for the addressee(s). Please
> inform us immediately if you are not the addressee.
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Jan-Marc Verlinden]

Dear Seref,

At moment we do not support AQL, mainly because none of our current
partners/developers are asking for it. We have discussed using AQL
internally several (lots of-) times, but also with for instance Ian.

If this changes, so if there is a need for AQL, we could easily support it.
Its just a matter of development. In that case our architecture is to
convert AQL to SQL, and go from there.

Regards, Jan-Marc

Op di 26 jan. 2016 om 12:26 schreef Seref Arikan <
serefari...@kurumsalteknoloji.com>:

> Thanks,
> My feelings for postgresql are well known, it is a great piece of code.
> Final question, do you support AQL with your current design?
>
> On Tue, Jan 26, 2016 at 10:40 AM, Jan-Marc Verlinden <
> jan-m...@medvision360.com> wrote:
>
>> Hi Seref,
>>
>> We tested with lots of queries, not with large datasets (tens of
>> thousands) within the system itself. But given the nature of the "normal"
>> relational DB schema and queries, this should be no problem at all. It's
>> good old postgres..:-).
>>
>> Jan-Marc
>>
>> Op di 26 jan. 2016 om 10:53 schreef Seref Arikan <
>> serefari...@kurumsalteknoloji.com>:
>>
>>> Hi Jan-Marc,
>>>
>>> To clarify: when you say huge: do you mean that the result set is huge,
>>> or the the amount of existing data is huge? Are you able to comment on how
>>> query performance changes/stays the same when the result set size begins to
>>> grow?
>>>
>>> On Tue, Jan 26, 2016 at 9:38 AM, Jan-Marc Verlinden <
>>> jan-m...@medvision360.com> wrote:
>>>
 I believe your paper is about performance of an openEHR based system
 with a relational database. It does not argues if this is the right
 approach. And specifically on the performance we could not agree 
 more...:-).

 In the past year we worked on three different versions, all completely
 openEHR compliant. We compared them, so I believe we are entitled to
 discuss. Let's see:

1. Our first version was Java based with a postgres DB, everything
stored as path/values.
Every query would take about a second. We did not even try complex
queries..:-). Also the GUI side did not know what to do with the 
 pathvalues.
2. Second version was completely XML based, lots of Java with an
Exist DB. This version can be found in GitHub.
Results: a single query over 900 records took 100ms. But the scary
part was that performance exploded linear, so 9 would take about 10
seconds, even for the most simple (already indexed and optimized) query.
3. Our current stack converts archetypes to an Object model and
persists this model to postgres. Indeed this needs some fancy tricks
(@Thomas), but it's doable...:-).
Performance is comparable with the findings in the paper, even with
huge queries. Performance is steady at about 1 to 3ms per query. No
optimization done yet, is not yet needed but could/will make it even 
 faster.

 Cheers, Jan-Marc

 Op di 26 jan. 2016 om 09:55 schreef 吕旭东 :

> Dear all,
>
> I just found there are lots of comments on our recent paper. Thanks
> for all these comments, I will read them and reply later.
>
> @Shinji, Of course the slides could be shared.
>
> Regards
> Xudong
>
> 2016-01-26 10:44 GMT+08:00 Shinji KOBAYASHI :
>
>> Hi Ian and all,
>>
>> We, openEHR Japan had an unconference with Dr Lu and he gave us a
>> presentation for us about this research.
>> I will ask him if the slides would be shareble.
>>
>> Shinji KOBAYASHI
>>
>> 2016-01-25 22:04 GMT+09:00 Ian McNicoll :
>>
>>> Interesting paper from China
>>>
>>>
>>> http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
>>>
>>> Ian
>>>
>>> Dr Ian McNicoll
>>> mobile +44 (0)775 209 7859
>>> office +44 (0)1536 414994
>>> skype: ianmcnicoll
>>> email: i...@freshehr.com
>>> twitter: @ianmcnicoll
>>>
>>> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
>>> Director, freshEHR Clinical Informatics Ltd.
>>> Director, HANDIHealth CIC
>>> Hon. Senior Research Associate, CHIME, UCL
>>>
>>> ___
>>> openEHR-technical mailing list
>>> openEHR-technical@lists.openehr.org
>>>
>>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>>
>>
>>
>> ___
>> openEHR-technical mailing list
>> openEHR-technical@lists.openehr.org
>>
>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> 

Re: Archetype relational mapping - a practical openEHR persistence solution

[吕旭东]

Dear all,

I just found there are lots of comments on our recent paper. Thanks for all
these comments, I will read them and reply later.

@Shinji, Of course the slides could be shared.

Regards
Xudong

2016-01-26 10:44 GMT+08:00 Shinji KOBAYASHI :

> Hi Ian and all,
>
> We, openEHR Japan had an unconference with Dr Lu and he gave us a
> presentation for us about this research.
> I will ask him if the slides would be shareble.
>
> Shinji KOBAYASHI
>
> 2016-01-25 22:04 GMT+09:00 Ian McNicoll :
>
>> Interesting paper from China
>>
>>
>> http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
>>
>> Ian
>>
>> Dr Ian McNicoll
>> mobile +44 (0)775 209 7859
>> office +44 (0)1536 414994
>> skype: ianmcnicoll
>> email: i...@freshehr.com
>> twitter: @ianmcnicoll
>>
>> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
>> Director, freshEHR Clinical Informatics Ltd.
>> Director, HANDIHealth CIC
>> Hon. Senior Research Associate, CHIME, UCL
>>
>> ___
>> openEHR-technical mailing list
>> openEHR-technical@lists.openehr.org
>>
>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>
>
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Seref Arikan]

Hi Pablo,
I agree with you re the indirect use of IM. I don't necessarily see a big
problem with this paper's approach because every approach has its
downsides. In fact, some really successful projects and products are built
on designs that are shunned by the "collective wisdom" of academia and
engineering communities, but they work, they do the job, and most important
of all, the cost of dealing with design related issues is acceptable, given
the expectations from the system.
This is the key: what is the expectation? What defines success in an
openEHR implementation? It is very context specific, with human,
institutional and other factors having their effect on technical factors.
I'm not saying that you're suggesting anything on the contrary, just trying
to make a point that this is an implementation that made it to a
functioning state and many factors in that process is unknown to us. The
authors may not even be able to share those factors.
If my memory is correct, when I read this paper some time ago, it mentioned
an earlier study, probably by the same group, but I could not access it.
That'd be interesting to read as well.



On Tue, Jan 26, 2016 at 1:52 AM, pablo pazos <pazospa...@hotmail.com> wrote:

> ORM is not a problem with current tools. In fact frameworks like Hibernate
> and Grails make Object-Relational Mapping something enjoyable to work with.
> I think the problem with the described approach is the growth of the
> relational schema when your knowledge base grows.
>
> But there are design challenges, ORM doesn't solve all the problems
> itself. IMHO, the object model that should be mapped to relational, if
> relational is chosen as DBMS, is not the raw openEHR IM. Simplifications
> over the IM are needed in order to prevent excessive JOINs and huge
> hierarchies. In fact I teach this in one of my courses and this was part of
> the tutorial we did on MEDINFO. For example, the OBJECT_REFs can be
> designed as simple relationships, because plays the role of a FK in the
> object model. There are many simplifications that can be done to reach an
> object model that is compatible with the openEHR model but more "relational
> friendly".
>
> --
> Kind regards,
> Eng. Pablo Pazos Gutiérrez
> http://cabolabs.com <http://cabolabs.com/es/home>
> <http://twitter.com/ppazos>
>
> ----------
> Date: Mon, 25 Jan 2016 18:42:01 +0100
> Subject: Re: Archetype relational mapping - a practical openEHR
> persistence solution
> From: bert.verh...@rosa.nl
> To: openehr-technical@lists.openehr.org
>
>
> Another problem is you have to convert your object oriented model (which
> RM is) to a relational model, which becomes complex in converting
> templates/aql to SQL. I have been that way. More then five years ago I left
> it. It is difficult doable, if you want a full featured openehr kernel. I
> would never recommend going this way, unless someone has a really smart
> idea.
>
> It can work for a light featured openehr light derived application model.
>
> Best regards
> Bert
> Op 25 jan. 2016 15:26 schreef "pazospa...@hotmail.com" <
> pazospa...@hotmail.com>:
>
> I talked about this approach with a colleague from China during MEDINFO.
> The problem is your schema grows with your archetypes. Also, that storing
> data from many templates that don't use all the fields in the archetype,
> will generate sparse tables (lots of null columns). I told him it was
> easier to do an ORM from the IM, because the schema doesn't change and
> allows to store data from any archetype/template. But they already have a
> system working this way.
>
>
> Sent from my LG Mobile
>
> -- Original message--
>
> *From: *Ian McNicoll
>
> *Date: *Mon, Jan 25, 2016 10:06
>
> *To: *For openEHR technical discussions;
>
> *Subject:*Archetype relational mapping - a practical openEHR persistence
> solution
> Interesting paper from China
>
>
> http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
>
> Ian
>
> Dr Ian McNicoll
> mobile +44 (0)775 209 7859
> office +44 (0)1536 414994
> skype: ianmcnicoll
> email: i...@freshehr.com
> twitter: @ianmcnicoll
>
> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
> Director, freshEHR Clinical Informatics Ltd.
> Director, HANDIHealth CIC
> Hon. Senior Research Associate, CHIME, UCL
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
>
> ___ openEHR-technical mailing
> list openEHR-technical@lists.openehr.org
> http://lists.openehr.o

Re: Archetype relational mapping - a practical openEHR persistence solution

[Jan-Marc Verlinden]

Hi Seref,

We tested with lots of queries, not with large datasets (tens of thousands)
within the system itself. But given the nature of the "normal" relational
DB schema and queries, this should be no problem at all. It's good old
postgres..:-).

Jan-Marc

Op di 26 jan. 2016 om 10:53 schreef Seref Arikan <
serefari...@kurumsalteknoloji.com>:

> Hi Jan-Marc,
>
> To clarify: when you say huge: do you mean that the result set is huge, or
> the the amount of existing data is huge? Are you able to comment on how
> query performance changes/stays the same when the result set size begins to
> grow?
>
> On Tue, Jan 26, 2016 at 9:38 AM, Jan-Marc Verlinden <
> jan-m...@medvision360.com> wrote:
>
>> I believe your paper is about performance of an openEHR based system with
>> a relational database. It does not argues if this is the right approach.
>> And specifically on the performance we could not agree more...:-).
>>
>> In the past year we worked on three different versions, all completely
>> openEHR compliant. We compared them, so I believe we are entitled to
>> discuss. Let's see:
>>
>>1. Our first version was Java based with a postgres DB, everything
>>stored as path/values.
>>Every query would take about a second. We did not even try complex
>>queries..:-). Also the GUI side did not know what to do with the 
>> pathvalues.
>>2. Second version was completely XML based, lots of Java with an
>>Exist DB. This version can be found in GitHub.
>>Results: a single query over 900 records took 100ms. But the scary
>>part was that performance exploded linear, so 9 would take about 10
>>seconds, even for the most simple (already indexed and optimized) query.
>>3. Our current stack converts archetypes to an Object model and
>>persists this model to postgres. Indeed this needs some fancy tricks
>>(@Thomas), but it's doable...:-).
>>Performance is comparable with the findings in the paper, even with
>>huge queries. Performance is steady at about 1 to 3ms per query. No
>>optimization done yet, is not yet needed but could/will make it even 
>> faster.
>>
>> Cheers, Jan-Marc
>>
>> Op di 26 jan. 2016 om 09:55 schreef 吕旭东 :
>>
>>> Dear all,
>>>
>>> I just found there are lots of comments on our recent paper. Thanks for
>>> all these comments, I will read them and reply later.
>>>
>>> @Shinji, Of course the slides could be shared.
>>>
>>> Regards
>>> Xudong
>>>
>>> 2016-01-26 10:44 GMT+08:00 Shinji KOBAYASHI :
>>>
 Hi Ian and all,

 We, openEHR Japan had an unconference with Dr Lu and he gave us a
 presentation for us about this research.
 I will ask him if the slides would be shareble.

 Shinji KOBAYASHI

 2016-01-25 22:04 GMT+09:00 Ian McNicoll :

> Interesting paper from China
>
>
> http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
>
> Ian
>
> Dr Ian McNicoll
> mobile +44 (0)775 209 7859
> office +44 (0)1536 414994
> skype: ianmcnicoll
> email: i...@freshehr.com
> twitter: @ianmcnicoll
>
> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
> Director, freshEHR Clinical Informatics Ltd.
> Director, HANDIHealth CIC
> Hon. Senior Research Associate, CHIME, UCL
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>


 ___
 openEHR-technical mailing list
 openEHR-technical@lists.openehr.org

 http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

>>>
>>> ___
>>> openEHR-technical mailing list
>>> openEHR-technical@lists.openehr.org
>>>
>>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>
>> --
>>
>> Jan-Marc Verlinden
>> MedVision (mobile)
>>
>> *MedVision BV*
>> Aagje Dekenkade 71
>> 2251 ZV, Voorschoten
>> www.medvision360.com
>>
>> This e-mail message is intended exclusively for the addressee(s). Please
>> inform us immediately if you are not the addressee.
>>
>
>> ___
>> openEHR-technical mailing list
>> openEHR-technical@lists.openehr.org
>>
>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

-- 

Jan-Marc Verlinden
MedVision (mobile)

-- 
*MedVision BV*
Aagje Dekenkade 71
2251 ZV, Voorschoten
www.medvision360.com

This e-mail message is intended exclusively for the addressee(s). Please 
inform us immediately if you are not the addressee. 

Re: Archetype relational mapping - a practical openEHR persistence solution

[Jan-Marc Verlinden]

Hi Thomas,

Your solution sounds interesting (looks like postgres hstore). But we are
(very) happy with our current stack, so we will not test it.

Jan-Marc

Op di 26 jan. 2016 om 12:40 schreef Thomas Beale :

>
> I have not performance tested my own implementation (although I did
> actually build one over a decade ago). But the Informix path system was
> real and performant (they used a hierachical number node code approach e.g.
> 1.2.3, 1.2.3.1.2 etc). There are others I have read about as well, but I'd
> have to spend a bit of time to track them down, not having looked at it for
> a few years.
>
> The reason why it can be fast is that you don't have many columns and
> essentially no joins, and you can do extremely efficient matching on number
> spaces to determine the 'type' of something (i.e. whether it is a systolic
> BP or whatever) *before* you decompress the value (a DV_QUANTITY). Then
> you do value comparison (e.g. bp > 160 or whatever) as a second step.
>
> Before doing any of this, you apply basic AQL logic that cuts out whole
> parts of the DB because their values are not inside (AQL CONTAINS; XML
> '//') the required kind of Composition.
>
> If you have run an actual study of path-based performance and found it not
> to be working, it would be very interesting to know the details. It may be
> that with some adjustments you can significantly change the performance
> characteristic.
>
>
> - thomas
>
>
> On 26/01/2016 10:43, Jan-Marc Verlinden wrote:
>
> Hi Thomas,
>
> *this is also what I would expect. Path-based storage does rely on very
> smart ways to figure match terms in a query to paths of course.*
>
> Did you test, or is this theoretical?
>
> Jan-Marc
>
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

-- 

Jan-Marc Verlinden
MedVision (mobile)

-- 
*MedVision BV*
Aagje Dekenkade 71
2251 ZV, Voorschoten
www.medvision360.com

This e-mail message is intended exclusively for the addressee(s). Please 
inform us immediately if you are not the addressee. 
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Thomas Beale]

On 26/01/2016 09:51, Bert Verhees wrote:

On 26-01-16 10:38, Jan-Marc Verlinden wrote:
# Our first version was Java based with a postgres DB, everything 
stored as path/values.
Every query would take about a second. We did not even try complex 
queries..:-). Also the GUI side did not know what to do with the 
pathvalues.

Hi Jan-Marc,

There where some problems handling the path/values, most problems were 
based on giving a semantic meaning to the paths.
Storing path and an according a value is very, very quick. I asked 
database specialists, and they say this is the best way to go until 
billions of records.


this is also what I would expect. Path-based storage does rely on very 
smart ways to figure match terms in a query to paths of course. There 
are some tricks to use here. For example, the path to systolic BP 
DV_QUANTITY node from the archetype is


/data[id2|history|]/events[id7|any 
event|]/data[id4]/items[id5|Systolic|]/value


In the whole of CKM there are probably about 7,000 'interesting' leaf 
paths (if you assume that you crunch DATA_VALUE subtypes into little 
blobs). That's a tiny number. Assume that when they've modelled 
everything in medicine (outside of genomics and proteomics) that we have 
50,000 such 'paths of interest'. That's a very small number. These paths 
can be mapped in smart ways to a 64-but number space so that finding out 
if a specific query term is in some EHR is very quick. When you include 
a coded list of archetype ids in the mix, I think querying can be made 
extremly quick.


The devil is in the details. Various large DBs used path-based approachs 
in the past, Informix was one.




Also easy to migrate to another database, for clustering or other reasons.

But there are some problems to solve, which were harder to solve five 
years ago.


One problem is the GUI builders, they are looking at a difficult to 
understand database-approach, and also easy to create errors in, hard 
to debug.

They need JSON to write their datasets in.

The other problem is querying. As long as it are predefined queries, 
you can do anything, but then you are no different from an old 
monolithic system.

But writing new templates heavily relies on on the fly query building

There are however, some technological progresses, also in the open 
source domain.


The path/value storage could come to a better life again with help of 
ANTLR, which can help to interpret AQL for this purpose. I even think 
this is promising.


Let engineers read the Definitive ANTLR4 Reference by Terence Parr, 
and read it with path/values in the back of the mind. Both the GUI 
problem as the query problem can be solved.


It should be worth the spent time and the price of the book ;-)



It is.

- thomas
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Bert Verhees]

On 26-01-16 11:25, Jan-Marc Verlinden wrote:
Oracle rdbms is indeed ACID. You have to convert the archetype (ADL or 
XML) to XSD, that will be used to store the XML in a relational DB 
(for better performance). 
One small correction, Jan-Marc, this is not possible. Not all 
constraints possible in an archetype can be expressed in XSD, you run 
against this very quick.


That is not needed also. The RM can be represented in XSD, that is 
enough, it is used as meta-information for Oracle. From the archetypes 
Schematron can be generated, and that can handle all constraints which 
can be defined in an archetype.


But when you use path/values, of course none of this is needed.

Bert

___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Bert Verhees]

On 26-01-16 11:35, Thomas Beale wrote:
50,000 such 'paths of interest'. That's a very small number. These 
paths can be mapped in smart ways to a 64-but number space so that 
finding out if a specific query term is in some EHR is very quick. 
When you include a coded list of archetype ids in the mix, I think 
querying can be made extremly quick.

I am impressed already ;-)

Very well possible it seems. The only thing is interfacing the beast 
towards the human minds of developers, but that is a one time job.


I'll keep this in mind.

Bert

___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[吕旭东]

Dear all,

I just found there are lots of comments on our recent paper. Thanks for all
these comments, I will read them and reply later.

@Shinji, Of course the slides could be shared.

Regards
Xudong

2016-01-26 10:44 GMT+08:00 Shinji KOBAYASHI :

> Hi Ian and all,
>
> We, openEHR Japan had an unconference with Dr Lu and he gave us a
> presentation for us about this research.
> I will ask him if the slides would be shareble.
>
> Shinji KOBAYASHI
>
> 2016-01-25 22:04 GMT+09:00 Ian McNicoll :
>
>> Interesting paper from China
>>
>>
>> http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
>>
>> Ian
>>
>> Dr Ian McNicoll
>> mobile +44 (0)775 209 7859
>> office +44 (0)1536 414994
>> skype: ianmcnicoll
>> email: i...@freshehr.com
>> twitter: @ianmcnicoll
>>
>> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
>> Director, freshEHR Clinical Informatics Ltd.
>> Director, HANDIHealth CIC
>> Hon. Senior Research Associate, CHIME, UCL
>>
>> ___
>> openEHR-technical mailing list
>> openEHR-technical@lists.openehr.org
>>
>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>
>
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Thomas Beale]

This is correct. The usual way I do this with an object model is to 
create a set of P_XXX classes, where 'P_' means 'persistent form'. The 
P_ classes are a transform of the main IM (whatever it is) that does 
things like


 * stringifying a lot of low-level fields
 * ignoring derived fields
 * occasionally using a blob transform where it makes sense.

Then one can start to consider tools like hibernate on the P_ model.

Both the openEHR BMM files and JSON/XML/ODIN save format use this approach.

- thomas


On 26/01/2016 01:52, pablo pazos wrote:
ORM is not a problem with current tools. In fact frameworks like 
Hibernate and Grails make Object-Relational Mapping something 
enjoyable to work with. I think the problem with the described 
approach is the growth of the relational schema when your knowledge 
base grows.


But there are design challenges, ORM doesn't solve all the problems 
itself. IMHO, the object model that should be mapped to relational, if 
relational is chosen as DBMS, is not the raw openEHR IM. 
Simplifications over the IM are needed in order to prevent excessive 
JOINs and huge hierarchies. In fact I teach this in one of my courses 
and this was part of the tutorial we did on MEDINFO. For example, the 
OBJECT_REFs can be designed as simple relationships, because plays the 
role of a FK in the object model. There are many simplifications that 
can be done to reach an object model that is compatible with the 
openEHR model but more "relational friendly".


--
Kind regards,
Eng. Pablo Pazos Gutiérrez
http://cabolabs.com 


___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

RE: Archetype relational mapping - a practical openEHR persistence solution

[Birger Haarbrandt]

Thanks! Would be nice to hear about the progress of this effort. Mikael Nyström <mikael.nyst...@liu.se> hat am 25. Januar 2016 um 23:20 geschrieben:Hi Birger, It might be this paper you are thinking of. Freire S, Sundvall E, Karlsson D, Lambrix P. Performance of XML Databases for Epidemiological Queries in Archetype-Based EHRs. Scandinavian Conference on Health Informatics 2012; October 2-3; Linköping; Sweden. P 51-57. Linköping Electronic Conference Proceedings. http://www.ep.liu.se/ecp/070/009/ecp1270009.pdfhttp://www.ep.liu.se/ecp_article/index.en.aspx?issue=070;article=009  Regards Mikael   From: openEHR-technical [mailto:openehr-technical-boun...@lists.openehr.org] On Behalf Of Birger Haarbrandt Sent: den 25 januari 2016 22:57 To: Bert Verhees; For openEHR technical discussions Subject: Re: Archetype relational mapping - a practical openEHR persistence solution Actually, we use such mappings to promote some "important" elements to relational tables to get sort of indices on the data. Otherwise, I don't think we would be able to do efficient ad-hoc cross-patient queries directly on the database. Exporting data to I2B2 or SSAS would be inconvenient sometimes and SQL still has some advantages. BTW, is there an AQL implementation that is optimized for such "epidemiolocial querying"? I think Erik Sundvall mentioned a hadoop-based research project a while ago. Best,Birger  Bert Verhees <bert.verh...@rosa.nl> hat am 25. Januar 2016 um 18:42 geschrieben:Another problem is you have to convert your object oriented model (which RM is) to a relational model, which becomes complex in converting templates/aql to SQL. I have been that way. More then five years ago I left it. It is difficult doable, if you want a full featured openehr kernel. I would never recommend going this way, unless someone has a really smart idea.It can work for a light featured openehr light derived application model.Best regards  BertOp 25 jan. 2016 15:26 schreef "pazospa...@hotmail.com" <pazospa...@hotmail.com>:I talked about this approach with a colleague from China during MEDINFO. The problem is your schema grows with your archetypes. Also, that storing data from many templates that don't use all the fields in the archetype, will generate sparse tables (lots of null columns). I told him it was easier to do an ORM from the IM, because the schema doesn't change and allows to store data from any archetype/template. But they already have a system working this way. Sent from my LG Mobile-- Original message--From: Ian McNicollDate: Mon, Jan 25, 2016 10:06To: For openEHR technical discussions;Subject:Archetype relational mapping - a practical openEHR persistence solutionInteresting paper from China http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0 IanDr Ian McNicoll mobile +44 (0)775 209 7859 office +44 (0)1536 414994 skype: ianmcnicoll email: i...@freshehr.com twitter: @ianmcnicoll  Co-Chair, openEHR Foundation ian.mcnic...@openehr.orgDirector, freshEHR Clinical Informatics Ltd. Director, HANDIHealth CIC Hon. Senior Research Associate, CHIME, UCL ___ openEHR-technical mailing list openEHR-technical@lists.openehr.org http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org___ openEHR-technical mailing list openEHR-technical@lists.openehr.org http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org   ___openEHR-technical mailing listopenEHR-technical@lists.openehr.orghttp://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

RE: Archetype relational mapping - a practical openEHR persistence solution

[Bert Verhees]

Not sure if you ask me Birger, but I will announce the github link here,
and will build a working prototype in open source. Maybe parallel I will do
something else, I am not sure about that

Bert

Op 26 jan. 2016 17:26 schreef "Birger Haarbrandt" :
>
> Thanks! Would be nice to hear about the progress of this effort.
>>
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: RE: Archetype relational mapping - a practical openEHR persistence solution

[birger . haarbrandt]

Hi Bert,

great to hear that you are working on this! I had in mind that someone from 
Linkoping University is working on a prototype, Erik Sundvall mentioned it once 
or twice if I remember correctly. My working group will surely try to find a 
good solution, too. However it would not make sense to invent the exact same 
wheel twice.

Best,

Birger



-Ursprüngliche Nachricht-
Gesendet: Dienstag, 26 Januar 2016 um 23:00:16 Uhr
Von: "Bert Verhees" <bert.verh...@rosa.nl>
An: "For openEHR technical discussions" <openehr-technical@lists.openehr.org>
Betreff: RE: Archetype relational mapping - a practical openEHR persistence 
solution
Not sure if you ask me Birger, but I will announce the github link here,
and will build a working prototype in open source. Maybe parallel I will do
something else, I am not sure about that

Bert

Op 26 jan. 2016 17:26 schreef "Birger Haarbrandt" <birger.haarbra...@plri.de
>:
>
> Thanks! Would be nice to hear about the progress of this effort.
>>
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Bert Verhees]

:52, pablo pazos wrote:
ORM is not a problem with current tools. In fact frameworks like 
Hibernate and Grails make Object-Relational Mapping something 
enjoyable to work with. I think the problem with the described 
approach is the growth of the relational schema when your knowledge 
base grows.


But there are design challenges, ORM doesn't solve all the problems 
itself. IMHO, the object model that should be mapped to relational, if 
relational is chosen as DBMS, is not the raw openEHR IM. 
Simplifications over the IM are needed in order to prevent excessive 
JOINs and huge hierarchies. In fact I teach this in one of my courses 
and this was part of the tutorial we did on MEDINFO. For example, the 
OBJECT_REFs can be designed as simple relationships, because plays the 
role of a FK in the object model. There are many simplifications that 
can be done to reach an object model that is compatible with the 
openEHR model but more "relational friendly".


--
Kind regards,
Eng. Pablo Pazos Gutiérrez
http://cabolabs.com <http://cabolabs.com/es/home>


Date: Mon, 25 Jan 2016 18:42:01 +0100
Subject: Re: Archetype relational mapping - a practical openEHR 
persistence solution

From: bert.verh...@rosa.nl
To: openehr-technical@lists.openehr.org

Another problem is you have to convert your object oriented model 
(which RM is) to a relational model, which becomes complex in 
converting templates/aql to SQL. I have been that way. More then five 
years ago I left it. It is difficult doable, if you want a full 
featured openehr kernel. I would never recommend going this way, 
unless someone has a really smart idea.


It can work for a light featured openehr light derived application model.

Best regards
Bert

Op 25 jan. 2016 15:26 schreef "pazospa...@hotmail.com 
<mailto:pazospa...@hotmail.com>" <pazospa...@hotmail.com 
<mailto:pazospa...@hotmail.com>>:


I talked about this approach with a colleague from China during
MEDINFO. The problem is your schema grows with your archetypes.
Also, that storing data from many templates that don't use all the
fields in the archetype, will generate sparse tables (lots of null
columns). I told him it was easier to do an ORM from the IM,
because the schema doesn't change and allows to store data from
any archetype/template. But they already have a system working
this way.


Sent from my LG Mobile

-- Original message--

*From: *Ian McNicoll

*Date: *Mon, Jan 25, 2016 10:06

*To: *For openEHR technical discussions;

*Subject:*Archetype relational mapping - a practical openEHR
persistence solution

Interesting paper from China


http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0

Ian

Dr Ian McNicoll
mobile +44 (0)775 209 7859
office +44 (0)1536 414994
skype: ianmcnicoll
email: i...@freshehr.com <mailto:i...@freshehr.com>
twitter: @ianmcnicoll

Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
<mailto:ian.mcnic...@openehr.org>
Director, freshEHR Clinical Informatics Ltd.
Director, HANDIHealth CIC
Hon. Senior Research Associate, CHIME, UCL

___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
<mailto:openEHR-technical@lists.openehr.org>

http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org


___ openEHR-technical 
mailing list openEHR-technical@lists.openehr.org 
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org



___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org


___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[吕旭东]

Thanks, pazospablo.

Yes, you are right. There may generate sparse tables. We found the problem
when we using it in the implementation. Currently, we update our method to
use "storage template" to specialise the archetypes. The "storage template"
will be generated automatically through combining multiple templates
defined by the domain experts.

Regards
Xudong

2016-01-25 22:25 GMT+08:00 pazospa...@hotmail.com :

> I talked about this approach with a colleague from China during MEDINFO.
> The problem is your schema grows with your archetypes. Also, that storing
> data from many templates that don't use all the fields in the archetype,
> will generate sparse tables (lots of null columns). I told him it was
> easier to do an ORM from the IM, because the schema doesn't change and
> allows to store data from any archetype/template. But they already have a
> system working this way.
>
>
> Sent from my LG Mobile
>
> -- Original message--
>
> *From: *Ian McNicoll
>
> *Date: *Mon, Jan 25, 2016 10:06
>
> *To: *For openEHR technical discussions;
>
> *Subject:*Archetype relational mapping - a practical openEHR persistence
> solution
> Interesting paper from China
>
>
> http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
>
> Ian
>
> Dr Ian McNicoll
> mobile +44 (0)775 209 7859 <+44%20(0)775%20209%207859>
> office +44 (0)1536 414994 <+44%20(0)1536%20414994>
> skype: ianmcnicoll
> email: i...@freshehr.com
> twitter: @ianmcnicoll
>
> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
> Director, freshEHR Clinical Informatics Ltd.
> Director, HANDIHealth CIC
> Hon. Senior Research Associate, CHIME, UCL
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Jan-Marc Verlinden]

I believe your paper is about performance of an openEHR based system with a
relational database. It does not argues if this is the right approach. And
specifically on the performance we could not agree more...:-).

In the past year we worked on three different versions, all completely
openEHR compliant. We compared them, so I believe we are entitled to
discuss. Let's see:

   1. Our first version was Java based with a postgres DB, everything
   stored as path/values.
   Every query would take about a second. We did not even try complex
   queries..:-). Also the GUI side did not know what to do with the pathvalues.
   2. Second version was completely XML based, lots of Java with an Exist
   DB. This version can be found in GitHub.
   Results: a single query over 900 records took 100ms. But the scary part
   was that performance exploded linear, so 9 would take about 10 seconds,
   even for the most simple (already indexed and optimized) query.
   3. Our current stack converts archetypes to an Object model and persists
   this model to postgres. Indeed this needs some fancy tricks (@Thomas), but
   it's doable...:-).
   Performance is comparable with the findings in the paper, even with huge
   queries. Performance is steady at about 1 to 3ms per query. No optimization
   done yet, is not yet needed but could/will make it even faster.

Cheers, Jan-Marc

Op di 26 jan. 2016 om 09:55 schreef 吕旭东 :

> Dear all,
>
> I just found there are lots of comments on our recent paper. Thanks for
> all these comments, I will read them and reply later.
>
> @Shinji, Of course the slides could be shared.
>
> Regards
> Xudong
>
> 2016-01-26 10:44 GMT+08:00 Shinji KOBAYASHI :
>
>> Hi Ian and all,
>>
>> We, openEHR Japan had an unconference with Dr Lu and he gave us a
>> presentation for us about this research.
>> I will ask him if the slides would be shareble.
>>
>> Shinji KOBAYASHI
>>
>> 2016-01-25 22:04 GMT+09:00 Ian McNicoll :
>>
>>> Interesting paper from China
>>>
>>>
>>> http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
>>>
>>> Ian
>>>
>>> Dr Ian McNicoll
>>> mobile +44 (0)775 209 7859
>>> office +44 (0)1536 414994
>>> skype: ianmcnicoll
>>> email: i...@freshehr.com
>>> twitter: @ianmcnicoll
>>>
>>> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
>>> Director, freshEHR Clinical Informatics Ltd.
>>> Director, HANDIHealth CIC
>>> Hon. Senior Research Associate, CHIME, UCL
>>>
>>> ___
>>> openEHR-technical mailing list
>>> openEHR-technical@lists.openehr.org
>>>
>>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>>
>>
>>
>> ___
>> openEHR-technical mailing list
>> openEHR-technical@lists.openehr.org
>>
>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

-- 

Jan-Marc Verlinden
MedVision (mobile)

-- 
*MedVision BV*
Aagje Dekenkade 71
2251 ZV, Voorschoten
www.medvision360.com

This e-mail message is intended exclusively for the addressee(s). Please 
inform us immediately if you are not the addressee. 
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Bert Verhees]

On 26-01-16 10:38, Jan-Marc Verlinden wrote:
# Our first version was Java based with a postgres DB, everything stored 
as path/values.
Every query would take about a second. We did not even try complex 
queries..:-). Also the GUI side did not know what to do with the 
pathvalues.

Hi Jan-Marc,

There where some problems handling the path/values, most problems were 
based on giving a semantic meaning to the paths.
Storing path and an according a value is very, very quick. I asked 
database specialists, and they say this is the best way to go until 
billions of records.

Also easy to migrate to another database, for clustering or other reasons.

But there are some problems to solve, which were harder to solve five 
years ago.


One problem is the GUI builders, they are looking at a difficult to 
understand database-approach, and also easy to create errors in, hard to 
debug.

They need JSON to write their datasets in.

The other problem is querying. As long as it are predefined queries, you 
can do anything, but then you are no different from an old monolithic 
system.

But writing new templates heavily relies on on the fly query building

There are however, some technological progresses, also in the open 
source domain.


The path/value storage could come to a better life again with help of 
ANTLR, which can help to interpret AQL for this purpose. I even think 
this is promising.


Let engineers read the Definitive ANTLR4 Reference by Terence Parr, and 
read it with path/values in the back of the mind. Both the GUI problem 
as the query problem can be solved.


It should be worth the spent time and the price of the book ;-)

best regards and good luck
Bert Verhees
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Jan-Marc Verlinden]

Interesting discussion..:-).

Concerns for our current architecture:

   - Should be completely according openEHR IM
   - Accessible for external developers (front-end) through an API.
   - KISS; hide all openEHR complexity for GUI developers. So no pathvalues
   output, just simple GET and PUT.
   - Scalable, should be able to handle large queries and large datasets
   without any (huge) challenges.
   - Providing REST access (Json documents)
   - Access with Javadocs

Some options we compared:

   - Exist DB is not ACID https://en.wikipedia.org/wiki/ACID. So in fact
   not usable at all. Also performance sucks.. (we tested).
   - Oracle rdbms is indeed ACID. You have to convert the archetype (ADL or
   XML) to XSD, that will be used to store the XML in a relational DB (for
   better performance).
   - Like Bert already stated; MarkLogic is somewhat pricey..;-), but
   -could- provide better performance. We never tested.

So we came back to good old postgres and store the archetype Object model
into a relational DB straight forward. None of the compared systems could
even come close in matters of performance and scalability. In the end we
-could- provide XML access alongside with current Json, but no GUI
developer asked for it..:-).

Other papers that show the same:

   - Performance of XML Databases for Epidemiological Queries in
   Archetype-Based EHRs. By Sergio Miranda Freire, Erik Sundvall, Daniel
   Karlsson and Patrick Lambrix
   - AN OPENEHR REPOSITORY BASED ON A NATIVE XML DATABASE. By Linda Velte,
   Tiago Pedrosa, Carlos Costa and José Luís Oliveira.

Thanks for all input, Jan-Marc

Op di 26 jan. 2016 om 10:38 schreef Jan-Marc Verlinden <
jan-m...@medvision360.com>:

> I believe your paper is about performance of an openEHR based system with
> a relational database. It does not argues if this is the right approach.
> And specifically on the performance we could not agree more...:-).
>
> In the past year we worked on three different versions, all completely
> openEHR compliant. We compared them, so I believe we are entitled to
> discuss. Let's see:
>
>1. Our first version was Java based with a postgres DB, everything
>stored as path/values.
>Every query would take about a second. We did not even try complex
>queries..:-). Also the GUI side did not know what to do with the 
> pathvalues.
>2. Second version was completely XML based, lots of Java with an Exist
>DB. This version can be found in GitHub.
>Results: a single query over 900 records took 100ms. But the scary
>part was that performance exploded linear, so 9 would take about 10
>seconds, even for the most simple (already indexed and optimized) query.
>3. Our current stack converts archetypes to an Object model and
>persists this model to postgres. Indeed this needs some fancy tricks
>(@Thomas), but it's doable...:-).
>Performance is comparable with the findings in the paper, even with
>huge queries. Performance is steady at about 1 to 3ms per query. No
>optimization done yet, is not yet needed but could/will make it even 
> faster.
>
> Cheers, Jan-Marc
>
> Op di 26 jan. 2016 om 09:55 schreef 吕旭东 :
>
>> Dear all,
>>
>> I just found there are lots of comments on our recent paper. Thanks for
>> all these comments, I will read them and reply later.
>>
>> @Shinji, Of course the slides could be shared.
>>
>> Regards
>> Xudong
>>
>> 2016-01-26 10:44 GMT+08:00 Shinji KOBAYASHI :
>>
>>> Hi Ian and all,
>>>
>>> We, openEHR Japan had an unconference with Dr Lu and he gave us a
>>> presentation for us about this research.
>>> I will ask him if the slides would be shareble.
>>>
>>> Shinji KOBAYASHI
>>>
>>> 2016-01-25 22:04 GMT+09:00 Ian McNicoll :
>>>
 Interesting paper from China


 http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0

 Ian

 Dr Ian McNicoll
 mobile +44 (0)775 209 7859
 office +44 (0)1536 414994
 skype: ianmcnicoll
 email: i...@freshehr.com
 twitter: @ianmcnicoll

 Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
 Director, freshEHR Clinical Informatics Ltd.
 Director, HANDIHealth CIC
 Hon. Senior Research Associate, CHIME, UCL

 ___
 openEHR-technical mailing list
 openEHR-technical@lists.openehr.org

 http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

>>>
>>>
>>> ___
>>> openEHR-technical mailing list
>>> openEHR-technical@lists.openehr.org
>>>
>>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>>>
>>
>> ___
>> openEHR-technical mailing list
>> openEHR-technical@lists.openehr.org
>>
>> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
> --
>
> Jan-Marc Verlinden
> 

Re: Archetype relational mapping - a practical openEHR persistence solution

[pazospablo]

I talked about this approach with a colleague from China during MEDINFO. 
The problem is your schema grows with your archetypes. Also, that storing data 
from many templates that don't use all the fields in the archetype, will 
generate sparse tables (lots of null columns). I told him it was easier to do 
an ORM from the IM, because the schema doesn't change and allows to store data 
from any archetype/template. But they already have a system working this way.
Sent from my LG Mobile


-- Original message--From: Ian McNicollDate: Mon, Jan 25, 2016 10:06To: 
For openEHR technical discussions;Subject:Archetype relational mapping - a 
practical openEHR persistence solutionInteresting paper from China
http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
Ian
Dr Ian McNicoll
mobile +44 (0)775 209 7859
office +44 (0)1536 414994
skype: ianmcnicoll
email: i...@freshehr.com
twitter: @ianmcnicoll

Co-Chair, openEHR Foundation ian.mcnicoll@openehr.orgDirector, freshEHR 
Clinical Informatics Ltd.
Director, HANDIHealth CIC
Hon. Senior Research Associate, CHIME, UCL___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Thomas Beale]

based on a quick look, my reaction is the same, unless they have some 
very interesting Archetype => Schema transformation.


On 25/01/2016 14:25, pazospa...@hotmail.com wrote:


I talked about this approach with a colleague from China during 
MEDINFO. The problem is your schema grows with your archetypes. Also, 
that storing data from many templates that don't use all the fields in 
the archetype, will generate sparse tables (lots of null columns). I 
told him it was easier to do an ORM from the IM, because the schema 
doesn't change and allows to store data from any archetype/template. 
But they already have a system working this way.






___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Bert Verhees]

Another problem is you have to convert your object oriented model (which RM
is) to a relational model, which becomes complex in converting
templates/aql to SQL. I have been that way. More then five years ago I left
it. It is difficult doable, if you want a full featured openehr kernel. I
would never recommend going this way, unless someone has a really smart
idea.

It can work for a light featured openehr light derived application model.

Best regards
Bert
Op 25 jan. 2016 15:26 schreef "pazospa...@hotmail.com" <
pazospa...@hotmail.com>:

> I talked about this approach with a colleague from China during MEDINFO.
> The problem is your schema grows with your archetypes. Also, that storing
> data from many templates that don't use all the fields in the archetype,
> will generate sparse tables (lots of null columns). I told him it was
> easier to do an ORM from the IM, because the schema doesn't change and
> allows to store data from any archetype/template. But they already have a
> system working this way.
>
>
> Sent from my LG Mobile
>
> -- Original message--
>
> *From: *Ian McNicoll
>
> *Date: *Mon, Jan 25, 2016 10:06
>
> *To: *For openEHR technical discussions;
>
> *Subject:*Archetype relational mapping - a practical openEHR persistence
> solution
> Interesting paper from China
>
>
> http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
>
> Ian
>
> Dr Ian McNicoll
> mobile +44 (0)775 209 7859 <+44%20(0)775%20209%207859>
> office +44 (0)1536 414994 <+44%20(0)1536%20414994>
> skype: ianmcnicoll
> email: i...@freshehr.com
> twitter: @ianmcnicoll
>
> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
> Director, freshEHR Clinical Informatics Ltd.
> Director, HANDIHealth CIC
> Hon. Senior Research Associate, CHIME, UCL
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Dmitry Baranov]

Alas, the hyperlink seems to be broken. 25.01.2016, 16:06, "Ian McNicoll" :Interesting paper from China -- Regards, Dmitry

___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Birger Haarbrandt]

Actually, we use such mappings to promote some "important" elements to relational tables to get sort of indices on the data. Otherwise, I don't think we would be able to do efficient ad-hoc cross-patient queries directly on the database. Exporting data to I2B2 or SSAS would be inconvenient sometimes and SQL still has some advantages. BTW, is there an AQL implementation that is optimized for such "epidemiolocial querying"? I think Erik Sundvall mentioned a hadoop-based research project a while ago. Best,Birger Bert Verhees  hat am 25. Januar 2016 um 18:42 geschrieben:Another problem is you have to convert your object oriented model (which RM is) to a relational model, which becomes complex in converting templates/aql to SQL. I have been that way. More then five years ago I left it. It is difficult doable, if you want a full featured openehr kernel. I would never recommend going this way, unless someone has a really smart idea.It can work for a light featured openehr light derived application model.Best regards  BertOp 25 jan. 2016 15:26 schreef "pazospa...@hotmail.com" :I talked about this approach with a colleague from China during MEDINFO. The problem is your schema grows with your archetypes. Also, that storing data from many templates that don't use all the fields in the archetype, will generate sparse tables (lots of null columns). I told him it was easier to do an ORM from the IM, because the schema doesn't change and allows to store data from any archetype/template. But they already have a system working this way.Sent from my LG Mobile-- Original message--From: Ian McNicollDate: Mon, Jan 25, 2016 10:06To: For openEHR technical discussions;Subject:Archetype relational mapping - a practical openEHR persistence solutionInteresting paper from Chinahttp://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0IanDr Ian McNicollmobile +44 (0)775 209 7859office +44 (0)1536 414994skype: ianmcnicollemail: i...@freshehr.comtwitter: @ianmcnicollCo-Chair, openEHR Foundation ian.mcnic...@openehr.orgDirector, freshEHR Clinical Informatics Ltd.Director, HANDIHealth CICHon. Senior Research Associate, CHIME, UCL___ openEHR-technical mailing list openEHR-technical@lists.openehr.org http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org___openEHR-technical mailing listopenEHR-technical@lists.openehr.orghttp://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org 

___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

RE: Archetype relational mapping - a practical openEHR persistence solution

[Mikael Nyström]

Hi Birger,

It might be this paper you are thinking of.

Freire S, Sundvall E, Karlsson D, Lambrix P. Performance of XML Databases for 
Epidemiological Queries in Archetype-Based EHRs. Scandinavian Conference on 
Health Informatics 2012; October 2-3; Linköping; Sweden. P 51-57. Linköping 
Electronic Conference Proceedings.

http://www.ep.liu.se/ecp/070/009/ecp1270009.pdf
http://www.ep.liu.se/ecp_article/index.en.aspx?issue=070;article=009

 Regards
 Mikael



From: openEHR-technical [mailto:openehr-technical-boun...@lists.openehr.org] On 
Behalf Of Birger Haarbrandt
Sent: den 25 januari 2016 22:57
To: Bert Verhees; For openEHR technical discussions
Subject: Re: Archetype relational mapping - a practical openEHR persistence 
solution


Actually, we use such mappings to promote some "important" elements to 
relational tables to get sort of indices on the data. Otherwise, I don't think 
we would be able to do efficient ad-hoc cross-patient queries directly on the 
database. Exporting data to I2B2 or SSAS would be inconvenient sometimes and 
SQL still has some advantages.

BTW, is there an AQL implementation that is optimized for such "epidemiolocial 
querying"? I think Erik Sundvall mentioned a hadoop-based research project a 
while ago.

Best,

Birger


Bert Verhees <bert.verh...@rosa.nl<mailto:bert.verh...@rosa.nl>> hat am 25. 
Januar 2016 um 18:42 geschrieben:

Another problem is you have to convert your object oriented model (which RM is) 
to a relational model, which becomes complex in converting templates/aql to 
SQL. I have been that way. More then five years ago I left it. It is difficult 
doable, if you want a full featured openehr kernel. I would never recommend 
going this way, unless someone has a really smart idea.

It can work for a light featured openehr light derived application model.

Best regards
Bert
Op 25 jan. 2016 15:26 schreef 
"pazospa...@hotmail.com<mailto:pazospa...@hotmail.com>" 
<pazospa...@hotmail.com<mailto:pazospa...@hotmail.com>>:

I talked about this approach with a colleague from China during MEDINFO. The 
problem is your schema grows with your archetypes. Also, that storing data from 
many templates that don't use all the fields in the archetype, will generate 
sparse tables (lots of null columns). I told him it was easier to do an ORM 
from the IM, because the schema doesn't change and allows to store data from 
any archetype/template. But they already have a system working this way.



Sent from my LG Mobile

-- Original message--

From: Ian McNicoll

Date: Mon, Jan 25, 2016 10:06

To: For openEHR technical discussions;

Subject:Archetype relational mapping - a practical openEHR persistence solution
Interesting paper from China

http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0

Ian

Dr Ian McNicoll
mobile +44 (0)775 209 7859
office +44 (0)1536 414994
skype: ianmcnicoll
email: i...@freshehr.com<mailto:i...@freshehr.com>
twitter: @ianmcnicoll

[https://docs.google.com/uc?id=0BzLo3mNUvbAjT2R5Sm1DdFZYTU0=download]
Co-Chair, openEHR Foundation 
ian.mcnic...@openehr.org<mailto:ian.mcnic...@openehr.org>
Director, freshEHR Clinical Informatics Ltd.
Director, HANDIHealth CIC
Hon. Senior Research Associate, CHIME, UCL

___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org<mailto:openEHR-technical@lists.openehr.org>
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org<mailto:openEHR-technical@lists.openehr.org>
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org


___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

RE: Archetype relational mapping - a practical openEHR persistence solution

[pablo pazos]

ORM is not a problem with current tools. In fact frameworks like Hibernate and 
Grails make Object-Relational Mapping something enjoyable to work with. I think 
the problem with the described approach is the growth of the relational schema 
when your knowledge base grows.
But there are design challenges, ORM doesn't solve all the problems itself. 
IMHO, the object model that should be mapped to relational, if relational is 
chosen as DBMS, is not the raw openEHR IM. Simplifications over the IM are 
needed in order to prevent excessive JOINs and huge hierarchies. In fact I 
teach this in one of my courses and this was part of the tutorial we did on 
MEDINFO. For example, the OBJECT_REFs can be designed as simple relationships, 
because plays the role of a FK in the object model. There are many 
simplifications that can be done to reach an object model that is compatible 
with the openEHR model but more "relational friendly".

-- 
Kind regards,
Eng. Pablo Pazos Gutiérrez
http://cabolabs.com

Date: Mon, 25 Jan 2016 18:42:01 +0100
Subject: Re: Archetype relational mapping - a practical openEHR persistence 
solution
From: bert.verh...@rosa.nl
To: openehr-technical@lists.openehr.org

Another problem is you have to convert your object oriented model (which RM is) 
to a relational model, which becomes complex in converting templates/aql to 
SQL. I have been that way. More then five years ago I left it. It is difficult 
doable, if you want a full featured openehr kernel. I would never recommend 
going this way, unless someone has a really smart idea.
It can work for a light featured openehr light derived application model.
Best regards 

Bert
Op 25 jan. 2016 15:26 schreef "pazospa...@hotmail.com" <pazospa...@hotmail.com>:






I talked about this approach with a colleague from China during MEDINFO. 
The problem is your schema grows with your archetypes. Also, that storing data 
from many templates that don't use all the fields in the archetype, will 
generate sparse tables (lots of null columns). I told him it was easier to do 
an ORM from the IM, because the schema doesn't change and allows to store data 
from any archetype/template. But they already have a system working this way.
Sent from my LG Mobile


-- Original message--From: Ian McNicollDate: Mon, Jan 25, 2016 10:06To: 
For openEHR technical discussions;Subject:Archetype relational mapping - a 
practical openEHR persistence solutionInteresting paper from China
http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
IanDr Ian McNicoll
mobile +44 (0)775 209 7859
office +44 (0)1536 414994
skype: ianmcnicoll
email: i...@freshehr.com
twitter: @ianmcnicoll

Co-Chair, openEHR Foundation ian.mcnicoll@openehr.orgDirector, freshEHR 
Clinical Informatics Ltd.
Director, HANDIHealth CIC
Hon. Senior Research Associate, CHIME, UCL
___

openEHR-technical mailing list

openEHR-technical@lists.openehr.org

http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org


___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org   
  ___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org

Re: Archetype relational mapping - a practical openEHR persistence solution

[Shinji KOBAYASHI]

Hi Ian and all,

We, openEHR Japan had an unconference with Dr Lu and he gave us a
presentation for us about this research.
I will ask him if the slides would be shareble.

Shinji KOBAYASHI

2016-01-25 22:04 GMT+09:00 Ian McNicoll :

> Interesting paper from China
>
>
> http://bmcmedinformdecismak.biomedcentral.com/articles/10.1186/s12911-015-0212-0
>
> Ian
>
> Dr Ian McNicoll
> mobile +44 (0)775 209 7859
> office +44 (0)1536 414994
> skype: ianmcnicoll
> email: i...@freshehr.com
> twitter: @ianmcnicoll
>
> Co-Chair, openEHR Foundation ian.mcnic...@openehr.org
> Director, freshEHR Clinical Informatics Ltd.
> Director, HANDIHealth CIC
> Hon. Senior Research Associate, CHIME, UCL
>
> ___
> openEHR-technical mailing list
> openEHR-technical@lists.openehr.org
>
> http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org
>
___
openEHR-technical mailing list
openEHR-technical@lists.openehr.org
http://lists.openehr.org/mailman/listinfo/openehr-technical_lists.openehr.org