Using OPENOFFICE to directly access My Encore data

[brasshopper]

First off, you should know that if you do this and break everything, no one might be able to help you - you are probably best off doing everything in read only mode, after having made appropriate backups. Secondly, if you don't have experience in dealing with SQL - if "Third Form Normalized" does not mean anything to you, the way that the data is organized will probably not make any sense to you.

If your eyes glazed over at the above, well, this is not for you,

OK, you read all that and you are eager to see how the data is internally stored, OR you want some ability to access the data, sort, list or summarize that you do not now have and you are familiar with database access, well, read on. None of this stuff is at all easy. It is a programmer level interface, not an end user level interface.

This is how you could do it.

Install Openoffice. You can get openoffice.org 2.0.2 at this http://download.openoffice.org/2.0.2/index.html and it has an application called "database" that allows you to do most of what Microsoft Access does - without having to pirate any software. Runs on linux, Mac or Windows, although the database only runs on Windows.

Go to the quickstarter in the tray, right click and select database - or to START->All Programs->Openoffice 2.0->openoffice.org Base.

This should get you the "Database Wizard".

You should be in "Step 1. Select Database". On the right, select "Connect to an existing database." The pulldown below it should enable. Select "ADO", then Next.

Now, it will tell you to "2. Set up ADO connection" on the left, and on the right, there will be a place to put in "Datasource URL" with a browse button next to it. Press "Browse".

There will first be a list of providers. The provider that worked for me was, "Microsoft OLE Provider for ODBC Drivers".

Select that, and then "Next".

At the top of the next page, you should see, "1. Specify the source of data" and under it, a pulldown. If you pull it down, you should see "myencorepro" all lower case like that. The above providers should have found the instance of Microsoft SQL Server running on your system.

Leave item "2" blank for passwords (unless you put in a password, in which case it might not work with myencore). There is a pulldown in item 3 labeled "3. Enter the initial catalog to use." There is a pulldown there - pull it down and you should see "EncorePro" in mixed case like that. Select it.

You should now be able to "Test Connection", a separate button, and it will tell you, "Test connection succeeded" as a popup.

This is all you NEED to do - but I then go to "Advanced" and Select "Read" under access permissions. I believe (but am not sure) that will make it so that I can't change the tables, just look at them. At this point, click OK - you are done browsing.

Click Next again, and put the password in if you need it and test connection again.

One more next. First off, I can't imagine that you want to register this database at openoffice.org, so select "No, do not register the database".

Finally, you want to "open the database for editing" - you don't want to add tables, you want to use the tables that they give you.

Finish allows you to put all of this stuff into a document file so that when you just click on the file, it will automatically launch the openoffice application, tied to the database and all of the tables.

What I decided to do here was to call the file I was about to save "EncorePro Read Only" and to save it on my desktop so that I could just click on it.

I used automatic file name extension.

Sure enough, I closed all the openoffice stuff, got to the desktop and double clicked on it - and bang, there it was. I can see the database internals through the openoffice access.

What COULD you do with this? With the right backups, read write access and update statements for a number of databases, you could, perhaps, combine several downloads into a single one so that Encorepro would put them all into one report. Or you could fix data you know to be bad. Or you could fake data for your doctor so that you got the wrong therapy, but that would be stupid.

You could do your own detail extractions so that you can then make whatever charts you wanted - or prove that you are seeing what you should be in someone else's work.

Anyway, now you can look at the tables - and many of them may not make a lot of sense. This is a "well designed, third form normalized" database, which means that, generally, text data appears only in one place and you use pointers to reference it from other tables. For example, download data is referenced by download number which is looked up (I believe) in the patient table to convert from patient name to number, then in the "Interaction" table to get from patient id to interaction id and then in the Download table to get from interaction id to download id. To tell whose data is whose in the combined table you have to reference all these tables - this is how SQL works - this is actually good design - just confusing to someone who does not know what to expect.

But the tables - and the fields are labeled mnemonically. I had a friend who wrote a program that would read his programs and convert all the labels to 8 characters of random O and C - in the bad old days when the print chains did not give you distinct O and C - he could provide source code that you simply could not easily read - that has NOT been done here - I'm familiar with third form normalized - if I knew more Microsoft SQL I would not have a problem getting around.

If you have only one user on the box there are lots of shortcuts you can take. If you plan on passing your queries or procedures around to other people, avoid shortcuts.

Thus, a statement to get the data from the most recent interaction for a particular person might be something like, well, I know a little SQL but not enough to make things work like they should - to do that requires a subquery and I can't make one work - and I think that I am using query structures that mySQL allows but which Microsoft SQL does not. Anyway, back to the procedure for accessing the structure of the database.


Something like: select SleepTherapyAPAPEvents.Value from Patient, Download, Interaction, InteractionTypes, SleepTherapyAPAPEvents where Patient.LastName like 'S%' and Patient.Firstname like 'Ni%' and Patient.PatientID = Interaction.PatientID and Interaction.InteractionTypeID = InteractionTypes.InteractionTypeID and InteractionTypes.InteractionTypeDescription = 'Sleep Therapy Compliance Download' and Interaction.InteractionID = Download.InteractionID and Download.DownloadID = SleepTherapyAPAPEvents.DownloadID having max(Interaction.InteractionTime) = Interaction.InteractionTime order by sleepTherapyAPAPEventsID ;

The where/max does not work - I've tried putting that in a subselect with an "in" and I can't make that work either. But this is probably not what we want as it just does the same stuff that used to be done by EncorePro. What we want is combined queries that do not pay attention to downloads.

I hope this helps someone. I would have liked to have a post like this since I ended up discovering much of the above just by trying things randomly until something worked.

[Offerocker]

I hope this helps someone. I would have liked to have a post like this since I ended up discovering much of the above just by trying things randomly until something worked.

THANK YOU, BRASSHOPPER, OR should I say PLATINUMHOPPER (promotion )

I will try this when I (finally) receive my card reader!

Kathleen

[oldgearhead]

Great Post! We use Open Office at work. I'll give this a try...

[yardbird]

Now... if I could read the smart card directly into OpenOffice......
And OpenOffice database does work under linux. I use linux. I have the database app in OpenOffice.

Problem is, since I no longer use windows, I'd like to read directly into linux. There is a linux driver for the smart card reader so that's not a problem.

[oldgearhead]

BrassHopper - Isn't OpenOffice compatable with 'My SQL', but not compatable with MS SQL Server?

[Roger...]

Hello BrassHopper,

I'm sorry it has come to this, but it is great to hear about another bit-twiddler on the hose.

I'm in the early stages of trying to connect my Oximeter data to my APAP records so I can better understand how the titration settings I'm using are affecting the oxygen saturation level. Included in that effort is an attempt to understand how much of a leak can a mask have before it begins to affect the staturation levels.

At this stage I've got the data capture and massaging working well for the oximeter and have just started down the path of understanding MS MSSQL-Server. To get a handle on the SQL-SERVER, I found this free application was a great tool for getting a look at the tables and saved views: DbaMgr - DbaMgr2K

In looking at your SQL statement, some of the work that is being done by the SQL statement has been handled by a saved view and that might be a less complicated path to take for capturing the data. If you have not seen the view "vDailyDetailEvents" yet, give this link a click and a column heading listing with some data will appear in a GIF file image.

I like your approach of using the OpenOffice approach. I've not worked with that software in a long time, but maybe its time to get a recent download and see how things work.

[dsm]

BrassHopper,

Good work.

Nice to know someone has the interest & energy to get into this level of detail.

This type of info (with refinement) would be very welcome data to go on the Cpap Users Group when finally set up & operational.

I hope you will be willing to contribute it.

Cheers

DSM

(a long time system integrator who once actually coded in machine language (sys/360-sys/370-Intel 8085) back in the dark ages, but who now does Java, Smalltalk & Ruby).

[brasshopper]

Well, openoffice seems to support standards - ODBC is a standard - they support MySQL directly, not requiring you to use MySQL's OSBC interface, but there is no direct support for Microsoft SQL Server.

Of course, the proof is in the pudding. I can use open office's database manager with the Microsoft SQL Server deal, so it does work.

I'll take a look at the dbamgr software.

My first exposure to sql was back in the early 1980s. The problem is that I have had no regular exposure and every dialect is different.

And, well, my most recent use was MySQL - incredibly fast, but they left out a lot of the hard stuff - no views, no triggers. I tried to figure out how to display the view definitions and I was unable to do that, so I didn't worry.

The real problem with a pure linux shop is that applications like this just don't work. Not that you might not be able to knock out the apps given that you can do the extraction, but that we don't know how the data is stored on the card. Even with drivers, how do we get the data down? My guess is that it is just different than how it is extracted into the base tables,

Finally, I'm also interested in oximeter stuff. I have bought a Nellcor N-200, which has about 12 hours of memory and a serial interface and some documentation about the output format, and I think I want to see how I'm doing in terms of overnight desaturation - and I might want to try time correlating the desats, if there are any, to the events in the record. This is just an amusement thing - but I do plan on writing something which will, in some environment, download data from the Nellcor.

[Sleepless on LI]

Brasshopper,

I did what you said and downloaded OpenOffice, was doing great until I had to use the pull down box after trying to get an ADO connect and find myencorepro in it. There was no mention of anything Encore at all. I hit refresh, even opened up both Encore Pro and MyEncore to see if it would find it then, when they were active, but no luck. What could I be doing wrong? I tried all the providers to see if any others would work, but no on that, too. Any ideas? Thanks.

[Roger...]

Good news - Views are implemented beginning with MySQL Server 5.0.1. This has been a long time coming and it will continue to make this a great database system.

As for the oximeter, I have two tiny and simple Windows utilities (Communication program with file capture) and a data cleanup utility that I cobbled together here over the last month and a half. Right now the process I use to get data from the Nellcor to EXCEL has many steps because I'm putting the software together in small utilities as I discover what I need, and until I understand everything I want. When I've got it working as I need, all the steps will be in one program that will handle the process of moving the data from the Nellcor in a more professional manner.

Doing in it steps has me up to the point where I can capture the data from the Nellcor (N-395), save it to disk, massage it into a CSV file and then load it into EXCEL where charts can be displayed.

There is nothing magic in what I have other than it is working for me when I need to see a oximeter chart. If you would like to play with what I've got, ping me using the PM and I'll send you a copy with some instruction. I did that earlier today for another forum participant, so sending out another copy won't be any trouble.